Types of Statements-DBMS

Types of Statements
1.DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
· CREATE – to create objects in the database
· ALTER – alters the structure of the database
· DROP – delete objects from the database
· TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
· COMMENT – add comments to the data dictionary
· RENAME – rename an object
2.DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
· SELECT – retrieve data from the a database
· INSERT – insert data into a table
· UPDATE – updates existing data within a table
· DELETE – deletes all records from a table, the space for the records remain
· MERGE – UPSERT operation (insert or update)
· CALL – call a PL/SQL or Java subprogram
· EXPLAIN PLAN – explain access path to data
· LOCK TABLE – control concurrency
3.DCL
Data Control Language (DCL) statements. Some examples:
· GRANT – gives user’s access privileges to database
· REVOKE – withdraw access privileges given with the GRANT command
4.TCL
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
· COMMIT – save work done
· SAVEPOINT – identify a point in a transaction to which you can later roll back
· ROLLBACK – restore database to original since the last COMMIT
· SET TRANSACTION – Change transaction options like isolation level and what rollback segment to use
Example:
The table given below (named Student) has two fields id and Name.
Student Table

id	Name
1	Ravi
2	Ajay

The commands are based on this table:
1.DDL COMMANDS
CREATE TABLEThis statement is used to create a table. The syntax for this command is

create table tablename (colname1 datatype [constraint], colname2 datatype [constraint]);

Example:

create table Student (id number(4) primary key, Name varchar2(20));

It creates the table Student which has two fields id i.e. Student id and Name i.e. the student name. The number and varchar2 are the data types of id and Name respectively. Field ‘id’ has the size 4 means it can take id up to 4 digits and same for Name, it can take the size up to 20 characters. And also added the constraint Primary key to the field ‘id’.
ALTER TABLEThis command is used to add, drop columns in a table. The syntax for this command is

alter table tablename add colname1 datatype [constraint]; alter table tablename drop column colname1;

Example:

alter table Student add DOB date;

This command is used to add new field DOB in Student table. It’s datatype is date. This is also used for drop column from the table. It will drop the DOB field by query given below-

Alter table Student drop column DOB;

DROP TABLEThe syntax for this command is-

drop table tablename;

Example:

drop table Student;

This statement is used for destroy the table from database.
2.DML COMMANDS
INSERT ROWSThe syntax for this command is

insert into tablename(colname1,colname2) values(value1,value2);

Example:

insert into Student (id, Name) values(1,’Ravi’);

This statement is used to insert a row of data into Student table.
UPDATE ROWSThe syntax for this command is

update tablename set colname1=colvalue where colname2=colvalue;

Example:

update Student set Name = ‘Ajay’ where id = 2;

This command has updated the Name ‘Rose’ in Student table whose id is 2.
SELECT ROWSThis command is used to select rows from a table.The syntax for this command is

select colname1,colname2 from tablename;

Example:

select Name from Student;

It will display all names from Student table. Like Ravi.
DELETE ROWSThe syntax for this command is-

delete from tablename where [search_conditions];

Example:

delete from Student where id=1;

This statement is used to delete the row from Student table where the student id is 1.
3.DCL COMMANDS
GRANTThis command is used for gives access privileges to users for database. The syntax is-

GRANT dba to username;

REVOKEThis command is used for withdraws access privileges to users for database. The syntax is-

REVOKE permissions on tablename from username;

4.TCL COMMANDS
COMMITThis command is used for save the work done. The syntax is:

COMMIT;

ROLLBACKThis command is used to restore the database to original since the last commit. The syntax is-

ROLLBACK;