The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
+17 votes

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

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

\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
What pids are returned by the following SQL query for the above instance of the tables?

FROM Reservation
WHERE class='AC' AND
            FROM Passenger 
            WHERE age>65 AND 
  1. $1, 0$
  2. $1, 2$
  3. $1, 3$
  4. $1, 5$
asked in Databases by Veteran (97.1k points)
edited by | 2.1k views

1 Answer

+25 votes
Best answer
(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$
answered by Active (3.3k points)
selected by

@ 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 }.

FROM Reservation
WHERE class='AC'

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


SELECT pid FROM Reservation WHERE class='AC'

second pid = 1 which is again compared with all the pid's of 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.


Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
49,808 questions
54,489 answers
74,658 users