?

The Gateway to Computer Science Excellence

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

- Karthikeyan, Boris
- Sachin, Salman
- Karthikeyan, Boris, Sachin
- Schumacher, Senna

+32 votes

Best answer

+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}

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}

- All categories
- General Aptitude 1.9k
- Engineering Mathematics 7.5k
- Digital Logic 2.9k
- Programming and DS 4.9k
- Algorithms 4.4k
- Theory of Computation 6.2k
- Compiler Design 2.1k
- Databases 4.1k
- CO and Architecture 3.4k
- Computer Networks 4.2k
- Non GATE 1.4k
- Others 1.4k
- Admissions 595
- Exam Queries 573
- Tier 1 Placement Questions 23
- Job Queries 72
- Projects 18

50,737 questions

57,388 answers

198,578 comments

105,416 users