Hi!
I'm a SQL beginner struggling with some SQL textbook examples, and i'm stuck at one exercise. :eek:
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:
SELECT FL.FLIGHTNUMBER, SEATS - (SELECT COUNT(RESERVATION_NO)
FROM FLIGHT F, ...
WHERE ...
AND F.FLIGHTNUMBER = FL.FLIGHTNUMBER)
FROM FLIGHT FL, ...;
