2.1k views

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 | 2.1k views

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

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

0
it is a nested co related query, right?
+1

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.

1
2