SQL stands for Structured Query Language. SQL is a standard language for storing, manipulating, and retrieving data in databases. SQL statements are used to perform tasks such as updating data on a database or retrieving data from a database.
There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.
Data Definition Language (DDL) defines the structure of the database like creating a table, deleting a table, or altering a table.
Data Manipulation Language (DML) commands are used to modify the database such as Insert, Update, and Delete.
Data Control Language (DCL) commands are used to grant and take back authority from any database user such as Grant and Revoke.
Transaction Control Language (TCL) commands can only be used with DML commands like Insert, Delete and Update. These commands include Commit, Rollback, and Savepoint.
Data Query Language (DQL) is used to fetch the data from the database. It uses only one command: Select.
In this blog post, I am sharing some basic and advanced SQL statements so that they could serve as a handy reference for you while accessing a database. SQL Syntax vary slightly based on the database used such as SQL Server, Oracle, or Microsoft Access. In this post, I have mostly used SQL Server Database.
Show all the columns from a table (square brackets and database name are optional):
SELECT * FROM [HumanResources].[Employee]
Show the loginid and gender column:
SELECT [LoginID],[Gender] FROM [HumanResources].[Employee]
Use alias for the column name:
SELECT Gender g FROM [HumanResources].[Employee]
Use alias for the table name:
SELECT e.Gender FROM [HumanResources].[Employee] e
Select distinct values:
SELECT DISTINCT Employee.Gender FROM [HumanResources].[Employee]
Show the first 10 rows of the table:
SELECT TOP 10 e.[BusinessEntityID], e.Gender g FROM [HumanResources].[Employee] e
Sorting the results:
SELECT [BusinessEntityID] FROM [HumanResources].[Employee] ORDER BY [BusinessEntityID] desc
Filter the data by using WHERE clause:
select [BusinessEntityID], [JobTitle] from [HumanResources].[Employee] where JobTitle='Design Engineer'
Search for a pattern by using LIKE:
SELECT [BusinessEntityID], [JobTitle] FROM [HumanResources].[Employee] WHERE JobTitle LIKE 'Design%'
Filter for specific values by using IN operator:
SELECT [BusinessEntityID],JobTitle FROM [HumanResources].[Employee] WHERE JobTitle in ('Engineering Manager','Senior Tool Designer')
Aggregate the data by using SUM and AVG functions:
SELECT SUM([SubTotal]) AS SUBTOTAL,AVG([SubTotal]) AS AVERAGESUBTOTAL FROM [Sales].[SalesOrderHeader]
Get the sum of the orderQty column, group the information by salesorderid, and ordering the sum in descending order. Note that the GROUP BY clause will be executed first to group the data by salesorderid. Then the sum function will add the quantities ordered for that salesorderid:
SELECT SUM([OrderQty]) AS Qty, [SalesOrderID] FROM [Sales].[SalesOrderDetail] GROUP BY [SalesOrderID] ORDER BY SUM([OrderQty]) DESC
Having Clause for Aggregate Functions
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC
SELECT customerName, customercity, customermail, salestotal FROM onlinecustomers INNER JOIN orders ON onlinecustomers.customerid = orders.customerid INNER JOIN sales ON orders.orderId = sales.orderId
Left Join (Will display all customers and their orders if any):
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName
Right Join (Will display all employees and their orders if any):
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID
Full Join (Will display all customers and all orders)
SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName
Self Join (matches customers that are from the same city)
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City FROM Customers A, Customers B WHERE A.CustomerID <> B.CustomerID AND A.City = B.City ORDER BY A.City
Exists (List the suppliers with a product price equal to 22)
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22)
Here SP1 is first SAVEPOINT created before deletion. Deletion is then undone:
SAVEPOINT SP1; DELETE FROM Student WHERE AGE = 20; SAVEPOINT SP2; ROLLBACK TO SP1;
SQL Commands: DDL, DML, DCL, TCL, DQL
SQL Queries in SQL Server – a beginner’s guide
SQL multiple joins for beginners with examples
SQL Server Interview Questions and Answers by kudvenkat