Exercise (2) - UTPA Faculty Web - The University of Texas

Report
CSCI 4333 Database Design and
Implementation – Exercise (2)
Xiang Lian
The University of Texas – Pan American
Edinburg, TX 78539
[email protected]
1
Relational Algebra and SQL (1)
• Find ids of sailors who’ve reserved boat with bid 103
– Sailors (sid, sname)
– Reserves (sid, bid, date)
– Boats (bid, bname, color)
1. Relational algebra (s and p):
2. SQL:
2
Relational Algebra and SQL (2)
• Find the names of sailors who’ve reserved boat with bid
103
– Sailors (sid, sname)
– Reserves (sid, bid, date)
– Boats (bid, bname, color)
1. Relational algebra (s and p):
2. SQL:
3
Relational Algebra and SQL (3)
• Find the names of sailors who’ve reserved a red boat
– Sailors (sid, sname)
– Reserves (sid, bid, date)
– Boats (bid, bname, color)
1. Relational algebra (s and p):
2. SQL:
4
Relational Algebra and SQL (4)
• Find the names of sailors who’ve reserved a red or a
green boat
– Sailors (sid, sname)
– Reserves (sid, bid, date)
– Boats (bid, bname, color)
1. Relational algebra (s and p):
2. SQL:
5
Relational Algebra and SQL (5)
• Find the names of sailors who’ve reserved a red and a
green boat
– Sailors (sid, sname)
– Reserves (sid, bid, date)
– Boats (bid, bname, color)
1. Relational algebra (s and p):
2. SQL:
6
Relational Algebra and SQL (6)
• Find the ids of sailors who have made at least two
reservations on the same date
– Sailors (sid, sname)
– Reserves (sid, bid, date)
– Boats (bid, bname, color)
1. Relational algebra (s and p):
2. SQL:
7
Relational Algebra and SQL (7)
• 1. Find the ids of sailors who’ve reserved all boats
• 2. Find the ids of sailors who’ve reserved all red boats
• 3. Find the names of sailors who’ve reserved all red boats
– Sailors (sid, sname)
– Reserves (sid, bid, date)
– Boats (bid, bname, color)
1. Relational algebra (s and p):
2. SQL:
8
9

similar documents