Benvenuto a SQL Intermedio!

Manipolazione dei dati in SQL

Mona Khalil

Data Scientist, Greenhouse Software

Argomenti trattati:

Istruzioni CASE
Sottoquery semplici
Sottoquery correlate
Funzioni finestra

Prerequisiti

Selezionare, filtrare e raggruppare dati

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

Usare i join

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;

Selezionare dal Database di Calcio Europeo

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          |

Selezionare dal Database di Calcio Europeo

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         |

Selezionare dal Database di Calcio Europeo

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         |

Istruzioni CASE

Contiene un'istruzione WHEN, THEN, e ELSE, terminata con 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 'Vittoria Casa'
       WHEN home_goal < away_goal THEN 'Vittoria Trasferta'
       ELSE 'Pareggio' END AS outcome
FROM match
WHERE season = '2013/2014';

| id   | home_goal | away_goal | outcome       |
|------|-----------|-----------|---------------|
| 1237 | 2         | 0         | Vittoria Casa |
| 1238 | 0         | 1         | Vittoria Trasferta |
| 1239 | 1         | 0         | Vittoria Casa |
| 1240 | 0         | 0         | Pareggio      |

Ayo berlatih!

Manipolazione dei dati in SQL