Rides homework

# Скачки:
# tasl 1
SELECT name FROM Horse WHERE owner_id = '1';
# task 2
SELECT COUNT(id),owner_id FROM Horse GROUP BY owner_id;
# task 3
SELECT Horse.name AS horse_name, Rider.name, participants.taken_place, participants.shown_time FROM Horse JOIN (Rider JOIN participants ON Rider.id=participants.jockey_id) ON Horse.id = participants.horse_id WHERE participants.competition_id=1;
# task 4
SELECT Horse.name, Rider.name, participants.competition_id FROM Horse JOIN (Rider JOIN participants ON Rider.id=participants.jockey_id) ON Horse.id=participants.horse_id WHERE participants.taken_place=1;
# task 5
SELECT DISTINCT Horse.name, Horse.owner_id FROM Horse JOIN participants ON Horse.id = participants.horse_id WHERE Horse.id IN (SELECT horse_id FROM participants);
# task 6
SELECT MIN(shown_time), jockey_id FROM participants GROUP BY jockey_id;



# task 7
# Напишите запрос для получения наилучшего результата (времени) каждого
# жокея, показанного на каждом ипподроме.

SELECT R.name, time, place
FROM Team_result
         JOIN Rider R ON R.id = Team_result.rider_id
ORDER BY time
LIMIT 1;

# task 8
# Напишите запрос для получения информации о жокеях, показавших
# результат выше среднего в определенном соревновании.

SELECT R.name, time, place
FROM Team_result
         JOIN Rider R ON R.id = Team_result.rider_id
WHERE time > (SELECT AVG(time) FROM Team_result)
  AND Team_result.ride_id = 1;


# task 9
# Напишите запрос для получения результатов (времени) для каждого жокея,
# если они выше среднего для жокея.

SELECT *
FROM Rider R
         RIGHT OUTER JOIN Team_result Tr ON R.id = Tr.rider_id
WHERE Tr.time > (SELECT AVG(Tr1.time)
                 FROM Team_result Tr1
                 WHERE Tr1.rider_id);

# Task 10
SELECT COUNT(Competition.place), Competition.place FROM participants JOIN Competition ON competition_id = Competition.id GROUP BY competition_id HAVING COUNT(Competition.place)>1;