Sample Solutions for Quiz 4, Question 3, SQL for a Query Tree/Plan 2 MARKS
Marking Scheme:
-1 if they left off the join (the most important part!)
o But the order of the join (inner vs. outer doesn’t matter)
-0.5 per WHERE-clause condition missed
No penalty for omitting r. or s. (but warn them if they didn’t qualify sid).
Ignore spelling mistakes or capitalization. Single-quote or double-quote is fine
(or even no quotes, but warn them).
Don’tworryabout
-0.5 for other non-trivial errors
Version 1:
SELECT
FROM
WHERE
r.bid, r.sid, s.rating Reserves r, Sailors s r.sid = s.sid AND r.name = “Jill” AND r.bid < 125 AND s.rating > 8
1
Version 2:
SELECT
FROM
WHERE
a.Name, f.fromCity, f.toCity Airplanes a, Flights f
a.aID = f.aID AND a.company = “WestJet” AND f.length > 120
2
Version 3:
SELECT
FROM
WHERE
r.flightID, r.rating, r.comment Reviews r, Flights f
a.flightID = f.flightID AND r.rating > 3 AND f.agency = “Air Canada”
3
Version 4:
SELECT
FROM
WHERE
r.bid, r.sid, s.rating Reserves r, Sailors s r.sid = s.sid AND r.rname = “Jill”AND r.bid < 525 AND s.sid > 200
4