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??
The answer should be:
219
98

Thanks!

Without getting into the details of your query, you need to correlate the subquery to the main query something like this: