Do you know subsets of SQL?

Written on Apr 28, 2016

This is very basic question asked by some interviewers. We can split SQL into these five areas.

  1. Querying
  2. Data Manipulation Language (DML)
  3. Data Definition Language (DDL)
  4. Data Control Language (DCL)
  5. Transaction Control Language (TCL)

Querying

It mainly and solely contains "SELECT" statement. SELECT is concerned simply with retrieving data stored in database. It has optional clauses and tweaks.


DML

It is concerned about manipulation of data within database. DML consists of four commonly used statements: INSERT, UPDATE, DELETE and MERGE.

INSERT Insert new records in table.
UPDATE Updates existing records within table.
DELETE Deletes records within tables.
MERGE It's like upsert operation. It performs both insert and update operation based of condition.

DDL

The primary purpose of DDL is to create, modify or remove tables and other objects such as stored procedures, functions, triggers; from the database. It consists of variations of CREATE, ALTER and DROP statements.

CREATE It creates SQL objects like stored procedures, tables, columns, etc.
ALTER It modifies existing SQL objects.
DROP Deletes existing objects.

DCL

It's for restrict access to tables and database objects. It's composed of various GRANT and REVOKE statements that allow or deny user access to database objects.

GRANT Allows users to read/write on certain objects.
REVOKE Denies user to access specific objects.

TCL

TCL is concerned about initiating, committing or rolling back of transaction. A transaction is basically unit of work performed by server. It's main purpose is to maintain integrity of data within SQL statements. It contains statements like BEGIN TRANSACTION, COMMIT and ROLLBACK.

BEGIN TRANSACTION Opens a transaction.
COMMIT Indicates transaction is completed. Closes transaction.
ROLLBACK ROLLBACK a transaction in case of error.