Examples of SQL

An Instance S3 of Sailors				R2 of Reserves			B1 of Boats
sid	sname	rating	age	sid	bid	day	bid	bname	color
22	Dustin	7	45.0	22	101	10/10/98	101	Interlake	blue
29	Brutus	1	33.0	22	102	10/10/98	102	Interlake	red
31	Lubber	8	55.5	22	103	10/8/98	103	Clipper	green
32	Andy	8	25.5	22	104	10/7/98	104	Marine	red
58	Rusty	10	35.0	31	102	11/10/98
64	Horatio	7	35.0	31	103	10/6/98
71	Zorba	10	16.0	31	104	11/12/98
74	Horatio	9	35.0	64	101	9/5/98
85	Art	3	25.5	64	102	9/8/98
95	Bob	3	63.5	74	103	9/8/98

1. Find names of sailors who’ve reserved boat #103

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

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

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

2. Find names of sailors who’ve reserved a red boat

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

SELECT DISTINCT S.sname
FROM   Sailors AS S
WHERE S.sid IN (SELECT R.sid
                 FROM   Reserves AS R
                 WHERE R.bid IN (SELECT B.bid
                                  FROM Boats B
                                  WHERE B.color = 'red'));

3. Find the colors of boats by Lubber

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

SELECT B.color
FROM   Boats B
WHERE B.bid IN (SELECT R.bid
                 FROM   Reseves R
                 WHERE R.sid IN (SELECT S.sid
                                   FROM   Sailors S
                                  WHERE S.sname='Lubber'));

4. Find the names of sailors who’ve reserved at least one boat

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

SELECT DISTINCT S.sid, S.sname
FROM Sailors S
WHERE S.sid IN (SELECT DISTINCT R.sid
FROM Reserves R);

SELECT DISTINCT S.sid, S.sname
FROM Sailors S
WHERE EXISTS (SELECT R.sid
FROM Reserves R
WHERE S.sid = R.sid);

5. Find sailors who’ve reserved a red or a green boat

SELECT DISTINCT S.sname, S.sid
FROM Sailors AS S, Reserves AS R, Boats AS B
WHERE S.sid = R.sid AND R.bid = B.bid AND
(B.color = 'red' OR B.color = 'green');

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

6. Find sailors who’ve reserved a red and a green boat

SELECT DISTINCT S.sname
FROM Sailors S, Reserves R1, Boats B1, Reserves R2, Boats B2
WHERE S.sid = R1.sid AND R1.bid =B1.bid AND
S.sid = R2.sid AND R2.bid = B2.bid AND
B1.color = 'red' AND B2.color = 'green';

Use INTERSECT instead of UNION

7. Find the names of sailors who’ve reserved at least two boats
SELECT DISTINCT sname
FROM Sailors S, Reserves R1, Reserves R2
WHERE S.sid = R1.sid AND R1.sid = R2.sid AND R1.bid <> R2.bid;

8. Find the sids of sailors with age over 20 who've not reserved a red boat

SELECT sid
FROM Sailors
WHERE age >20 AND sid NOT IN ( SELECT R.sid
FROM Reserves R, Boats B
WHERE R.bid = B.bid AND B.color ='red');

9. Find the names of sailors who’ve reserved all boats

SELECT S.sname
FROM Sailors AS S
WHERE NOT EXISTS (SELECT B.bid
                  FROM Boats B
                  WHERE B.bid NOT IN(SELECT R.bid
                                     FROM Reserves R
                                     WHERE R.sid = S.sid));

SELECT S.sname
FROM Sailors AS S
WHERE S.sid IN ( SELECT DISTINCT R.sid
                 FROM Reserves R
                 WHERE R.sid NOT IN (SELECT DISTINCT R1.sid
                                FROM Reserves AS R1, Boats AS B
                             WHERE B.bid NOT IN ( SELECT R2.bid
                                                FROM Reserves R2
                                     WHERE R1.sid = R2.sid )));

10. Find the names of sailors who’ve reserved all boats called Interlake

SELECT DISTINCT S.sname, S.sid
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND B.bname = 'Interlake' AND NOT EXISTS (SELECT sid, bid
FROM Reserves R1
WHERE R.sid = R1.sid AND B.bid = R1.bid );

How to solve?

sid

bid

101

102

3. Cross Product

R x B
sid	bid
22	101
22	102
31	101
31	102
64	101
64	102
74	101
74	102

SELECT DISTINCT R.sid, B.bid
FROM Reserves R, Boats B
WHERE B.bname = 'Interlake';

R3
sid	bid
22	101
22	102
22	103
22	104
31	102
31	103
31	104
64	101
64	102
74	103

5. Difference

R X B - R3
sid	bid
31	101
74	101
74	102

6. Join with Sailors to get sname.

SELECT DISTINCT R.sid, B.bid
FROM Reserves R, Boats B
WHERE B.bname = 'Interlake' AND NOT EXISTS
(SELECT sid, bid
FROM Reserves R1
WHERE R.sid = R1.sid AND B.bid = R1.bid );