SQL – Basic SQL on one table

SQL BASICs

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 – DISTINCT

 

Select – COUNT

 

FILTERING ROWS

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 Functions

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.