# 📊 HW 7 DB HSE
## by Anna Sibirtseva BSE206
**07.11.2022**

### ⭐ Task 1 - Создать скрипт для автоматического наполнения базы данных

Не знала, как лучше сдать, поэтому перекинула всё в гугл колаб:
https://colab.research.google.com/drive/1ZdiCVeXZRIoXLtVBedemcdridfO04YBw?usp=sharing

### ⭐ Task 2 - Выполнить следующие задания с помощью SQL

🔸 task 3.1
Для Олимпийских игр 2004 года сгенерируйте список (год рождения, количество игроков, количество золотых медалей), содержащий годы, в которые родились игроки, количество игроков, родившихся в каждый из этих лет, которые выиграли по крайней мере одну золотую медаль, и количество золотых медалей, завоеванных игроками, родившимися в этом году.

SELECT extract(year FROM birthdate) AS birthday, COUNT(medal), COUNT(DISTINCT players.player_id)
FROM players, results, events
WHERE players.player_id = results.player_id AND 
      medal='GOLD' AND events.event_id = results.event_id AND 
      events.olympic_id = 'ATH2004'
GROUP BY extract(year FROM birthdate)

🔸 task 3.2
Перечислите все индивидуальные (не групповые) соревнования, в которых была ничья в счете, и два или более игрока выиграли золотую медаль.

SELECT name, olympic_id
FROM events
WHERE is_team_event = 0
AND event_id IN(SELECT event_id
                FROM results
                WHERE medal LIKE "%GOLD%"
                GROUP BY event_id
                HAVING COUNT(medal) > 1)

🔸 task 3.3
Найдите всех игроков, которые выиграли хотя бы одну медаль (GOLD, SILVER и
BRONZE) на одной Олимпиаде. (player-name, olympic-id).

(SELECT player.name, olympic_id
FROM events, results, players
WHERE players.player_id = results.player_id AND events.event_id = results.event_id AND medal LIKE %GOLD%)
INTERSECT 
(SELECT player.name, olympic_id
FROM events, results, players
WHERE players.player_id = results.player_id AND events.event_id = results.event_id AND medal LIKE %SILVER%)
INTERSECT 
(SELECT player.name, olympic_id
FROM events, results, players
WHERE players.player_id = results.player_id AND events.event_id = results.event_id AND medal LIKE %BRONZE%)

🔸 task 3.4
В какой стране был наибольший процент игроков (из перечисленных в наборе данных), чьи имена начинались с гласной?

WITH temp AS (SELECT c1.country_id, CAST(c2.num_players AS FLOAT) / c1.num_players AS ratio
              FROM (
                  SELECT country_id, COUNT(player_id) AS num_players 
                  FROM players GROUP BY country_id) c1,
             (SELECT country_id, COUNT(player_id) AS num_players 
              FROM players 
              WHERE substr(name, 1, 1) IN ('A', 'E', 'O', 'I', 'U') 
              GROUP BY country_id) c2
             WHERE c1.country_id = c2.country_id)
SELECT c.name
FROM temp t, countries c
WHERE ratio = (SELECT MAX(ratio) from temp) AND t.country_id = c.country_id

🔸 task 3.5
Для Олимпийских игр 2000 года найдите 5 стран с минимальным соотношением количества групповых медалей к численности населения.

WITH temp AS (SELECT c.name, COUNT(*) / CAST(c.population AS FLOAT) AS ratio
              FROM TeamMedals im, events e, countries c
              WHERE e,event_id = im.event_id AND e.olympic_id = 'SYD2000' AND im.country_id = c.country_id
              GROUP BY c.name, c.population)
SELECT name
FROM temp t1
WHERE (SELECT COUNT(*) 
       FROM temp t2 
       WHERE t2 < t1.ratio) > 5