Questions related to SQL – Part 5

Question 1 : Consider the instance of the Sailors relation
Sid Sname rating Age
18 Jones 3 30.0
41 Jonah 6 56.0
22 Ahab 7 44.0
63 Moby Null 15.0
Table 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.
Solution : SELECT AVG (S.rating) AS AVERAGE
           FROM Sailors S
           SELECT SUM (S.rating)
           FROM Sailors S
           SELECT COUNT (S.rating)
           FROM Sailors S
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?
Solution : The result using SUM and COUNT would be smaller than the 
           result using AVERAGE if there are tuples with 
           rating = NULL. 
           This is because all the aggregate operators, except for
           COUNT, ignore NULL values. So the first approach would
           compute the average over all tuples while the second 
           approach would compute the average over all tuples with 
           non-NULL rating values. However,if the aggregation is done
           on the age field, the answers using both approaches would 
           be the same since the age field does not take NULL values.
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.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 )
Solution : Only the first query is correct. The second query returns 
           the names of sailors with a higher rating than at least 
           one sailor with age < 21. Note that the answer to the 
           second query does not necessarily contain the answer to 
           the first query. In particular, if all the sailors are at
           least 21 years old, the second query will return an empty
           set while the first query will return all the sailors. 
           This is because the NOT EXISTS predicate in the first     
           query will evaluate to true if its subquery evaluates to 
           an empty set, while the ANY predicate in the second query 
           will evaluate to false if its subquery evaluates to an
           empty set. The two queries give the same results if and
           only if one of the following two conditions hold : 
        1. The Sailors relation is empty, or
        2. There is at least one sailor with age > 21 in the Sailors
           relation, and for every sailor s, either s has a higher 
           rating than all sailors under 21 or s has a rating no
           higher than all sailors under 21.
4. Consider the instance of Sailors shown in Table 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.
Solution :
          Consider the instance of Sailors shown in Table 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.
(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.
Solution :
          Consider the instance of Sailors shown in Table 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.

Previous Home Next
SQL Based Questions – Part 4 Questions On SQL – Part 6

     

Leave a Reply