This is a very basic question asked by some interviewers. The Subsets are the pillars of Structured query language. Without subsets, it is not possible to manipulate the database and to create and build applications successfully. The DDL, DML, DCL, and TCL are responsible for building robust database applications and they all can be effectively utilized even in procedures and functions of SQL. We can split SQL into these five areas.
It mainly and solely contains the “SELECT” statement. SELECT is concerned simply with retrieving data stored in a database. It has optional clauses and tweaks.
It is concerned about the manipulation of data within a database. DML consists of four commonly used statements: INSERT, UPDATE, DELETE and MERGE. DML gives a method to understand, modify, delete, or merge data accurately. The SQL commands mostly include the modification of data. This contains most of the SQL statements.
|INSERT||Insert new records in the table.|
|UPDATE||Updates existing records within the table.|
|DELETE||Deletes records within tables.|
|MERGE||It’s like an upsert operation. It performs both insert and update operations based on conditions.|
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.
Data Definition Language includes the SQL commands that can be applied to specify the database schema. It entirely deals with depictions of the database schema and is utilized to build and change the design of database objects.DDL is a collection of SQL commands utilized to develop, alter, and delete database designs but not data. These commands are usually not utilized by a common user, who should be using the database.
|CREATE||It creates SQL objects like stored procedures, tables, columns, etc.|
|ALTER||It modifies existing SQL objects.|
|DROP||Deletes existing objects.|
It’s for restricting access to tables and database objects. It’s composed of various GRANT and REVOKE statements that allow or deny user access to database objects.
Data control language (DCL) is applied to use the accumulated data. It is primarily utilized for revoking and to allow the user the necessary privileges to a database. It aids in maintaining access to data held in a database. It fulfills the data manipulation language (DML) and the data definition language (DDL). It delivers the managers, to clear and establish database permissions to the users as required.
|GRANT||Allows users to read/write on certain objects.|
|REVOKE||Denies users to access specific objects.|
TCL is concerned about initiating, committing, or rolling back transactions. A transaction is basically a unit of work performed by a server. Its main purpose is to maintain the integrity of data within SQL statements. It contains statements like BEGIN TRANSACTION, COMMIT, and ROLLBACK.
TCL contains statements that are utilized to manage the modifications that are created from DML statements. It improves the transactional character of SQL. A transaction symbolizes any modification in a database; it is the outcome executed against a database, invariably, and autonomous of other transactions. TCL commands are utilized to follow the consequences of other commands on the database. They are also utilized to maintain transactional processing in a database. The modifications completed by utilizing TCL commands are enduring and cannot be rolled back.
|BEGIN TRANSACTION||Opens a transaction.|
|COMMIT||Indicates transaction is completed. Closes transaction.|
|ROLLBACK||ROLLBACK a transaction in case of error.|