Advertisement
user1man

Rides homework

Sep 29th, 2022
1,190
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 2.19 KB | None | 0 0
  1. # Скачки:
  2. # tasl 1
  3. SELECT name FROM Horse WHERE owner_id = '1';
  4. # task 2
  5. SELECT COUNT(id),owner_id FROM Horse GROUP BY owner_id;
  6. # task 3
  7. 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;
  8. # task 4
  9. 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;
  10. # task 5
  11. 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);
  12. # task 6
  13. SELECT MIN(shown_time), jockey_id FROM participants GROUP BY jockey_id;
  14.  
  15.  
  16.  
  17. # task 7
  18. # Напишите запрос для получения наилучшего результата (времени) каждого
  19. # жокея, показанного на каждом ипподроме.
  20.  
  21. SELECT R.name, time, place
  22. FROM Team_result
  23.          JOIN Rider R ON R.id = Team_result.rider_id
  24. ORDER BY time
  25. LIMIT 1;
  26.  
  27. # task 8
  28. # Напишите запрос для получения информации о жокеях, показавших
  29. # результат выше среднего в определенном соревновании.
  30.  
  31. SELECT R.name, time, place
  32. FROM Team_result
  33.          JOIN Rider R ON R.id = Team_result.rider_id
  34. WHERE time > (SELECT AVG(time) FROM Team_result)
  35.   AND Team_result.ride_id = 1;
  36.  
  37.  
  38. # task 9
  39. # Напишите запрос для получения результатов (времени) для каждого жокея,
  40. # если они выше среднего для жокея.
  41.  
  42. SELECT *
  43. FROM Rider R
  44.          RIGHT OUTER JOIN Team_result Tr ON R.id = Tr.rider_id
  45. WHERE Tr.time > (SELECT AVG(Tr1.time)
  46.                  FROM Team_result Tr1
  47.                  WHERE Tr1.rider_id);
  48.  
  49. # Task 10
  50. SELECT COUNT(Competition.place), Competition.place FROM participants JOIN Competition ON competition_id = Competition.id GROUP BY competition_id HAVING COUNT(Competition.place)>1;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement