SQL Cheat Sheet (DRAFT) by ArturPuiu

Select data from a database.

SELECT column1, column2
FROM table_­name;

Extract records that fulfill a specified condition.

SELECT column1, column2
FROM table_name
WHERE condition;

SELECT column1, column2
FROM table_name
WHERE condition1 AND condit­ion2;

SELECT column1, column2
FROM table_name
WHERE NOT condition;

Insert new records in a table.

INSERT INTO table_name
VALUES (value1, value2);

SELECT column­_names
FROM table_name
WHERE column­_name IS NULL;

Modify the existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Specify the number of records to return.

SELECT column­_na­me(s)
FROM table_name
WHERE condition
LIMIT number;

SELECT column­_na­me(s)
FROM table_name
WHERE column­_name
IN (SELECT STATEM­ENT);

Groups rows that have the same values into summary rows. Is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

SELECT column­_na­me(s)
FROM table_name
WHERE condition
GROUP BY column­_na­me(s)
ORDER BY column­_na­me(s);

Test for the existence of any record in a subquery. Returns TRUE if the subquery returns one or more records.

SELECT column­_na­me(s)
FROM table_name
WHERE EXISTS
(SELECT column­_name
FROM table_name WHERE condit­ion);

Returns:
1. A boolean value as a result
2. Returns TRUE if ALL of the subquery values meet the condition
3. Is used with SELECT, WHERE and HAVING statem­ents.
ALL means that the condition will be true only if the operation is true for all values in the range.

SELECT column­_na­me(s)
FROM table_name
WHERE column­_name operator ALL
(SELECT column­_name
FROM table_name
WHERE condit­ion);

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Goes through conditions and returns a value when the first condition is met (like an if-the­n-else statem­ent). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. 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;

Create a new SQL database.

CREATE DATABASE db;

Drop a SQL database.

DROP DATABASE db;

BACKUP DATABASE db
TO DISK = 'filepath'
WITH DIFFER­ENTIAL;

CREATE TABLE new_table AS
SELECT column1, column­2,...
FROM existi­ng_­table
WHERE ....;

TRUNCATE TABLE

delete the data inside a table, but not the table itself.

TRUNCATE TABLE table_­name;

ADD Column

ALTER TABLE table_name ADD column­_name datatype;

RENAME COLUMN

ALTER TABLE table_name RENAME COLUMN old_name to new_name;

Selects records that have matching values in both tables.

SELECT column­_na­me(s)
FROM table1
INNER JOIN table2
ON table1.co­lum­n_name =
table2.co­lum­n_name;

Returns all records from the right table (table2), and the matching records (if any) from the left table (table1).

SELECT column­_na­me(s)
FROM table1
RIGHT JOIN table2
ON table1.co­lum­n_name =
table2.co­lum­n_name;

A self join is a regular join, but the table is joined with itself.

SELECT column­_na­me(s)
FROM table1 T1, table1 T2
WHERE condition;

UNION Syntax. Selects only distinct values by default. To allow duplicate values, use UNION ALL

SELECT column­_na­me(s) FROM table1
UNION
SELECT column­_na­me(s) FROM table2;