SQL Queries for Flight and Passenger Data
Uploaded by
ScribdTranslationsSQL Queries for Flight and Passenger Data
Uploaded by
ScribdTranslationsCorrected Database Exam
My query must display all the information of the passengers whose names contain the sequence
from the two letters 'MA', and having booked flight number 2. Express this query in SQL.
SELECT Passenger.*
FROM Passenger INNER JOIN Reservation ON [Link] = [Link]
Where [Link] like '*MA*' and [Link] = 2;
4. Queries in QBE and SQL mode:
a. Display the number and date of flights made in March of the years 2014 or 2015, from the airport
from Fez to Marseille airport.
SELECT [Link], [Link]
FROM Flight As V Inner Join (Airport As A Inner Join [Airport-Of-Flight] AS AV ON
[Link] = [Link]) ON [Link] = [Link]
WHERE (Month([DateVol])=3 AND Year([DateVol]) in (2014,2015) AND
VilleAeroport="Fès" AND Destination="Marseille");
b. Display the name, first name, and nationality of the pilots who flew on the last day of each
month of the first quarter of the years 2014 and 2015.
2/2
SELECT [Link], [Link], [Link]
FROM Pilot Inner Join Flight ON [Link] = [Link]
WHERE (Day([DateVol])=31 AND Month([DateVol]) IN (1,3) AND Year([DateVol]) IN
(2014,2015)) OR (Day([DateVol])=28 AND Month([DateVol])=2 AND Year([DateVol])
In (2014, 2015));
c. Display the flight number and the flight months of Boeing type airplanes, whose months are
displayed in letters and the number of places is not between 350 and 500.
SELECT [Link], Format([DateVol],"mmmm") AS MoisEnLettres
FROM Aircraft INNER JOIN Flight ON [Link] = [Link]
WHERE [Link]='Boeing' AND [Link] Not Between 350
And 500;
Or well
SELECT [Link], MonthName(Month(DateVol)) AS MoisEnLettres
FROM Aircraft INNER JOIN Flight ON [Link] = [Link]
WHERE [Link]='Boeing' AND [Link] Not Between 350
And 500;
d. Display the number, the name, the first name, and the reduced price of the passengers if we gave a
5% reduction on the flight price for first-class or business travelers and 0% in
the opposite case.
2/2
SELECT [Link], [Link], [Link]
IIf([FlightClass] In ("First", "Business"), [FlightPrice] * 0.95, [FlightPrice]) AS ReducedPrice
FROM Passenger Inner Join Reservation ON [Link] =
[Link];
e. For each pilot, display the number, the name, and the total number of their flights during the
last quarter of a given year as a parameter.
SELECT [Link], [Link], Count([Link]) AS TotalNumber
FROM Pilot Inner Join Flight ON [Link] = [Link]
WHERE Month([datevol])>9 AND Year([datevol])=[Year]
GROUP BY [Link], [Link];
f. Increase the ticket price by 10% for booked business class flights.
3 days before the flight date in the year 2016.
UPDATE Flight Inner Join Reservation ON [Link] = [Link] SET
[Link] = "PrixVol*1,1"
WHERE [Link]='Business' AND DateDiff('d',[reservationDate],
[datevol])<=3 AND Year([DateReserv])=2016;
g. Display the average monthly flight prices for the last six months of
the current year, by flight class. The classes should be displayed in columns and the names
for months online.
2/2
TRANSFORM Avg(PriceVolume) AS AvgPriceVolume
SELECT Format([DateVol],"mmmm") AS MoisVol
FROM Flight INNER JOIN Reservation ON [Link] = [Link]
WHERE (((DateDiff("m",[DateVol],Date()))<=6))
GROUP BY Format([DateVol],"mmmm")
PIVOT [Link];
2/2