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

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?

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
asked in Databases by Veteran (106k points)
edited by | 1.4k views

1 Answer

+23 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
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.

Answer:

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

42,599 questions
48,600 answers
155,672 comments
63,739 users