edited by
8,285 views
29 votes
29 votes

A relational schema for a train reservation database is given below.

  • passenger(pid, pname, age)
  • reservation(pid, class, tid)

$$\overset{\text{Passenger}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{pname}& \textbf{Age} \\\hline
0&     \text{Sachine}&    65  \\ \hline   
1&     \text{Rahul}& 66 \\     \hline
2&     \text{Sourav}&  67      \\\hline
3&     \text{Anil}& 69      \\\hline
\end{array}} \qquad \overset{\text{Reservation}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{class}& \textbf{tid} \\\hline
0&     \text{AC}&    8200   \\ \hline   
1&     \text{AC}& 8201  \\     \hline
2&     \text{SC}&  8201      \\\hline
5&     \text{AC}& 8203      \\\hline1&     \text{SC}& 8204      \\\hline3&     \text{AC}& 8202      \\\hline
\end{array}}$$
What pids are returned by the following SQL query for the above instance of the tables?

SELECT pid
FROM Reservation
WHERE class='AC' AND
    EXISTS (SELECT * 
            FROM Passenger 
            WHERE age>65 AND 
            Passenger.pid=Reservation.pid)
  1. $1, 0$
  2. $1, 2$
  3. $1, 3$
  4. $1, 5$
edited by

2 Answers

Best answer
36 votes
36 votes
(C) $1,3$

The inner query gives passenger_id with age above $65$ i.e., $1,2,3$
The outer query chooses the class as AC, which are $1$ and $3$
selected by
18 votes
18 votes

$$\overset{\text{Passenger}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{pname}& \textbf{Age} \\\hline
0&     \text{Sachine}&    65  \\ \hline   
1&     \text{Rahul}& 66 \\     \hline
2&     \text{Sourav}&  67      \\\hline
3&     \text{Anil}& 69      \\\hline
\end{array}} \qquad \overset{\text{Reservation}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{class}& \textbf{tid} \\\hline
0&     \text{AC}&    8200   \\ \hline   
1&     \text{AC}& 8201  \\     \hline
2&     \text{SC}&  8201      \\\hline
5&     \text{AC}& 8203      \\\hline1&     \text{SC}& 8204      \\\hline3&     \text{AC}& 8202      \\\hline
\end{array}}$$

SELECT 
FROM Reservation
WHERE class='AC' AND
EXISTS (SELECT * 
FROM Passenger 
WHERE age>65 AND 
Passenger.pid=Reservation.pid)

 

Working of a co related query

 

As we can see Reservation is written outside brackets and passenger is written inside brackets

So here we will take each tuple of Reservation table and compare it with all the tuples of passenger table


$1^{st}$ Tuple of Reservation table

$$\overset{\text{Reservation}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{class}& \textbf{tid} \\\hline
0&     \text{AC}&    8200   \\ \hline 
\end{array}} \qquad \overset{\text{Passenger}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{pname}& \textbf{Age} \\\hline 0&     \text{Sachine}&    65  \\ \hline   
1&     \text{Rahul}& 66 \\     \hline
2&     \text{Sourav}&  67      \\\hline
3&     \text{Anil}& 69      \\\hline  
\end{array}}$$

 We will fist check the conditions that are given  for Reservation table i.e. Class = "AC" which is True

So, we will now switch to Passenger table and check the conditions given for it i.e.

age>65 AND Passenger.pid=Reservation.pid

As we can see here the $1^{st}$ tuple of Passenger table is satisfying Passenger.pid =0 (= Resesrvation.pid ) but it is not satisfying age>65 so we can't select it.

The remaining tuples of Passenger table are satisfying age>65 but not Passenger.pid =0 (= Resesrvation.pid ) so we will not select pid=0 since no match is found.

$\therefore$ pid =0 from Reservation table is not selected in the output


$2^{nd}$ Tuple of Reservation table

$$\overset{\text{Reservation}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{class}& \textbf{tid} \\\hline
1&     \text{AC}&    8201   \\ \hline 
\end{array}} \qquad \overset{\text{Passenger}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{pname}& \textbf{Age} \\\hline 0&     \text{Sachine}&    65  \\ \hline   
1&     \text{Rahul}& 66 \\     \hline
2&     \text{Sourav}&  67      \\\hline
3&     \text{Anil}& 69      \\\hline  
\end{array}}$$

 We will fist check the conditions that are given  for Reservation table i.e. Class = "AC" which is True

So, we will now switch to Passenger table and check the conditions given for it i.e.

age>65 AND Passenger.pid=Reservation.pid

As we can see here only the $2^{nd}$ tuple of Passenger table is satisfying Passenger.pid =1 (= Resesrvation.pid ) and satisfying age>65 condition so we found a match

$\therefore$ pid =1 from Reservation table is selected in the output.


$3^{rd}$ Tuple of Reservation table

$$\overset{\text{Reservation}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{class}& \textbf{tid} \\\hline
2&     \text{SC}&    8201   \\ \hline 
\end{array}} \qquad \overset{\text{Passenger}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{pname}& \textbf{Age} \\\hline 0&     \text{Sachine}&    65  \\ \hline   
1&     \text{Rahul}& 66 \\     \hline
2&     \text{Sourav}&  67      \\\hline
3&     \text{Anil}& 69      \\\hline  
\end{array}}$$

 We will fist check the conditions that are given  for Reservation table i.e. Class = "AC" which is False

So, we will not select this tuple.

$\therefore$ pid =2 from Reservation table is not selected in the output.


$4^{th}$ Tuple of Reservation table

$$\overset{\text{Reservation}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{class}& \textbf{tid} \\\hline
5&     \text{AC}&    8203   \\ \hline 
\end{array}} \qquad \overset{\text{Passenger}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{pname}& \textbf{Age} \\\hline 0&     \text{Sachine}&    65  \\ \hline   
1&     \text{Rahul}& 66 \\     \hline
2&     \text{Sourav}&  67      \\\hline
3&     \text{Anil}& 69      \\\hline  
\end{array}}$$

 We will fist check the conditions that are given  for Reservation table i.e. Class = "AC" which is True

So, we will now switch to Passenger table and check the conditions given for it i.e.

age>65 AND Passenger.pid=Reservation.pid

As we can see here only the no tuple of Passenger table is satisfying Passenger.pid =5 (= Resesrvation.pid )

So, we will not select this tuple.

$\therefore$ pid =5 from Reservation table is not selected in the output.


$5^{th}$ Tuple of Reservation table

$$\overset{\text{Reservation}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{class}& \textbf{tid} \\\hline
1&     \text{SC}&    8204   \\ \hline 
\end{array}} \qquad \overset{\text{Passenger}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{pname}& \textbf{Age} \\\hline 0&     \text{Sachine}&    65  \\ \hline   
1&     \text{Rahul}& 66 \\     \hline
2&     \text{Sourav}&  67      \\\hline
3&     \text{Anil}& 69      \\\hline  
\end{array}}$$

 We will fist check the conditions that are given  for Reservation table i.e. Class = "AC" which is False

So, we will not select this tuple.

$\therefore$ pid =1 from Reservation table is not selected in the output.


$6^{th}$ Tuple of Reservation table

$$\overset{\text{Reservation}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{class}& \textbf{tid} \\\hline
3&     \text{AC}&    8202   \\ \hline 
\end{array}} \qquad \overset{\text{Passenger}}{\begin{array}{|c|c|c|}\hline\\
\textbf{pid}&  \textbf{pname}& \textbf{Age} \\\hline 0&     \text{Sachine}&    65  \\ \hline   
1&     \text{Rahul}& 66 \\     \hline
2&     \text{Sourav}&  67      \\\hline
3&     \text{Anil}& 69      \\\hline  
\end{array}}$$

 We will fist check the conditions that are given  for Reservation table i.e. Class = "AC" which is True

So, we will now switch to Passenger table and check the conditions given for it i.e.

age>65 AND Passenger.pid=Reservation.pid

As we can see here only the $4^{nd}$ tuple of Passenger table is satisfying Passenger.pid =3 (= Resesrvation.pid ) and satisfying age>65 condition so we found a match

$\therefore$ pid =3 from Reservation table is selected in the output.


So, finally only 1 and 3 are selected in the output.

$\therefore$ Option $C.$ is the correct choice

Answer:

Related questions

41 votes
41 votes
8 answers
1
31 votes
31 votes
4 answers
2
go_editor asked Sep 30, 2014
10,438 views
Consider the following schedule for transactions $T1, T2$ and $T3:$$$\begin{array}{|c|c|c|}\hline \textbf{T1} & \textbf{T2} & \textbf{T3} \\\hline \text{Read(X)} & \text...
51 votes
51 votes
6 answers
3
go_editor asked Sep 29, 2014
22,544 views
Which of the following concurrency control protocols ensure both conflict serializability and freedom from deadlock?$2$-phase lockingTime-stamp orderingI onlyII onlyBoth ...
56 votes
56 votes
8 answers
4
go_editor asked Sep 29, 2014
32,689 views
Consider a $B^+$-tree in which the maximum number of keys in a node is $5$. What is the minimum number of keys in any non-root node?$1$$2$$3$$4$