Page 4 - DDL and DML Commands

Creating Table From Existing Table

CREATE TABLE orditem AS
( SELECT icode, descp, rol
from items
where qoh<rol
);
Above command will create orditem table with three columns and the table will be initialised with
the content of items table as per the where condition.

Inserting data into a table

Syntax is:
INSERT INTO <table name>
(column list) //optional if values for all columns to be entered
VALUES(data values) //if char or date type with in single quote
Example:
1. Insert into SUBJECT
values('01', 'ENGLISH'), ('02', NULL); #inserting null
2. Insert into SUBJECT (subcode) values('03'); # no value for subName is NULL
3. Insert into STUDENT
(admNo, sname, fname, mname, DOB, Xmarks)
values(3456, 'Anil Kumar', 'ABC', 'XYZ', '20-Sep-98', 92);

Inserting Data from Another Table

Example:
Insert into Achiever
Select * from Student
where Avg>90;
Above command will extract data as per the where condition from Student table and will insert it
into already existing table Achiever.

Deleting Record

Syntax is:
DELETE FROM <tablename>
WHERE <condition>
Example:
delete from Student
where stream = 2; //delete all records corresponding to stream 2
delete from Student; //command will delete all records from the table

Updating/Modifying Record

Syntax is:
UPDATE <tablename>
SET <column1> = <expression1>, ………
WHERE<condition>;