Mobile Computing Applications
CS 284 (MCA), Interim 2018
% psql mca_i18 -h shelob.cs.stolaf.edu
1. Find the names of sailors who have reserved boat number 103
2. Find the names of sailors who have reserved a red boat
3. Find the colors of boats reserved by Lubber
4. Find the names of sailors who have reserved at least one boat
5. Find the names of sailors who have reserved a red or a green boat
6. Find the names of sailors who have reserved both a red and a green boat
9. Find the names of sailors who have reserved all boats.
11. Find all sailors with a rating above 7.
15. Find the names and ages of all sailors
16. Find the sids of sailors who have reserved a red boat.
17. Compute increments for the ratings of persons who have sailed two different boats on the same day.
EXCEPT,UNION
19. Find sids of all sailors who have reserved red boats but not green boats.
20. Find all sids of sailors who have a rating of 10 or have reserved boat 104.
21. Find the names of sailors who have NOT reserved a red boat.
Aggregates
22. Find sailors whose rating is better than some sailor called Horatio.
23. Find sailors whose rating is better than every sailor called Horatio.
24. Find the sailors with the highest rating.
25. Find the average age of all sailors.
26. Find the average age of all sailors with a rating of 10.
27. Find the name and age of the oldest sailor.
28. Count the number of sailors.
29. Count the number of different sailor names.
30. Find the names of sailors who are older than the oldest sailor with a rating of 10.
GROUP BY, HAVING
31. Find the age of the youngest sailor for each rating level.
32. Find the age of the youngest sailor who is elegible to vote (at least 18 years old) for each rating level with at least two such sailors.
33. For each red boat, find the number of reservations for that boat.
34. Find the average age of sailors for each rating level that has at least two sailors.
35. Find the average age of sailors who are of voting age (at least 18 years old) for each rating level that has at least two sailors.
36. Find the average age of sailors who are of voting age (at least 18 years old) for each rating level that has at least two such sailors.
37. Find those ratings for which the average age of sailors is the minimum over all ratings.