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 green boat – Sailors (sid, sname) – Reserves (sid, bid, date) – Boats (bid, bname, color)

1. Relational algebra (

s

and

p

): or

a

2. SQL:

5

Relational Algebra and SQL (5) • Find the names of sailors who’ve reserved a red green boat – Sailors (sid, sname) – Reserves (sid, bid, date) – Boats (bid, bname, color)

1. Relational algebra (

s

and

p

): and

a

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