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