Consider the instance of the Sailors relation shown in Figure 5.1.
1. Write SQL queries to compute the average rating, using AVG; the sum of the ratings, using SUM; and the number of ratings, using COUNT.
2. If you divide the sum just computed by the count, would the result be the same as the average? How would your answer change if these steps were carried out with respect to the age field instead of rating?
3. Consider the following query: Find the names of sailors with a higher rating than all sailors with age

4. Consider the instance of Sailors shown in Figure 5.1. Let us define instance S1 of Sailors to consist of the first two tuples, instance S2 to be the last two tuples, and S to be the given instance.
(a) Show the left outer join of S with itself, with the join condition being sid = sid.
(b) Show the right outer join of S with itself, with the join condition being sid = sid.
(c) Show the full outer join of S with itself, with the join condition being sid = sid.
(d) Show the left outer join of S1 with S2, with the join condition being sid = sid.
(e) Show the right outer join of S1 with S2, with the join condition being sid = sid.
(f) Show the full outer join of S1 with S2, with the join condition being sid = sid.

Transcribed Image Text:

SELECT S.sname FROM Sailors S WHERE NOT EXISTS (SELECT * FROM Sailors S2 WHERE S2.age< 21 AND S.rating <= S2.rating) SELECT FROM Sailors S WHERE S.rating >ANY (SELECT S2.rating FROM Sailors S2 WHERE S2.age < 21)