Embedded Files
SDD Topic has been refreshed!
Listed below are the designs of queries that cover all of the new content at Advanced Higher. The query design is shown with the SQL code and explanation being hidden in the box below. This will allow you to try to design the queries before checking the SQL
Database Required
Database Required
The queries here are all based on the Travel Agency Database. The SQL file to import can be downloaded here
Example 1: HAVING with GROUPING and row COUNT
Example 1: HAVING with GROUPING and row COUNT
Display the resort name and number of hotels in any resort that has at least two hotels.
Example 1 SQL Query and Explanation
Example 1 SQL Query and Explanation
Explanation
The number of hotels are counted and assigned an alias of Number of Hotels. As we only want to see those resorts that have 2 or more hotels we need to use the COUNT aggregate function on grouped records. As this cannot be in the WHERE clause we must place it in the HAVING clause.
Example 2: HAVING with GROUPING and sort
Example 2: HAVING with GROUPING and sort
Display the full name and the total cost of all bookings for each customer. The query should only list details of customers whose total cost exceeds £2000 and should list the details of the biggest spending customer first.
Example 2 SQL Query and Explanation
Example 2 SQL Query and Explanation
Explanation
The total cost of the booking is found by performing a SUM of the price per night * number of nights * number in the party. This is assigned an ALIAS of Total cost of all Bookings’
These are grouped by firstname and surname as they are non aggregate fields.
As we are working with grouped records and we only want to see the total cost of all bookings that are >=2000 the HAVING clause is used and then it is put in ORDER of the total cost of booking expression.
Example 3: HAVING with GROUPING and sort
Example 3: HAVING with GROUPING and sort
Display the average price per person, per night for each holiday resort. Display only those resorts with an average price per person, per night that exceeds £100.
Example 3 SQL Query and Explanation
Example 3 SQL Query and Explanation
Explanation
The average price of the pricepersonNight is found and rounded to 2 decimal places. This is assigned an ALIAS of 'Average Price'
These are grouped by resortName as it is a non aggregate fields.
As we are working with grouped records and we only want to see those records that have an average of > 100 the HAVING clause is used.
Example 4: NOT Operator
Example 4: NOT Operator
Display the name and type of non-coastal resort, together with the name and meal plan for each hotel that meets these criteria.
Example 4 SQL Query and Explanation
Example 4 SQL Query and Explanation
Explanation
The resortname, type, hotelname and mealplan are only shown when the ResortType is NOT 'coastal'
Example 5: BETWEEN operator with numeric values
Example 5: BETWEEN operator with numeric values
Display the full name and total number of bookings made by each customer who has made between two and four bookings.
Example 5 SQL Query and Explanation
Example 5 SQL Query and Explanation
Explanation
The number of bookings per user is found using the COUNT function and assigned an ALIAS of 'Total Bookings'.
The results are grouped by surname and forename as they are non aggregate fields. As we want to restrict the results that are displayed to only those that have between 2 and 4 booking we use the BETWEEN operator that has to be in the HAVING clause. Remember to note that the BETWEEN operator will fetch those that are 2, 3 and 4.
Example 6: BETWEEN operator with text
Example 6: BETWEEN operator with text
Display the surname, postcode, and town of customers who live in towns that begin with the letters ‘E’ through to ‘M’. The query should list customers in alphabetical order of town.
Example 6 SQL Query and Explanation
Example 6 SQL Query and Explanation
Explanation
Only towns that begin with E and M (inclusive) would be returned by the query and are displayed in ascending order.
Example 7: IN Operator
Example 7: IN Operator
Display the hotel name and meal plan for hotels that offer room only, half board or full board.
Example 7 SQL Query and Explanation
Example 7 SQL Query and Explanation
Explanation
The hotel name and mealplan will only be returned where the mealplan value matches any of the values in the brackets ("Room Only", "Half Board", "Full Board").
Example 8: NOT with the IN operator
Example 8: NOT with the IN operator
Display the name and type of resorts that are neither city nor country resorts.
Example 8 SQL Query and Explanation
Example 8 SQL Query and Explanation
Explanation
The resortType will be evaluated as whether it matches any of the values in the brackets and then the result will be inverted. So if the value is IN the brackets then the IN will return TRUE. The NOT will then INVERT this TRUE to FALSE.
Example 9: Subquery in the WHERE clause
Example 9: Subquery in the WHERE clause
Display the hotel name, star rating, and price per person for the most expensive hotel.
Example 9 SQL Query and Explanation
Example 9 SQL Query and Explanation
Explanation
The inner query SELECT MAX(pricePersonNight)FROM Hotel will be evaluated first and return the maximum value. This is then used to complete the WHERE clause of the outer query.
Example 10: Subquery in the where clause
Example 10: Subquery in the where clause
Display the resort name, hotel name, and star rating of all hotels that have a below-average star rating.
Example 10 SQL Query and Explanation
Example 10 SQL Query and Explanation
Explanation
The inner query SELECT AVG(starRating)FROM Hotel will be evaluated first and return the average star rating from the Hotel table. This is then used to complete the WHERE clause of the outer query to find all hotels whose rating is less than this average value.
Example 11: Subquery using the NOT operator
Example 11: Subquery using the NOT operator
Display the full name and postcode of the customer who booked the same hotel as the customer with ID 111.
Example 11 SQL Query and Explanation
Example 11 SQL Query and Explanation
Explanation
The inner query SELECT hotelRef FROM Booking WHERE customerNo = 111 will be evaluated first and return all Hotel References that were booked by CustomerNo 111. This is then used to complete the WHERE clause of the outer query. This outer query first checks that the customerno is not 111 ( so there is not a duplicate result returned) and then compares the hotelref to that which was booked by customer 111.
Example 12: Subquery using the NOT operator
Example 12: Subquery using the NOT operator
Display the name and star rating of all hotels booked by the customer with ID 315.
Example 12 SQL Query and Explanation
Example 12 SQL Query and Explanation
Explanation
The inner query SELECT hotelName FROM Hotel, Booking WHERE Hotel.hotelRef = Booking.hotelRef AND customerNo = 315 will be evaluated first and return all Hotel Names that were booked by CustomerNo 315. This is then used to complete the WHERE clause of the outer query. This outer query then only displays the HotelNames that were returned.
Note: This could also have been achieved by:
Example 13: Subquery using the NOT and IN operators
Example 13: Subquery using the NOT and IN operators
Display the names and types of resort not booked by the customer with ID 315.
Example 13 SQL Query and Explanation
Example 13 SQL Query and Explanation
Explanation
The inner query will be evaluated first and return all Resort Names that were booked by CustomerNo 315. This is then used to complete the WHERE clause of the outer query. This outer query then only displays the ResortNames that were not returned by the Inner Query.
Example 14: Subquery using the ANY operator
Example 14: Subquery using the ANY operator
Display the customer number, hotel reference, and booking cost for any booking that costs more than any bookings made by customers with surnames Lowden, Shawfair or Sheriffhall.
Example 14 SQL Query and Explanation
Example 14 SQL Query and Explanation
Explanation
The inner query will be evaluated first and return the total price of any booking that was made by Customers with the surnames of "Lowden", "Shawfair" or "Sheriffhall". This is then used to complete the WHERE clause of the outer query. This outer query will return the results where the total cost is greater than any of the results that were returned by the inner query.
Example 15: Subquery using the EXISTS operator
Example 15: Subquery using the EXISTS operator
Display the details (hotel name, star rating, meal plan and resort name) of all 3-star hotel bookings. The query should list the hotels in alphabetical order of meal plan.
Example 15 SQL Query and Explanation
Example 15 SQL Query and Explanation
Explanation
The inner query will be evaluated first and returns all of the bookings that were made . This is then used to complete the WHERE clause of the outer query. This outer query will return the results where the rating is >3 and a booking has been made for that hotel ( it was returned from the inner query).
Example 16: Subquery using the NOT and EXISTS operators
Example 16: Subquery using the NOT and EXISTS operators
Display the full name and address of customers who have never made a booking.
Example 16 SQL Query and Explanation
Example 16 SQL Query and Explanation
Explanation
The inner query will be evaluated first and returns all of the bookings that were made . This is then used to complete the WHERE clause of the outer query. This outer query will return the results where the record was not returned from the inner query.
Example 17: Query requiring two subqueries
Example 17: Query requiring two subqueries
Display the name, star rating, and total number of customer nights booked for hotels that have:
a total number of customer nights booked that is more than the total number of nights booked by the customer with ID 290 (number of nights booked multiplied by number in party)
and
a star rating which is less than that of the hotel with the highest star rating
The query should list the hotels from lowest star rating to the highest.
Example 17 SQL Query and Explanation
Example 17 SQL Query and Explanation
Explanation
The first inner query SELECT SUM(numberNights*numberInParty) FROM Booking WHERE customerNo =290 is evaluated first this will return the total number of nights booked by customer 290. This is so that the first condition of the query which is to return all results with a number of nights greater than that.
The second inner query is then executed which finds the maximum star rating. This is then used to complete the second condition of the WHERE clause of the outer query which is to find the hotels which are less than ( but not including) this star rating.
Page updated
Report abuse