Professor Adler's SQL Cheatsheet

SQL Statements

SQL Statement Syntax Example Description
CREATE TABLE CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name2 data_type,
...
)		 CREATE TABLE Employees(
empID INT,
lastName VARCHAR(15),
firstName VARCHAR(15),
salary DECIMAL(9, 2)) 		Creates a table
DROP TABLE DROP TABLE table_name DROP TABLE Employees Removes a table
INSERT INTO INSERT INTO table_name
VALUES (value1, value2, value3,....)

or

INSERT INTO table_name
(column1, column2, column3,...)
VALUES (value1, value2, value3,....)

 INSERT INTO Employees
VALUES (12345, 'Simpson', 'Bart', 100000.50) 		Inserts rows into a table
SELECT SELECT column_name(s)
FROM table_name		 Select lastname from Employees Retrieve specific columns from the database for all employees
SELECT * SELECT *
FROM table_name		 SELECT * FROM Employees Retrieve all columns from specified table for all employees.
SELECT DISTINCT SELECT DISTINCT column_name(s)
FROM table_name		 SELECT DISTINCT salary
FROM Employees		 Retrieve unique column value from specified table.
ORDER BY SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]		 SELECT * FROM Employees
ORDER BY lastName		 Can order the retrieved data
UPDATE UPDATE table_name
SET column1=value, column2=value,...
WHERE some_column=some_value		 UPDATE Employees
SET firstName='Marge'
where empID=12345		 Update any values in the database
WHERE SELECT column_name(s)
FROM table_name
WHERE column_name operator value		 SELECT lastName
FROM Employees
WHERE salary>100000		 Retrieve only certain rows -use Where
DELETE DELETE FROM table_name
WHERE some_column=some_value

or

DELETE FROM table_name
(Note: Deletes the entire table!!)

DELETE FROM Employees
WHERE empID=12345		 Delete rows from specified table
AND / OR SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition		 SELECT * FROM Employees
WHERE SALARY>100000 AND lastName='Simpson'		 You can use and and or in your Where condition to make it more or less specific
AS (alias) SELECT column_name AS column_alias
FROM table_name

or

SELECT column_name
FROM table_name  AS table_alias

 SELECT AVG(salary) as AverageSalary
FROM Employees		 'AS' lets you create a name for the returned column
BETWEEN SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2		 SELECT * FROM Employees
WHERE salary BETWEEN 50000 and 100000 		You can specify between values. (You can also use <= >= etc)
IN SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)		 SELECT * FROM Employees
WHERE lastName in ('Simpson', 'Adler', 'Smith', 'Pitt')		 Instead of using OR many times you can use IN
LIKE SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern		 SELECT * FROM Employees
WHERE lastname LIKE '%SIMP%'		 Will return something that has those letters in it
GROUP BY SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name		 SELECT jobTitle, COUNT(*)
FROM Employees
GROUP BY jobTitle		 The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
HAVING SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value		 SELECT deptNo, COUNT(empID)
FROM Department
GROUP BY deptNo
HAVING count(empID)>1		 The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
INNER JOIN Different Ways to do this. Two Variations:
SELECT column_name(s)
FROM table_name1 t1, table_name2 t2
WHERE t1.column_name=t2.column_name

OR

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name 		Select e.empID, department, salary
FROM Employee e, Departments d
WHERE e.empID=d.empID		 Combine Data from two or more tables. Any unmatched row is omitted from the table.
LEFT JOIN SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name		 SELECT e.empID, department, salary
FROM Employees e LEFT JOIN Department d
ON e.empID = d.empID 		Retains rows that do not satisfy the condition. Returns all rows from the left table, with the matching rows in the right table. The result is NULL in the right side when there is no match.
RIGHT JOIN SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name		 SELECT e.empID, department, salary
FROM Employees e RIGHT JOIN Department d
ON e.empID = d.empID		 Retains rows that do not satisfy the condition. Returns all rows from the right table, with the matching rows in the left table. The result is NULL in the left side when there is no match.
UNION SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2		 SELECT city
FROM Branch
UNION
SELECT city
FROM PropertyForRent		 Combines the result of two or more SELECT statements.
ALTER TABLE ALTER TABLE table_name
ADD column_name datatype

or

ALTER TABLE table_name
DROP COLUMN column_name

 ALTER TABLE Employees
ADD jobTitle VARCHAR(20)		 Use Alter to modify the specified table.
CREATE VIEW CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition		 Example 1: CREATE VIEW topEmployees AS
SELECT * FROM Employees WHERE salary > 100000

Example 2: CREATE VIEW secureEmployees AS
SELECT lastName, firstName, empID
FROM Employees		 Views are dynamic tables - or you can think of them as subsets of tables. Perhaps it only has some of the columns or some of the rows, or is a combination fo tables.

Common Column Types

Type Example Description
INT age INT Integer
DECIMAL(precision, scale) salary DECIMAL(7,2) Decimal number where Precision = total number of digits including decimal places and Scale = the total number after the decimal places
VARCHAR lastName VARCHAR(15) Characters (maximum length)
CHAR state CHAR(2) Fixed length strings
DATE birthday DATE Displays in 'YYYY-MM-DD' format. Example.
TIME TIME Displays date and time 'YYYY-MM-DD HH:MM:SS'
TIMESTAMP lastprocessed TIMESTAMP current date and time, can leave out and will automatically get filled in when the row is inserted (or updated by default). Example.

Aggregate Functions

SQL USAGE Description
COUNT(column | *) SELECT COUNT(*) FROM Employees Count rows per group
AVG(column) SELECT AVG(salary) FROM Employees Average value of group
MIN(column) SELECT MIN(salary) FROM Employees Minumum value of group
MAX(column) SELECT MAX(salary) FROM Employees Maximum value of group
SUM(column) SELECT SUM(salary) FROM Employees Sum values in a group