- Easy
to learn and use.
- Standard
database language. It is non procedural language.
- Supports
Create, Read, Update, Delete (CRUD).
- Works
with multiple databases (Oracle, MySQL,
SQL Server, PostgreSQL).
- Supports
security using GRANT and REVOKE.
- Supports
constraints (Primary Key, Foreign Key,
etc.).
- Supports
joins and relationships.
- Handles
large amounts of data efficiently.
- Supports
transactions (COMMIT, ROLLBACK).
- Can
create reports using queries.
Rules of SQL
|
Rule |
Description |
Example |
|
SQL keywords are not case-sensitive |
SELECT and select are the same |
SELECT * FROM Student; |
|
Every SQL statement ends with a semicolon (;) |
Terminates the command |
SELECT * FROM Student; |
|
Table names should be meaningful |
Use descriptive names |
Student, Employee |
|
Avoid spaces in
table names |
Use underscore (_) instead |
Student_Details |
|
SQL keywords are
usually written in uppercase |
Improves readability |
SELECT, INSERT |
|
Strings are enclosed in
single quotes |
Text values |
'Rahul' |
|
Numbers do not require
quotes |
Numeric values |
101 |
|
Comments
can be written |
Single-line: --
comment; Multi-line: /* comment */ |
|
|
Reserved word cannot
be used |
|
INTO, ALTER, INSERT, VALUES |
|
Space used between
words |
A space separate clause |
Drop table student; |
SQL*Plus vs PL/SQL
(there is no Oracle product called
"PL*Plus").
|
SQL*Plus |
PL/SQL |
|
Command-line interface provided
by Oracle |
Programming language extension of
SQL |
|
Used to execute SQL and
PL/SQL statements |
Used to write programs,
procedures, functions, and triggers |
|
Mainly interactive |
Supports programming
logic |
|
Does not support loops
or conditions by itself |
Supports IF, LOOP,
WHILE, FOR, EXCEPTION handling |
|
Used by DBAs and
students for running commands |
Used by developers to
build database applications |
Oracle Data Types
What is a Data Type?
A data type defines what
kind of data can be stored in a table column.
It helps the database store, validate, and process
data correctly.
Example:
- Student
Name → Text
- Age
→ Number
- Date
of Birth → Date
- Salary
→ Decimal Number
---------------------------------------------------------------------------------------------------------------Common SQL Data Types (Oracle SQL)
|
Data Type |
Purpose |
Example |
|
NUMBER |
Stores whole numbers or decimal numbers |
100, 2500.50 |
|
VARCHAR2(n) |
Stores variable-length text |
Rahul, Ahmedabad |
|
CHAR(n) |
Stores fixed-length text |
M, F, IND |
|
DATE |
Stores dates |
15-JUL-2026 |
|
TIMESTAMP |
Stores date and time |
15-JUL-2026 10:30:45 |
|
CLOB |
Stores large text |
Paragraphs, Articles |
|
BLOB |
Stores images, videos, PDF files |
Student Photo |
|
FLOAT |
Stores decimal values |
89.75 |
---------------------------------------------------------------------------------------------------------------
Character Data Types
|
Data Type |
Size |
Description |
Example |
|
CHAR(n) |
255 characters |
Fixed-length character data |
CHAR(10) |
|
VARCHAR2(n) |
1–4000 bytes |
Variable-length character data |
VARCHAR2(50) |
|
NCHAR(n) |
Up to 2000 characters |
Fixed-length Unicode |
NCHAR(20) |
|
NVARCHAR2(n) |
Up to 4000 characters |
Variable-length Unicode |
NVARCHAR2(100) |
|
LONG |
Up to 2 GB |
Large character data (legacy) |
LONG |
Example
Name VARCHAR2(50)
Gender CHAR(1)
Number Data Types
|
Data Type |
Size |
Description |
Example |
|
NUMBER(p,s) |
Up to 38 digits |
Integer or decimal numbers |
NUMBER(8,2) |
|
FLOAT |
Up to 126 binary digits |
Floating-point number |
FLOAT |
Example
Salary NUMBER(8,2)
Values: 45000.50
---------------------------------------------------------------------------------------------------------------
Date and Time Data Types
|
Data Type |
Description |
Example |
|
DATE |
Stores date and time |
10-JUL-2026 |
|
TIMESTAMP |
Stores date, time, and fractional seconds |
10-JUL-2026 10:30:15.123 |
|
TIMESTAMP WITH TIME ZONE |
Includes time zone |
10-JUL-2026 10:30:15 +05:30 |
Example
JoiningDate DATE
Binary Data Types
|
Data Type |
Description |
|
RAW |
Binary data |
|
LONG RAW |
Large binary data (legacy) |
Large Object (LOB) Data Types
|
Data Type |
Description |
Example |
|
BLOB |
Binary Large Object |
Images, videos, PDFs |
|
CLOB |
Character Large Object |
Large text documents |
|
NCLOB |
Unicode text |
Multilingual documents |
|
BFILE |
External file stored outside the database |
PDF, Image |
---------------------------------------------------------------------------------------------------------------Difference Between Oracle Data Types
CHAR vs VARCHAR2
|
CHAR |
VARCHAR2 |
|
Fixed length |
Variable length |
|
Wastes space if data is shorter than the defined
size |
Saves space by storing only actual characters |
|
Best for fixed-length values (e.g., Gender, State
Code) |
Best for names, addresses, emails |
Example
Gender CHAR(1)
Name VARCHAR2(50)
If Name = Rahul
CHAR(50)
Rahul_____________________________________
VARCHAR2(50)
Rahul
NUMBER vs FLOAT
|
NUMBER |
FLOAT |
|
Exact values |
Approximate values |
|
Used for salaries, marks, account balances |
Used for scientific calculations |
Example:
Salary NUMBER(8,2)
Temperature FLOAT
DATE vs TIMESTAMP
|
DATE |
TIMESTAMP |
|
Stores date and time up to seconds |
Stores date, time, and fractional seconds |
Example:
DATE
10-JUL-2026
TIMESTAMP
10-JUL-2026 10:30:15.123456
BLOB vs CLOB
|
BLOB |
CLOB |
|
Stores binary data |
Stores text data |
|
Images, videos, audio, PDFs |
Books, articles, reports |
---------------------------------------------------------------------------------------------------------------
CHAR, VARCHAR and VARCHAR2 in SQL
When creating a table, we need to
define the data type of each column. For storing text (characters),
SQL provides CHAR, VARCHAR, and VARCHAR2.
1. CHAR
CHAR stores fixed-length character data.
If the entered value is shorter than
the defined size, SQL automatically adds blank spaces to fill the remaining
length.
Simple
Definition
CHAR always stores a fixed number of
characters. Maximum 255 characters to stored.
Syntax
Column_Name CHAR(size);
Practical
Example
CREATE TABLE Student
(
StudentID NUMBER,
Gender CHAR(1)
);
Best Use
Gender
(M/F)
Grade
(A/B/C)
Status
(Y/N)
Country
Code (IN, US)
2. VARCHAR
VARCHAR
stores variable-length character data.
It
uses only the required storage for the entered value.
Simple
Definition
VARCHAR
stores only the number of characters entered by the user. Up to 2000 bytes to
entered.
Syntax
Column_Name
VARCHAR(30);
Practical
Example
CREATE
TABLE Student
(
StudentName VARCHAR(30)
);
Best
Use
- Name
- Address
- Email
- City
3. VARCHAR2 (Oracle)
VARCHAR2
is the preferred variable-length character data type in Oracle Database.
It
stores only the entered characters without adding extra spaces. Up to 4000
bytes to entered
Simple
Definition
VARCHAR2
stores variable-length text efficiently and is recommended in Oracle.
Syntax
Column_Name VARCHAR2(30);
Practical
Example
CREATE
TABLE Student
(
StudentName VARCHAR2(30)
);
Best
Use
- Student Name
- Email
- Mobile Number
- Address
- Department Name
Practical
Comparison
Suppose
the following values are stored:
|
Data
Type |
Defined
Size |
Stored
Value |
Storage
Used |
|
CHAR(10) |
10 |
Rahul |
10
characters (5 letters + 5 spaces) |
|
VARCHAR(10) |
10 |
Rahul |
5
characters |
|
VARCHAR2(10) |
10 |
Rahul |
5
characters |
---------------------------------------------------------------------------------------------------------------
Difference
Between CHAR, VARCHAR and VARCHAR2
|
Feature |
CHAR |
VARCHAR |
VARCHAR2 |
|
Full
Form |
Character |
Variable
Character |
Variable
Character 2 |
|
Length |
Fixed |
Variable |
Variable |
|
Size |
255 |
Up
to 2000 bytes |
Up
to 4000 bytes |
|
Extra
Spaces |
Yes |
No |
No |
|
Storage |
Always
fixed size |
Only
actual data |
Only
actual data |
|
Performance |
Good
for fixed-length values |
Depends
on DBMS |
Recommended
in Oracle |
|
Oracle
Recommendation |
Used
for fixed values |
Supported
in some DBMSs (e.g., MySQL, PostgreSQL) |
Recommended
text type in Oracle |
|
Best
For |
Gender,
Grade, Status |
Names,
Email, Address |
Names,
Email, Address in Oracle |
---------------------------------------------------------------------------------------------------------------
Memory
Trick
- CHAR = Constant
Length (Fixed size)
- VARCHAR = Variable
Length (Varies by DBMS)
- VARCHAR2 = Variable
Length in Oracle (Preferred choice)
Summary
CHAR Fixed-length text
VARCHAR2 Variable-length
text
NUMBER Exact numeric values
FLOAT Approximate floating-point values
DATE Stores date
TIMESTAMP Stores
date, time, and fractional seconds
BLOB Binary files (images, videos, PDFs)
CLOB Large text documents
Or follow my blog from the below link
){kind=link}
0 Comments