SQL database (Oracle)

SQL database (Oracle)

In the field of Information Technology (IT), databases play a very important role in storing, managing, and retrieving data efficiently. One of the most widely used database systems in the world is the Oracle Database. Oracle is a powerful Relational Database Management System (RDBMS) that uses SQL (Structured Query Language) to manage data.

Oracle Database is developed by Oracle Corporation and is commonly used in large organizations, banks, hospitals, government offices, and IT companies because of its high performance, security, and reliability. It stores data in the form of tables, which consist of rows and columns. Each table represents a real-world entity such as employees, students, or customers.

SQL is the standard language used to interact with the Oracle database. Using SQL, users can create tables, insert data, update records, delete data, and retrieve information easily. SQL commands in Oracle are mainly divided into:

DDL (Data Definition Language) – CREATE, ALTER, DROP

DML (Data Manipulation Language) – INSERT, UPDATE, DELETE

DQL (Data Query Language) – SELECT

DCL (Data Control Language) – GRANT, REVOKE

TCL (Transaction Control Language) – COMMIT, ROLLBACK

Oracle supports various data types such as NUMBER, VARCHAR2, DATE, CLOB, and BLOB, which help in storing different kinds of data. It also provides advanced features like indexes, views, stored procedures, triggers, and functions to improve performance and automate tasks.

Security is one of the strongest features of Oracle Database. It allows user authentication, role-based access, and data encryption, ensuring that sensitive information is well protected. Oracle also supports backup and recovery options, making it highly reliable.

In conclusion, the Oracle SQL Database is an essential tool in IT for efficient data management. Its powerful features, strong security, and ability to handle large amounts of data make it a preferred choice for modern database applications.

 

Oracle datatypes:(in detail)

Oracle data types define what kind of data a column can store in a table.

1. Character Data types

Used to store text, letters, and symbols.

a) CHAR(size)

Fixed-length character data

Maximum size: 2000 bytes

If data is shorter, Oracle adds spaces

Faster than VARCHAR2

Example:name CHAR(10);

b) VARCHAR(size)

Variable-length character data

Maximum size: 4000 bytes

Most commonly used

Saves space

Example:name VARCHAR2(50);

c) NCHAR(size)

Fixed-length Unicode character data

Used for multilingual data

d)NVARCHAR2(size)

Variable-length Unicode character data

Supports international languages

 

2. Numeric Data types

Used to store numbers.

a) NUMBER (p,s)

Stores integers and decimal numbers

p = precision (total digits)

s = scale (digits after decimal)

Example:salary NUMBER(8,2);

➡ Can store up to 999999.99

b) Float

Subtype of NUMBER

Used for scientific calculations

c) INTEGER/INT

Whole numbers only

Subtype of NUMBER

 

3. Date and Time Data types

Used to store date and time values.

a) DATE

Stores date + time

Format: DD-MON-YY

Time stored up to seconds

Example:dob DATE;

b) TIMESTAMP

Stores date and time with fractional seconds

More precise than DATE

c) Timestamp with time zone

Stores time zone information

d) Timestamp with local time zone

Converts time to user’s local time zone

 

4. Binary Data types

Used to store raw binary data.

a)Raw(size)

Stores binary data (images, files)

Maximum size: 2000 bytes

b) Long Raw

Stores large binary data

Deprecated (use BLOB instead)

 

5. Large object (LOB) Data types

Used to store large data.

a) CLOB

Character Large Object

Stores large text data (up to TBs)

b) NCLOB

Unicode character large object

c) BLOB

Binary Large Object

Stores images, audio, video, PDFs

d) BFILE

Stores file outside the database

Read-only

 

6. Row Identifier Data Types

a) ROWID

Stores physical address of a row

Used for fast row access

b) UROWID

Universal row identifier

Used for index-organized tables

 

7. Boolean Data Type

BOOLEAN exists only in PL/SQL

Values: TRUE, FALSE, NULL

Not allowed in table columns

 

8. Other Important Data Types

a) LONG

Stores long character data

Deprecated (use CLOB)

b) XMLTYPE

Stores XML data

Name	DATATYPE
Character	CHAR, VARCHAR2, NCHAR & NVARCHAR2
Numeric	NUMBER, FLOAT & INTEGER
Date/Time	DATE, TIMESTAMP
Binary	RAW, LONG RAW
LOB	CLOB, NCLOB, BLOB, gg.  . BFILE
Row ID	ROWID, UROWID
PL/SQL	Boolean

 

 

Author    : Lavanya Nandairi

LinkedIn : https://www.linkedin.com/in/lavanya-nandagiri-817194375/

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates.

KTExperts is always active on social media platforms.

Facebook  : https://www.facebook.com/ktexperts

LinkedIn    : https://www.linkedin.com/company/ktexperts/

Twitter       : https://twitter.com/ktexpertsadmin

YouTube   :  https://www.youtube.com/c/ktexperts

Instagram  : https://www.instagram.com/knowledgesharingplatform