GATE IT 2006 | Question: 84

Question

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'
                         )

• A. Karthikeyan, Boris
• B. Sachin, Salman
• C. Karthikeyan, Boris, Sachin
• D. Schumacher, Senna

Comments

A very very important read related to the treatment of NULL’s by SELECT and SET OPERATIONS http://dcx.sybase.com/1200/en/dbusage/sorting-s-4872731.html

---

@jatinmittal199510 Sir, please consider sharing more such informative links if possible because in SQL there are so many rules which I came across while solving PYQs and reading comments and answers on GO. The link you shared may be one of its kind but the link isn't accessible. Thank you very much.

Answer 1

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

Comments

For color red, it should be {22,22,31,31,64}

?

---

intersection removes duplicate but intersection ALL  does not remove duplicate

Answer 2

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}

Comments

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

Answer 3

answer should be (A)

it will give rid as 22,31.

Answer 4

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