Consider the following flight activity log relational schema. This database only

Question

Consider the following flight activity log relational schema. This database only records completed flights. The primary key for each relation is underlined.

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 SQL. Note that you have to write exactly one SQL statement for each question. Also, the query answers must not contain duplicates, but you should use the SQL keyword distinct only when necessary.  

1. Find the age of the oldest passenger for each flight that flew between October 1, 2014 and October 30, 2014.

2. Find the name of all passengers who took flight numbers

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

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.