Consider a database consisting of the following tables (relations): SAILORS(sid,

Question

Consider a database consisting of the following tables (relations):

SAILORS(sid, name, rating, age)

BOATS(bid, name, color)

RESERVED(sid, bid, day). Write SQL statement for each of the following queries.

(12) Find the names of sailors with age over 20 who have not reserved a green boat.

(13) Find the names of sailors who have reserved at least five boats.

(14) Calculate the average age of all sailors.

(15) Find the name and the age of the youngest sailor.

(16) Find the names of sailors with the highest rating.

Explanation / Answer

Solution:

12)

SELECT sname from SAILORS INNER JOIN RESERVED ON SAILORS.sid = RESERVED.sid INNER JOIN BOATS ON BOATS.bid = RESERVED.bid WHERE age>20 AND (BOATS.color != 'green'));

13)

SELECT sname from SAILORS INNER JOIN RESERVED ON SAILORS.sid = RESERVED.sid INNER JOIN BOATS ON BOATS.bid = RESERVED.bid WHERE COUNT(RESERVED.bid)>= 5

14)

SELECT AVG(age) FROM SAILORS

15)

SELECT sname, age FROM SAILORS WHERE MIN(age)

16)

SELECT sname, FROM SAILORS WHERE MAX(rating)

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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