In this article, let's talk about Structured Query Language (SQL).
What is Structured Query Language (SQL)?
Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them. Initially created in the 1970s, SQL is regularly used not only by database administrators, but also by developers writing data integration scripts and data analysts looking to set up and run analytical queries.
![SQL Mind Map]()
Note: image taken from google
SQL Commands
There are 4 important commands in SQL
DDL (Data Query Language)
- It is used to create and modify the structure of database objects in a database. These database objects include views, schemas, tables, indexes, etc.
- Its commonly used DDL in SQL querying are CREATE, ALTER, DROP, and TRUNCATE.
CREATE - It is used to create a new table in the database.
โSyntax
CREATE TABLE [table name] ([column definitions]) [table parameters];
Example
CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);
DROP - It is used to delete both the structure and record stored in the table.
โSyntax
DROP TABLE table_name;
Example
DROP TABLE EMPLOYEE;
ALTER - It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.
โSyntax
ALTER TABLE table_name ADD column_name COLUMN-definition;
Example
ALTER TABLE Employee ADD Address VARCHAR2(20);
TRUNCATE - It is used to delete all the rows from the table and free the space containing the table.
Syntax
TRUNCATE TABLE table_name;
Example
TRUNCATE TABLE EMPLOYEE;
DQL (Data Query Language)
- It is used to fetch the data from the database
SELECT - This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause.
Syntax
SELECT expressions FROM Tables WHERE conditions;
Example
SELECT Email FROM Employee WHERE Name='Vishal';
DML (Data Manipulation language)
- It allows to modify the database instance by inserting, modifying, and deleting its data. It is responsible for performing all types of data modification in a database.
- Its commonly used DML in SQL querying are CRUD, SELECT, INSERT, UPDATE, DELETE.
SELECT - It is used to fetch data records from the database table and present it in the form of a result set.
Syntax
SELECT column_name1, column_name2,..
FROM table_name;
OR
SELECT * FROM table_name;
Example
SELECT EmployeeId, EmployeeName, Designation, Country FROM Employee;
OR
SELECT * FROM Employee;
INSERT - It is used to fetch data records from the database table and present it in the form of a result set.
Syntax
INSERT INTO TABLE_NAME (col1, col2, col3,.... col N)
VALUES (value1, value2, value3, .... valueN);
OR
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3, .... valueN);
Example
INSERT INTO Employee(EmployeeID, EmployeeName, Designation) VALUES (1, 'Vishal Yelve', 'Architect');
UPDATE - It is used to update or modify the value of a column in the table.
Syntax
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]
Example
UPDATE Employee
SET Designation = 'Technical Architect'
WHERE EmployeeId = 1;
DELETE - It is used to remove one or more rows from a table.
Syntax
DELETE FROM table_name [WHERE condition];
Example
DELETE FROM Employee WHERE EmployeeId = 10;
DCL (Data Control Language)
- Its deals with the commands used in SQL that permit a user to access, modify or work on the different privileges in order to control the database.
- Commonly used DCL in SQL are GRANT and REVOKE.
GRANT - It is used to give user access privileges to a database.
Syntax
GRANT SELECT, UPDATE ON TABLE_NAME to USER, SPECIFIC_USER;
Example
GRANT SELECT ON Users TO Vishal@admin;
REVOKE - It is used to take back permissions from the user.
Syntax
REVOKE privilege_name ON object_name FROM {user_name | PUBLIC | role_name}
Example
REVOKE CREATE Table from Vishal;
TCL (Transaction Control Language)
- It's used to manage transactions in the database. These are used to manage the changes made by DML statements. It also allows statements to be grouped together into logical transactions.
- Its commonly used DML in SQL querying are COMMIT, ROLLBACK, and SAVEPOINT.
COMMIT - It is used to save all the transactions to the database.
Syntax
COMMIT;
Example
DELETE FROM Employee WHERE EmployeeId = 25;
COMMIT;
ROLLBACK - It allows you to return or undo any transaction that is not present in the database.
Syntax
ROLLBACK;
Example
DELETE FROM Employee WHERE EmployeeId = 25;
ROLLBACK;
SAVEPOINT - Its helps you to sets a savepoint within a transaction.
Syntax
SAVEPOINT SAVEPOINT_NAME;
Example
SAVEPOINT EmployeeId;
ALIAS - The temporary names given to table or column for the purpose of a particular SQL query. Alias is created with the AS keyword.
Syntax
Example
- Alias Table
SELECT E.EmployeeId, E.EmployeeName, D.DepartmentName
FROM Employee AS E, Department AS D
WHERE E.DeptId=D.DepartmentId;
GROUP BY - It is used to group rows by one or more columns.
Note: GROUP BY clause is used in conjunction with aggregate functions such as MIN(), MAX(), SUM(), AVG() and COUNT(), etc.
Group By Column - Its helps you to sets a savepoint within a transaction.
Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
Example
SELECT COUNT(DepartmentId), DepartmentName
FROM Department
GROUP BY DepartmentName;
Group By Clause With JOIN
Example
SELECT D.DepartmentId, D.DepartmentName, Count(E.EmployeeId) AS Employee_Count
FROM Department D
LEFT JOIN Employee E ON D.DepartmentId = E.DeptId
GROUP BY D.DepartmentId;
Group By With Multiple Columns
SELECT Country, State, MIN(Age) as Min_Age
FROM Employee
GROUP BY Country, State;
HAVING - It is used if we need to filter the result set based on aggregate functions such as MIN() and MAX(), SUM() and AVG() and COUNT().
Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
Example
SELECT COUNT(EmployeeId), Designation
FROM Employee
GROUP BY Designation
HAVING COUNT(EmployeeId) > 5;
Note: HAVING clause was introduced because the WHERE clause does not support aggregate functions. Also, GROUP BY must be used before the HAVING clause.
HAVING Vs WHERE
| HAVING clause checks the condition on a group of rows. |
WHERE clause checks the condition on each individual row. |
| HAVING is used with aggregate functions. |
WHERE clause cannot be used with aggregate functions. |
| HAVING clause is executed after the GROUP BY clause. |
WHERE clause is executed before the GROUP BY clause. |
ORDER BY - It is used to sort the data in ascending or descending order, based on one or more columns.
Note: The ORDER BY clause sorts result set in ascending by default; it's not necessary to use ASC explicitly.
Syntax
SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];
Example
SELECT * FROM Employee
ORDER BY FirstName;
- ORDER BY ASC (Ascending Order) - It is used to sort the data in ascending order (ASC).
SELECT * FROM Employees
ORDER BY Department ASC;
- ORDER BY DESC (Descending Order) - It is used to sort the data in descending order (DESC).
SELECT * FROM Employee
ORDER BY Department DESC;
JOIN - It joins two tables based on a common column, and selects records that have matching values in these columns.
- INNER JOIN - It returns only matching rows. Non matching rows are eliminated.
![Inner Join]()
Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
Example
SELECT E.EmployeeId, E.EmployeeName, D.DepartmentName
FROM Employee E
INNER JOIN Department D ON E.DeptId = D.DepartmentId;
- LEFT JOIN - It returns all the matching rows + non matching rows from the left table.
![Left Join]()
Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
Example
SELECT E.EmployeeId, E.EmployeeName, D.DepartmentName
FROM Employee E
LEFT JOIN Department D ON E.DeptId = D.DepartmentId;
- RIGHT JOIN - It returns all the matching rows + non matching rows from the right table.
![Right Join]()
Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Example
SELECT E.EmployeeId, E.EmployeeName, D.DepartmentName
FROM Employee E
RIGHT JOIN Department D ON E.DeptId = D.DepartmentId
- FULL OUTER JOIN - It returns all rows from both tables, including the non-matching rows.
![Full Join]()
Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
Example
SELECT E.EmployeeId, E.EmployeeName, D.DepartmentName
FROM Employee E
FULL JOIN Department D ON E.DeptId = D.DepartmentId
- CROSS JOIN - It returns Cartesian product of the tables involved in the join.
![CROSS Join]()
Syntax
SELECT ColumnName_1, ColumnName_2, ColumnName_N
FROM [Table_1]
CROSS JOIN [Table_2];
Example
SELECT * FROM Employee E
CROSS JOIN Department D
FUNCTIONS - SQL has many built-in functions for performing calculations on data.
- SQL Aggregate Functions - It is used to do operations from the values of the column and a single value is returned.
- SQL Scalar functions - These functions are based on user input, these too returns single value.
Aggregate functions
AVG() - It returns the average value after calculating from values in a numeric column.
Syntax
SELECT AVG(column_name) FROM table_name;
Example
SELECT AVG(Price) AS Avg_Price FROM Products;
SUM() - It returns the sum of all the values of the selected column.
Syntax
SELECT SUM(column_name) FROM table_name;
Example
SELECT SUM(Price) AS Total_Price FROM Products;
COUNT() - It is used to count the number of rows returned in a SELECT statement.
Syntax
SELECT COUNT(column_name) FROM table_name;
Example
SELECT COUNT(ProductId) AS ProductCount FROM Products;
MIN() - It is used to return the minimum value of the selected column.
Syntax
SELECT MIN(column_name) FROM table_name;
Example
SELECT MIN(Price) AS Min_Price FROM Products;
MAX() - It is used to return the maximum value of the selected column.
Syntax
SELECT MAX(column_name) FROM table_name;
Example
SELECT MAX(Price) AS Max_Price FROM Products;
Scalar functions
UCASE() - It converts the value of a field to uppercase.
Syntax
SELECT UCASE(column_name) FROM table_name;
OR
SELECT UPPER(column_name) FROM table_name;
Example
SELECT UCASE(EmployeeName) FROM Employee;
LCASE() - It converts the value of a field to uppercase.
Syntax
SELECT LCASE(column_name) FROM table_name;
OR
SELECT LOWER(column_name) FROM table_name;
Example
SELECT LCASE(EmployeeName) FROM Employee;
SUBSTRING() - It is used to extract some characters from a string.
Syntax
SELECT SUBSTRING(column_name,start,length) AS some_name FROM table_name;
Example
SELECT SUBSTRING(EmployeeName, 1, 3) as Name FROM Employee
LEN() - It returns the length of a string.
Syntax
SELECT LEN(column_name) FROM table_name;
Example
SELECT EmployeeID, LEN(EmployeeName) as LengthOfEmployeeName FROM Employee;
ROUND() - It is used to round the value and returns it.
Syntax
SELECT ROUND(column_name,decimals) FROM table_name;
Example
SELECT EmployeeName, ROUND(Salary,0) AS Rounded_Salary
FROM Employee;
NOW() - It returns the current system date and time.
Syntax
SELECT NOW() FROM table_name;
Example
SELECT EmployeeName, NOW() AS DateTime FROM Employee;
FORMAT() - It is used to format how a field is to be displayed.
Syntax
SELECT FORMAT(column_name,format) FROM table_name;
Example
SELECT EmployeeName, FORMAT(Now(),'YYYY-MM-DD') AS Date FROM Employee;
WINDOW FUNCTIONS - Window functions operate on a set of rows and return a single aggregated value for each row.
Syntax
window_function ( [ ALL ] expression )
OVER ( [ PARTITION BY partition_list ] [ ORDER BY order_list] );
OVER() - It is used with PARTITION BY to break up the data into partitions
Syntax
<function> OVER ( [PARTITION BY clause]
[ORDER BY clause]
[ROWS or RANGE clause])
Example
SELECT EmployeeName,
Salary,
Department,
COUNT(Department) OVER(PARTITION BY Department) AS DepartmentTotals,
SUM(Salary) OVER(PARTITION BY Department) AS TotalSalary,
AVG(Salary) OVER(PARTITION BY Department) AS AvgSalary,
MIN(Salary) OVER(PARTITION BY Department) AS MinSalary,
MAX(Salary) OVER(PARTITION BY Department) AS MaxSalary
FROM Employees
ROW_NUMBER() - It is basically used when you want to return a sequential number starting from 1.
Syntax
ROW_NUMBER() OVER (
[PARTITION BY expr1, expr2,...]
ORDER BY expr1 [ASC | DESC], expr2,...
)
Example
SELECT
EmployeeName,
Country,
ROW_NUMBER() OVER (PARTITION BY Country ORDER BY EmployeeName) row_num
FROM Employee
ORDER BY Company;
RANK() - It is used to determine the rank for each row in the result set.
Syntax
RANK() OVER (
[PARTITION BY expression]
ORDER BY expression (ASC | DESC) );
Example
SELECT EmployeeName, RANK () OVER ( ORDER BY EmployeeName) AS Rank_no
FROM Employee;
OR
SELECT EmployeeName, Designation,
RANK () OVER (PARTITION BY Designation ORDER BY EmployeeName) AS Rank_No
FROM Employee;
2nd Highest Salary using RANK()
-- Fetch the 2nd Hight Salary
WITH EmployeeCTE AS
(
SELECT Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank_Salry
FROM Employees
)
SELECT TOP 1 Salary FROM EmployeeCTE WHERE Rank_Salry = 2
DENSE_RANK() - It assigns a unique rank for each row within a partition as per the specified column value without any gaps. It always specifies ranking in consecutive order. If we get a duplicate value, this function will assign it with the same rank, and the next rank being the next sequential number. This characteristic differs DENSE_RANK() function from the RANK() function.
Syntax
DENSE_RANK() OVER (
[PARTITION BY expression]
ORDER BY expression [ASC|DESC]);
Example
SELECT EmployeeName, Department, Salary,
DENSE_RANK() OVER (
PARTITION BY Department
ORDER BY Salary DESC) AS [DenseRank]
FROM Employees;
2nd Highest Salary using DENSE_RANK()
-- Fetch the 2nd Hight Salary
WITH EmployeeCTE AS
(
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank_Salry
FROM Employees
)
SELECT TOP 1 Salary FROM EmployeeCTE WHERE DenseRank_Salry = 2
NTILE() - It distributes rows of an ordered partition into a pre-defined number of roughly equal groups. It assigns each group a number_expression ranging from 1. NTILE() function assigns a number_expression for every row in a group, to which the row belongs.
Syntax
NTILE(number_expression) OVER (
[PARTITION BY partition_expression ]
ORDER BY sort_expression [ASC | DESC]
)
Example
SELECT EmployeeName, Designation, Country,
NTILE(3) OVER(ORDER BY Country) AS my_rank
FROM Employee;
LAG() - It is very useful in case the current row values need to be compared with the data/value of the previous record or any record before the previous record. The previous value can be returned on the same record without the use of self join making it straightforward to compare.
Syntax
LAG (scalar_expression [, offset] [, default])
OVER ( [ partition_by ] order_by )
Example
SELECT EmployeeId, FYear, Salary,
LAG(Salary) OVER (
PARTITION BY EmployeeId
ORDER BY FYear) PreviousSalary
FROM Employee;
LEAD() - It provides access to a row at a set physical offset following this row. LEAD() function will allow to access data of the following row, or the row after the subsequent row, and continue on.
Syntax
LEAD(return_value, offset [, default])
OVER (
[PARTITION BY partition_expression]
ORDER BY sort_expression [ASC | DESC]
)
Example
SELECT EmployeeName, Gender, Salary,
LEAD(Salary, 2, -1) OVER (ORDER BY Salary) AS Lead_2
FROM Employees
WHERE - It specifies criteria that field values must meet for the records that contain the values to be included in the query results.
Syntax
SELECT column1, column2, columnN
FROM table_name
WHERE [condition]
Example
SELECT * FROM Employee
WHERE Country='India';
Operators in The WHERE Clause
| Operator |
Description |
| > |
Greater Than |
| >= |
Greater than or Equal to |
| < |
Less Than |
| <= |
Less than or Equal to |
| = |
Equal to |
| <> |
Not Equal to |
Examples
SELECT EmployeeId, EmployeeName, Salary
FROM Employee
WHERE Salary > 20000;
SELECT EmployeeId, EmployeeName, Salary
FROM Employee
WHERE Salary >= 20000;
SELECT EmployeeId, EmployeeName, Salary
FROM Employee
WHERE Salary < 20000;
SELECT EmployeeId, EmployeeName, Salary
FROM Employee
WHERE Salary <= 20000;
SELECT EmployeeId, EmployeeName, Salary
FROM Employee
WHERE Salary = 20000;
SELECT EmployeeId, EmployeeName, Salary
FROM Employee
WHERE Salary <> 20000;
AND, OR, NOT - it is used to combine multiple conditions.
AND - It displays a record if all the conditions separated by AND are TRUE.
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
Example
Select* From Employees
WHERE Designation='Developer' AND Country='UK';
OR - Its displays a record if any of the conditions separated by OR is TRUE.
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
Example
SELECT * FROM Employee
WHERE Country='UK' OR Country='United Kingdom';
NOT - It displays a record if the condition(s) is NOT TRUE.
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
Example
SELECT * FROM Employee
WHERE NOT Country='UK';
BETWEEN - It's used to fetch filtered data in a given range inclusive of two values.
Syntax
SELECT column1,column2
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Example
Select * FROM Employee
WHERE Salary BETWEEN 20000 AND 50000;
LIKE - It is used to fetch filtered data by searching for a particular pattern in where clause.
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
Example
Select * From Employee
WHERE EmployeeName LIKE 'A%';
Note: It will select all Employees with a EmployeeName starting with "A"
Select * From Employee
WHERE EmployeeName LIKE '%A';
Note: It will select all Employees with a EmployeeName ending with "A"
Select * From Employee
WHERE EmployeeName LIKE '%AM%';
Note: It will select all Employees with a EmployeeName contains the pattern "AM"
IN - It is used to specify the list of values or sub query in the WHERE clause. A sub-query or list of values must be specified in the parenthesis
Syntax
SELECT column1, column2,..
FROM table
WHERE column IN (value1, value2, value3,...);
-- OR -----
SELECT column1, column2,..
FROM table
WHERE column IN (SELECT query);
Example
Select * From Employee
WHERE Designation IN ('Architect', 'Technical Lead', 'Sr. Developer');
SELECT EmployeeId, EmployeeName, Designation, DeptId
FROM Employee
WHERE DeptId IN (SELECT ID from Department WHERE ID > 3);
ANY - It compares a value to each value in a list or results from a query and evaluates to true if the result of an inner query contains at least one row.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name comparison_operator ANY
(SELECT column_name
FROM table_name
WHERE condition(s));
Example
SELECT * FROM Employee
WHERE DeptId = ANY(
SELECT DepartmentId FROM Department
WHERE DepartmentName = 'IT' OR DepartmentName = 'Operations'
);
ALL - It is used to select all tuples of SELECT STATEMENT. It is also used to compare a value to every value in another value set or result from a subquery.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);
Example
SELECT * FROM Employee
WHERE EmployeeId = ALL(
SELECT EmployeeId FROM Employee_backup
WHERE Salary > 15000
);
EXIST - It is used in combination with a subquery and is considered to be met, if the subquery returns at least one row. It can be used in a SELECT, UPDATE, INSERT or DELETE statement.
Syntax
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
Example
// EXISTS
SELECT EmployeeId, EmployeeName, EmployeeLocation
FROM Employee
WHERE EXISTS (
SELECT 1
FROM Department, Location
WHERE Department.EmployeeId = Employee.EmployeeId
);
// NOT EXISTS
SELECT EmployeeName
FROM Employee
WHERE NOT EXISTS (
SELECT *
FROM Department
WHERE Department.EmployeeId = Employee.DepartmentId
);
// EXISTS with DELETE
DELETE
FROM Employee
WHERE EXISTS (SELECT *
FROM Employee
WHERE Employee.FirstName = 'Vishal');
// EXISTS with UPDATE
UPDATE Employee
SET Country = 'India'
WHERE EXISTS ( SELECT * From Employee WHERE EmployeeId = 10220);
Conclusion
In this article, I have tried to explain what is SQL. Which will help many fresher and experienced developers.
Thanks You & Happy Learning..!!