Consider a database with three relation instances shown below. The primary…
2006
Consider a database with three relation instances shown below. The primary keys for the Drivers and Cars relation are did and cid respectively and the records are stored in ascending order of these primary keys as given in the tables. No indexing is available in the database.
What is the output of the following SQL query?
select D.dname
from Drivers D
where D.did in (
select R.did
from Cars C, Reserves R
where R.cid = C.cid and C.colour = 'red'
intersect
select R.did
from Cars C, Reserves R
where R.cid = C.cid and C.colour = 'green'
)- A.
Karthikeyan, Boris
- B.
Sachin, Salman
- C.
Karthikeyan, Boris, Sachin
- D.
Schumacher, Senna
Attempted by 72 students.
Show answer & explanation
Correct answer: A
Goal: find drivers who have reserved at least one red car and at least one green car (the intersection of drivers who reserved red cars and drivers who reserved green cars).
Identify car IDs by colour: red cars have cid 102 and 104; green cars have cid 103.
Find drivers who reserved red cars by scanning Reserves for cid 102 or 104: dids found are 22, 31, and 64 (Karthikeyan, Boris, Sachin).
Find drivers who reserved green cars by scanning Reserves for cid 103: dids found are 22, 31, and 74 (Karthikeyan, Boris, Senna).
Compute the intersection of the two driver sets: {22, 31, 64} ∩ {22, 31, 74} = {22, 31}.
Map the resulting dids to driver names: did 22 = Karthikeyan, did 31 = Boris.
Final output: Karthikeyan, Boris