Exercise 5.5 Consider the instance of the Sailors relation shown in Figure 5.1. Write SQL...

Exercise 5.5 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 < 21. The following two SQL queries attempt to obtain the answer to this question. Do they both compute the result? If not, explain why. Under what conditions would they compute the same result?
   

   SELECT S.snameFROM Sailors SWHERE 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 )

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.
   1. Show the left outer join of S with itself, with the join condition being sid=sid.
   2. Show the right outer join of S with itself, with the join condition being sid=sid.
   3. Show the full outer join of S with itself, with the join condition being sid=sid.
   4. Show the left outer join of S1 with S2, with the join condition being sid=sid.
   5. Show the right outer join of S1 with S2, with the join condition being sid=sid.
   6. Show the full outer join of S1 with S2, with the join condition being sid=sid.