Data Manipulation in SQL
Mona Khalil
Data Scientist, Greenhouse Software
SELECT column
FROM (SELECT column
FROM table) AS subquery;
Can be in any part of a query
SELECT
, FROM
, WHERE
, GROUP BY
Can return a variety of information
3.14159
, -2
, 0.001
)id = (12, 25, 392, 401, 939)
)SELECT home_goal FROM match WHERE home_goal > ( SELECT AVG(home_goal) FROM match);
SELECT AVG(home_goal) FROM match;
1.56091291478423
SELECT home_goal
FROM match
WHERE home_goal > (
SELECT AVG(home_goal)
FROM match);
SELECT AVG(home_goal) FROM match;
1.56091291478423
SELECT date, hometeam_id, awayteam_id, home_goal, away_goal
FROM match
WHERE season = '2012/2013'
AND home_goal > 1.56091291478423;
SELECT date, hometeam_id, awayteam_id, home_goal, away_goal
FROM match
WHERE season = '2012/2013'
AND home_goal > (SELECT AVG(home_goal)
FROM match);
| date | hometeam_id | awayteam_id | home_goal | away_goal |
|------------|-------------|-------------|-----------|-----------|
| 2012-07-28 | 9998 | 1773 | 5 | 2 |
| 2012-07-29 | 9987 | 9984 | 3 | 3 |
| 2012-10-05 | 9993 | 9991 | 2 | 2 |
SELECT
team_long_name,
team_short_name AS abbr
FROM team
WHERE
team_api_id IN
(SELECT hometeam_id
FROM match
WHERE country_id = 15722);
| team_long_name | abbr |
|----------------|------|
| Ruch Chorzów | CHO |
| Jagiellonia | BIA |
| Lech Pozna? | POZ |
| P. Warszawa | PWA |
| Cracovia | CKR |
| Górnik ??czna | LEC |
| Polonia Bytom | GOR |
| Zag??bie Lubin | ZAG |
| Pogo? Szczecin | POG |
| Widzew ?ód? | WID |
| ?l?sk Wroc?aw | SLA |
Data Manipulation in SQL