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 schema

CREATE SCHEMA sch;

Drop a SQL database

DROP DATABASE db;

A differ­ential back up only backs up the parts of the database that have changed since the last full database backup.

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

A copy of an existing table can also be created using CREATE TABLE. The new table will be filled with the existing values from the old table

CREATE TABLE tb AS
SELECT col1, col2,...
FROM existing tb
WHERE ....;

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

TRUNCATE TABLE tb;

Add a column in a table

ALTER TABLE tb
ADD col datatype;

Rename a column in a table

ALTER TABLE tb
RENAME COLUMN old_name
TO new_name;

Change the data type of a column in a table

SQL Server / MS Access
ALTER TABLE tb_name
ALTER COLUMN col_name datatype;

Oracle 10G and later
ALTER TABLE tb_name
MODIFY col_name datatype;

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;