1 / 18

Tutorial 5 : Advanced SQL

Tutorial 5 : Advanced SQL. Question: Consider the following schema where sailors can reserve boats. The primary keys are underlined. Sailor s ( sid: integer , sname: string , rating: integer , age: real ) Boats( bid: integer , bname: string , color: string )

geoffrey-hogan
Download Presentation

Tutorial 5 : Advanced SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Tutorial 5: Advanced SQL

  2. Question: • Consider the following schema where sailors can reserve boats. The primary keys are underlined. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) • Write the following queries in SQL.

  3. Q1:Find names of sailors who’ve reserved boat #103. Use IN. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

  4. Q2:Find names of sailors who’ve reserved a red boat. Use IN. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

  5. Q3:Find names of sailors who’ve NOT reserved a red boat. Use NOT IN and IN. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

  6. Q4:Find names of sailors who’ve reserved a boat that is NOT red. Use NOT IN and IN. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

  7. Q5:Find names of sailors who’ve NOT reserved a boat that is NOT red. (i.e., who have reserved only red boats.) Use NOT IN. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

  8. Q6:Find names of sailors who’ve reserved boat #103.Use EXISTS. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

  9. Q7:Find names of sailors who’ve made at most one reservation for boat #103. Use nested query. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

  10. Q8:Find sailors whose rating is greater than that of some sailor called Tom. Use SOME. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

  11. Q9:Find sailors whose rating is greater than every sailor called Tom.Use ALL. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

  12. Q10:Find the sailors with the highest rating.Use ALL. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

  13. Q11:Find the average age of all sailors with a rating of 10. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

  14. Q12:Count the number of different sailors names. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

  15. Q13:Find name and age of the oldest sailor(s). Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

  16. Q14:Find the name of sailors who are older than the oldest sailors with a rating of 10. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

  17. Q15:Find the age of the youngest sailor for each rating level. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

  18. Q16:For each red boat, find the number of reservations for this boat. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date)

More Related