1. Print the names of sailors who have reserved at least two boats 2. Print the

Question

1. Print the names of sailors who have reserved at least two boats
2. Print the names of sailors whose rating is within (inclusive) 5 and 8 and have
reserved more than two boats
3. Find the average age of sailors who have a rating greater than 7 and have reserved
a boat named Clipper or Interlake
4. Find the age of the oldest sailor for each rating level that has reserved a red boat
5. Find the sailors whose rating is worse than some sailor named Andy

CREATE TABLE boats( bid integer, bname char(20), color char (20), PRIMARY KEY (bid)); CREATE TABLE sailors(sid integer, sname char(30) rating number, age number PRIMARY KEY (sid)); CREATE TABLE reserves sid integer, bid integer, day date, PRIMARY KEY (sid,bid,day), CONSTRAINT FK. BID-RES FOREIGN CONSTRAINT FK. SID-RES FOREIGN KEY KEY (bid) (sid) REFERENCES boats(bid), REFERENCES sailors(sid));

Explanation / Answer

Assumption : Working in Microsoft SQL Server

-- 1

Select s.sid, s.sname, count(*)

From sailors s inner join reserves r

on s.sid = r.sid

inner join boats b

on r.bid = b.bid

Group by s.sid, s.sname

having count(*) > 1

-- 2

Select s.sid, s.sname, count(*)

From sailors s inner join reserves r

on s.sid = r.sid

inner join boats b

on r.bid = b.bid

Where s.rating between 5 and 8

Group by s.sid, s.sname

having count(*) > 1

-- 3

Select AVG(s.age) 'AverageAge'

From sailors s inner join reserves r

on s.sid = r.sid

inner join boats b

on r.bid = b.bid

Where s.rating > 7

AND (b.bname = 'Clipper' OR b.bname = 'Interlake')

-- 4

Select Max(s.age)

From sailors s inner join reserves r

on s.sid = r.sid

inner join boats b

on r.bid = b.bid

Where b.color = 'Blue'

Group by s.rating

-- 5

Select sname

From sailors

Where rating < (select rating from sailors where sname = 'Andy')

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.