SQL – Basic SQL on one table


Order of SQL Operations

  1. FROM –> table selectopn
  2. WHERE –> row(s) selection
  3. GROUP BY –> rows grouping by column
  4. Aggregations on columns –> in SELECT  or ORDER BY
  5. HAVING –> in case you have to select on an aggregated value
  6. WINDOW functions –> operations on a selection of rows
  7. SELECT –> column selection and aggregation on selected columns
  8. DISTINCT –> on a selected column
  9. ORDER BY –> on columns or aggregated column
  10. LIMIT/FETCH/TOP : return a limited set

Selecting Columns

Select – Basic




Select – COUNT



Filtering – numeric values


Filtering – text values


Filtering – multiple selections – AND


Filtering – multiple selections – OR + AND


Filtering – BETWEEN values


Filtering – certain values – WHERE IN


Filtering – Missing Data

Filtering – Patterns text values



Aggregation – basic

  • A single aggregate function in the SELECT clause (in the absence of GROUP BY) will force aggregation into a single row

Aggregation – with filtering


Aggregation – Aliasing


ORDER BY – sorting rows

Sorting – single column


Sorting – multiple columns

  • when using DISTINCT in the SELECT statement, an ORDER BY on a non-selected column can lead to errors
    • eg SELECT DISTINCT name, last_name FROM actors ORDER BY actor_id


GROUP BY – grouping rows with columns

  • A GROUP BY must be committed on all selected columns to have the aggregated field to be calculated
    • Error : SELECT release_year,  language , AVG(budget) FROM films  GROUP BY release_year 
    • Will cause an error because language wasn’t in the ‘group by’


HAVING – Filtering with aggregate functions

  • Using a GROUP BY clause, only expressions built from GROUP BY expressions , or aggregate functions can be used in HAVING, SELECT, and ORDER BY clauses.