Sailors sid: integer, sname: string, rating: integer, age: real

1
Sailors( sid integer, sname string, rating
integer, age real) Reserves( sid integer, bid
integer, day date)
Figure 1 an instance S1 of Sailors
2
Q1. Find the names and ages of all sailors.
SELECT S.sname, S.age FROM Sailors S
DB
3
Q1. Find the names and ages of all sailors.
SELECT S.sname, S.age FROM Sailors S
Result
DB
4
Q1. Find the names and ages of all sailors.
SELECT S.sname, S.age FROM Sailors S
Result
DB
5
Q1. Find the names and ages of all sailors.
SELECT S.sname, S.age FROM Sailors S
Result
DB
6
Q1. Find the names and ages of all sailors.
SELECT S.sname, S.age FROM Sailors S
Result
DB
7
Q1. Find the names and ages of all sailors.
SELECT S.sname, S.age FROM Sailors S
Result
DB
8
Q1. Find the names and ages of all sailors.
SELECT S.sname, S.age FROM Sailors S
Result
DB
9
Q1. Find the names and ages of all sailors.
SELECT S.sname, S.age FROM Sailors S
Result
DB
10
Q1. Find the names and ages of all sailors.
SELECT S.sname, S.age FROM Sailors S
Result
DB
11
Q1. Find the names and ages of all sailors.
SELECT S.sname, S.age FROM Sailors S
Result
DB
12
Q1. Find the names and ages of all sailors.
SELECT S.sname, S.age FROM Sailors S
Result
DB
13
Q1. Find the names and ages of all sailors.
SELECT S.sname, S.age FROM Sailors S
Result
DB
14
Q1. Find the names and ages of all sailors.
SELECT S.sname, S.age FROM Sailors S
Result
DB
15
Q2. Find all sailors with a rating above 7.
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
DB
16
Q2. Find all sailors with a rating above 7.
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
Result
DB
17
Q2. Find all sailors with a rating above 7.
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
Result
DB
18
Q2. Find all sailors with a rating above 7.
7gt7?
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
No!
Result
DB
19
Q2. Find all sailors with a rating above 7.
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
Result
DB
20
Q2. Find all sailors with a rating above 7.
8gt7?
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
Yes!
Result
DB
21
Q2. Find all sailors with a rating above 7.
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
Result
DB
22
Q2. Find all sailors with a rating above 7.
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
Result
DB
23
Q2. Find all sailors with a rating above 7.
10gt7?
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
Yes!
Result
DB
24
Q2. Find all sailors with a rating above 7.
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
Result
DB
25
Q2. Find all sailors with a rating above 7.
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
Result
DB
26
Q2. Find all sailors with a rating above 7.
10gt7?
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
Yes!
Result
DB
27
Q2. Find all sailors with a rating above 7.
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
Result
DB
28
Q2. Find all sailors with a rating above 7.
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
Result
DB
29
Q2. Find all sailors with a rating above 7.
9gt7?
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
Yes!
Result
DB
30
Q2. Find all sailors with a rating above 7.
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
Result
DB
31
Q2. Find all sailors with a rating above 7.
SELECT S.sid, S.sname FROM Sailors S WHERE
S.ratinggt7
Result
DB
32
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.name FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
DB (new)
Figure 3 Instance of Reserves
Figure 2 Instance of Sailors
This query requires information from both
tables. To answer this query requires a JOIN
operation to be performed
33
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
A join operation roughly works as follows we
start with a row in table Sailors So, this
JOIN requires about O(nm) operations Where n
Reserves
34
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
Reserves
35
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
Reserves
36
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
S.sid 22
Reserves
37
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
S.sid 22
R.sid 22
S.sid R.sid!
Reserves
38
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
S.sid 22
R.sid 22
S.sid R.sid!
R.bid 101
101 ? 103!
Reserves
39
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
S.sid 22
R.sid 58
S.sid ? R.sid!
Reserves
40
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
Reserves
41
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
S.sid 31
Reserves
42
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
S.sid 31
R.sid 22
S.sid ? R.sid!
Reserves
43
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
S.sid 31
R.sid 58
S.sid ? R.sid!
Reserves
44
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
Reserves
45
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
S.sid 58
Reserves
46
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
S.sid 58
R.sid 22
S.sid ? R.sid!
Reserves
47
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
S.sid 58
R.sid 58
S.sid R.sid!
Reserves
48
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
S.sid 58
R.sid 58
S.sid R.sid!
R.bid 103!
Reserves
49
Q3. Find the names of sailors who have reserved
boat number 103.
SELECT S.sname FROM Sailors S, Reserves R WHERE
S.sid R.sid AND R.bid 103
Result
DB
Sailors
Reserves