DBMS_Examples/Q7.sql at master · lucas-acc/DBMS_Examples · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- (Q7) Find the names of sailors who have reserved at least two boats.

/* relational algebra
sigma :: selection
pi :: projection
x :: natural join
p :: renaming
U :: union

p(Reservations, pi_{sid,sname,bid}(Sailors x Reserves))
p(Reservationpairs(1 -> sid1, 2 -> sname1, 3 -> bid1,
                   4 -> sid2, 5 -> sname2, 6 -> bid2),
                   Reservations x Reservations )
pi_sname sigma_{sid1=sid2 and bid1 != bid2} Reservations

*/

use dbms_exercises;

SELECT S.sname
FROM Sailors S, Reserves R, Sailors S2, Reserves R2
WHERE S.sid = R.sid
  and S2.sid = R2.sid
  and S.sid = S2.sid
  and R.bid != R2.bid

/* result
sname
Dustin
Dustin
Dustin
Dustin
Dustin
Dustin
Dustin
Dustin
Dustin
Dustin
Dustin
Dustin
Lubber
Lubber
Lubber
Lubber
Lubber
Lubber
Horatio
Horatio
*/