Homework #1 - SQL

1

SELECT DISTINCT(name) FROM medal_info ORDER BY name;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT co.name, COUNT(*)
FROM coaches co
JOIN (
    SELECT m.winner_code, m.discipline, a.country_code
    FROM medals m
    JOIN athletes a ON m.winner_code = a.code
    UNION ALL
    SELECT DISTINCT m.winner_code, m.discipline, t.country_code
    FROM medals m
    JOIN teams t ON m.winner_code = t.code
) winners ON co.discipline = winners.discipline AND co.country_code = winners.country_code
GROUP BY co.code
ORDER BY COUNT(*) DESC, co.name ASC
;

1
2
3
4
5
6
7
8

SELECT DISTINCT a.name, count(m.winner_code)
FROM athletes a
LEFT JOIN teams t ON a.code = t.athletes_code
LEFT JOIN medals m ON m.winner_code = a.code OR m.winner_code = t.code
WHERE a.disciplines LIKE '%Judo%'
GROUP BY a.name
ORDER BY count(m.winner_code) DESC, a.name
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

WITH p AS (
    SELECT *
    FROM athletes
    WHERE code in (
        SELECT DISTINCT participant_code
        FROM (
            SELECT participant_code
            FROM results r
            LEFT JOIN venues v ON r.venue = v.venue
            WHERE v.disciplines LIKE '%Athletics%' AND participant_type = 'Person'
            UNION
            SELECT athletes_code AS participant_code
            FROM teams t
            WHERE code IN (
                SELECT participant_code
                FROM results r
                LEFT JOIN venues v ON r.venue = v.venue
                WHERE v.disciplines LIKE '%Athletics%' AND participant_type = 'Team'
            )
        )
    )
)

SELECT p.name, p.country_code, p.nationality_code
FROM p
LEFT JOIN (
    SELECT code, c.Latitude cLatitude, c.Longitude cLongitude
    FROM countries c
) c1 ON p.country_code = c1.code
LEFT JOIN (
    SELECT code, c.Latitude nLatitude, c.Longitude nLongitude
    FROM countries c
) c2 ON p.nationality_code = c2.code
WHERE cLatitude IS NOT NULL AND cLongitude IS NOT NULL AND nLatitude IS NOT NULL AND nLongitude IS NOT NULL
ORDER BY (cLatitude - nLatitude) * (cLatitude - nLatitude) + (cLongitude - nLongitude) * (cLongitude - nLongitude) DESC, p.name
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

WITH t AS (
    SELECT code, country_code
    FROM teams
    GROUP BY code, country_code
), cr AS (
    SELECT code, 
        RANK() OVER (ORDER BY c."GDP ($ per capita)" DESC) gdprk,
        RANK() OVER (ORDER BY c.Population DESC) poprk
    FROM countries c
)

SELECT date, ccode, app, gdprk, poprk
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY date ORDER BY app DESC, ccode) as rn
    FROM (
        SELECT *, 
        CASE WHEN t.country_code IS NOT NULL THEN t.country_code ELSE a.country_code END AS ccode,
        COUNT(rank) as app
        FROM (
            results r LEFT JOIN t ON r.participant_code = t.code
            LEFT JOIN athletes a ON r.participant_code = a.code
        )
        WHERE rank <= 5
        GROUP BY date, ccode
    )
) ranked, cr
WHERE rn = 1 AND cr.code = ranked.ccode
ORDER BY date
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

with t as (
	select code, country_code from teams group by code, country_code
),
paris_medals as (
    select t2.country_code as country_code, count(t2.country_code) as medal_number 
    from
        (select 
            t1.medal_code, 
            case when t1.country_code is not null then t1.country_code else athletes.country_code end as country_code
        from 
            (select * from medals left join t on medals.winner_code = t.code) as t1 
            left join athletes on athletes.code = t1.winner_code
        ) as t2, 
        medal_info mi
    where t2.medal_code = mi.code and mi.name = 'Gold Medal'
    group by t2.country_code
    order by medal_number desc
),
cs as (
    select 
        paris_medals.country_code as country, 
        paris_medals.medal_number - tokyo_medals.gold_medal as progress
    from tokyo_medals, paris_medals
    where paris_medals.country_code = tokyo_medals.country_code
    order by progress desc
    limit 5
)

select country_code, progress as increased_gold_medal_number, tcode
from (
    select *, teams.code as tcode
    from athletes
    JOIN teams ON teams.athletes_code = athletes.code
    JOIN cs ON teams.country_code = cs.country
    group by teams.code
    having sum(1 - gender) = 0
    )
order by progress desc, country, tcode
;