Welcome to Intermediate SQL!

Data Manipulation in SQL

Mona Khalil

Data Scientist, Greenhouse Software

Topics covered:

CASE statements
Simple subqueries
Correlated subqueries
Window functions

Prerequisites

Selecting, filtering, and grouping data

SELECT user_id, SUM(sales) 
FROM sales_data
WHERE user_id BETWEEN 300 AND 400
GROUP BY user_id;

Using joins

SELECT c.country, c.team, SUM(m.goals)
FROM countries AS c
LEFT JOIN matches AS m
ON c.team_id = m.home_team_id
WHERE m.year > 1990
GROUP BY c.country, c.team;

Selecting from the European Soccer Database

SELECT 
    l.name AS league,
    COUNT(m.country_id) as total_matches
FROM league AS l
LEFT JOIN match AS m
ON l.country_id = m.country_id
GROUP BY l.name;

| league                 | total_matches |
|------------------------|---------------|
| Belgium Jupiler League | 732           |
| England Premier League | 1520          |
| France Ligue 1         | 1520          |
| Germany 1. Bundesliga  | 1224          |

Selecting from the European Soccer Database

SELECT 
    date,
    id,
    home_goal,
    away_goal
FROM match
WHERE season = '2013/2014';

| date                | id   | home_goal | away_goal |
|---------------------|------|-----------|-----------|
| 2014-03-29 00:00:00 | 1237 | 2         | 0         |
| 2014-03-29 00:00:00 | 1238 | 0         | 1         |
| 2014-04-05 00:00:00 | 1239 | 1         | 0         |
| 2014-04-05 00:00:00 | 1240 | 0         | 0         |

Selecting from the European Soccer Database

SELECT 
    date,
    id,
    home_goal,
    away_goal
FROM match
WHERE season = '2013/2014'
      AND home_team_goal > away_team_goal;

| date                | id   | home_goal | away_goal |
|---------------------|------|-----------|-----------|
| 2014-03-29 00:00:00 | 1237 | 2         | 0         |
| 2014-04-05 00:00:00 | 1239 | 1         | 0         |
| 2014-04-12 00:00:00 | 1241 | 2         | 1         |
| 2014-04-12 00:00:00 | 1242 | 2         | 0         |

CASE statements

Contains a WHEN, THEN, and ELSE statement, finished with END

CASE WHEN x = 1 THEN 'a' 
     WHEN x = 2 THEN 'b'
     ELSE 'c' END AS new_column

CASE WHEN

SELECT 
  id,
  home_goal,
  away_goal,
  CASE WHEN home_goal > away_goal THEN 'Home Team Win'
       WHEN home_goal < away_goal THEN 'Away Team Win'
       ELSE 'Tie' END AS outcome
FROM match
WHERE season = '2013/2014';

| id   | home_goal | away_goal | outcome       |
|------|-----------|-----------|---------------|
| 1237 | 2         | 0         | Home Team Win |
| 1238 | 0         | 1         | Away Team Win |
| 1239 | 1         | 0         | Home Team Win |
| 1240 | 0         | 0         | Tie           |

Let's practice!

Data Manipulation in SQL