Description
I consider you already read my previous articles. If not, at least read this section. Before going deep let’s discuss some basic concepts or terminology that is associated with SQL.
- SQL stands for Structured Query Language.
- It is a query language which helps us retrieving and manipulating data stored in DBMS that is based on Relational Model.
- Most of the RDBMS softwares like MySQL, Informix, Oracle, MS Access, PostgreSQL, Mariadb, DB2, SAP HANA, Microsoft SQL server, SQLite, etc use SQL as their standard database language.
Statements in SQL
A statement is any text that the database engine recognizes as a valid command. We can write a single SQL statement in one or multiple lines. The symbol semicolon (“;”) is used as a statement terminator (delimiter) in SQL statements. Though not required on every platform, it is defined as a standard part of the SQL grammar. Insignificant whitespace is generally ignored in SQL statements and queries, making it easier to format SQL code for readability. However SQL statements are not case sensitive. E.g SELECT is same as select. But SQL Data or variable is case sensitive.
Note: Don’t be confused with Statement and Query. Both the terminology do the same work. Only difference is when a statement returns a recordset based on specific criteria we call it query. Not much difference !!!
Types of SQL Statements
SQL statements are broadly divided into five different categories. I have described each category with few example statements. Those examples are given here for sake of simplicity. Later on I will be discussing in details.
- Data Definition Language (DDL)
DDL actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in database.
Examples :- CREATE – is used to create the database or its objects (like table, index, function, views, stored procedure and triggers).
- DROP – is used to delete objects from the database.
- ALTER – is used to alter the structure of the database.
- TRUNCATE – is used to remove all records from a table, including all spaces allocated for the records are removed.
- COMMENT – is used to add comments to the data dictionary.
- RENAME – is used to rename an object existing in the database.
- Data Manipulation Language (DML)
The SQL commands that deals with the manipulation of data present in database belong to DML or Data Manipulation Language and this includes most of the SQL statements.
Examples :- SELECT – is used to retrieve data from the database.
- INSERT – is used to insert data into a table.
- UPDATE – is used to update existing data within a table.
- DELETE – is used to delete records from a database table.
- Data Control Language (DCL)
DCL includes commands mainly deals with the rights, permissions and other controls of the database system.
Examples :- GRANT – gives an user the access privilege control to a database.
- REVOKE – withdraw user’s access privileges from a database that was given by using the GRANT command.
- Transaction Control Statement (TCS)
TCL commands deals with the transaction within the database.
Examples :- COMMIT– commits a Transaction.
- ROLLBACK– rollbacks a transaction in case of any error occurs.
- SAVEPOINT–sets a savepoint within a transaction to which you can later roll back.
- SET TRANSACTION–specify characteristics for the transaction. E.g It changes the transaction options like isolation level and what rollback segment to use
- Session Control Statements (SCS)
SCS statements are used to dynamically manage properties of a user session.
Examples :- ALTER SESSION– This statement is used set or modify the conditions or parameters that affect your connection to the database. The statement stays in effect until session closed.
- SET ROLE– This statement is used to enable or disable roles which have been granted.