Refer to the schemas for Sailors, Boats and Reserves introduced in the class, sp

Question

Refer to the schemas for Sailors, Boats and Reserves introduced in the class, specify the following queries in SQL:

Retrieve the names of the boats that have been reserved by either “John Smith” or “James Brown”.

Retrieve the bids of the red boats that are not reserved by any one in “August,3, 2007”.

Retrieve the names of the sailors who have reserved a red boat but not green boat. Use different copies of relevant relation to specify this query. Can you use except operator? Why or why not?

For each boat color, retrieve the total number of reservations made by “John Smith”.

For each sailor who has reserved more than three red boats, give his/her name and sid, and total number of red boats he/she has reserved.

For each green boat which has been reserved by more than three sailors, give the total number of sailors who have reserved it and the maximum age of these sailors.

For each rating level with at least two sailors who have made some reservations and at most five boats reserved by the sailors at that level, retrieve the average age of the sailors who have made at least one reservatio

Explanation / Answer

Ok, My SQL is a little rusty so please make sure to create these tables with dummy data and test these out, play with them until they do what you want from them.

The data from your post is:
Sailors (sid; interger, sname:string, rating:interger,age: real)
Boats(bid:integer, bname: string, color:string)
Reserves( sid:integer, bid:integer, day:date)

There are many ways to retrieve the same data in SQL, some more complex than others, some more elegant than others. I'm not sure which operators you are encouraged to use for this exercise, so I'll try and write each one in more than one way that I'm "almost" sure is correct. So make sure to test them all out with real data from your database and run the correct SQL syntax. DON'T EXPECT TO JUST COPY PASTE THESE AND HAVE THEM WORK JUST LIKE THAT. Fair warning :). I hope this helps.

1.Retrieve the names of the boats that have been reserved by either “John Smith” or “James Brown”.

(opt. 1) SELECT B.bname FROM Boats B, Reserves R, Sailors S WHERE (B.bid=R.bid) AND (R.sid=S.sid) AND (S.sname = "John Smith" OR S.sname = "James Brown");
(opt. 2) SELECT B.bname FROM (Boats B INNER JOIN Reserves R ON B.bid = R.bid) INNER JOIN Sailors S ON R.sid = S.sid WHERE S.sname = "John Smith" OR S.sname = "James Brown";
(opt. 3) SELECT bname FROM Boats WHERE bid IN ( SELECT bid FROM Reserves WHERE sid IN ( SELECT sid FROM Sailors WHERE sname = "John Smith" OR sname = "James Brown") );

2.Retrieve the bids of the red boats that are not reserved by any one in “August,3, 2007”.

(opt. 1) SELECT bid FROM Boats WHERE color="Red" AND bid NOT IN (SELECT bid FROM Reserves WHERE day= 'August,3, 2007');
(opt. 2) SELECT bid FROM Boats WHERE color="Red" EXCEPT SELECT bid FROM Reserves WHERE day='August,3, 2007';

3.Retrieve the names of the sailors who have reserved a red boat but not green boat. Use different copies of relevant relation to specify this query.
Can you use except operator? Why or why not?
Intuitively, you can since what we're doing is finding the SET of sailors having reserved a red boat, within that set there could be some sailors who have reserved a green boat at some point as well. So, from that red SET we want to remove those that have reserved a green boat.
(opt. 1) SELECT sname FROM Sailors WHERE sid IN ( SELECT sid FROM Reserves WHERE bid IN ( SELECT bid FROM Boats WHERE color ="Red") ) INTERSECT
    SELECT sname FROM Sailors WHERE sid IN ( SELECT sid FROM Reserves WHERE bid NOT IN ( SELECT bid FROM Boats WHERE color ="Green") );
(opt. 2) SELECT sname FROM Sailors WHERE sid IN ( SELECT sid FROM Reserves WHERE bid IN ( SELECT bid FROM Boats WHERE color ="Red") ) EXCEPT or MINUS
    SELECT sname FROM Sailors WHERE sid IN ( SELECT sid FROM Reserves WHERE bid IN ( SELECT bid FROM Boats WHERE color ="Green") );

4.For each boat color, retrieve the total number of reservations made by “John Smith”.

(opt. 1) SELECT B.color, COUNT(R.bid) AS Reserv_Count FROM Reserves R, Boats B, Sailors S WHERE R.bid = B.bid AND R.sid=S.sid AND sname="John Smith" GROUP BY B.color;
(opt. 2) SELECT B.color, COUNT(R.bid) AS Reserv_Count FROM (Reserves R INNER JOIN Boats B ON R.bid = B.bid) INNER JOIN Sailors S ON R.sid = S.sid WHERE S.sname = "John Smith" GROUP BY B.color;

5.For each sailor who has reserved more than three red boats, give his/her name and sid, and total number of red boats he/she has reserved.
  
(opt. 1) SELECT S.sname, S.sid, COUNT(R.bid) FROM Reserves R, Boats B, Sailors S WHERE R.bid = B.bid AND R.sid=S.sid AND B.color = "Red" GROUP BY S.sid HAVING COUNT(R.bid) > 3;
(opt. 2) SELECT S.sname, S.sid, COUNT(R.bid) FROM (Reserves R INNER JOIN Boats B ON R.bid = B.bid) INNER JOIN Sailors S ON R.sid = S.sid WHERE B.color = "Red" GROUP BY S.sid HAVING COUNT(R.bid) > 3;

6.For each green boat which has been reserved by more than three sailors, give the total number of sailors who have reserved it and the maximum age of these sailors.
  
(opt. 1) SELECT B.bname, COUNT(DISTINCT R.sid) AS Sailor_Count, MAX(S.age) AS Max_Age FROM Reserves R, Boats B, Sailors S WHERE R.bid = B.bid AND R.sid=S.sid AND B.color = "Green" GROUP BY B.bid HAVING COUNT(DISTINCT R.sid) > 3;
(opt. 2) SELECT B.bname, COUNT(DISTINCT R.sid) AS Sailor_Count, MAX(S.age) AS Max_Age FROM (Boats INNER JOIN Reserves ON R.bid = B.bid) INNER JOIN Sailors ON R.sid = S.sid WHERE B.color = "Green" GROUP BY B.bid HAVING COUNT(DISTINCT R.sid) > 3;

7.For each rating level with at least two sailors who have made some reservations and at most five boats reserved by the sailors at that level, retrieve the average age of the sailors who have made at least one reservation
Here I assume the "at most five boats reserved) is counting boat reservations - so if the sailor booked the same boat twice it counts as 2 reservations.
On the other hand, if the meaning here is for each distinct boat reserved X times only counts once, then just modify the having clause count to count(distinct R.bid).

   (opt. 1) SELECT DISTINCT S.rating , avg(S.sid) AS Avg_Age FROM Reserves R, Sailors S WHERE R.sid = S.sid GROUP BY S.rating HAVING COUNT(DISTINCT R.Sid) >= 2 AND COUNT(R.bid) > 0 AND COUNT(R.bid) <= 5;
   (opt. 2) SELECT DISTINCT S.rating , AVG(S.sid) AS Avg_Age FROM Reserves R INNER JOIN Sailors S ON R.sid = S.sid GROUP BY S.rating HAVING COUNT(DISTINCT R.Sid) >= 2 AND COUNT(R.bid) > 0 AND COUNT(R.bid) <= 5;

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

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