Common SQL Statements

Querying

Execution order for queries:

1. FROM
2. ON
3. OUTER / INNER
4. WHERE
5. GROUP BY
6. ROLLUP / CUBE
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. LIMIT / TOP

Basic query

SELECT *
FROM table_name;

Projection

SELECT column_name_1, column_name_2 
FROM table_name;

Conditional Query

SELECT *
FROM table_name
WHERE condition;

Predicates

Operator	Description	Example
=	Equal to	WHERE gender = 'M'
<>, !=	Not equal to	WHERE gender <> 'M'
>	Greater than	WHERE num > 5
<	Less than	WHERE num < 5
>=	Greater than or equal to	WHERE num >= 5
<=	Less than or equal to	WHERE num <= 5
IS NULL	Value is NULL	WHERE num IS NULL
IS NOT NULL	Value is NULL	WHERE num IS NOT NULL
BETWEEN	Between an inclusive range	WHERE num BETWEEN 3 AND 5
IN	Value is a list of values	WHERE num IN (3, 5, 8)
LIKE	Search for a pattern	WHERE str LIKE 'F%'
EXISTS	Does subquery have any rows	WHERE EXISTS (subquery)

Grouping & Aggregation

SELECT grouped_column_names, aggregate_function
FROM table_name
WHERE condition
GROUP BY grouped_column_names
HAVING aggregate_condition;

INNER JOIN

SELECT *
FROM table_name_1 AS t1 INNER JOIN table_name_2 AS t2 
  ON t1.column_name = t2.column_name;

OUTER JOIN

SELECT *
FROM table_name_1 AS t1 LEFT OUTER JOIN table_name_2 AS t2 
  ON t1.column_name = t2.column_name;

SELECT *
FROM table_name_1 AS t1 RIGHT OUTER JOIN table_name_2 AS t2 
  ON t1.column_name = t2.column_name;

SELECT *
FROM table_name_1 AS t1 FULL OUTER JOIN table_name_2 AS t2 
  ON t1.column_name = t2.column_name;

NATURAL JOIN

SELECT *
FROM table_name_1 NATURAL JOIN table_name_2;

CROSS JOIN

SELECT *
FROM table_name_1 CROSS JOIN table_name_2;

SELECT *
FROM table_name_1, table_name_2;

Set Operations

UNION

SELECT statement
UNION
SELECT statement;

INTERSECT

SELECT statement
INTERSECT
SELECT statement;

INTERSECT

SELECT statement
INTERSECT
SELECT statement;

Difference (EXCEPT)

SELECT statement
EXCEPT
SELECT statement;

Subqueries

Non-Correlated Subqueries

SELECT *
FROM table_name AS t
WHERE t.column_name IN (SELECT statement);

SELECT *
FROM (SELECT statement)
WHERE condition;

SELECT *
FROM table_name AS t
WHERE condition
GROUP BY t.column_tuple
HAVING t.column_name IN (SELECT statement);

Correlated Subqueries

SELECT column_names, (SELECT statement_returning_single_value) 
FROM table_name;

SELECT *
FROM table_name
WHERE EXISTS (SELECT statement);

SELECT *
FROM table_name AS t
WHERE condition
GROUP BY t.column_tuple
HAVING t.column_name IN (SELECT statement);

Places to put Subqueries:

Non-Correlated	Correlated
FROM	SELECT
WHERE	WHERE
HAVING	HAVING

WITH Clause

WITH subquery_name_1 (column_name_1, column_name_1) AS
  (SELECT statement)
  subquery_name_2 (column_name_1, column_name_1) AS
  (SELECT statement)
SELECT statement;

CASE Expressions

CASE column_name
  WHEN condition_1 THEN result_1 
  WHEN condition_2 THEN result_2
  ...
  ELSE result_n
END

SELECT column_name_1, CASE column_name
  WHEN condition_1 THEN result_1 
  WHEN condition_2 THEN result_2
  ...
  ELSE result_n
END, column_name_2
FROM table_name;

Ordering Rows

SELECT *
FROM table_name
ORDER BY column_name_1;

Limiting Results

SELECT *
FROM table_name
LIMIT no_of_rows;

Datatypes

For SQLite:

1. INTEGER The value is a signed integer.

2. REAL The value is a floating point value, stored as an 8-byte IEEE floating point number.

3. TEXT The value is a text string.

4. BLOB The value is a blob of data, stored exactly as it was input.

5. NUMERIC May contain integer or real values. The data is stored as text but converted as necessary.

In general for SQL:

Data type	Description
CHAR(n)	Character string. Fixed length n
VARCHAR(n)	Character string. Variable length <= n
BINARY(n)	Binary string. Fixed length n
BOOLEAN	TRUE or FALSE values
VARBINARY(n)	Binary string. Variable length <= n
INTEGER(p)	Integer numerical (no decimal). Precision p
SMALLINT	Integer numerical (no decimal). Precision 5
INTEGER	Integer numerical (no decimal). Precision 10
BIGINT	Integer numerical (no decimal). Precision 19
DECIMAL(p,s)	Exact numerical. precision p, scale s
NUMERIC(p,s)	Exact numerical. precision p, scale s
FLOAT(p)	Floating point number. mantissa precision p
REAL	Approximate numerical. Mantissa precision 7
FLOAT	Approximate numerical. Mantissa precision 16
DATE	Stores year, month, and day values
TIME	Stores hour, minute, and second values
TIMESTAMP	Stores year, month, day, hour, minute, and second values

Creating a Table

CREATE TABLE table_name (
column_name_1 datatype,
column_name_2 datatype,
...
);

Creating a Table with Constraints

CREATE TABLE table_name (
column_name_1 datatype constraint,
column_name_2 datatype,
...
);

Primary and Foreign Key Constraints

CREATE TABLE table_name_1 (
column_name_1 datatype,
column_name_2 datatype,
..
PRIMARY KEY(primary_key_1, primary_key_2, ...)
FOREIGN KEY(foreign_key_1, foreign_key_2, ...) 
  REFERENCES table_name_2(column_name_1, column_key_2, ...) 
);

Creating a View

CREATE VIEW view_name
AS SELECT statement;

Deleting a Table

DROP TABLE table_name;

Deleting a View

DROP VIEW view_name;

Inserting a Row

INSERT INTO table_name(column_name_1, ...)
VALUES (value_1, ...),
(value_1, ...),
...;

Inserting the result of a query into a table.

INSERT INTO table_name(column_name_1, ...)
SELECT statement;

Updating a Row

UPDATE table_name
SET column_name = value_1, ...
WHERE condition;

Deleting a Row

DELETE FROM table_name
WHERE condition;