SQL STATEMENTS

SQL STATEMENTS are used to communicate with the database .These statements can be used  for searching the database,create tables, add data to tables, or modify data, drop the table, set permissions for users and many more...

SQL statements are grouped into four main categories depending on their functionality:

1. DML (Data Manipulation Language)

2. DDL (Data Definition Language)

3. DCL (Data Control Language)

4. TCL (Transaction Control Language)

Data Definition Language (DDL)

DDL statements used to define the database structure or schema of a table.

When we execute any DDL statement, it shows its effect immediately.

Some examples of DDL statements are: 

1. CREATE - to create objects in the database 

2. ALTER - alters the structure of the database 

3. DROP - delete objects from the database 

4. TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed 

5. COMMENT - add comments to the data dictionary 

6. RENAME - Rename an object/Table 

Data Manipulation Language (DML) 

DML statements are used for managing data within schema objects. When you are work in multi-user databases, we are in effect of working with a private copy of our tables that can’t be seen by anyone else until we are finished. DML statements are used to modify the data stored in the tables. These statements are used for managing data within schema objects. DML commands can't be rollback when a DDL command is executed immediately after a DML. DDL after DML means "auto commit". The changes will return on disk not on the buffer. If the changes return on the buffer it is possible to rollback not from the disk.

Some examples of DML statements are:

1.SELECT – retrieve data from the a database

2.INSERT – insert data into a table

3.UPDATE – updates existing data within a table

4.DELETE – deletes all records from a table, the space for the records remain

5.MERGE – UPSERT operation (insert or update)

6.CALL – call a PL/SQL or Java subprogram

7.EXPLAIN PLAN – explain access path to data

8.LOCK TABLE – control concurrency

Data Control Language (DCL) 

It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it. 

Some examples of DCL statements are: 

1. GRANT – It gives user's access privileges to database. 

2. REVOKE – It withdraw access privileges given with the GRANT command

Transactional Control Language (TCL) 

It is used to manage different transactions occurring within a database. Transaction control commands manage changes made by DML commands. These SQL commands are used for managing changes affecting the data. 

Some examples of TCL statements are: 

1. COMMIT – It save work done. It is used for committing the transactions to the database. 

2. SAVEPOINT - identify a point in a transaction to which you can later roll back 

3. ROLLBACK - restore database to original since the last COMMIT. It used for revoking the transactions until last commit. 

4. SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use.