Types of SQL Commands

The Structured Query Language is used to deal with structured data. The data which are stored in the form of tables are structured data. These SQL commands store records or data in the table and are also used to fetch the records from the stored tables. There are the four types of Structured Query Language Commands:        

1. Data Definition Language Commands.
2. Data Manipulation Language Commands.
3. Data Control Language Commands.
4. Transaction Control Language Commands.

Data Definition Language Commands

DDL is an abbreviation for Data Definition Language. Data Definition Language commands are used to deal with the structure, i.e., the table or relation in which our records or data is stored. Data Definition Language commands are used to create, delete, and alter the table. Data Definition Language commands are:

1. CREATE Command.
2. ALTER Command.
3. DROP Command.
4. TRUNCATE Command.

CREATE Commands: In Structured Query Language, we store data in the table, and to create a new table or Database, we use CREATE Command of the Data Definition Language Commands. Below is the syntax to create a Database:

CREATE DATABASE DATABASE_NAME;

For examples,

• Write a query to create a new database followed by database name Company.

CREATE DATABASE COMPANY;	

To check whether a database Company is created, we will use the following query:

SHOW DATABASES; 

Database

company

employee

information_schema

mysql

performance_schema

phpmyadmin

The above result says we successfully created the Company Database.

• Write a query to create a new table:

We will create a new table of Employees inside the Company Database. We must select Company Database.

CREATE TABLE TABLE_NAME(COLUMN_NAME1 DATATYPE(COLUMN SIZE), COLUMN_NAME2 DATATYPE (COLUMN SIZE),COLUMN_NAME3 DATATYPE (COLUMN SIZE));

To select Company Database, we will use the following query:

USE COMPANY;

Now, we select Company as a Database; we will execute CREATE TABLE query to create a table:

CREATE TABLE EMPLOYEES (EMPLOYEE INT PRIMARY KEY, FIRST_NAME VARCHAR (20), LAST_NAME VARCHAR (20), SALARY INT, CITY VARCHAR (20) );

We created the table name Employees using CREATE TABLE query.

To check whether table Employees is created, we will use the following query:

SHOW TABLES;

Tables_in_company

employees

The above result says we successfully created the 'Employees' Table.

We will create another table using CREATE TABLE.

CREATE TABLE DEPARTMENT (DEPARTMENT INT PRIMARY KEY, DEPARTMENT_NAME VARCHAR (20));

We created the table name 'Department' using CREATE TABLE query.

To check whether table Department is created, we will use the following query:

SHOW TABLES;  

Tables_in_company

department employees

The above result says we successfully created the 'Employees' Table.

ALTER Command: In the Structured Query Language, whenever we want to modify the table schema or structure, we will use ALTER command of the Data Definition Language command. ALTER Command is used to modify table schema by adding a new column, deleting an existing column from the table, etc. below is the syntax of ALTER Command to add a new column:

ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATATYPE (COLUMN SIZE);

Syntax of ALTER Command to remove an existing column:

ALTER TABLE TABLE-NAME DROP COLUMN COLUMN_NAME;

Syntax of ALTER Command to rename the existing table’s column:

ALTER TABLE TABLE_NAME RENAME COLUMN OLD_COLUMN-NAME TO NEW_COLUMN_NAME;

For example,

• Write a query to add a new column in the Employees table:

ALTER TABLE EMPLOYEES ADD DEPARTMENT VARCHAR (20);

Here, we have executed ALTER TABLE command on the table Employees followed by the ADD keyword, which means to add a column with the column name Department, the data type VARCHAR and column size 20. The above query says we added a new column named 'Department' with the data type VARCHAR and the column size is 20.

To check whether a new column is added to the table Employees, we will use the DESC command on the employee's table.

DESC EMPLOYEES; 

Field	Type	Null	Key	Default	Extra
EMPLOYEEID	int(11)	NO	PRI	NULL
FIRST_NAME	varchar(20)	YES		NULL
LAST_NAME	varchar(20)	YES		NULL
SALARY	int(11)	YES		NULL
CITY	varchar(20)	YES		NULL
DEPARTMENT	varchar(20)	YES		NULL

The above result verifies that a new column is successfully added to the employee's table.

DROP Command: DROP Command in the Structured Query Language is used to delete the Table and table schema from the Database. Here is the syntax of DROP Command;

DROP TABLE TABLE_NAME;

Write a query to drop the Department table from the Employees Database.

DROP TABLE DEPARTMENT;

Here, we have executed the DROP TABLE query on the table Department.

To check whether table Department is deleted, we will use the following query:

SHOW TABLES;  

Tables_in_company

Employees

The above result says we successfully deleted the Department table from the Company Database.

TRUNCATE Command: TRUNCATE Command in the Structured Query Language removes all the data from the table without disturbing the table schema in the Database. Below is the syntax of the TRUNCATE Command:

TRUNCATE TABLE TABLE_NAME  

Write a query to delete all the records from the table Employees.

We will use the SELECT query to fetch the data first of the Employees table and then execute the TRUNCATE query.

SELECT * FROM EMPLOYEES;

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT
1	Vaibhav	Sharma	65000	Noida	Oracle
2	Shweta	Sharma	55000	Jaipur	Oracle
3	Sonali	Mali	60000	Nashik	FMW

TRUNCATE TABLE EMPLOYEES;

Here, we successfully removed all the data from the Employees table. Now, we will use the SELECT query on the Employees table.

TRUNCATE TABLE EMPLOYEES;

After executing a SELECT query on the Employees table, the result will display an empty set. This means we successfully removed the entire table.

Data Manipulation Language Command

DML Stands for Data Manipulation Language. Data Manipulation Language commands in Structured Query Language manipulate the data in the Database. Data Manipulation Language commands are used to fetch data, store data, modify the data, and delete the data from the existing table.

The Data Manipulation Language commands in the Structured Query Language are as follows:

1. SELECT Command.
2. INSERT Command.
3. UPDATE Command.
4. DELETE Command.

First, we will look at INSERT commands of the Data Manipulation Language in the Structured Query Language. After INSERT, we will move toward the SELECT Command.

INSERT command: INSERT Command stores data or records in the database table. INSERT command is the important Command of Data Manipulation Language in the Structured Query Language.

Syntax of INSERT Data Manipulation Language command:

INSERT INTO TABLE_NAME VALUES (VALUE1, VALUE2, VALUE3, VALUE);

This syntax allows all the fields of the table. In case if you want to insert values for selected fields, then use the below Syntax:

INSERT INTO TABLE_NAME (COLUMN_NAME1, COLUMN_NAME2) VALUES (VALUE1, VALUE2);

 The above syntax is used to store values for selected fields of the table.

For example,

In this example, we will insert a record for all the fields in the table.

Let’s take the Employees table, which is empty. We will INSERT records in the stud table.

Now we will store new records into the Employees table. To store records into the table, we will execute the following Data Manipulation Language INSERT command:

INSERT INTO EMPLOYEES VALUES (1001, 'VAIBHAVI', 'MISHRA', 65000, 'PUNE', 'ORACLE');
INSERT INTO EMPLOYEES VALUES (1002, 'VAIBHAV', 'SHARMA', 60000, 'NOIDA', 'ORACLE');
INSERT INTO EMPLOYEES VALUES (1003, 'NIKHIL', 'VANI', 50000, 'JAIPUR', 'FMW');
INSERT INTO EMPLOYEES VALUES (1004, 'ANUJA', 'WANRE', 55000, 'DELHI', 'FMW');
INSERT INTO EMPLOYEES VALUES (2001, 'PRACHI', 'SHARMA', 55500, 'CHANDIGARH', 'ORACLE');
INSERT INTO EMPLOYEES VALUES (2002, 'BHAVESH', 'JAIN', 65500, 'PUNE', 'ORACLE');

We inserted six records into the employees table.

SELECT Command: The Most important Data Manipulation Language commands in the SQL is the SELECT Command. The SELECT command is used to fetch the data of the specified table, and SELECT Command is also used to fetch selected fields of the table using the WHERE clause in the query.

Syntax of SELECT Data Manipulation Language Command

SELECT * FROM TABLE_NAME;

The above syntax is used to fetch the whole table.

SELECT COLUMN_NAME1, COLUMN_NAME2, COLUMN_NAMEN FROM TABLE_NAME;

The above syntax is to fetch all the records of the selected column.

SELECT * FROM TABLE_NAME WHERE COLUMN_NAME;

The above syntax is used to fetch the single records using the WHERE Clause specified by the Column name.

For example, suppose you want to fetch all the data of each column from the table:

SELECT * FROM EMPLOYEES;

This Structured Query Language shows the following data of the EMPLOYEES table:

• Fetch single row all data using WHERE clause specified by column name from the table:

SELECT * FROM EMPLOYEES WHERE SALARY > 55000;

The above query will fetch all the records of the employees whose salary is greater than 55000.

This Structured Query Language shows the following data of the EMPLOYEES table:

UPDATE Command: UPDATE Command is another valuable Data Manipulation Language Command in SQL. UPDATE commands are used to modify the records in a database table.

Syntax of UPDATE Data Manipulation Language Command

UPDATE TABLE_NAME SET COLUMN_NAME1 = VALUES, COLUMN_NAME2 = VALUES WHERE CONDITION;

UPDATE, SET, AND WHERE are the Structured Query Language Keywords.

For Examples,

• This example describes how to modify the value.

Let’s consider the EMPLOYEES table:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT
1001	VAIBHAVI	MISHRA	65000	PUNE	ORACLE
1002	VAIBHAV	SHARMA	60000	NOIDA	ORACLE
1003	NIKHIL	VANI	50000	JAIPUR	FMW
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE
2002	BHAVESH	JAIN	65500	PUNE	FMW
2003	RUCHIKA	JAIN	50000	MUMBAI	TESTING
3001	PRANOTI	SHENDE	55500	PUNE	JAVA
3002	ANUJA	WHERE	50500	JAIPUR	FMW
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA
4001	RAJESH	GOUD	60500	MUMBAI	TESTING

• Write a query to modify a record whose Last Name is 'Jain' and set the Department value as 'FMW'.

UPDATE TABLE_NAME SET COLUMN_NAME1 = VALUES, COLUMN_NAME2 = VALUES WHERE CONDITION;

We will use the SELECT query on the Employees table where the Last Name is Jain to verify whether the value is modified or not.

SELECT * FROM EMPLOYEES WHERE LAST_NAME = ‘JAIN’;

This Structured Query Language shows the following data of the EMPLOYEES table:

DELETE command: DELETE is a data manipulation language command used to delete the records from the table. We delete records from the table using the WHERE clause in the query. 

Syntax of UPDATE Data Manipulation Language Command

DELETE FROM TABLE_NAME WHERE CONDITION;

For example, let’s consider the EMPLOYEES table:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT
1001	VAIBHAVI	MISHRA	65000	PUNE	ORACLE
1002	VAIBHAV	SHARMA	60000	NOIDA	ORACLE
1003	NIKHIL	VANI	50000	JAIPUR	FMW
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE
2002	BHAVESH	JAIN	65500	PUNE	FMW
2003	RUCHIKA	JAIN	50000	MUMBAI	TESTING
3001	PRANOTI	SHENDE	55500	PUNE	JAVA
3002	ANUJA	WHERE	50500	JAIPUR	FMW
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA
4001	RAJESH	GOUD	60500	MUMBAI	TESTING

• Write a query to delete the data whose city name is ‘Mumbai’;

DELETE FROM EMPLOYEES WHERE CITY = ‘MUMBAI’;

We will use the SELECT query on the Employees table to verify whether the value is modified or not.

SELECT * FROM EMPLOYEES;

This Structured Query Language shows the following data of the EMPLOYEES table:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT
1001	VAIBHAVI	MISHRA	65000	PUNE	ORACLE
1002	VAIBHAV	SHARMA	60000	NOIDA	ORACLE
1003	NIKHIL	VANI	50000	JAIPUR	FMW
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE
2002	BHAVESH	JAIN	65500	PUNE	FMW
3001	PRANOTI	SHENDE	55500	PUNE	JAVA
3002	ANUJA	WHERE	50500	JAIPUR	FMW

The above result shows that the record with City Mumbai was deleted successfully.

Data Control Language Commands

DCL is an abbreviation of Data Control Language. We use Data Control Language Commands to control the access privileges to the records inside the Structured Query Language tables. Using Data Control Language commands, we can give privileges to authorized users only to access the data placed in the tables or databases.

The user has some pre-built access privileges to access the data placed in the tables for a particular user only. Using Data Control Language Commands, we can assign privileges to the users on the Structured Query Language or Database or take back (Revoke) privileges from the users. There are two Data Control Language Commands:

1. GRANT: Grant Commands are used to assign access privileges to the users for the databases or the tables.
2. REVOKE: If we can GRANT access privileges to the users. In that case, we want to take back assigned privileges from the user for the databases. We can use REVOKE Command to revoke all the assigned privileges from the user.

Transaction Control Language

In Structured Query Language, we have Data Definition Language (DDL) and Data Manipulation Language (DML) commands the same way we have Transaction Control Language (TCL) commands in the Structured Query Language. Transaction Control Language commands in the Structured Query Language are Commit and Rollback.

Whatever commands we executed wrapped in one unit of work known as a transaction. Suppose we have executed one update or delete operation on a table executed in a transaction. All the operations or commands of DDL or DML are stored or executed in a transaction. To save such executed DDL or DML, we have to execute the commit command of a Transaction Control Language. Commit is used to save all the operations we performed on a table. All the operation is saved. All this is about Commit.

Now, think, what if we want to undo the operations we saved using the commit commands? Then can we undo the operation in the Structured Query Language? Yes, we can undo the operations which are committed. We will use another command of Transaction Control Language to undo the operations, and that Command is Rollback. We will use the Rollback command to undo the commit operation in the Structured Query Language. Commands under Transaction Control Language are:

1. Commit
2. Rollback

Consider the existing employee's tables which have the following records:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT
1001	VAIBHAVI	MISHRA	65000	PUNE	ORACLE
1002	VAIBHAV	SHARMA	60000	NOIDA	ORACLE
1003	NIKHIL	VANI	50000	JAIPUR	FMW
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE
2002	BHAVESH	JAIN	65500	PUNE	FMW
2003	RUCHIKA	JAIN	50000	MUMBAI	TESTING
3001	PRANOTI	SHENDE	55500	PUNE	JAVA
3002	ANUJA	WHERE	50500	JAIPUR	FMW
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA
4001	RAJESH	GOUD	60500	MUMBAI	TESTING

We can use the commit and Rollback command without starting the transactions, but we will start our transaction using Start Transaction Command for good practice.

Let’s begin and see each operation example one by one.

First, we will turn off the auto-commit by assigning a value of auto-commit to 0

SET AUTOCOMMIT = 0;

• Insert new Values into existing table’s employees, and then we will use Commit Command:

INSERT INTO EMPLOYEES VALUES (2003, ‘RUCHIKA’, ‘JAIN’, 50000, ‘MUMBAI’, ‘TESTING’);

We will use the SELECT query to check data is inserted into a table or not:

SELECT * FROM EMPLOYEES WHERE EMPLOYEE = 2003;

Now, to save this transaction, we will use the commit command.

COMMIT;

The employee id 2001 details are saved now. It won't be Rollback unless we delete it using the Delete command.

• We will delete all the information of employee id 2003, and then we will Rollback the delete command using Rollback.

DELETE FROM EMPLOYEES WHERE EMPLOYEE ID = 2003;

We will use the SELECT query to check data is deleted from a table or not:

SELECT * FROM EMPLOYEES;

The employee id 2003 is deleted from the table. Now, we will use Rollback Command to undo the delete operation.

ROLLBACK;

After executing Rollback Command, we will use the SELECT query again to check delete operation is Rollback or not.

SELECT * FROM EMPLOYEES WHERE EMPLOYEE ID = 2003;