1. CREATE TABLE

Syntax:

CREATE TABLE table_name (
    column1 datatype(size),
    column2 datatype(size),
    column3 datatype(size)
);

Example:

CREATE TABLE STUDENT (
    SID NUMBER(5),
    SNAME VARCHAR2(30),
    CITY VARCHAR2(20),
    MARKS NUMBER(3)
);

2. INSERT INTO

Syntax:

INSERT INTO table_name
VALUES (value1, value2, value3, value4);

Example:

INSERT INTO STUDENT
VALUES (101, 'Rahul', 'Rajkot', 85);

INSERT INTO STUDENT
VALUES (102, 'Priya', 'Ahmedabad', 90);

INSERT INTO STUDENT
VALUES (103, 'Amit', 'Surat', 78);

Display Data

SELECT * FROM STUDENT;

Output

3. UPDATE

Syntax:

UPDATE table_name
SET column_name = value
WHERE condition;

Example

UPDATE STUDENT
SET MARKS = 95
WHERE SID = 102;

Output

4. DELETE

Syntax:

DELETE FROM table_name
WHERE condition;

Example

DELETE FROM STUDENT
WHERE SID = 103;

Output

5. ALTER TABLE – ADD COLUMN

Syntax

ALTER TABLE table_name
ADD column_name datatype(size);

Example

ALTER TABLE STUDENT
ADD EMAIL VARCHAR2(40);

Insert Email Values

UPDATE STUDENT
SET EMAIL = 'rahul@gmail.com'
WHERE SID = 101;

UPDATE STUDENT
SET EMAIL = 'priya@gmail.com'
WHERE SID = 102;

Output

6. ALTER TABLE – MODIFY COLUMN

Syntax

ALTER TABLE table_name
MODIFY column_name datatype(size);

Example

ALTER TABLE STUDENT
MODIFY SNAME VARCHAR2(50);

This changes the size of the SNAME column from 30 to 50 characters.

7. ALTER TABLE – DROP COLUMN

Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

Example

ALTER TABLE STUDENT
DROP COLUMN EMAIL;

8. DROP TABLE

Syntax

DROP TABLE table_name;

Example

DROP TABLE STUDENT;

This command permanently deletes the STUDENT table and all its data.

Complete Oracle SQL Program

-- Create Table
CREATE TABLE STUDENT(
    SID NUMBER(5),
    SNAME VARCHAR2(30),
    CITY VARCHAR2(20),
    MARKS NUMBER(3)
);

-- Insert Records
INSERT INTO STUDENT VALUES(101,'Rahul','Rajkot',85);
INSERT INTO STUDENT VALUES(102,'Priya','Ahmedabad',90);
INSERT INTO STUDENT VALUES(103,'Amit','Surat',78);

-- Display Records
SELECT * FROM STUDENT;

-- Update Record
UPDATE STUDENT
SET MARKS=95
WHERE SID=102;

-- Delete Record
DELETE FROM STUDENT
WHERE SID=103;

-- Add Column
ALTER TABLE STUDENT
ADD EMAIL VARCHAR2(40);

-- Modify Column
ALTER TABLE STUDENT
MODIFY SNAME VARCHAR2(50);

-- Drop Column
ALTER TABLE STUDENT
DROP COLUMN EMAIL;

-- Display Final Records
SELECT * FROM STUDENT;

-- Drop Table
DROP TABLE STUDENT;

This example covers all the basic Oracle SQL DDL and DML operations commonly asked in BCA, B.Sc. IT, and diploma practical examinations.