col1    

300     

But to which AircraftType does this seating capacity belong? We need to list the AircraftType which corresponds to this value of NoSeats, ie

SELECT AircraftType

FROM Aircraft

WHERE NoSeats = "largest seating capacity"

SQL allows us to specify a sub-query within the WHERE clause which is executed before the main outer query to return one or more rows which may then be compared with the rows returned by the outer query. Hence putting the above queries together the required SQL is as follows.

SELECT AircraftType

FROM Aircraft

WHERE NoSeats = ( SELECT MAX(NoSeats)

FROM Aircraft );

Result:

aircra  

737     

Note the sub-query appears is in brackets indicating that it will return its result before the execution of the outer query.

This query could have been alternatively expressed as:

SELECT AircraftType

FROM Aircraft

WHERE NoSeats >= ALL ( SELECT NoSeats

FROM Aircraft );

Example 6.1.2 - List all aircraft which are not allocated to any timetabled Flights

For this example we will consider the SQL components required for each part of the query. Firstly we need to obtain a list of aircraft types from the Flights tables; those aircraft which are allocated to one or more timetabled flights, ie:

SELECT DISTINCT AircraftType

FROM Flights;

Result:

aircra  

ATP     
DC9     
737     

We require the AircraftTypes recorded in the Aircraft table which do not appear in this list,ie:

(Craft table created earlier will be used for this example.)

SELECT AircraftType

FROM Craft

WHERE AircraftType NOT IN ("the list of allocated aircraft")

Putting these two queries together as before gives:

SELECT AircraftType

FROM Craft

WHERE AircraftType NOT IN

( SELECT DISTINCT AircraftType

FROM Flights );

Result:

aircra  

S60     
F24     

Exercise 8.

1. By use of the IN operator and a sub-query list the names and addresses of passengers with tickets for flight BD80.

2. List the names and addresses of passengers with Standby Single (SBS) or Standard Return (SDR) tickets for flight BD54.

3. Provide an alternative to the SQL used in Example 6.1.2.

4. Find the AircraftType with the smallest seating capacity:

a) using an appropriate aggregate function;

b) without using an aggregate function.

name        address             

A Smithson  16 Bedford St       
C Evans     63 Kew Green        
T Pittman   The Little House    
K E         11 Rosedale Avenue  
Kendall                         

For each row in the passenger table the value of Pid for that row is passed into the subquery. The subquery executes once for each value of Pid passed from the outer query and returns the value of COUNT (the number of rows in the ticket-itinerary join, ie flight legs, which satisfy the subquery WHERE condition, ie flights for this Pid) to the WHERE clause in the outer query. Where COUNT equals one for the current Pid its corresponding row in the passenger table is listed in the result.

6.2.1 Testing for Existence.

The EXISTS operator enables us to test whether or not a subquery has returned any rows.

EXISTS evaluates to true if one or more rows have been selected and evaluates to false if zero rows have been selected.

NOT EXISTS evaluates to true if zero rows have been selected and evaluates to false if one or more rows have been selected.

Example 6.2.2 - List the names and addresses of passengers who purchased their Tickets on 01-AUG-92.

SELECT Name, Address

FROM Passenger P

WHERE EXISTS ( SELECT *

FROM Ticket T

WHERE P.Pid = T.Pid AND

T.TicketDate = '01/08/94 );

Result:

name        address            

J Millar    Englewood Cliffs   
T Pittman   The Little House   

name        address             

D Etheridge 4 Maylands Avenue    
G B Davis   25 Allenby Road      
R H Miller  155 Kingston Road    
                       

The first query returns the names and addresses of passengers flying from Heathrow to Birmingham.

The second query returns the names and addresses of passengers flying from Birmingham to Heathrow.

The UNION operator returns the rows from the first query plus the rows from the second query and removes any resulting duplicates. Hence we see listed those passengers who are either flying from Heathrow to Birmingham, or who are flying from Birmingham to Heathrow, or both.

Exercise 9.

Give the SQL required to:

1. List the names and addresses of any passenger who on a single ticket have more than 5 flights.

2. Find the names of those passengers who are taking all of the flights which

J Millar is taking.

3. Rewrite the SQL statement given for Example 6.3.2 so that it compares flights for the same ticket and hence find those passenger's with return tickets HROW to BIRM and back. (Note. You will need to use correlated sub-queries and an alternative query strategy to find the intersection.)

4. Using correlated sub-queries find the Names of those passengers with a ticket to fly from HROW (Heathrow) to PARI (Paris) without a return flight to HROW; ie those passengers with non-return tickets.