What is SQL? Explain DQL, DML, DDL, DCL and TCL statements with examples.

Learn about SQL and categories of SQL commands like DQL, DML, DDL, DCL and TCL statements with specific command examples.

 

SQL: 

SQL stands for Structured Query Language. It is the language for database that helps retrieving, storing, and manipulation of data in a relational database.

All RDBMS like Oracle, SQL Server, MySQL, PostgresSQL, DB2 etc uses SQL as database language. NoSQL database don't use it.

SQL uses certain commands like Select, Create, Insert, Update, Drop, Truncate etc. to carry out the specific tasks in the database.

These SQL commands are primarily categorized into following categories:

1. DDL – Data Definition Language
2. DQL – Data Query Language
3. DML – Data Manipulation Language
4. DCL – Data Control Language
5. TCL – Transaction Control Language

Data Definition Language (DDL)

Data Definition Language or DDL commands are comprised of the SQL commands that are used to define the database schema and objects like tables, index, procedures, triggers etc. 

DDL commands are auto-committed, meaning that changes saved permanently in the database. 

Examples of DDL commands:

• CREATE, 
• DROP, 
• ALTER, 
• TRUNCATE, 
• COMMENT,
• RENAME 

Data Query Language (DQL) :

Data Query Language or DQL statements are used to retrieve data from database tables. It is also in some form works for inserting data.

Example of DQL commands:

• SELECT

 

Data Manipulation Language(DML): 

DML SQL commands are used to manipulate data in the database. 

DML commands are not auto-committed, meaning that changes have to committed explicitly and it can be roolback

Examples of DML commands:

• INSERT
• UPDATE
• DELETE

 

Data Control Language(DCL): 

DCL commands deal with the rights, permissions and other controls of the database system.

Examples of DCL commands:

• GRANT
• REVOKE

Transaction Control Language(TCL): 

TCL commands are used to manage the transaction at the database level. These operations are automatically committed in the database.

Examples of TCL commands:

• COMMIT
• ROLLBACK
• SAVEPOINT
• SET TRANSACTION

What is the difference between primary key and unique key in SQL Server?

How to answer DBMS interview question about the difference between primary key and unique key

This is very commonly asked interview question from SQL Server or database. Following is the answer of this question:

1. By default, Primary key creates clustered index in the table but unique key creates non-clustered index.

2. Primary key column does not accept any null values, where as a unique key column accept only one null value.

3. A table can have only one primary key. On the other hand a table can have more than one unique key.

4. Duplicate values are not allowed in primary key where as duplicate value will be accepted if one or more key parts are null

5. The purpose of implementing primary key is to enforce integrity between entities of database, on the other hand the purpose of unique key is to enforce unique data within the table.