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

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

passenger(pid, pname, age)

reservation(pid, class, tid)

Table: Passenger
pid pname Age
0 'Sachin' 65
1 'Rahul' 66
2 'Sourav' 67
3 'Anil' 69
Table: Reservation
pid class tid
0 'AC' 8200
1 'AC' 8201
2 'SC' 8201
5 'AC' 8203
1 'SC' 8204
3 'AC' 8202

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 (115k points)
edited by | 1.9k views

1 Answer

+24 votes
Best answer

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
47,894 questions
52,260 answers
67,678 users