{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Data Mgmt Lab_Part_19

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

This preview shows pages 1–2. Sign up to view the full content.

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.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}