Structured Query Language (SQL)

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.

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

• Alias Column

  SELECT column_name AS alias_name
  FROM table_name;

• Alias Table

  SELECT column_name(s)
  FROM table_name AS alias_name;

Example

• Alias Column

  SELECT ID As EmployeeId, EmployeeName As Name
  FROM Employee;

• 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.

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.

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.

 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.

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.

   

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..!!