edited by
4,162 views
22 votes
22 votes

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
edited by

4 Answers

Best answer
39 votes
39 votes
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
edited by
8 votes
8 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}
edited by
6 votes
6 votes
answer should be (A)

it will give rid as 22,31.
1 votes
1 votes

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...

edited by
Answer:

Related questions

31 votes
31 votes
3 answers
2
22 votes
22 votes
2 answers
3
Ishrat Jahan asked Nov 1, 2014
6,318 views
Consider a relation R with five attributes $V, W, X, Y,$ and $Z.$ The following functional dependencies hold:$VY→ W, WX → Z,$ and $ZY → V.$Which of the following is...
42 votes
42 votes
4 answers
4