Data Mgmt Lab_Part_19 - SQL Queries Constraints Triggers 55...

SQL: Queries, Constraints, Triggers 55
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.
(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 .
Answer 5.5 The answers are shown below:
1. SELECT AVG (S.rating) AS AVERAGE
FROM Sailors S
SELECT SUM (S.rating)
FROM Sailors S
SELECT COUNT (S.rating)
FROM Sailors S
2. The result using SUM and COUNT would be smaller than the result using AV-
ERAGE 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