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
This
preview
has intentionally blurred sections.
Sign up to view the full version.

This is the end of the preview.
Sign up
to
access the rest of the document.
- Fall '12
- Dr.Vishak
- Strain, Relational model, Ahab, Query optimizer, Join
-
Click to edit the document details