Skip to content
GATETECH
Official PYQ · 2010Q19Tuple Calculus and SQLMCQ1 mark

GATE CS 2010 Q19 — Tuple Calculus and SQL

Explain with AI

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

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

pidpnameAge0Sachine651Rahul662Sourav673Anil69Passengerpidclasstid0AC82001AC82012SC82015AC82031SC82043AC8202Reservation\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)
Choose one option
View worked solution

Answer: C

To find the pids returned by the SQL query, we evaluate the query step-by-step.

Step 1: Analyze the Outer Query

The outer query selects pid from the Reservation table where the class is 'AC':

SELECT pid FROM Reservation WHERE class = 'AC'

From the given Reservation table, the rows with class = 'AC' are:

  • pid = 0 (tid = 8200)
  • pid = 1 (tid = 8201)
  • pid = 5 (tid = 8203)
  • pid = 3 (tid = 8202)

These pid values {0,1,3,5}\{0, 1, 3, 5\} are our candidates.

Step 2: Evaluate the Correlated Subquery

For each candidate pid from the outer query, the EXISTS clause checks if there is a matching record in the Passenger table where age > 65 and Passenger.pid = Reservation.pid.

  • For pid = 0:

    In the Passenger table, the passenger with pid = 0 (Sachine) has age = 65.

    Since 65>6565 > 65 is False, the subquery returns no rows. EXISTS evaluates to False.

  • For pid = 1:

    In the Passenger table, the passenger with pid = 1 (Rahul) has age = 66.

    Since 66>6566 > 65 is True, the subquery returns a row. EXISTS evaluates to True. Thus, 1 is selected.

  • For pid = 5:

    There is no record for pid = 5 in the Passenger table.

    The subquery returns no rows. EXISTS evaluates to False.

  • For pid = 3:

    In the Passenger table, the passenger with pid = 3 (Anil) has age = 69.

    Since 69>6569 > 65 is True, the subquery returns a row. EXISTS evaluates to True. Thus, 3 is selected.

Conclusion

The pids that satisfy all conditions are 11 and 33.

Free practice · Your answers on this page are not saved.

Published
Report a correction
Keep exploringMore Tuple Calculus and SQL questions