Example: Converting Relational Division to SQL
Get names of guests who have reservations for all presidential suites (i.e. those that have two beds).
pName(g |´| (pGuestNr,RoomNrs ¸ pRoomNrsNrBeds=2r)
pName(g |´| (pGuestNrs - pGuestNr((pGuestNrs |´| pRoomNrsNrBeds=2r) - pGuestNr,RoomNrs)))
pGuestNrs |´| pRoomNrsNrBeds=2r
mysql> select distinct s.GuestNr, r.RoomNr
-> from Reservation s, Room r
-> where NrBeds = 2;
+----------+-----------+
| GuestNr | RoomNr |
+----------+-----------+
| 101 | 1 |
| 101 | 2 |
| 101 | 3 |
| 102 | 1 |
| 102 | 2 |
| 102 | 3 |
| 103 | 1 |
| 103 | 2 |
| 103 | 3 |
| 104 | 1 |
| 104 | 2 |
| 104 | 3 |
| 105 | 1 |
| 105 | 2 |
| 105 | 3 |
| 106 | 1 |
| 106 | 2 |
| 106 | 3 |
+----------+-----------+
18 rows in set (0.00 sec)
(pGuestNrs |´| pRoomNrsNrBeds=2r) - pGuestNr,RoomNrs
mysql> select distinct s.GuestNr, r.RoomNr
-> from Reservation s, Room r
-> where NrBeds = 2 and
-> (s.GuestNr, r.RoomNr) not in
-> (select GuestNr, RoomNr from Reservation);
+----------+-----------+
| GuestNr | RoomNr |
+----------+-----------+
| 102 | 1 |
| 102 | 2 |
| 103 | 2 |
| 103 | 3 |
| 104 | 1 |
| 104 | 2 |
| 104 | 3 |
| 105 | 2 |
| 105 | 3 |
| 106 | 1 |
| 106 | 3 |
+----------+-----------+
11 rows in set (0.00 sec)
pGuestNr((pGuestNrs |´| pRoomNrsNrBeds=2r) - pGuestNr,RoomNrs))
mysql> select distinct s.GuestNr
-> from Reservation s, Room r
-> where NrBeds = 2 and
-> (s.GuestNr, r.RoomNr) not in
-> (select GuestNr, RoomNr from Reservation);
+----------+
| GuestNr |
+----------+
| 102 |
| 103 |
| 104 |
| 105 |
| 106 |
+----------+
5 rows in set (0.00 sec)
pGuestNrs - pGuestNr((pGuestNrs |´| pRoomNrsNrBeds=2r) - pGuestNr,RoomNrs))
mysql> select distinct GuestNr
-> from Reservation
-> where GuestNr not in
-> (select distinct s.GuestNr
-> from Reservation s, Room r
-> where NrBeds = 2 and
-> (s.GuestNr, r.RoomNr) not in
-> (select GuestNr, RoomNr from Reservation));
+----------+
| GuestNr |
+----------+
| 101 |
+----------+
1 row in set (0.00 sec)
pName(g |´| (pGuestNrs - pGuestNr((pGuestNrs |´| pRoomNrsNrBeds=2r) - pGuestNr,RoomNrs)))
mysql> select Name
-> from Guest natural join
-> (select distinct GuestNr
-> from Reservation
-> where GuestNr not in
-> (select distinct s.GuestNr
-> from Reservation s, Room r
-> where NrBeds = 2 and
-> (s.GuestNr, r.RoomNr) not in
-> (select GuestNr, RoomNr from Reservation)))
-> guestsWithReservations;
+-------+
| Name |
+-------+
| Smith |
+-------+
1 row in set (0.00 sec)