SQL Cheat Sheet (DRAFT) by ArturPuiu

Select data from a database.

SELECT columns
FROM table;

Extract records that fulfill a specified condition.

SELECT columns
FROM table
WHERE condition;

Specify the number of records to return. OFFSET is used to skip a specified number of rows.

SELECT columns
FROM table
WHERE condition
LIMIT number
OFFSET number;

SELECT columns
FROM table
WHERE cond1 AND cond2;

SELECT columns
FROM table
WHERE NOT cond;

Allows to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR condit­ions.

SELECT columns
FROM table
WHERE col IN (value1, value2);

Is used in a WHERE clause to search for a specified pattern in a column.

SELECT columns,
FROM table
WHERE column LIKE pattern;

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.

Was added to SQL because the WHERE keyword cannot be used with aggregate functions.

SELECT columns
FROM table
WHERE condition
GROUP BY columns
HAVING condition
ORDER BY columns;

Create a new SQL schema

CREATE SCHEMA sch;

Drop a SQL schema

DROP SCHEMA Sch;

Check the list of databases

SHOW DATABASES

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 columns
FROM table1
INNER JOIN table2
ON table1.col = table2.col;

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;