Data Manipulation in SQL
Mona Khalil
Data Scientist, Greenhouse Software
SELECT
date,
season,
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;
| date | season | outcome |
|------------|-----------|-------------------|
| 2011-08-09 | 2011/2012 | Home team win! |
| 2011-09-01 | 2011/2012 | Away team win! |
| 2011-09-14 | 2011/2012 | Tie |
| 2011-10-04 | 2011/2012 | Home team win! |
WHEN
clause!SELECT date, hometeam_id, awayteam_id,
CASE WHEN hometeam_id = 8455 AND home_goal > away_goal
THEN 'Chelsea home win!'
WHEN awayteam_id = 8455 AND home_goal < away_goal
THEN 'Chelsea away win!'
ELSE 'Loss or tie :(' END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;
| date | hometeam_id | awayteam_id | outcome |
|------------|-------------|-------------|-------------------|
| 2011-08-14 | 10194 | 8455 | Loss or tie :( |
| 2011-08-20 | 8455 | 8659 | Chelsea home win! |
| 2011-08-27 | 8455 | 9850 | Chelsea home win! |
| 2011-09-10 | 8472 | 8455 | Chelsea away win! |
ELSE
clause?SELECT date, hometeam_id, awayteam_id,
CASE WHEN hometeam_id = 8455 AND home_goal > away_goal
THEN 'Chelsea home win!'
WHEN awayteam_id = 8455 AND home_goal < away_goal
THEN 'Chelsea away win!'
ELSE 'Loss or tie :(' END AS outcome
FROM match;
| date | hometeam_id | awayteam_id | outcome |
|------------|-------------|-------------|----------------|
| 2011-07-29 | 1773 | 8635 | Loss or tie :( |
| 2011-07-30 | 9998 | 9985 | Loss or tie :( |
| 2011-07-30 | 9987 | 9993 | Loss or tie :( |
| 2011-07-30 | 9991 | 9984 | Loss or tie :( |
SELECT date, hometeam_id, awayteam_id,
CASE WHEN hometeam_id = 8455 AND home_goal > away_goal
THEN 'Chelsea home win!'
WHEN awayteam_id = 8455 AND home_goal < away_goal
THEN 'Chelsea away win!'
ELSE 'Loss or tie :(' END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;
| date | hometeam_id | awayteam_id | outcome |
|------------|-------------|-------------|-------------------|
| 2011-08-14 | 10194 | **8455** | Loss or tie :( |
| 2011-08-20 | **8455** | 8659 | Chelsea home win! |
| 2011-08-27 | **8455** | 9850 | Chelsea home win! |
| 2011-09-10 | 8472 | **8455** | Chelsea away win! |
SELECT date, CASE WHEN date > '2015-01-01' THEN 'More Recently' WHEN date < '2012-01-01' THEN 'Older' END AS date_category FROM match;
SELECT date, CASE WHEN date > '2015-01-01' THEN 'More Recently' WHEN date < '2012-01-01' THEN 'Older' ELSE NULL END AS date_category FROM match;
| date | date_category |
|------------|---------------|
| 2011-11-18 | Older |
| 2012-02-11 | NULL |
| 2014-11-07 | NULL |
| 2015-02-14 | More Recently |
SELECT date, season,
CASE WHEN hometeam_id = 8455 AND home_goal > away_goal
THEN 'Chelsea home win!'
WHEN awayteam_id = 8455 AND home_goal < away_goal
THEN 'Chelsea away win!'
END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;
| date | season | outcome |
|------------|-----------|-------------------|
| 2011-08-14 | 2011/2012 | NULL |
| 2011-12-22 | 2011/2012 | NULL |
| 2012-12-08 | 2012/2013 | Chelsea away win! |
| 2013-03-02 | 2012/2013 | Chelsea home win! |
SELECT date, season,
CASE WHEN hometeam_id = 8455 AND home_goal > away_goal
THEN 'Chelsea home win!'
WHEN awayteam_id = 8455 AND home_goal < away_goal
THEN 'Chelsea away win!' END AS outcome
FROM match;
SELECT date, season,
CASE WHEN hometeam_id = 8455 AND home_goal > away_goal
THEN 'Chelsea home win!'
WHEN awayteam_id = 8455 AND home_goal < away_goal
THEN 'Chelsea away win!' END AS outcome
FROM match
WHERE CASE WHEN hometeam_id = 8455 AND home_goal > away_goal
THEN 'Chelsea home win!'
WHEN awayteam_id = 8455 AND home_goal < away_goal
THEN 'Chelsea away win!' END IS NOT NULL;
| date | season | outcome |
|------------|-----------|-------------------|
| 2011-11-05 | 2011/2012 | Chelsea away win! |
| 2011-11-26 | 2011/2012 | Chelsea home win! |
| 2011-12-03 | 2011/2012 | Chelsea away win! |
Data Manipulation in SQL