520 likes | 855 Views
SQL (2). UNION, INTERSECT, AND EXCEPT. Find the names of sailors who have reserved a red or a green boat. Find the names of sailors who have reserved a red or a green boat. Find the names of sailors who have reserved both a red and a green boat.
E N D
UNION, INTERSECT, AND EXCEPT Find the names of sailors who have reserved a red or a green boat.
Find the names of sailors who have reserved a red or a green boat.
Find the names of sailors who have reserved both a red and a green boat.
Find the names of sailors who have reserved both a red and a green boat.
Find the names of sailors who have reserved both a red and a green boat. – use INTERSECT
Find the names of sailors who have reserved a red or a green boat. – Use UNION
Find the sids of all sailors who have reserved red boats but not green boats
Find the sids of all sailors who have reserved red boats but not green boats
Find all sids of all sailors who have a rating of 10 or have reserved boat 104
Find all sids of all sailors who have a rating of 10 or have reserved boat 104
Nested Query(SubQuery) • A nested query is a query that has another query embedded within it; • The embedded query is called a subquery. • A subqueryappears within the WHERE clause of a query. • Subqueriescan sometimes appear in the FROM clause or the HAVING clause
Set-Comparison Operators • op ANY and op ALL • op : <, <=, =, <>, >=, > • SOME : a synonym for ANY
Find the sailors whose rating is better than some sailor called Horatio
Find the sailors whose rating is better than some sailor called Horatio
Replace ANY with ALL SELECT S.Sid FROM Sailors S WHERE S.Rating > ALL (SELECT S2.Rating FROM Sailors S2 WHERE S2.sname = ‘Horatio’)
Find the names of sailors who have reserved both a red and a green boat.
Find the names of sailors who have reserved both a red and a green boat.
AGGREGATE OPERATORS • COUNT ([DISTINCT] A) : the number of (unique) values in the A column. • SUM ([DISTINCT] A) : the sum of all (unique) values in the A column. • AVG ([DISTINCT] A) : the average of all (unique) values in the A column. • MAX : the maximal value in the column A • MIN : the minimal value in the column A
Find the name of sailors who are older than the oldest sailor with the rating of 10
Find the name of sailors who are older than the oldest sailor with the rating of 10
The Group By and Having Clauses Find the age of youngest sailor for each rating level
Find the age of youngest sailor for each rating level Where i = 1, 2, …, 10
Find the age of the youngest sailor who is eligible to vote (at least 18 years old) for each rating level with at least two such sailors
Find the age of the youngest sailor who is eligible to vote (at least 18 years old) for each rating level with at least two such sailors
For each red boat, find the number of reservations for this boat
For each red boat, find the number of reservations for this boat X
Find the average age of sailors for each rating level that has at least two sailors
Find the average age of sailors for each rating level that has at least two sailors