GATE | GATE IT 2006 | Question 85

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.

2006_84_85

2006_84_85_2
2006_84_85_3
What is the output of the following SQL query?
2006_84_85_4
(A) Karthikeyan, Boris
(B) Sachin, Salman
(C) Karthikeyan, Boris, Sachin
(D) Schumacher, Senna


Answer: (A)

Explanation:  



The sub-query always executes before the execution of the main query. Subqueries are completed first.The result of the subquery is used as input for the outer query.

from the first inner query:

select R.did from Cars C, Reserves R
where R.cid = C.cid and C.colour = ‘red’.

C.color = “Red”, corresonding C.cid={102,104}.

R.cid=C.cid so there are five rows extracted to this where condition.
R.did = {22, 22, 31,31, 64} from the second inner query:
select R.did from Cars C, Reserves R
where R.cid = C.cid and C.colour = ‘green’

C.color = “Green”, corresponding C.cid={103}

R.cid=C.cid so there are three rows extracted to this where condition.
R.did = {22, 31, 74}

Finally, the outer query selects driver names whose did = {22,31} which
corresponds to Karthikeyan and Boris.
so the correct option is A.

 

This solution is contributed by Nitika Bansal.

Quiz of this Question



My Personal Notes arrow_drop_up


Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.