SQL cheat sheet

cheat.txt

SQL

Statements always end with a semi-colon ;

Statement breakdown:

CREATE TABLE table_name (
  column_1 data_type,
  column_1 data_type
);

CREATE TABLE is a clause
  Clauses are conventionally written in capitals.
  Clauses perform specific tasks within the database
table_name
  refers to the table that the statement applies to
  the context in which the clause will be executed
( column_1 data_type )
  these are the parameters of the statement
  a list of columns, data types or values that are passed to the statement as parameters
  Here the parameter is a list of column names and the associated data type

When writing statements they can be across multiple lines or on a single line. How the statement is formatted is chosen primarily for readability.

Clauses:

CREATE TABLE - create a new table CREATE TABLE table_name (column datatype, column datatype)
INSERT INTO - Insert a row into a new table INSERT INTO table_name (column,s)
VALUES - indicates data to be inserted VALUES (value,s)
SELECT - retrieve values from the database. This statement always returns a new table called the 'result set' SELECT column1, column2
FROM - specify where data should be coming from FROM table_name
UPDATE - change existing records in a particular table UPDATE table_name
SET - indicates the column to edit SET column_name = value
WHERE - filter results of a query WHERE condition (id = | IS 1)
ALTER TABLE - edit an existing table ALTER TABLE table_name
ADD COLUMN - add a column to a table ADD COLUMN column_name data_type
DELETE FROM - delete row from a table DELETE FROM table_name
DISTINCT - return unique values within the result_set SELECT DISTINCT
ORDER BY - Sort the results of a query
LIMIT - Specify the max. number of rows in the result set
GROUP BY - arrange identical data into groups

Operators:

=
!=
>
<
>=
<=
LIKE - compare similar values
IS
BETWEEN - filter the results set within a range (numbers, text and dates)
AND - combine conditions
OR

Patterns:

_ : wildcard characters exactly one missing letter
% : wildcard 0 or more missing letters

Keywords:

DESC: Apply descending order by clause
ASC: Apply ascending order by clause
AS: Rename a column or table using an Alias for the result set

Functions:
COUNT() - calculate the number of rows in a table COUNT(*)
MAX() - Find largest value in a column
MIN() - Find the smallest value in a column
AVG() - Find the average for values in a column
ROUND(column_name, dec_places) - Round values in a column to the number of decimal places

Data types:

INTEGER
TEXT
DATE
PRIMARY KEY - Only one column in a table can have this, ensures that the data in a column is unique, ensures none of the values in a column are NULL

Concepts:
PRIMARY KEY is a unique identifier for rows within a particular table
FOREIGN KEY is a unique identifier for a different table which is used to associate two tables. foreign keys do not need to be unique and can be NULL

Joins:

Cross Join: Specify multiple tables separated by a comma. Columns must be specified using dot notation between the table name and the column name

SELECT table1.id, table2.name FROM table1, table2

Inner Join: Combine rows from different tables if the join condition is true

SELECT
  table1.name, category, table2.name
FROM
  table1
JOIN table2 ON
  table1.foreign_key = table2.id
ORDER BY table2.name;

Outer join: These joins do not require the join condition to be met
Left Join: Every row in the left table is returned in the result set filling the columns in the right table with NULL values when the condition is not met

Left table: The first table in the join
Right table: The second table in the join