SQL Command and Theory Part:-

In this article, we will talk about DDL, DML, DCL, and TCL and some important concepts that you should know.

Let's start with DDL:-

DDL (Data Definition Language):-

Data Definition Language is used to define the database structure or schema. DDL is also used to specify additional properties of the data. The storage structure and access methods used by the database system by a set of statements in a special type of DDL called a data storage and definition language.

some commands:-

CREATE : to create objects in database
ALTER : alters the structure of database
DROP : delete objects from database
RENAME : rename an objects

DML (Data Manipulation Language):-

DML statements are used for managing data within schema objects.

some commands:-

SELECT: retrieve data from the database
INSERT: insert data into a table
UPDATE: update existing data within a table
DELETE: deletes all records from a table, space for the records remain

What is the main difference between DDL and DML?

“ DDL deals with table structure while DML deals with data directly”

TCL (Transaction Control Language):-

Transaction Control Language commands are used to manage transactions in the database.

some commands:-

COMMIT: Commit command is used to permanently save any transaction
            into the database.
ROLLBACK: This command restores the database to last committed state.
            It is also used with savepoint command to jump to a savepoint
            in a transaction.
SAVEPOINT: Savepoint command is used to temporarily save a transaction so
            that you can rollback to that point whenever necessary.

DCL (Data Control Language):-

A Data Control Language is a syntax similar to a computer programming language used to control access to data stored in a database (Authorization). In particular, it is a component of Structured Query Language (SQL).

some commands:-

GRANT: allow specified users to perform specified tasks.
REVOKE: cancel previously granted or denied permissions.

Some Important Concepts:-

DELETE

DML COMMAND 
-Delete Rows from the table one by one
-We can use where clause with Delete to delete a single row
-Delete is slower than truncate -ROLLBACK is possible with DELETE.

DROP

DDL COMMAND
-Delete the entire structure or schema
-We can’t use where clause with drop
-Drop is slower than DELETE & TRUNCATE
-ROLLBACK IS NOT POSSIBLE WITH DROP.

TRUNCATE

DDL COMMAND
-Truncate deletes rows at a one goal
-We can’t use where clause with Truncate
-Truncate faster than both DELETE & DROP
-Rollback is not possible with Truncate

Primary Key Vs Unique Key:-

PRIMARY KEY

1. Primary Key is used to identify all the records in a relation uniquely.
2. Primary key can not have null value. 
3. Primary key can be only one per table. 
4. Primary key creats clustered index.

UNIQUE KEY

1. Unique key is also used to identify a records uniquely. 
2. Unique key can accept one null value. 
3. Unique key can be multiple in a table. 
4. Unique key creates non-clustered index.

Thank you for reading !!!

If you enjoy this article and would like to Buy Me a Coffee, please click here.

you can connect with me on Linkedin.