Nov 2018 Paper II
SQL - BookingDB
1.1 Write a query that will display the information of all the tourists whose email address ends with "seattletimes.com". A sample of the correct output is shown below. (Note that date format may differ on your system). (4)
1.1 Write a query that will display the information of all the tourists whose email address ends with "seattletimes.com". A sample of the correct output is shown below. (Note that date format may differ on your system). (4)
SELECT *
FROM Tourist
WHERE Email LIKE '*seattletimes.com‘;
ALTERNATIVE
WHERE RIGHT(Email, 16) = 'seattletimes.com‘;
1.2 All guests currently staying at the "Lunar Hotel" have been moved to the
"Three Seasons Hotel". Write a query to update this information. (3)
1.2 All guests currently staying at the "Lunar Hotel" have been moved to the
"Three Seasons Hotel". Write a query to update this information. (3)
UPDATE Tourist
SET Hotel = 'Three Seasons Hotel'
WHERE Hotel = 'Lunar Hotel‘;
1.3 Write a query that will display the name and duration of morning excursions that are at most 3 hours. Recall that morning excursions start on the hour of 0 to 11 inclusive. Name the column displaying the calculated hours "Duration". A sample of the correct output is shown below. (6)
1.3 Write a query that will display the name and duration of morning excursions that are at most 3 hours. Recall that morning excursions start on the hour of 0 to 11 inclusive. Name the column displaying the calculated hours "Duration". A sample of the correct output is shown below. (6)
SELECT ExcursionName,
EndHour – StartHour
AS Duration
FROM Excursion
WHERE EndHour <= 11 -- alternative < 12
AND EndHour - StartHour <= 3;
1.4 Write a query that will display the names of the hotels with at least 3 tourists staying there. Also display the number of tourists staying at each of these hotels. (6)
1.4 Write a query that will display the names of the hotels with at least 3 tourists staying there. Also display the number of tourists staying at each of these hotels. (6)
SELECT Hotel, COUNT(*)
FROM Tourist
GROUP BY Hotel
HAVING COUNT(*)>=3;
1.5 Write a query that will list the names of the tourists who have not yet
booked for any excursion. Display the tourists' names alphabetically. (6)
1.5 Write a query that will list the names of the tourists who have not yet
booked for any excursion. Display the tourists' names alphabetically. (6)
SELECT TouristName
FROM Tourist
WHERE TouristID NOT IN
(SELECT TouristID FROM Booking)
ORDER BY TouristName;
ALTERNATIVE:
SELECT TouristName
FROM Tourist LEFT JOIN Booking
ON Tourist.TouristID = Booking.TouristID
WHERE ExcursionID IS NULL
ORDER BY TouristName;
1.6 Excursion codes are created by combining the first three characters of the excursion name with a random number between 10 and 99 inclusive. Write a query to generate excursion codes for each excursion. (6)
1.6 Excursion codes are created by combining the first three characters of the excursion name with a random number between 10 and 99 inclusive. Write a query to generate excursion codes for each excursion. (6)
SELECT ExcursionName,
LEFT (ExcursionName, 3) & INT (Rnd(ExcursionID) *90 + 10)
FROM Excursion;
1.7 All of the tourists staying at the "President Hotel" are going on all the following excursions today.
• Sunrise Breakfast River Cruise (ExcursionID: 1)
• Township Excursion 2 (ExcursionID: 7)
• National Art Museum Excursion 3 (ExcursionID: 13)
• Night Safari (ExcursionID: 5)
Write a query that will sign them up for these excursions by inserting appropriate entries into the BOOKING table.
Note that:
• the cost charged can be calculated by adding the surcharge to the current cost.
• today's date should not be hard coded (use an appropriate function to determine today's date).
• the excursion IDs 1, 7, 13 and 5 as well as the name of the hotel may be
hard coded. (These values may form part of the SQL statement).
(9)
1.7 All of the tourists staying at the "President Hotel" are going on all the following excursions today.
• Sunrise Breakfast River Cruise (ExcursionID: 1)
• Township Excursion 2 (ExcursionID: 7)
• National Art Museum Excursion 3 (ExcursionID: 13)
• Night Safari (ExcursionID: 5)
Write a query that will sign them up for these excursions by inserting appropriate entries into the BOOKING table.
INSERT INTO Booking (TouristID, ExcursionID, CostCharged, ExcursionDate)
SELECT TouristID, ExcursionID, CurrentCost+Surcharge, NOW()
FROM Excursion , Tourist
WHERE ExcursionID IN ( 1, 7, 13, 5 )
AND Hotel = 'President Hotel‘; (9)