Learn SQL (Structured Query Language) by Examples

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

Inner Join:

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;

Useful Resources:

SQL Commands: DDL, DML, DCL, TCL, DQL
https://www.javatpoint.com/dbms-sql-command

SQL Queries in SQL Server – a beginner’s guide
https://www.sqlshack.com/sql-queries-in-sql-server-a-beginners-guide

SQL multiple joins for beginners with examples
https://www.sqlshack.com/sql-multiple-joins-for-beginners-with-examples

SQL Joins
https://www.w3schools.com/sql/sql_join.asp

SQL Transactions
https://www.geeksforgeeks.org/sql-transactions

SQL Server Interview Questions and Answers by kudvenkat
https://www.youtube.com/playlist?list=PL6n9fhu94yhXcztdLO7i6mdyaegC8CJwR

Leave a Reply

Your email address will not be published.