-- General Query Pattern for SELECT
--
-- SELECT        [DISTINCT]  target-list
-- FROM         relation-list
-- WHERE        qualification

-- QUERY EXAMPLES

-- Find the sid of sailors who’ve reserved boat #103

SELECT  S.sname
FROM     Sailors S, Reserves R
WHERE  S.sid=R.sid AND R.bid=103

-- Find the sid of sailors who’ve reserved a red boat 

SELECT  R.sid
FROM  Boats B, Reserves R
WHERE  R.bid=B.bid AND B.color = 'red'

-- Find the name of sailors who’ve reserved a red boat 

SELECT  S.sname
FROM  Sailors S, Boats B, Reserves R
WHERE  S.sid=R.sid AND R.bid=B.bid AND B.color = 'red'

-- Find sailors who’ve reserved at least one boat 

SELECT  S.sid
FROM  Sailors S, Reserves R
WHERE  S.sid=R.sid

-- Find distinct sailors who’ve reserved at least one boat 

SELECT DISTINCT S.sid
FROM  Sailors S, Reserves R
WHERE  S.sid=R.sid

-- Do math in your query, rename columns
-- Find the ages of sailors with names starting with B

SELECT  S.age, S.age-5 as age1, 2*S.age AS age2
FROM  Sailors S
WHERE  S.sname LIKE 'B_%';

-- Set operations
-- UNION, EXCEPT, INTERSECT

-- Find sid’s of sailors who’ve reserved a red or a green boat 

SELECT  S.sid
FROM  Sailors S, Boats B, Reserves R
WHERE  S.sid=R.sid AND R.bid=B.bid
                AND B.color='red'
UNION
SELECT  S.sid
FROM  Sailors S, Boats B, Reserves R
WHERE  S.sid=R.sid AND R.bid=B.bid
                AND B.color='green'

-- Find sid’s of sailors who’ve reserved a red boat but not a green boat 

SELECT  S.sid
FROM  Sailors S, Boats B, Reserves R
WHERE  S.sid=R.sid AND R.bid=B.bid
                AND B.color='red'
EXCEPT
SELECT  S.sid
FROM  Sailors S, Boats B, Reserves R
WHERE  S.sid=R.sid AND R.bid=B.bid
                AND B.color='green'

-- Find sid’s of sailors who’ve reserved a red and a green boat 

SELECT  S.sid
FROM  Sailors S, Boats B, Reserves R
WHERE  S.sid=R.sid AND R.bid=B.bid
                AND B.color='red'
INTERSECT
SELECT  S.sid
FROM  Sailors S, Boats B, Reserves R
WHERE  S.sid=R.sid AND R.bid=B.bid
                AND B.color='green'

-- Another way to find names of sailors who’ve reserved boat #103 

SELECT  S.sname
FROM  Sailors S
WHERE  S.sid IN  (SELECT  R.sid
                               FROM  Reserves R
                               WHERE  R.bid=103)

-- Find names of sailors who’ve not reserved boat #103 

SELECT  S.sname
FROM  Sailors S
WHERE  S.sid NOT IN  (SELECT  R.sid
                               FROM  Reserves R
                               WHERE  R.bid=103)


-- Find names of sailors who’ve reserved boat #102:

SELECT  S.sname
FROM  Sailors S
WHERE   EXISTS  (SELECT  *
                             FROM  Reserves R
                             WHERE  R.bid=102 AND S.sid=R.sid)

-- Find sailors who’ve reserved all boats 
-- NOT EXISTS works like EXISTS, except the WHERE clause in which it is used is satisfied if no rows are returned by the subquery.
-- (i.e. Find all the sailors where there are no boats I have not reserved.)

SELECT  S.sname
FROM  Sailors S
WHERE  NOT EXISTS  (SELECT  B.bid
                                    FROM  Boats B
                                    WHERE  NOT EXISTS  (SELECT  R.bid
                                                                        FROM  Reserves R
                                                                        WHERE  R.bid=B.bid
                                                                                              AND R.sid=S.sid))
-- Aggregation Queries

-- Find the number of sailors

SELECT  COUNT (*)
FROM  Sailors S

-- Find the average age for sailors with a rating of 10

SELECT  AVG (S.age)
FROM  Sailors S
WHERE  S.rating=10

-- Find the name of sailors with the maximum rating

SELECT  S.sname
FROM  Sailors S
WHERE  S.rating= (SELECT  MAX(S2.rating)
                                 FROM  Sailors S2)

-- Find the number of unique ratings for sailors named Beryl

SELECT  COUNT (DISTINCT S.rating)
FROM  Sailors S
WHERE S.sname='Beryl'

-- Find the average of unique ages for sailors with a rating of 10

SELECT  AVG ( DISTINCT S.age)
FROM  Sailors S
WHERE  S.rating=10

-- Find name and age of the oldest sailor(s) 

SELECT  S.sname, S.age
FROM  Sailors S
WHERE  (SELECT  MAX (S2.age)
               FROM  Sailors S2)
               = S.age

-- Find the age of the youngest sailor with age  >=  18, for each rating with at least 2 such sailors 

SELECT  S.rating,  MIN (S.age)
FROM  Sailors S
WHERE  S.age >= 18
GROUP BY  S.rating
HAVING  COUNT (*) > 1

-- Find the age of the youngest sailor with age > 18, for each rating with at least 2 sailors (of any age) 

SELECT  S.rating,  MIN (S.age)
FROM  Sailors S
WHERE  S.age > 18
GROUP BY  S.rating
HAVING  1  <  (SELECT  COUNT (*)
                         FROM  Sailors S2
                         WHERE  S.rating=S2.rating)