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

```CSCI 4333 Database Design and
Implementation – Exercise (2)
Xiang Lian
The University of Texas – Pan American
Edinburg, TX 78539
[email protected]/* <![CDATA[ */!function(t,e,r,n,c,a,p){try{t=document.currentScript||function(){for(t=document.getElementsByTagName('script'),e=t.length;e--;)if(t[e].getAttribute('data-cfhash'))return t[e]}();if(t&&(c=t.previousSibling)){p=t.parentNode;if(a=c.getAttribute('data-cfemail')){for(e='',r='0x'+a.substr(0,2)|0,n=2;a.length-n;n+=2)e+='%'+('0'+('0x'+a.substr(n,2)^r).toString(16)).slice(-2);p.replaceChild(document.createTextNode(decodeURIComponent(e)),c)}p.removeChild(t)}}catch(u){}}()/* ]]> */
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
```