sql_cheat_sheet.md

SQL Cheat Sheet

Glossary

SQL

SQL is a standard language for accessing and manipulating databases. It stands for Structured Query Language.

RDBMS

RDBMS stands for Relational Database Management System. It is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

Table

A table is a collection of related data entries and it consists of columns and rows. The data in RDBMS is stored in database objects called tables.

Semicolon

A semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

SQL Comments

Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements.

Single Line Comments(--)

--Select all:
SELECT * FROM Customers;

SELECT * FROM Customers -- WHERE City='Berlin';

Multi-line Comments (start with /* and end with */)

/*SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;
SELECT * FROM Categories;*/
SELECT * FROM Suppliers;

SQL Stored Procedures for SQL Server

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

Stored Procedure Syntax

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute a Stored Procedure

EXEC procedure_name;

Stored Procedure Example

The following SQL statement creates a stored procedure named "SelectAllCustomers" that selects all records from the "Customers" table:

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Execute the Stored Procedure

EXEC SelectAllCustomers;

Stored Procedure With One Parameter

The following SQL statement creates a stored procedure that selects Customers from a particular City from the "Customers" table:

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

Execute the Stored Procedure

EXEC SelectAllCustomers @City = 'London';

Stored Procedure With Multiple Parameters

The following SQL statement creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the "Customers" table:

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

Execute the Stored Procedure

EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';   

Data Control Language (DCL) Commands

Command	Description	Example(s)	Illustration(s)
GRANT	Used to assign permission to users to access database objects.
REVOKE	Used to deny permission to users to access database objects.

Data Definition Language (DDL) Commands

Command	Description	Example(s)	Illustration(s)
ALTER	Adds, deletes, or modifies columns in a table, or changes the data type of a column in a table.
BACKUP DATABASE	Creates a back up of an existing database.	For MS SQL Server BACKUP DATABASE databasename TO DISK = 'filepath';	For MS SQL Server BACKUP DATABASE testDB TO DISK = 'D:\backups\testDB.bak';
CREATE	Creates a database, index, view, table, or procedure. Note: Make sure you have admin privilege before creating any database. Once a database is created, you can check it in the list of databases with the following SQL command: SHOW DATABASES;	CREATE DATABASE databasename;	CREATE DATABASE testDB;
CREATE TABLE	Creates a new table in the database.	Create a Single Table CREATE TABLE table_name ( __column1 datatype, __column2 datatype, .... ); Create a Table From Another Table CREATE TABLE new_table_name AS __SELECT column1, column2,... __FROM existing_table_name __WHERE ....;	Create a Single Table CREATE TABLE Persons ( __PersonID int, __LastName varchar(255), __FirstName varchar(255), ); Create a Table From Another Table CREATE TABLE TestTable AS SELECT customername, contactname FROM customers;
DROP	Deletes a column, constraint, database, index, table, or view. Note: Be careful before dropping a database. Deleting a database will result in loss of complete information stored in the database!	DROP DATABASE databasename;	DROP DATABASE testDB;

Data Manipulation Language (DML) Commands

Command	Description	Example(s)	Illustration(s)
DELETE	Delete rows from a table	DELETE FROM table_name WHERE condition;	DELETE Single Record DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste'; DELETE All Records DELETE FROM Customers;
INSERT INTO	Inserts new rows in a table. Note: You do not insert values into a primary key like id, CustomerID, since it is an auto-incremented field	INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, value3, ...);	INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');
INSERT INTO SELECT	Copies data from one table into another table	Copy all columns from one table to another table INSERT INTO table2 SELECT * FROM table1 WHERE condition; Copy only some columns from one table into another table INSERT INTO table2 (column1, column2, ...) SELECT column1, column2, ... FROM table1 WHERE condition;	Copy all columns from one table to another table INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers; Copy only some columns from one table into another table INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers WHERE Country='Germany';
UPDATE	Updates existing rows in a table Note: Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!	UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;	UPDATE Single Record UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1; UPDATE Multiple Records UPDATE Customers SET ContactName='Juan' WHERE Country='Mexico';

Data Query Language (DQL) Commands

Command	Description	Example(s)	Illustration(s)
SELECT	Select data from a database	Select all columns SELECT * FROM table_name; Select some columns SELECT column1, column2, ... FROM table_name;	Select all columns SELECT * FROM Customers; Select some columns SELECT CustomerName, City FROM Customers;
SELECT DISTINCT	Selects only distinct (different) values	SELECT DISTINCT column1, column2, ... FROM table_name; SELECT COUNT(DISTINCT column) FROM table_name;	SELECT DISTINCT Country FROM Customers; SELECT COUNT(DISTINCT Country) FROM Customers;
SELECT INTO	Copy all columns into a new table	Copies data from one table into a new table SELECT * INTO newtable [IN externaldb] FROM oldtable WHERE condition; Copy only some columns into a new table SELECT column1, column2, ... INTO newtable [IN externaldb] FROM oldtable WHERE condition;	Copy all columns into a new table SELECT * INTO CustomersBackup2017 FROM Customers; SELECT * INTO CustomersBackup2017 FROM Customers WHERE Country = 'Germany'; Copy only some columns into a new table SELECT CustomerName, ContactName INTO CustomersBackup2017 FROM Customers; SELECT Customers.CustomerName, Orders.OrderID INTO CustomersOrderBackup2017 FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Data Transfer Language (DTL) Commands

Command	Description	Example(s)	Illustration(s)
COMMIT	Used to save any transaction into the database permanently.
ROLLBACK	Restores the database to the last committed state.
SAVEPOINT	Creates points within groups of transactions in which to ROLLBACK

SET TRANSACTION Places a name on a transaction

Clauses

Command	Description	Example(s)	Illustration(s)
CASE	Creates different outputs based on conditions. Note: If there is no ELSE part and no conditions are true, it returns NULL.	CASE __WHEN condition1 THEN result1 __WHEN condition2 THEN result2 __WHEN conditionN THEN resultN __ELSE result END;	SELECT OrderID, Quantity, CASE __WHEN Quantity > 30 THEN 'The quantity is greater than 30' __WHEN Quantity = 30 THEN 'The quantity is 30' __ELSE 'The quantity is under 30' END AS QuantityText FROM OrderDetails; SELECT CustomerName, City, Country FROM Customers ORDER BY (CASE __WHEN City IS NULL THEN Country __ELSE City END);
FULL (OUTER) JOIN	Returns all records when there is a match in either left or right table. Note: The FULL OUTER JOIN keyword returns all matching records from both tables whether the other table matches or not.	SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;	Find values when the Customers Table belongs to the Orders Table (CustomerID is a foreign key in the Orders Table) SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName; Find values when the Users Table belongs to the Applications Table (UserID is a foreign key in the Applications Table) SELECT Users.id, Applications.id, Users.first_name, Users.email, Users.created_at FROM applications FULL OUTER JOIN Users ON Users.id = Applications.user_id WHERE appref IS NULL AND Users.created_at BETWEEN '2018-11-04' AND '2020-01-01' ORDER BY Users.created_at
HAVING	Used instead of WHERE with aggregate functions. Note: The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.	SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);	SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5; SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC; SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10; SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID WHERE LastName = 'Davolio' OR LastName = 'Fuller' GROUP BY LastName HAVING COUNT(Orders.OrderID) > 25;
INNER JOIN	Returns records that have matching values in both tables	SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;	Find values when the Customers Table belongs to the Orders Table (CustomerID is a foreign key in the Orders Table) SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; Find values when the Customers Table and the Shipper's Table belong to the Orders Table (CustomerID and the ShipperID are foreign keys in the Orders Table) SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM ((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
IS NULL	Tests for empty values	SELECT column_names FROM table_name WHERE column_name IS NULL;
IS NOT NULL	Tests for non-empty values	SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
LEFT (OUTER) JOIN	Returns all records from the left table, and the matched records from the right table. Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).	SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;	SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName;
LIMIT	Specifies the number of records to return in the result set Note: MySQL and PostgreSQL supports the LIMIT clause	SELECT column_name(s) FROM table_name WHERE condition LIMIT number;	SELECT * FROM Customers WHERE Country='Germany' LIMIT 3;
RIGHT (OUTER) JOIN	Returns all records from the right table, and the matched records from the left table. Note: The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).	SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;	SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID;
ROWNUM	Specifies the number of records to return in the result set Note: Oracle supports the ROWNUM clause	SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;	SELECT * FROM Customers WHERE Country='Germany' AND ROWNUM <= 3;
SELF JOIN	THis is a regular join, but the table is joined with itself.	SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;	FROM Customers A, Customers B WHERE A.CustomerID <> B.CustomerID AND A.City = B.City ORDER BY A.City;
TOP	Specifies the number of records to return in the result set Note: MS Access supports the ROWNUM clause	SELECT TOP numberIpercent column_name(s) FROM table_name WHERE condition;	SELECT TOP 3 * FROM Customers WHERE Country='Germany';
WHERE	Filters a result set to include only records that fulfill a specified condition. Note: SQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes.	SELECT column1, column2, ... FROM table_name WHERE condition;	SELECT * FROM Customers WHERE Country='Mexico'; SELECT * FROM Customers WHERE CustomerID=1; SELECT * FROM Customers WHERE Date='2014-04-23';

Functions

Command	Description	Example(s)	Illustration(s)
AVG()	Returns the average value of a numeric column. Note: NULL values are ignored.	SELECT AVG(column_name) FROM table_name WHERE condition;	SELECT AVG(Price) FROM Products;
COALESCE()	Returns an alternative value if an expression is NULL. Note: MySQL and PostgreSQL supports the COALESCE() function		SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products;
COUNT()	Returns the number of rows that matches a specified criteria. Note: NULL values are not counted.	SELECT COUNT(column_name) FROM table_name WHERE condition;	SELECT COUNT(ProductID) FROM Products;
IFNULL()	Returns an alternative value if an expression is NULL. Note: MySQL and PostgreSQL supports the IFNULL() function		SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products;
ISNULL()	Returns an alternative value when an expression is NULL. Note: SQL Server supports the ISNULL() function.		SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0)) FROM Products;
MAX()	Returns the largest value of the selected column.	SELECT MAX(column_name) FROM table_name WHERE condition;	SELECT MAX(Price) AS LargestPrice FROM Products;
MIN()	Returns the smallest value of the selected column.	SELECT MIN(column_name) FROM table_name WHERE condition;	SELECT MIN(Price) AS SmallestPrice FROM Products;
NVL()	Returns an alternative value if an expression is NULL. Note: Oracle supports the NVL() function		SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0)) FROM Products;
SUM()	Returns the total sum of a numeric column.	SELECT SUM(column_name) FROM table_name WHERE condition;	SELECT SUM(Quantity) FROM OrderDetails;

Operators

Command	Description	Example(s)	Illustration(s)
ALL	Returns true if all of the subquery values meet the condition. Note: The ALL operator returns true if all of the subquery values meet the condition.	SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);	SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
AND	Only includes rows where both conditions is true	SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2...;	SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin';
ANY	Returns true if any of the subquery values meet the condition. Note:The ANY operator returns true if any of the subquery values meet the condition.	SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);	SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10); SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);
AS	Renames a column or table with an alias. Note:SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of the query.	Alias Column Syntax SELECT column_name AS alias_name FROM table_name; Alias Table Syntax SELECT column_name(s) FROM table_name AS alias_name;	Alias for Columns Examples SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers; Alias for Tables Example SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;
BETWEEN	Selects values within a given range	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;	SELECT * FROM Products WHERE Price BETWEEN '2018-10-02' AND '2020-04-26'; SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20; SELECT * FROM Products WHERE Price BETWEEN 10 AND 20 AND CategoryID NOT IN (1,2,3);
EXISTS	Tests for the existence of any record in a subquery. Note: The EXISTS operator returns true if the subquery returns one or more records.	SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);	SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20); SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);
IN	Allows you to specify multiple values in a WHERE clause	SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);	SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK'); SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK');
LIKE	Searches for a specified pattern in a column.	SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;	Finds values that start with "a" SELECT * FROM Customers WHERE CustomerName LIKE 'a%'; Finds values that end with "a" SELECT * FROM Customers WHERE CustomerName LIKE '%a'; Finds values that have "or" in any position SELECT * FROM Customers WHERE CustomerName LIKE '%or%'; Finds values that have "r" in the second position SELECT * FROM Customers WHERE CustomerName LIKE '_r%'; Finds values that start with "a" and ends with "o" SELECT * FROM Customers WHERE ContactName LIKE 'a%o'; Finds values starting with "L", followed by any character, followed by "n", followed by any character, followed by "on" SELECT * FROM Customers WHERE CustomerName LIKE 'L_n_on'; Finds values that start with "b", "s", or "p" SELECT * FROM Customers WHERE CustomerName LIKE '[bsp]%'; Finds values that start with "a", "b", or "c": SELECT * FROM Customers WHERE CustomerName LIKE '[a-c]%'; Finds values that does NOT start with "a" SELECT * FROM Customers WHERE CustomerName NOT LIKE 'a%'; Finds values that does NOT start with "bsp" SELECT * FROM Customers WHERE CustomerName LIKE '[!bsp]%';
NOT	Only includes rows where a condition is not true	SELECT column1, column2, ... FROM table_name WHERE NOT condition;	SELECT * FROM Customers WHERE NOT Country='Germany'; SELECT * FROM Customers WHERE NOT Country='Germany' AND NOT Country='USA';
OR	Includes rows where either condition is true	SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 ...;	SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');
UNION	Combines the result set of two or more SELECT statements. Note: Only distinct values	SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;	SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City; SELECT City, Country FROM Customers WHERE Country='Germany' UNION SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City; SELECT 'Customer' As Type, ContactName, City, Country FROM Customers UNION SELECT 'Supplier', ContactName, City, Country FROM Suppliers; SELECT 'Customer' As Type, ContactName, City, Country FROM Customers UNION SELECT 'Supplier', ContactName, City, Country FROM Suppliers;
UNION ALL	Combines the result set of two or more SELECT statements. Note: Allows duplicate values	SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;	SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City; SELECT City, Country FROM Customers WHERE Country='Germany' UNION ALL SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;

Other Operators

Operator	Description	Example(s)	Illustration(s)
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<> OR !=	Not equal

Sorting

Command	Description	Example(s)	Illustration(s)
GROUP	Groups the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG)	SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);	SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country; SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC; SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID GROUP BY ShipperName;
ORDER	Sorts the result set in ascending or descending order	SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASCIDESC;	Order by Single Column SELECT * FROM Customers ORDER BY Country; Order by Single Column SELECT * FROM Customers ORDER BY Country DESC; Order by Several Columns SELECT * FROM Customers ORDER BY Country, CustomerName; Order by Several Columns SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;

Resources

1. W3 Schools SQL Tutorial
2. SQL Cheat Sheet