SQL Notes(Oracle 23 ai)

For B.Tech.(CSE/IT) V Sem ,Sec-B,C

On Date 21.8.2025.

by

kamal kant Tripathi

Asst.Prof.,CSE Dept.

🔹 Oracle Basic Data Types (with Range/Size, Use & Example)

Data Type Range / Size Use Example

Fixed text of
Fixed-length, 1–2000 exact length CHAR(5) → 'ABC'
CHAR(size)
bytes (pads with stored as 'ABC '
spaces).

Variable-length, 1–
Most common VARCHAR2(20) →
4000 bytes (SQL) /
VARCHAR2(size) string type, 'Oracle' stored as 6
up to 32767
saves space. chars
(PL/SQL)

General
Precision up to 38 number, NUMBER(5,2) →
NUMBER(p,s)
digits integers & 123.45
decimals.

Jan 1, 4712 BC –
Stores date & DATE '2025-08-21'
DATE Dec 31, 9999 AD (to
time. → 21-AUG-25
seconds)

Same as TIMESTAMP '2025-

TIMESTAMP WITH Global apps
TIMESTAMP + time 08-21 13:30:15
TIME ZONE with TZ support.
zone +05:30'

Stores in DB
TIMESTAMP WITH Normalized to DB Same as above but
TZ, shows in
LOCAL TIME ZONE time zone auto-adjusts
user TZ.

🔹 Difference between CHAR and VARCHAR2 in Oracle

Feature CHAR VARCHAR2

Storage type Fixed-length Variable-length

Length 1–4000 bytes (SQL) / up to 32767 in

1–2000 bytes
range PL/SQL

Always uses full defined size (pads with

Space usage Uses only the required space (no padding)
spaces if shorter)

Slightly faster for fixed-length values (like More efficient for variable-length values
Performance
codes, flags) (saves space)

Best for storing fixed-size values (e.g., Best for storing variable-length values (e.g.,
Usage
country code, gender, Y/N flags) names, addresses, emails)
✅ Example 1: CHAR

CREATE TABLE char_test (

code CHAR(5)

);

INSERT INTO char_test VALUES ('AB');

🔎 Stored as 'AB ' (padded with 3 spaces).

 Length is always 5 bytes, even though only 2 characters were inserted.

✅ Example 2: VARCHAR2

CREATE TABLE varchar2_test (

name VARCHAR2(20)

);

INSERT INTO varchar2_test VALUES ('Oracle');

🔎 Stored as 'Oracle' (only 6 characters).

 Length is 6 bytes, not the full 20.

✅ Demonstration of Difference

SELECT LENGTH(code) AS char_length FROM char_test;

-- Output: 5 (because CHAR pads with spaces)

SELECT LENGTH(name) AS varchar2_length FROM varchar2_test;

-- Output: 6 (actual characters stored)

🔹 Quick Summary

 Use CHAR → when size is always fixed.

 Use VARCHAR2 → when size is variable (almost always better for general strings).
 SQL ─ CREATE Table SQL

Creating a basic table involves naming the table and defining its columns and each
column's data type.

The SQL CREATE TABLE statement is used to create a new table.

Syntax
The basic syntax of the CREATE TABLE statement is as follows:

CREATE TABLE table_name(

column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);

CREATE TABLE is the keyword telling the database system what you want to do. In this
case, you want to create a new table. The unique name or identifier for the table follows
the CREATE TABLE statement.

Then in brackets comes the list defining each column in the table and what sort of data
type it is. The syntax becomes clearer with the following example.

A copy of an existing table can be created using a combination of the CREATE TABLE
statement and the SELECT statement. You can check the complete details at Create Table
Using another Table.

Example
The following code block is an example, which creates a CUSTOMERS table with an ID as
a primary key and NOT NULL are the constraints showing that these fields cannot be NULL
while creating records in this table:

CREATE TABLE CUSTOMERS(

ID NUMBER(15) PRIMARY KEY,
NAME CHAR(50),
AGE NUMBER(6,3),
ADDRESS varchar2 (100),
SALARY NUMBER (18, 2)

);

1
 SQL

You can verify if your table has been created successfully by looking at the message
displayed by the SQL server, otherwise you can use the

DESC command as follows:

SQL> DESC CUSTOMERS;

+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| ID | number(15) | NO | PRI | | |
| NAME | char(50) | NO | | | |
| AGE | NUMBER(6,3) | NO | | | |
| ADDRESS | varchar2(100) | YES | | | |
| SALARY | number(18,2) | YES | | | |
+ + + + + + +
5 rows in set (0.00 sec)

Now, you have CUSTOMERS table available in your database which you can use to store
the required information related to customers.

2
 SQL ─ DROP or DELETE Table SQL

The SQL DROP TABLE statement is used to remove a table definition and all the data,
indexes, triggers, constraints and permission specifications for that table.

NOTE: You should be very careful while using this command because once a table is
deleted then all the information available in that table will also be lost forever.

Syntax
The basic syntax of this DROP TABLE statement is as follows:

DROP TABLE table_name;

Example
Let us first verify the CUSTOMERS table and then we will delete it from the database as
shown below.

SQL> DESC CUSTOMERS;

+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| ID | int(11) | NO | PRI | | |
| NAME | varchar(20) | NO | | | |
| AGE | int(11) | NO | | | |
| ADDRESS | char(25) | YES | | NULL | |
| SALARY | decimal(18,2) | YES | | NULL | |
+ + + + + + +
5 rows in set (0.00 sec)

This means that the CUSTOMERS table is available in the database, so let us now drop it
as shown below.

SQL> DROP TABLE CUSTOMERS;

Query OK, 0 rows affected (0.01 sec)

Now, if you would try the DESC command, then you will get the following error:

SQL> DESC CUSTOMERS;

ERROR 1146 (42S02): Table 'TEST.CUSTOMERS' doesn't exist

Here, TEST is the database name which we are using for our examples.

3
 SQL ─ INSERT Query SQL

The SQL INSERT INTO Statement is used to add new rows of data to a table in the
database.

Syntax
There are two basic syntaxes of the INSERT INTO statement which are shown below.

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]

VALUES (value1, value2, value3,...valueN);

Here, column1, column2, column3,...columnN are the names of the columns in the table
into which you want to insert the data.

You may not need to specify the column(s) name in the SQL query if you are adding values
for all the columns of the table. But make sure the order of the values is in the same order
as the columns in the table.

The SQL INSERT INTO syntax will be as follows:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Example1
The following statements would create six records in the CUSTOMERS table.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

4
Example2
SQL

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (6, 'Komal', 22, 'MP', 4500.00 );

You can create a record in the CUSTOMERS table by using the second syntax as shown
below.

INSERT INTO CUSTOMERS

VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );

All the above statements would produce the following records in the CUSTOMERS table as
shown below.

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| | Ramesh | 32 | Ahmedabad | 2000.00 |
| | Khilan | 25 | Delhi | 1500.00 |
| | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| | Hardik | 27 | Bhopal | 8500.00 |
| | Komal | 22 | MP | 4500.00 |
| | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

5
 SQL ─ SELECT Query SQL

The SQL SELECT statement is used to fetch the data from a database table which returns
this data in the form of a result table. These result tables are called result-sets.

Syntax
The basic syntax of the SELECT statement is as follows.:

SELECT column1, column2, columnN FROM table_name;

Here, column1, column2... are the fields of a table whose values you want to fetch. If you
want to fetch all the fields available in the field, then you can use the following syntax.

SELECT * FROM table_name;

Example
Consider the CUSTOMERS table having the following records:

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| | Ramesh | 32 | Ahmedabad | 2000.00 |
| | Khilan | 25 | Delhi | 1500.00 |
| | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| | Hardik | 27 | Bhopal | 8500.00 |
| | Komal | 22 | MP | 4500.00 |
| | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

The following code is an example, which would fetch the ID, Name and Salary fields of the
customers available in CUSTOMERS table.

SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS;

6
 SQL

This would produce the following result:

+ + + +
| ID | NAME | SALARY |
+ + + +
| | Ramesh | 2000.00 |
| | Khilan | 1500.00 |
| | kaushik | 2000.00 |
| 4 | Chaitali | 6500.00 |
| | Hardik | 8500.00 |
| | Komal | 4500.00 |
| | Muffy | 10000.00 |
+ + + +

If you want to fetch all the fields of the CUSTOMERS table, then you should use the
following query.

SQL> SELECT * FROM CUSTOMERS;

This would produce the result as shown below.

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| | Ramesh | 32 | Ahmedabad | 2000.00 |
| | Khilan | 25 | Delhi | 1500.00 |
| | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| | Hardik | 27 | Bhopal | 8500.00 |
| | Komal | 22 | MP | 4500.00 |
| | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

7
 SQL ─ WHERE Clause SQL

The SQL WHERE clause is used to specify a condition while fetching the data from a single
table or by joining with multiple tables. If the given condition is satisfied, then only it
returns a specific value from the table. You should use the WHERE clause to filter the
records and fetching only the necessary records.

The WHERE clause is not only used in the SELECT statement, but it is also used in the
UPDATE, DELETE statement, etc., which we would examine in the subsequent chapters.

Syntax
The basic syntax of the SELECT statement with the WHERE clause is as shown below.

SELECT column1, column2, columnN

FROM table_name
WHERE [condition]

You can specify a condition using the comparison or logical operators like >, <, =, LIKE,
NOT, etc. The following examples would make this concept clear.

Example
Consider the CUSTOMERS table having the following records:

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| | Ramesh | 32 | Ahmedabad | 2000.00 |
| | Khilan | 25 | Delhi | 1500.00 |
| | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| | Hardik | 27 | Bhopal | 8500.00 |
| | Komal | 22 | MP | 4500.00 |
| | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

The following code is an example which would fetch the ID, Name and Salary fields from
the CUSTOMERS table, where the salary is greater than 2000:

SQL> SELECT ID, NAME, SALARY

FROM CUSTOMERS
WHERE SALARY > 2000;

8
 SQL

This would produce the following result:

+ + + +
| ID | NAME | SALARY |
+ + + +
| 4 | Chaitali | 6500.00 |
| | Hardik | 8500.00 |
| | Komal | 4500.00 |
| | Muffy | 10000.00 |
+ + + +

The following query is an example, which would fetch the ID, Name and Salary fields from
the CUSTOMERS table for a customer with the name Hardik.

Here, it is important to note that all the strings should be given inside single quotes ('').
Whereas, numeric values should be given without any quote as in the above example.

SQL> SELECT ID, NAME, SALARY

FROM CUSTOMERS
WHERE NAME = 'Hardik';

This would produce the following result:

+ + + +
| ID | NAME | SALARY |
+ + + +
| 5 | Hardik | 8500.00 |
+ + + +

9
 SQL ─ AND & OR Conjunctive Operators SQL

The SQL AND & OR operators are used to combine multiple conditions to narrow data in
an SQL statement. These two operators are called as the conjunctive operators.

These operators provide a means to make multiple comparisons with different operators
in the same SQL statement.

The AND Operator

The AND operator allows the existence of multiple conditions in an SQL statement's
WHERE clause.

Syntax
The basic syntax of the AND operator with a WHERE clause is as follows:

SELECT column1, column2, columnN

FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

You can combine N number of conditions using the AND operator. For an action to be taken
by the SQL statement, whether it be a transaction or a query, all conditions separated by
the AND must be TRUE.

Example
Consider the CUSTOMERS table having the following records:

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| | Ramesh | 32 | Ahmedabad | 2000.00 |
| | Khilan | 25 | Delhi | 1500.00 |
| | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| | Hardik | 27 | Bhopal | 8500.00 |
| | Komal | 22 | MP | 4500.00 |
| | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

10
 SQL

Following is an example, which would fetch the ID, Name and Salary fields from the
CUSTOMERS table, where the salary is greater than 2000 and the age is less than 25
years.

SQL> SELECT ID, NAME, SALARY

FROM CUSTOMERS
WHERE SALARY > 2000 AND age < 25;

This would produce the following result:

+ + + +
| ID | NAME | SALARY |
+ + + +
| 6 | Komal | 4500.00 |
| 7 | Muffy | 10000.00 |
+ + + +

The OR Operator
The OR operator is used to combine multiple conditions in an SQL statement's WHERE
clause.

Syntax
The basic syntax of the OR operator with a WHERE clause is as follows:

SELECT column1, column2, columnN

FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

You can combine N number of conditions using the OR operator. For an action to be taken
by the SQL statement, whether it be a transaction or query, the only any ONE of the
conditions separated by the OR must be TRUE.

Example
Consider the CUSTOMERS table having the following records:

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| | Ramesh | 32 | Ahmedabad | 2000.00 |
| | Khilan | 25 | Delhi | 1500.00 |
| | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |

11
 SQL

| 5 | Hardik | 27 | Bhopal | 8500.00 |

| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

The following code block has a query, which would fetch the ID, Name and Salary fields
from the CUSTOMERS table, where the salary is greater than 2000 OR the age is less than
25 years.

SQL> SELECT ID, NAME, SALARY

FROM CUSTOMERS
WHERE SALARY > 2000 OR age < 25;

This would produce the following result:

+ + + +
| ID | NAME | SALARY |
+ + + +
| 3 | kaushik | 2000.00 |
| 4 | Chaitali | 6500.00 |
| | Hardik | 8500.00 |
| | Komal | 4500.00 |
| | Muffy | 10000.00 |
+ + + +

12
 SQL ─ UPDATE Query SQL

The SQL UPDATE Query is used to modify the existing records in a table. You can use the
WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows
would be affected.

Syntax
The basic syntax of the UPDATE query with a WHERE clause is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

You can combine N number of conditions using the AND or the OR operators.

Example
Consider the CUSTOMERS table having the following records:

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| | Ramesh | 32 | Ahmedabad | 2000.00 |
| | Khilan | 25 | Delhi | 1500.00 |
| | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| | Hardik | 27 | Bhopal | 8500.00 |
| | Komal | 22 | MP | 4500.00 |
| | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

The following query will update the ADDRESS for a customer whose ID number is 6 in the
table.

SQL> UPDATE CUSTOMERS

SET ADDRESS = 'Pune'
WHERE ID = 6;

13
 SQL

Now, the CUSTOMERS table would have the following records:

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| | Ramesh | 32 | Ahmedabad | 2000.00 |
| | Khilan | 25 | Delhi | 1500.00 |
| | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| | Hardik | 27 | Bhopal | 8500.00 |
| | Komal | 22 | Pune | 4500.00 |
| | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

If you want to modify all the ADDRESS and the SALARY column values in the CUSTOMERS
table, you do not need to use the WHERE clause as the UPDATE query would be enough
as shown in the following code block.

SQL> UPDATE CUSTOMERS

SET ADDRESS = 'Pune', SALARY = 1000.00;

Now, CUSTOMERS table would have the following records:

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| | Ramesh | 32 | Pune | 1000.00 |
| | Khilan | 25 | Pune | 1000.00 |
| | kaushik | 23 | Pune | 1000.00 |
| 4 | Chaitali | 25 | Pune | 1000.00 |
| | Hardik | 27 | Pune | 1000.00 |
| | Komal | 22 | Pune | 1000.00 |
| | Muffy | 24 | Pune | 1000.00 |
+ + + + + +

14
 SQL ─ DELETE Query SQL

The SQL DELETE Query is used to delete the existing records from a table.

You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise
all the records would be deleted.

Syntax
The basic syntax of the DELETE query with the WHERE clause is as follows:

DELETE FROM table_name

WHERE [condition];

You can combine N number of conditions using AND or OR operators.

Example
Consider the CUSTOMERS table having the following records:

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| | Ramesh | 32 | Ahmedabad | 2000.00 |
| | Khilan | 25 | Delhi | 1500.00 |
| | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| | Hardik | 27 | Bhopal | 8500.00 |
| | Komal | 22 | MP | 4500.00 |
| | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

The following code has a query, which will DELETE a customer, whose ID is 6.

SQL> DELETE FROM CUSTOMERS

WHERE ID = 6;

Now, the CUSTOMERS table would have the following records.

15
 SQL

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| | Ramesh | 32 | Ahmedabad | 2000.00 |
| | Khilan | 25 | Delhi | 1500.00 |
| | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

If you want to DELETE all the records from the CUSTOMERS

table, you do not need to use the WHERE clause and the
DELETE query would be as follows:

SQL> DELETE FROM CUSTOMERS;

Now, the CUSTOMERS table would not have any record.

16