Templates for Basic SQL Queries

Here are six templates that may help you write SQL queries. Within the templates, the words written in UPPERCASE are SQL keywords and the words written in lowercase italics are the ones that you should replace for each specific query.

Simple Query

SELECT column1, column2, …
FROM schema.table
WHERE filter
ORDER BY column1, column2, …
LIMIT offset, count

Arithmetic Operators

The arithmetic operators cause the database server to perform arithmetic on the data in cells. The arithmetic operators available in MySQL include − (negation), * (multiplication), / (division), DIV (integer division), MOD (modulus), + (addition), and − (subtraction).

SELECT column1 operator column2 AS alias, …
FROM schema.table
WHERE filter
ORDER BY column1, column2, …
LIMIT offset, count

Scalar Functions

Scalar functions are functions that operate on the data in one row. The scalar functions available in MySQL include YEAR(), MONTH(), CONCAT(), LOWER(), UPPER(), SUBSTRING(), POW(), ROUND(), and SQRT()

SELECT FUNC(column) AS alias, …
FROM schema.table
WHERE filter
ORDER BY column1, column2, …
LIMIT offset, count

Aggregate Functions

Aggregate functions are functions that operate on the data in multiple rows. In other words, aggregate functions combine or aggregate the data in multiple rows into one value. The aggregate functions available in MySQL include MIN(), MAX(), COUNT(), SUM(), AVG(), STDDEV(), and VARIANCE()

SELECT AGGREGATE_FUNC(column) AS alias, …
FROM schema.table
WHERE filter
GROUP BY column1, column2, …
HAVING aggregate_filter
ORDER BY column1, column2, …
LIMIT offset, count

Join Tables

Use a join to select columns from multiple tables.

SELECT column1, column2, …
FROM schema.table1 AS t1
    INNER JOIN schema.table2 AS t2 ON t1.pk = t2.fk
    INNER JOIN schema.table3 AS t3 ON t2.pk = t3.fk
    ⋮
WHERE filter
ORDER BY column1, column2, …
LIMIT offset, count

Join Tables and Aggregate Functions

SELECT column1, aggregate_func(column2) AS alias, …
FROM schema.table1 AS t1
    INNER JOIN schema.table2 AS t2 ON t1.pk = t2.fk
    INNER JOIN schema.table3 AS t3 ON t2.pk = t3.fk
    ⋮
WHERE filter
GROUP BY column1, column2, …
HAVING aggregate_filter
ORDER BY column1, column2, …
LIMIT offset, count

Of course a single query can include elements from all the templates. For example, a single query can

• select columns from multiple tables with a join
• select values calculated using the arithmetic operators
• select values calculated using the scalar functions
• select values calculated using the aggregate functions
• filter the results
• group the results
• sort the results
• and much more

Execution Order

In the next template, the numbers on the left show the order in which the computer executes the clauses of a SQL query. Notice that the FROM clause is executed first even though a developer writes it second (after the SELECT clause). This execution order helps us understand why a table alias defined in the FROM clause may be used in all other clauses. The execution order also helps us understand why a column alias defined in the SELECT clause may not be used in the FROM, WHERE, GROUP BY, or HAVING clauses. A column alias defined in the SELECT clause may be used only in the ORDER BY and LIMIT clauses because the computer executes ORDER BY and LIMIT after SELECT.

5. SELECT column1, FUNC(column2) AS alias,
    AGGREGATE_FUNC(column3) AS alias, …
1. FROM schema.table1 AS t1
       INNER JOIN schema.table2 AS t2 ON t1.pk = t2.fk
       INNER JOIN schema.table3 AS t3 ON t2.pk = t3.fk
       ⋮
2. WHERE filter
3. GROUP BY column1, column2, …
4. HAVING aggregate_filter
6. ORDER BY column1, column2, …
7. LIMIT offset, count