GATE CSE 2010 | Question: 19

Question

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)

• A. $1, 0$
• B. $1, 2$
• C. $1, 3$
• D. $1, 5$

Comments

Detailed Video Solution: https://youtu.be/zb6vXpZVo1E?t=7210&feature=shared

Answer 1

(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$

Comments

@ Aravind can u plz tell me is there any effect  for pid 1 class SC alongwith AC.

---

it is a nested co related query, right?

---

It's a co related query{ identification : if outer query table used in inner query }.

SELECT pid
FROM Reservation
WHERE class='AC'

this part result in pid = 0 in first iteration. and this pid is compared with all passengers.pid values and then matching id with all other conditions verified then the pid is picked. Here passenger.pid = 0 is matched but age > 65 condition failed so, this id not displayed.

and

SELECT pid FROM Reservation WHERE class='AC'

second pid = 1 which is again compared with all the pid's of passenger.pid and matching pid will be selected then check all conditions applied on the query if satisfied then display that pid. Here pid = 1 satisfies all conditions. so display it. and so... on for all pid's .. pid 3 is also satisfying this condition hence selected for display. ans is 1,3.

Answer 2

$$\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

Comments

@Satbir

How to start with this type of question 1st execute from inner query or outer query please clarify me..

---

@satyaAchar, https://youtu.be/zb6vXpZVo1E?t=7210&feature=shared

---

useful answer. thank you