SQL Cheat Sheet

This is our SQL cheat sheet. It is a too long/detailed to be a cheat sheet but to short to be a tutorial.

Operators: + - * / % & | ^ = > < >= <= <> += -= *= /= %= &= ^-= |*=
ALL AND ANY BETWEEN EXISTS IN LIKE NOT OR SOME

Common MySQL types:
INT(size), BLOB, VARCHAR(size)

/*Comment */
SELECT CustomerName, /*City,*/ Country FROM Customers; -- comment
SELECT column1, column2, FROM table_name;
SELECT * FROM table_name;.
SELECT DISTINCT col1, col2 FROM table_name;
SELECT COUNT(DISTINCT Country) FROM Customers;
SELECT * FROM Customers WHERE Country='Mexico';
SELECT * FROM Customers ORDER BY Country DESC; -- or ASC
SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;
INSERT INTO Customers (a, b) VALUES ('abc', 'xyz');
...WHERE col1 IS NULL;
...WHERE col1 IS NOT NULL;
UPDATE Customers SET ContactName = 'Alfred', City= 'Frankfurt' WHERE CustomerID = 1;
UPDATE Customers SET ContactName='Juan'; -- NO!!!!
DELETE FROM Customers WHERE CustomerName='Alfreds';
DELETE FROM table_name; -- NO!!!!
DELETE * FROM table_name; -- NO!!!!
TOP / Limit -- different in MySQL, Oracle, etc.
SELECT MIN(column_name)....
SELECT MAX(column_name)....
SELECT COUNT(column_name) ....
SELECT AVG(column_name) ....
SELECT SUM(column_name) ....
SELECT * FROM table1 WHERE col1 LIKE ‘a_b%c’
WHERE City LIKE '[!bsp]%'; -- access/mssql only
% - zero, one, or multiple
_ - single char, mysql, mssql/access use ? instead
WHERE Country IN ('Germany', 'France', 'UK');
WHERE Country NOT IN ('Germany', 'France', 'UK');
WHERE Country IN (SELECT Country FROM Suppliers);
WHERE Price BETWEEN 10 AND 20; -- inclusinve
WHERE Price NOT BETWEEN 10 AND 20;
WHERE Price NOT BETWEEN ‘abc’ AND ‘xyz’;
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;
WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);
SELECT CustomerID as ID, CustomerName AS Customer FROM Customers;
SELECT o.OrderID, c.CustomerName FROM Customers AS c, Orders AS o;

(INNER) JOIN: matched from both tables
LEFT (OUTER) JOIN: all from left, matched from right
RIGHT (OUTER) JOIN: matched from the left, all from the right
FULL (OUTER) JOIN: matched in either table

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name =table2.column_name;
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); SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name =table2.column_name;

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name =table2.column_name;
-- self join, ( all customers from same city )
SELECT A.CustomerName, B.CustomerName, A.City FROM Customers A, Customers B WHERE A.ID <> B.ID AND A.City = B.City;
-- same cols, types, order
SELECT col1 FROM table1 UNION SELECT col1 FROM table2;
SELECT col1 FROM table1 UNION ALL SELECT col1 FROM table2; -- include dups
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;
SELECT SupplierName FROM Sup WHERE EXISTS (SELECT * FROM Products WHERE id = Sup.id);
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10); SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10); SELECT * INTO CustomersBackup2017 FROM Customers;
SELECT * INTO CustomersBackup2017 IN 'Backup.mdb' FROM Customers;
INSERT INTO table2 SELECT * FROM table1 WHERE condition;
INSERT INTO table2 (col1, col2) SELECT col1, col2 FROM table1 WHERE col3 = 5;
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products --MSSQL, Access, Oracle do it different …
CREATE DATABASE testDB;
DROP DATABASE testDB;
CREATE TABLE Persons (
PersonID int,
LastName varchar(255)
);
CREATE TABLE new_table_name AS SELECT col1, col2 FROM existing_table_name;
DROP TABLE Shippers; -- delete table
TRUNCATE TABLE table_name; -- delete data
ALTER TABLE table_name ADD col1 date;
ALTER TABLE table_name DROP COLUMN col1;
ALTER TABLE table_name MODIFY COLUMN col1 year; --mysql only, others differ
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL
UNIQUE (ID)
); --mysql only
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
); --multi col, all dbs
ALTER TABLE Persons ADD UNIQUE (ID);
ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
ALTER TABLE Persons DROP INDEX UC_Person; --multi col constraint, mysql only
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
PRIMARY KEY (ID)
); -- mysql only,
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
); -- multi col, named, mysql only
ALTER TABLE Persons ADD PRIMARY KEY (ID);
ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName); -- multi col, named
ALTER TABLE Persons DROP PRIMARY KEY; --mysql only
CREATE TABLE Orders (
OrderID int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);--mysql only
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
); -- named, all dbs
ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;--mysql only
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
); -- mysql only
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
); -- named all dbs
ALTER TABLE Persons ADD CHECK (Age>=18);
ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
ALTER TABLE Persons DROP CHECK CHK_PersonAge; --mysql only
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
CREATE TABLE Orders (
ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);
ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes'; -- mysql only
ALTER TABLE Persons
ALTER City DROP DEFAULT; -- mysql only
CREATE INDEX index_name
ON table_name (column1, column2, ...); -- dups
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...); -- no dups
ALTER TABLE table_name DROP INDEX index_name; -- mysql only CREATE TABLE Persons (
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
); -- mysql only
ALTER TABLE Persons AUTO_INCREMENT=100; -- mysql only
MYSQL only: DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
YEAR - format YYYY or YY
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
CREATE VIEW [Current Product List] AS
SELECT ProductID, ProductName
FROM Products
WHERE Discontinued = No;
SELECT * FROM [Current Product List];
CREATE OR REPLACE VIEW [Current Product List] AS
SELECT ProductID, ProductName, Category
FROM Products
WHERE Discontinued = No;
DROP VIEW view_name;