Passengers( pid : INT, firstName: VARCHAR(15), lastName: VARCHAR (15), middleIni
ID: 3567779 • Letter: P
Question
Passengers(pid: INT, firstName: VARCHAR(15), lastName: VARCHAR (15), middleInitial: CHAR(1), address: VARCHAR (30), phone: CHAR(13), age: TINYINT)
Flew(pid: INT, flightID: VARCHAR(10), departureDate: DATE, airfare: FLOAT(8,2), seatNo: VARCHAR(5) )
Flights(flightID: VARCHAR(10), flightNumber: VARCHAR(20),, deptartureAirportCode: CHAR(3), arrivalAirportCode: CHAR(3), takeOffTime: TIME, landingTime: TIME, distance: INT)
Write the following queries in relational algebra. Note that some of these queries may not be expressed in relational algebra! For such queries, explain why they cannot be expressed.
Explanation / Answer
select top 1 a.firstName,a.age from Flew a
INNER JOIN Passengers b
ON a.pid = b.pid
WHERE b.departureDate between #10/01/2014# AND #10/30/2014#
order by a.age desc
select a.firstName,a.lastName from Passenger a
INNER JOIN Flew b
ON a.pid = b.pid
INNER JOIN Flights c
ON b.flightID = c.flightID
where flightNumber IN('jetBlue 66','jetBlue 84')
group by a.pid,a.firstName,a.lastName