Structural Query Language (SQL)

SQL (STRUCTURED QUERY LANGUAGE)

It is designed to query database whether it is create, insert, delete or update a data. It is a next generational programming language. Its 4GL where the user/developer is not required to do programming but to invoke the program available in the dbms and pass them parameters, if any, to accomplish their tasks.

SQL divide into 5 categories:

DDL (data definition language)

Is responsible for defining, creating, and retaining the data structure needed to hold and manage data.

For example: CREATE DATABASE db_name;

                      USE db_name;   //connecting to that db

                      SHOW db_name; //to see th list of db

                      SHOW tables; // to see the list of tables (empty set if no table)

                      CREATE TABLE tb_name (column_name type null/not null);

    DML (data manipulation language)

Is responsible for manipulation of data i.e. insert, update, delete, select etc.

For example: INSERT INTO tb_name VALUES ( ___);

                       DESCRIBE table; // shows information about attributes

                       DELETE FROM tb_name;

                       SELECT * FROM tb_name;

DCL (data control language)

Is responsible for controlling the access to storage structures and the data authorized access to maintain consistency, integrity and security of a db i.e. grant, revoke etc are some privileges to access the control to storage structures.

For example: GRANT PRIVILEGE ON tb_name/db_name TO user_name IDENTIFIED BY password;

TCL (transaction control language)

 It is a set of operations and is said to be complete only if all the operations forming part of transaction completes successfully.

For example:  when transaction is complete -> “commit”

                             incomplete-> ”rollback”

                            unsuccessfully-> “savepoint” (it restart from this point)

                             completed.

It’s a part of SQL which deals with program related to initiating, rolling, committing and saving (save point) transactions.

ESQL (Embedded SQL)

It deals with all the programs related to operations which are initiated through other programs (programming languages) or events (save, no, cancel)

Functions: procedures, triggers, function, cursors etc.