Skip to content

Latest commit

 

History

History
160 lines (126 loc) · 21.4 KB

sql_cheat_sheet.md

File metadata and controls

160 lines (126 loc) · 21.4 KB

SQL Cheat Sheet

Glossary

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';

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;

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.
CREATE Creates a database, index, view, table, or procedure.
DROP Deletes a column, constraint, database, index, table, or view

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 * FROM table_name;

SELECT column1, column2, ...
FROM table_name;
SELECT DISTINCT Selects only distinct (different) values SELECT DISTINCT column1, column2, ...
FROM table_name;

SELECT COUNT(DISTINCT column)
FROM table_name;
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;		 SELECT
Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
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 SELECT column1, column2, ...
FROM table_name
WHERE condition="Promise"

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;

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;		 SELECT * FROM Customers
ORDER BY Country;

SELECT * FROM Customers
ORDER BY Country DESC;

SELECT * FROM Customers
ORDER BY Country, CustomerName;

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

Resources

  1. W3 Schools SQL Tutorial