Подправить запрос(ы) в БД - MySQL

367

12 декабря 2017, 17:25

ER диаграмма,та что плохая

Новая ER -

Прощу вашей помощи, как подредактировать запрос(-ы) или если есть более лаконичное решение, то подскажите. Буду рад любой помощи.Спасибо!

Первый запрос: Как вывести данные о спортсмене?

1.Вывести таблицу распределения мест в соревновании 'открытый чемпионат' в городе 'Киев' по 'шахматам' в 2017 г.

SELECT Position 
FROM Result 
WHERE ID_Competition =(SELECT ID_Competition 
                       FROM Competition 
                       WHERE Name="Открытый чемпионат" AND City = "Киев" AND 
                       YEAR(Date)=2017 AND ID_Kind = 2)

Второй запрос: Добавить вложенный запрос, чтобы определить количество спорт больше трёх.

2.Определить спортсменов, которые выступают более чем в 3 видах спорта.
SELECT ID_Athlete, ID_Competition 
FROM Result
WHERE ID_Athlete IN (SELECT ID_Athlete 
                     FROM Result GROUP BY ID_Athlete HAVING COUNT(*)>2)

Третий запрос:Преподаватель говорит, что "Неверная связь между результатом и видом спорта", не приходит в голову ничего.

3.Вывести список спортсменов, превысивших мировые рекорды.

SELECT DISTINCT Result.Total_Result AS T_Result, Kind_of_sport.Record AS T_Kind_of_sport 
FROM Result 
           INNER JOIN Kind_of_sport ON Result.ID_Competition = 
           Kind_of_sport.ID_Kind 
           WHERE Result.Total_Result > Kind_of_sport.Record

Четвёртый запрос:Проверить что ID_Competition - это бег, по сути мы уже заведомо знаем, что это бег, так каким образом это проверить? Сделать связи двух таблиц?( A.ID_Competition = B. ID_Competition)

4.Определить наилучший показатель заданного спортсмена в виде спорта 'бег'.

SELECT ID_Athlete, MIN(Total_Result) 
       FROM Result 
       WHERE ID_Competition = 1 AND ID_Athlete = 7

Исходник:(не получилось загрузить БД, если понадобится, загружу через ФО)

INSERT INTO Result (ID_Result, ID_Athlete, ID_Competition, Position, Total_Result) INSERT INTO Athlete (Full_Name, Year_of_birth, Command, Sports_category, ID_Athlete) INSERT INTO Competition (ID_Competition, Name, Date, ID_Kind, Country, City) INSERT INTO Kind_of_sport (ID_Kind, Name_Kind, Unit, Record, Date)

Answer 1

Первый вопрос, просто аккуратно идём от вида спорта и до атлета:

SELECT `a`.*, `r`.`Position`, `Total_Result`
FROM `Kind_of_sport` `k`
INNER JOIN `Competition` `c` USING (`ID_Kind`)
INNER JOIN `Result` `r` USING (`ID_Competition`)
INNER JOIN `Athlete` `a` USING (`ID_Athlete`)
WHERE
    `k`.`Name_Kind` = 'шахматы' AND
    year(`k`.`DATE`) = 2017 AND
    `c`.`City` = 'Киев' AND
    `c`.`NAME` = 'открытый чемпионат';

Второй вопрос, последнюю таблицу не клеим, потому что по условию нас не интересуют данные из неё, а идентификатор вида спорта мы можем взять и из таблицы "соревнования":

SELECT `a`.*, count(DISTINCT `c`.`ID_Kind`) as `count`
FROM `Athlete` `a`
INNER JOIN `Result` `r` USING (`ID_Athlete`)
INNER JOIN `Competition` `c` USING (`ID_Competition`)
GROUP BY `c`.`ID_Kind`
HAVING `count` > 3;

Третий вопрос:

SELECT DISTINCT `a`.*
FROM `Athlete` `a`
INNER JOIN `Result` `r` USING (`ID_Athlete`)
INNER JOIN `Competition` `c` USING (`ID_Competition`)
INNER JOIN `Kind_of_sport` `k` USING (`ID_Kind`)
WHERE `r`.`Total_Result` > `k`.`Record`;

Четвертый вопрос:

SELECT `a`.*, MAX(`Total_Result`) as `best_in_beg_result`
FROM `Athlete` `a`
INNER JOIN `Result` `r` USING (`ID_Athlete`)
INNER JOIN `Competition` `c` USING (`ID_Competition`)
INNER JOIN `Kind_of_sport` `k` USING (`ID_Kind`)
WHERE `k`.`Name_Kind` = 'бег'
GROUP BY `a`.`ID_Athlete`;