Consider a database consisting of the following tables. SAILORS(sid, sname, rati
ID: 3852059 • Letter: C
Question
Consider a database consisting of the following tables.
SAILORS(sid, sname, rating, age)
BOATS(bid, bname, color)
RESERVED(sid, bid day)
Write a SQL statement for each of the following queries.
1. Find the snames of sailors who have reserved a green boat but not a red boat.
2. Find the snames of sailors with age over 20 who have not reserved a red boat.
3. Find the snames of sailors who have reserved at least three boats.
4. Find the snames of sailors who have reserved all boats.
5. Calculate the average age of all sailors.
6. Find the name and the age of the youngest sailor.
7. For each boat, find the boat id and the average age of sailors who reserved it.
8. Find the sids of sailors with the highest rating.
Explanation / Answer
1.
SELECT sname
FROM SAILORS S, RESERVED R
WHERE S.sid=R.sid AND R.bid IN ( SELECT B.bid
FROM BOATS B
WHERE B.color='green')
EXCEPT
SELECT sname
FROM SAILORS S, RESERVED R
WHERE S.sid=R.sid AND R.bid IN ( SELECT B.sid
FROM BOATS B
WHERE B.color='red')
2.
SELECT sname
FROM SAILORS S, RESERVED R
WHERE S.sid=R.sid AND S.age>20 AND R.bid NOT IN ( SELECT B.bid
FROM BOATS B
WHERE B.color='red')
3.
SELECT sname
FROM SAILORS
WHERE sid IN ( SELECT S.sid
FROM SAILORS S, RESERVED R
WHERE S.sid = R.sid
GROUP BY S.sid
HAVING COUNT(*) >= 3)
4.
SELECT S.sname
FROM SAILORS S
WHERE NOT EXISTS (SELECT B.bid
FROM BOATS B
WHERE NOT EXISTS(SELECT R.bid
FROM RESERVED R
WHERE R.bid = B.bid
AND R.sid = S.sid))
5. SELECT AVG(age) FROM SAILORS
6.
SELECT sname, age
FROM SAILORS
WHERE age=(SELECT MAX(S.age)
FROM SAILORS S)
7.
SELECT bid, AVG(age)
FROM SAILORS S, RESERVED R
WHERE S.sid=R.sid
GROUP BY R.bid
8.
SELECT sid
FROM SAILOR
WHERE rating=(SELECT MAX(S.rating) FROM SAILORS S)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.