1. Registered User
Join Date
Oct 2005
Posts
1

Hi!
I'm a SQL beginner struggling with some SQL textbook examples, and i'm stuck at one exercise.
Can anyone give me a hint??

Ok, here it goes:
I'm trying to simulate a airport booking system, where CUSTOMER makes a RESERVATION to a FLIGHT. The FLIGHT have a ROUTE, a AIRCRAFT, and some CABINSTAFF. Plus some other minor attribures..

The question i'm trying to answer now is "how many seats are there left on all flights having a specific route?". I can get how many seats the airplanes have with this:

SELECt SEATS
FROM FLIGHT FL, AIRCRAFT AC, ROUTE R
WHERE FL.FNUMBER = R.FLIGHTNUMBER
AND FL.AIRCRAFT = AC.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND "DATE" = '2005-03-01';

It will return:
100
200
(there are two flights that match the selected route and date, one airplane have 100 seats, the other one 200).

I then find out how many customers that are booked on those flights:

SELECT COUNT(RESERVATION_NO)
FROM FLIGHT F, AIRCRAFT A, ROUTE R, RESERVATION RN, RESERVES RS
WHERE RN.RESERVATION_NO = RS.RESERVATION_NO
AND RN.FNUMBER = R.FLIGHTNUMBER
AND F.FNUMBER = RN.FNUMBER
AND F.AIRCRAFT = A.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND FDATE = '2005-03-01'
GROUP BY RN.RESERVATION_NO

It will correctly return:
1
2

Now i want to substract the second query from the first to get the number of seats left in those flights:

SELECT FLIGHTNUMBER, SEATS - (SELECT COUNT(RESERVATION_NO)
FROM FLIGHT F, AIRCRAFT A, ROUTE R, RESERVATION RN, RESERVES RS
WHERE RN.RESERVATION_NO = RS.RESERVATION_NO
AND RN.FNUMBER = R.FLIGHTNUMBER
AND F.FNUMBER = RN.FNUMBER
AND F.AIRCRAFT = A.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND FDATE = '2005-03-01'
GROUP BY RN.RESERVATION_NO)
FROM FLIGHT FL, AIRCRAFT AC, ROUTE R
WHERE FL.FNUMBER = R.FLIGHTNUMBER
AND FL.AIRCRAFT = AC.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND "DATE" = '2005-03-01';

But that does'nt work since you can't substract a set, only a single row, right?? So how do i fix this??
219
98

Thanks!

2. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Without getting into the details of your query, you need to correlate the subquery to the main query something like this:

Code:
```SELECT FL.FLIGHTNUMBER, SEATS - (SELECT COUNT(RESERVATION_NO)
FROM   FLIGHT F, ...
WHERE  ...
AND    F.FLIGHTNUMBER = FL.FLIGHTNUMBER)
FROM   FLIGHT FL, ...;```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•