1 / 50

SQL (2)

SQL (2). UNION, INTERSECT, AND EXCEPT. Find the names of sailors who have reserved a red or a green boat. Find the names of sailors who have reserved a red or a green boat. Find the names of sailors who have reserved both a red and a green boat.

abdalla
Download Presentation

SQL (2)

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. SQL (2)

  2. UNION, INTERSECT, AND EXCEPT Find the names of sailors who have reserved a red or a green boat.

  3. Find the names of sailors who have reserved a red or a green boat.

  4. Find the names of sailors who have reserved both a red and a green boat.

  5. Find the names of sailors who have reserved both a red and a green boat.

  6. Find the names of sailors who have reserved both a red and a green boat. – use INTERSECT

  7. Find the names of sailors who have reserved a red or a green boat. – Use UNION

  8. Find the sids of all sailors who have reserved red boats but not green boats

  9. Find the sids of all sailors who have reserved red boats but not green boats

  10. Find all sids of all sailors who have a rating of 10 or have reserved boat 104

  11. Find all sids of all sailors who have a rating of 10 or have reserved boat 104

  12. Nested Query(SubQuery) • A nested query is a query that has another query embedded within it; • The embedded query is called a subquery. • A subqueryappears within the WHERE clause of a query. • Subqueriescan sometimes appear in the FROM clause or the HAVING clause

  13. Find the names of sailors who have reserved boat 103

  14. Find the names of sailors who have reserved boat 103

  15. Find the name of sailors who have reserved a red boat

  16. Find the name of sailors who have reserved a red boat

  17. Find the name of sailors who have not reserved a red boat

  18. Find the name of sailors who have not reserved a red boat

  19. Set-Comparison Operators • op ANY and op ALL • op : <, <=, =, <>, >=, > • SOME : a synonym for ANY

  20. Find the sailors whose rating is better than some sailor called Horatio

  21. Find the sailors whose rating is better than some sailor called Horatio

  22. Replace ANY with ALL SELECT S.Sid FROM Sailors S WHERE S.Rating > ALL (SELECT S2.Rating FROM Sailors S2 WHERE S2.sname = ‘Horatio’)

  23. Find the sailors with the highest rating

  24. Find the sailors with the highest rating

  25. Find the names of sailors who have reserved both a red and a green boat.

  26. Find the names of sailors who have reserved both a red and a green boat.

  27. AGGREGATE OPERATORS • COUNT ([DISTINCT] A) : the number of (unique) values in the A column. • SUM ([DISTINCT] A) : the sum of all (unique) values in the A column. • AVG ([DISTINCT] A) : the average of all (unique) values in the A column. • MAX : the maximal value in the column A • MIN : the minimal value in the column A

  28. Find the average age of all sailors

  29. Find the average age of all sailors

  30. Find the average age of sailors with a rating of 10

  31. Find the average age of sailors with a rating of 10

  32. Find the name and age of the oldest sailor

  33. Find the name and age of the oldest sailor ?

  34. Find the name and age of the oldest sailor

  35. Count the number of sailors

  36. Count the number of sailors

  37. Count the number of different sailor names

  38. Count the number of different sailor names

  39. Find the name of sailors who are older than the oldest sailor with the rating of 10

  40. Find the name of sailors who are older than the oldest sailor with the rating of 10

  41. The Group By and Having Clauses Find the age of youngest sailor for each rating level

  42. Find the age of youngest sailor for each rating level Where i = 1, 2, …, 10

  43. Find the age of youngest sailor for each rating level

  44. Find the age of the youngest sailor who is eligible to vote (at least 18 years old) for each rating level with at least two such sailors

  45. Find the age of the youngest sailor who is eligible to vote (at least 18 years old) for each rating level with at least two such sailors

  46. For each red boat, find the number of reservations for this boat

  47. For each red boat, find the number of reservations for this boat X

  48. Find the average age of sailors for each rating level that has at least two sailors

  49. Find the average age of sailors for each rating level that has at least two sailors

More Related