What is SQL?

SQL stands for Structured Query Language. SQL is used to query and manipulate the underlying relational databases such as SQL Server, Oracle, MySQL, PostgreSQL, SQLite, etc.

SQL is an ANSI (American National Standards Institute) and ISO (International Organization for Standardization) standard language. However, not all the databases support the same SQL, but there is little variation. Also, most databases include their own addition to SQL.

SQL Syntax

SQL includes the following parts:

  • Keywords: Keywords are reserved or non-reserved words. Reserved keywords in SQL are SELECT, INTO, UPDATE, DELETE, DROP, ASC, DESC, etc.
  • Identifiers: Identifiers are the names of the database objects like table name, schema name, function name, etc.
  • Clauses: Clauses forms the components of SQL statements and queries such as WHERE, GROUP BY, HAVING, ORDER BY.
  • Expression: Expressions in SQL produce either scalar values, or columns and rows of data.
  • Boolean Conditions: Conditions are the expressions that result in the boolean value TRUE or FALSE. They are used to limit the effect of statements or queries.
  • Queries: Queries are the SQL statements that retrieve the data based on specific criteria. Statements that start with the SELECT clause are called queries because they retrieve data from the underlying database.
  • Statements: SQL statements may have a persistent effect on schema and data, or may control transactions, program flow, connections, sessions, or diagnostics. INSERT, UPDATE, DROP, DELETE statements are called SQL statements because they modify the underlying database structure or data.

The following figure illustrates the structure of the SQL:

SQL Structure
SQL Structure

SQL Classification

SQL is classified into the following categories. Note that statements mentioned in the following tables may vary in different databases.

Commands Description
DDL Data Definition Language
DML Data Manipulation Language
TCL Transaction Control Language
DCL Data Control Language

DDL – Data Definition Language

Data Definition Language (DDL) statements are used to define the structure of the data in the database such as tables, procedures, functions, views, etc. The following table lists DDL statements:

Statement Description
CREATE Create a new object(table, procedure, function, view etc.) in the database
ALTER Modify the structure of database table
DROP Delete database Objects
RENAME Rename database Objects (table, view, sequence, private synonym)
TRUNCATE Remove all records of a table

DML – Data Manipulation Language

Data Manipulation Language (DML) statements are used to manage data within a database object. It allows manipulation and querying the existing database schema objects. The following table lists DML statements:

Statement Description
SELECT Retrieve rows/columns from a table.
INSERT Insert new data to a table.
UPDATE Update existing records of table.
DELETE Delete existing records from table.
MERGE INSERT new rows or UPDATE existing rows in a table based on the specified conditions.
LOCK TABLE Lock one or more tables in the specified mode. Based on lock applied table access denied or only real only access given to another user.

TCL– Transaction Control Language

Transaction Control Language (TCL) statements are used to finalize the changes in the data made by executing the DML statements.

Statement Description
COMMIT Permanently save transaction changes to the database.
ROLLBACK Restore the database to its original state since the last COMMIT.
SAVEPOINT Create a SAVEPOINT to be later used by ROLLBACK Command to undo changes up to that point.
SET TRANSACTION Set the transaction properties such as READ WRITE or READ ONLY access.

DCL– Data Control Language

Data Control Language (DCL) statements are used to enforce database security by giving privileges to different users to access the database.

Statement Description
GRANT Gives privileges to the user for accessing data.
REVOKE Take back given privileges from the user.
COMMENT Specify comments on Database tables and columns.
ANALYZE Collect statistics of table, index, partition, cluster, etc.
AUDIT Track occurrence of specific or all SQL statements or operations on some specific Schema object.

SCL– Session Control Language

Session Control Language (SCL) statements are used to manage changes made to the database by executing DML statements. The SCL commands vary based on the database. The following table lists the SCL commands for the Oracle database.

Statement Description
ALTER SESSION Modify database parameters for current session.
SET ROLE To enable or disable roles for current session.