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
nonNULL 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 :
(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 :
Related