The Gateway to Computer Science Excellence
+16 votes
1.4k views

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.
$$\overset{\text{D: Drivers relation}}{\begin{array}{|c|c|c|c|}\hline\\
\textbf{did}&    \textbf{dname}&  \textbf{rating}& \textbf{age} \\\hline
22&     \text{Karthikeyan}&    7&      25  \\ \hline   
29&     \text{Salman}& 1&      33 \\     \hline
31&     \text{Boris}&  8&      55      \\\hline
32&     \text{Amoldt}& 8&      25      \\\hline
58&     \text{Schumacher}&     10&     35  \\\hline    
64&     \text{Sachin}& 7&      35     \\\hline   
71&     \text{Senna}&  10&     16       \\\hline 
74&     \text{Sachin}& 9&      35       \\\hline 
85&     \text{Rahul}&  3&      25       \\\hline 
95&     \text{Ralph}&  3&      53 \\\hline 
\end{array}} \qquad \overset{\text{R: Reserves relation}}{\begin{array}{|c|c|c|}\hline\\
\textbf {did} & \textbf {Cid} & \textbf {day} \\\hline
22 & 101 & 10/10/06   \\ \hline   
22 & 102 & 10/10/06\\     \hline
22 &   103 & 08/10/06    \\\hline
22 & 104   & 07/10/06     \\\hline
31 & 102 & 10/11/16  \\\hline    
31&103 &06/11/16    \\\hline   
31 & 104&12/11/16      \\\hline 
64 & 101 &05/09/06     \\\hline 
64& 102 & 08/09/06       \\\hline 
74 & 103 & 08/09/06  \\\hline 
\end{array}}$$
$$\overset{\text{C: Cars relation}}{\begin{array}{|c|c|c|c|}\hline\\
\textbf {Cid} & \textbf {Cname} & \textbf{colour} \\\hline
  101 & \text{Renault} & \text{blue}   \\ \hline    102 & \text{Renault} & \text{red}   \\ \hline   
103 & \text{Ferrari} & \text{green}   \\\hline
104 & \text{Jaguar} & \text{red}   \\\hline
\end{array}}$$
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'
                         )

  1. Karthikeyan, Boris
  2. Sachin, Salman
  3. Karthikeyan, Boris, Sachin
  4. Schumacher, Senna
in Databases by Boss (16.3k points)
edited by | 1.4k views

4 Answers

+32 votes
Best answer
For color = "Red", $did = \{22, 22, 31, 31,64\}$

For color = "Green", $did = \{22, 31, 74\}$

Intersection of Red and Green will give $did = \{22, 31\}$ which is Karthikeyan and Boris

Answer: A
by Boss (13.6k points)
edited by
+4
For color red, it should be {22,22,31,31,64}

?
+2
intersection removes duplicate but intersection ALL  does not remove duplicate
+7 votes
ANswer is option A.

Start from inner queries.

from the first inner query. You get did ={22,22,31,31,64}

from the second inner query. You get did={22,31,74}

now intesect operaion you get : {22,31}

Finally, the outer query selects driver names whose did = {22,31}

hence the o/p is :{Karthikeyan, Boris}
by Loyal (8.1k points)
edited by
+1

from the second inner query. You get did={22,31,74}

+6 votes
answer should be (A)

it will give rid as 22,31.
by Loyal (8.5k points)
+1 vote

The query prints names of drivers who has reserved atleast one red car AND atleast 1 green car ...

Only Karthikeyan and Boris have done this...

by Loyal (8k points)
edited by
Answer:

Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
50,737 questions
57,388 answers
198,578 comments
105,416 users