SQL Commands

What are SQL Commands?

SQL commands are simply commands that are used to send and receive information from a database. It can also be used to do specific tasks, functions, or data searches. SQL may, among other things, create tables, add data to tables, drop tables, alter tables, and specify user permissions.
CREATE, DROP, SELECT, UPDATE, DELETE, and INSERT are the most frequent SQL commands for working with RDBMS. These commands can be classified into the following classes based on their nature.

Types of SQL Commands

DDL, DML, DCL, and TCL are the four types of SQL commands.

Let's Discuss each comands in brief,

Data Definition Language (DDL)

Data Definition Language (DDL) is a short term for a programming language that deals with database schemas and descriptions of how data should be stored in a database.

DDL stands for Data Definition Language, a computer language that deals with database schemas and specifications of how data should be kept in a database.
DDL changes the structure of a table by adding, removing, or altering it. DDL commands are all auto-committed, meaning they save all database changes indefinitely.
CREATE - This command creates a new table, table view, or database object.
Syntax :

CREATE TABLE table_name  (COLUMN_NAME DATATYPES[,....]);

ALTER - Changes the contents of an existing database object, such as a table.

ALTER TABLE table_name ADD column_name COLUMN-definition;

DROP - A whole table, a view of a table, or other database objects are destroyed.

DROP TABLE table_name;

TRUNCATE -Removes all records from a table, as well as all records' assigned space.

TRUNCATE TABLE table_name;

Data Manipulation Language (DML)

DML stands for Data Manipulation Language, and it is used to store, edit, retrieve, delete, and update data in databases. It includes most popular SQL statements such as SELECT, INSERT, UPDATE, DELETE, and so on.

Data Manipulation Language (DML) is a programming language for storing, editing, retrieving, deleting, and updating data in databases. It includes the most often used SQL commands, such as SELECT, INSERT, UPDATE, and DELETE.
DML instructions are used to make changes to the database. It is in charge of any and all database updates. Because the DML command isn't auto-committed, it won't be able to save all database changes indefinitely. There's a chance they'll be rolled back.

SELECT - This operation retrieves data from a single or more tables.
Syntax :

SELECT column(s)
FROM table_name
WHERE condition;

INSERT - This command is used to create a new record in the database.

INSERT INTO table_name 
(col1, col2, col3,.... col N)  
VALUES (value1, value2, value3, .... valueN); 

UPDATE - This command is used to make changes to a record.

UPDATE table_name
SET [column_name1= value1,...column_nameN = valueN] 
WHERE condition

DELETE - This command is used to remove records from the database.

DELETE FROM table_name
WHERE condition;

DCL - Data Control Language

Data Control Language (DCL) is a programming language that includes commands like GRANT and is primarily concerned with database system rights, permissions, and other constraints.

Here are some examples of DCL commands:

GRANT - It is used to grant users database access privileges.

GRANT SELECT, UPDATE ON table_name TO SOME_USER, ANOTHER_USER;

REVOKE - Revoke the permissions of users.

REVOKE SELECT, UPDATE ON table_name TO USER1, USER2;

TCL - Transition Control Language

TCL stands for Transaction Control Language and is a database transaction language. Only DML commands like DELETE, INSERT, and UPDATE can be used with Transition Control commands. Because these actions are automatically committed to the database, they can't be used to create or drop tables.

COMMIT - It's used to confirm the completion of a transaction.

COMMIT;

ROLLBACK - The rollback command is used to undo transactions that have not yet been written to the database.

ROLLBACK;

SAVEPOINT - It's used to undo a transaction at a specific point instead of the entire transaction.
Syntax :

SAVEPOINT  SAVEPOINT_NAME;