SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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; 
FieldTypeNullKeyDefaultExtra
EMPLOYEEIDint(11)NOPRINULL 
FIRST_NAMEvarchar(20)YES NULL 
LAST_NAMEvarchar(20)YES NULL 
SALARYint(11)YES NULL 
CITYvarchar(20)YES NULL 
DEPARTMENTvarchar(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;
EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENT
1VaibhavSharma65000NoidaOracle
2ShwetaSharma55000JaipurOracle
3Sonali Mali60000NashikFMW
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:

Types of SQL Commands
  • 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:

Types of SQL Commands

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENT
1001VAIBHAVIMISHRA65000PUNEORACLE
1002VAIBHAVSHARMA60000NOIDAORACLE
1003NIKHILVANI50000JAIPURFMW
2001PRACHISHARMA55500CHANDIGARHORACLE
2002BHAVESHJAIN65500PUNEFMW
2003RUCHIKAJAIN50000MUMBAITESTING
3001PRANOTISHENDE55500PUNEJAVA
3002ANUJAWHERE50500JAIPURFMW
3003DEEPAMJAUHARI58500MUMBAIJAVA
4001RAJESHGOUD60500MUMBAITESTING
  • 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:

Types of SQL Commands

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENT
1001VAIBHAVIMISHRA65000PUNEORACLE
1002VAIBHAVSHARMA60000NOIDAORACLE
1003NIKHILVANI50000JAIPURFMW
2001PRACHISHARMA55500CHANDIGARHORACLE
2002BHAVESHJAIN65500PUNEFMW
2003RUCHIKAJAIN50000MUMBAITESTING
3001PRANOTISHENDE55500PUNEJAVA
3002ANUJAWHERE50500JAIPURFMW
3003DEEPAMJAUHARI58500MUMBAIJAVA
4001RAJESHGOUD60500MUMBAITESTING
  • 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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENT
1001VAIBHAVIMISHRA65000PUNEORACLE
1002VAIBHAVSHARMA60000NOIDAORACLE
1003NIKHILVANI50000JAIPURFMW
2001PRACHISHARMA55500CHANDIGARHORACLE
2002BHAVESHJAIN65500PUNEFMW
3001PRANOTISHENDE55500PUNEJAVA
3002ANUJAWHERE50500JAIPURFMW

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENT
1001VAIBHAVIMISHRA65000PUNEORACLE
1002VAIBHAVSHARMA60000NOIDAORACLE
1003NIKHILVANI50000JAIPURFMW
2001PRACHISHARMA55500CHANDIGARHORACLE
2002BHAVESHJAIN65500PUNEFMW
2003RUCHIKAJAIN50000MUMBAITESTING
3001PRANOTISHENDE55500PUNEJAVA
3002ANUJAWHERE50500JAIPURFMW
3003DEEPAMJAUHARI58500MUMBAIJAVA
4001RAJESHGOUD60500MUMBAITESTING

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;
Types of SQL Commands

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

COMMIT;
Types of SQL Commands

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;
Types of SQL Commands

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

ROLLBACK;
Types of SQL Commands

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;
Types of SQL Commands



ADVERTISEMENT
ADVERTISEMENT