What are the different types of SQL commands?

A database query refers to the request of data or information from a database. A database query is performed using a database query language that generates data of different formats according to function. A number of query languages have been developed according to different database engines and purposes, one such language is SQL. SQL stands for Structured Query Language. It is the most widely used and well-known database query language which generates result in the form of rows and columns. It is designed for the retrieval and management of data in a relational database. SQL can be used to perform the following operations in the database :

• Access data in relational database management systems.
• Describe the data.
• Manipulate that data.
• Create and drop databases and tables.
• Create a view, stored procedure, functions in a database.
• Set permissions on tables, procedures and views.

All these operations are performed using the SQL Commands. SQL commands are instructions, coded into SQL statements, which are used to communicate with the database to perform specific tasks, work, functions, and queries with data in the database. SQL Commands can be grouped into following depending on their functionality:

• DDL (Data Definition Language)
• DML (Data Manipulation Language)
• TCL (Transaction Control Language)
• DCL (Data Control Language)

DDL Commands

DDL stands for Data Definition Language. It consists of the SQL commands that can be used to define the database schema. It deals with descriptions of the database schema. These commands are used for creating, modifying, and dropping the structure of database objects. The database automatically commits the current transaction before and after every DDL Command. Some DDL commands are CREATE, ALTER, and DROP.

** Only with DDL Command we need to specify the database object with keywords(like table, view, procedure, index, function, etc)

Examples of DDL Commands :

• CREATE: It is used to create the database and its objects (like tables, functions, views, indexes, procedures, triggers).

CREATE DATABASE database_name; 
This query will create a new database in SQL and name the database as the provided name.

CREATE TABLE table_name
{
column1 data_type(size),
column2 data_type(size),
.
.
columnN data_type(size)
};
This query will create a new table in SQL and name the table as specified. The table will contain N columns of associated data_types and size.
 
CREATE TABLE Employees {
 Emp_Id int(3),
 Emp_Name varchar(20)
};
This will create a table Employees with column Emp_Id which stores integer data of length 3 and Emp_Name which stores string data of length 20.

• ALTER: Alter command is used to modify the existing database objects. It can add, delete/drop or modify columns in the existing table. It can also be used to add and drop various constraints on the existing table.

ALTER TABLE Employees ADD Salary DOUBLE(8,2);
This query will add a column Salary to the existing table Employees. If the table Employees do not exist, an error will be generated.

ALTER TABLE Employees DROP COLUMN Salary;
This query will delete the column Salary from the table Employees.

ALTER TABLE Employees MODIFY Name VARCHAR(40);
This query will modify the existing column Name in table Employees and change the size to 40 from 20.

• DROP: DROP command is used to delete the various existing database objects. It deletes an entire database, an entire table, a view of a table or other objects in the database. If you drop a table, all the rows in the table are deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back.

DROP object object_name;
object: Keyword that represents the database object.

DROP DATABASE Company;
The database Company is deleted from the relational database management system.

DROP TABLE Employees;
This SQL command will remove the table structure along with its data from the database.

DML Commands

DML stands for Data Manipulation Language. These commands are used for retrieving, inserting/storing, modifying and deleting the data stored in the database. DML Commands deals with the manipulation or managing of data present in the objects of a relational database. Some DML commands are SELECT, INSERT, UPDATE, and DELETE commands.

** The SELECT statement is a limited form of DML statement such that it can only access data in the database. It cannot manipulate data in the database, although it can operate on the accessed data before returning the results of the query. This is why SELECT is also called DQL(Data Query Language).

Examples of DML Commands:

• INSERT: Insert command is used to insert new records or new rows in a table. There are two ways of inserting data into a table.

# Both Column names and values: In this method, we need to specify both the column names of the table in which we are inserting the data as well as their corresponding values.

INSERT INTO table_name(col1, col2,…) VALUES(value1, value2,…);

INSERT INTO Employees(Emp_Id, Emp_Name) VALUES(007, “John Doe”);
This command creates a new record in the table Employees and inserts values for the specified column with corresponding values.

# Only Values: In the second method we provide only the values of the data to be inserted into the column without their column name. In this method, we need to provide values for every column in the table and they must be in the order of the columns.

INSERT INTO table_name VALUES(value1, value2,… valueN);

INSERT INTO Employees VALUES(007,”John Doe”,20000);
This command insert values in all 3 columns of the table Employees(Emp_Id,Emp_Name and Salary).

• SELECT: Select command is used to retrieve or fetch data from the tables in a database. We can retrieve either the entire table or some records based on our specified condition. It is also possible to retrieve one column or multiple columns from the tables. The data returned is stored in a result table. This result table is also called the result-set.

SELECT col1, col2,… colN FROM table_name WHERE condition;

col1,col2,… colN: represent the fields of the table that we want to retrieve. We can also use ‘*’ in place of column names to retrieve all the columns.
table_name: specifies the table which acts as the data source or from where we watch to fetch the data.
condition: the condition is specified in the WHERE clause which is optional. It is used to filter out the data according to the specified condition.

SELECT * FROM Employees;
Retrieves all the records with data from every field form the Employees table.

SELECT Emp_Id, Emp_Name FROM Employees;
Retrieves all the records with data of only field Emp_Id and Emp_Name form the Employees table.

SELECT * FROM Employees WHERE Emp_Id = 7;
Retrieves all field information form the Employees table of the Employees whose Emp_Id is 7.

• DELETE: The delete command is used to delete the existing records from a table. We can delete a single record or multiple records based on the condition specified in the WHERE clause.

DELETE FROM table_name WHERE condition;
table_name: name of the table from which the records are to be deleted.
condition: provides filter conditions to identify the particular records that are to be deleted.

DELETE FROM Employees WHERE Emp_Id = 7;
Deletes the record (single row) of the Employee whose Emp_Id is 7.

DELETE FROM Employees WHERE Salary = 20000;
Deletes records (multiple records) of the Employees whose salary is 20000.

** WHERE clause in DELETE command is optional. If we omit the WHERE clause then all of the records of the table will be deleted and the table will be empty.

DELETE FROM Employees;
Deletes all the records from the table Employees.

• UPDATE: The update command is used to update or modify the existing data of a table in the database. It changes the values of the records. Using UPDATE command we can update a single column as well as multiple columns simultaneously.

UPDATE table_name SET col1 = value1, col2 = value2,… colN = vlaueN WHERE condition;

table_name: name of the table whose data we are going to update.
SET: keyword used to specify fields and values.
col: name of fields to be updated.
value: the updated value of the corresponding field.
condition: condition specified to select rows in which data needs to be modified.

UPDATE Employees SET Salary = 10000 WHERE Emp_Id = 007;
Modifies the salary(single column) of the employee with Emp_Id 7 and sets Salary to 10000.

UPDATE Employees SET Salary=10000, Name=“John” WHERE Emp_Id = 007;
Modifies the salary and name (multiple-column) of employees with Emp_Id 7 and sets Salary to 10000.

** WHERE clause in the UPDATE command is optional. If we omit the WHERE clause then the specified field(s) of all the records of the table will be updated.

UPDATE Employees SET Salary = 10000;
Modifies the salary of every employee and sets Salary to 10000.

TCL Commands

TCL stands for Transaction Control Language. TCL commands deal with the transaction within the database. A transaction is a unit of work that is performed against a database. Transaction results in a change in the state of data. TCL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT. TCL commands are used with DML Commands only. DDL Commands automatically saves the state of the data.

Examples of TCL Commands

• COMMIT: The COMMIT command is the TCL command used to save changes invoked by a transaction to the database. The COMMIT command saves all the transactions or changes to the database since the last COMMIT or ROLLBACK command. After COMMIT the changes cannot be undone.

COMMIT;
This command will save the current state of data, in other words, it will make all the changes done to the database permanent.

• ROLLBACK: The ROLLBACK command is a TCL command used to undo transactions or changes that have not already been saved to the database. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was used. It restores the database to its original form since the last COMMIT.

ROLLBACK;
This command will undo all the changes made to the database since the last COMMIT or ROLLBACK.

ROLLBACK TO save_point;
This command will undo all the changes made to the database up to the specified save point.

• SAVEPOINT: A SAVEPOINT is a name given to a point in a transaction. It serves as a checkpoint or a marker within a transaction. A savepoint is a way of implementing subtransactions (also known as nested transactions) within a relational database management system by indicating a point within a transaction up to which all the changes to the database can be undone without affecting any work done in the transaction prior to the creation of savepoint. After COMMIT or ROLLBACK, the savepoints are removed from the database.

SAVEPOINT My_Savepoint;

This commands creates a savepoint of name My_Savepoint in the current transaction. Now rollback command can be used to undo changes to the database up to the savepoint My_Savepoint.

ROLLBACK TO My_Savepoint;

DCL Commands

DCL stands for Data Control Language. These SQL commands are used for providing security to database objects( like table, views, procedures, etc). Data control commands in SQL allow us to control access to data within the database. These DCL commands are normally used to create objects related to user access and also control the distribution of privileges among users. These commands are GRANT and REVOKE. To perform any operation in the database, such as for creating tables, sequences or views, a user needs privileges. Privileges are of two types:

• System: This includes permissions for creating sessions, tables, etc and all types of other system privileges.
• Object: This includes permissions for any command or query to perform any operation on the database tables.

Examples of DCL Commands

• GRANT: This command is used to give access or permission to specific users or roles on specific database objects like table, view, etc.

FOR OBJECT PRIVILEGES:

GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name} [WITH GRANT OPTION];

privilege_name: refers to the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT. Multiple privileges can be granted to the users simultaneously.
object_name: name of a database object like TABLE, VIEW, STORED PROC and SEQUENCE. It represents the objects on which the permission is to be granted. Permissions on multiple objects can be granted simultaneously.
user_name: it is the name of the user or role to whom an access right is being granted. Multiple users can be granted privileges simultaneously. PUBLIC keyword grants privileges to all the users.
[WITH GRANT OPTION]: this clause is optional. it is used to allow users to grant the same privileges to other users. It is applicable only for object privileges.

GRANT SELECT ON Employees TO user1;
This query will grant SELECT privileges on Employees object to user1, i.e., user1 is now able to run the SELECT query on Employees.

GRANT SELECT ON Employees TO user1 WITH GRANT OPTION;
This query will grant SELECT privileges on Employees object to user1 and user1 can also grant this privilege to the users.

FOR SYSTEM PRIVILEGES:

GRANT privilege_name TO user_name [WITH ADMIN OPTION];

GRANT CREATE TABLE TO User1;
This will grant system privilege CREATE TABLE to User1; 

GRANT CREATE TABLE TO User1 WITH ADMIN OPTION;
This will grant system privilege CREATE TABLE to User1 and User1 will be able to grant the same privileges to other users;

• REVOKE: The REVOKE command removes user access rights or privileges to the database objects given by using the GRANT command. Only the privileges which were granted to a user can be revoked. We can revoke multiple privileges from multiple users in the same SQL REVOKE statement.

** When revoking privileges, the list of privileges can only be either all system privileges or all object privileges, i.e., we cannot revoke both system and object privileges in the same SQL REVOKE statement.

REVOKE privilege_name ON object_name TO user_name [CASCADE | RESTRICT];

privilage_name: refers to the access right or privilege granted to the user.
object_name: name of a database object like TABLE, VIEW, STORED PROC and SEQUENCE. It represents the objects on which the permission is to be revoked.
user_name: it is the name of the user or role from whom an access right is to be revoked.
[CASCADE|RESTRICT]: This clause is optional. By default, it is a cascade. It determines whether the privileges from the subsequent grantee should be revoked or not.

REVOKE SELECT ON Employees FROM user1;
This query will revoke the SELECT privilege granted to User1 on Employees.

REVOKE SELECT ON Employees FROM user1 ;
This query will revoke the SELECT privilege granted to User1 on Employees.

These are the SQL commands that we use in our Database. Hope you learned something new today.

Do share this blog with your friends to spread the knowledge. Visit our YouTube channel for more content. You can read more blogs from here.

Connect with us on Twitter and LinkedIn.

Happy Learning 🙂

Team AfterAcademy!