DDD Implementation

Open Ended

What are some key considerations when designing an SQL query for a database?

Multiple Choice

Why is it important to carefully design and implement a database in computing science?

• To ensure data is organized, secure, and easily accessible

• To make the database look visually appealing

• To reduce the cost of hardware

• To avoid using programming languages

Multiple Choice

Which SQL operator is commonly used with wildcard characters to perform search operations in a WHERE clause?

• LIKE

• IN

• BETWEEN

• ORDER BY

Multiple Choice

Based on the examples provided, which of the following WHERE clauses would find any values in the surname field that have 'h' as the second character?

• WHERE surname LIKE '_h%'

• WHERE surname LIKE 'h_%'

• WHERE surname LIKE '%h_'

• WHERE surname LIKE '%_h'

Open Ended

Compare the wildcard syntax used in the two tables shown in images 3 and 4. What is the main difference between them?

Open Ended

Design a SQL WHERE clause to find all resorts whose names end with the letter 'n'.

Multiple Choice

Which fields would you need to include in a query to display the name, swimming pool details, resort, and resort type of any hotel in a coastal resort that starts with the letter 'A'?

• hotelName, swimmingPool, resortName, resortType

• hotelRef, resortID, bookingNo, customer#

• hotelName, starRating, resortType, trainStation

• customer#, surname, address, town

Multiple Choice

Which SQL clause would you use to filter results so that only customers whose surname has 'h' as the second letter are shown?

• WHERE surname LIKE '?h*'

• WHERE surname = 'h*'

• WHERE surname LIKE 'h*'

• WHERE surname = '?h*'

Open Ended

Design a query to display a customer’s full name, booking number, start date, hotel name, and resort name for all customers who have an ‘h’ as the second letter of their surname. List these details in alphabetical order of surname; listing customers with the same surname in order of the earliest holiday first.

Multiple Choice

What is the purpose of an equi-join in SQL?

• To combine tables based on matching values in specified columns

• To remove duplicate rows from a table

• To sort the results in ascending order

• To count the number of rows in a table

Multiple Select

Which of the following are aggregate functions in SQL?

• AVG()

• COUNT()

• MAX()

• SELECT()

Multiple Choice

Which aggregate functions can be applied to both numeric and character data types?

• MIN() and MAX()

• SUM() and AVG()

• COUNT() and SUM()

• AVG() and MAX()

Open Ended

Explain why the following SQL query is considered illegal: SELECT productName, MAX(price) FROM Product;

Multiple Choice

Which of the following statements about aggregate functions in SQL is correct?

• You can mix aggregate and non-aggregate expressions in a SELECT statement without restrictions.

• A SELECT statement must contain either all non-aggregate expressions or all aggregate expressions.

• You cannot use more than one aggregate expression in a SELECT statement.

• Aggregate functions can only be used with the GROUP BY clause.

Multiple Choice

Design a query to display the average number of nights booked. What aggregate function would you use and on which table?

• SUM(numberNights) on Booking

• AVG(numberNights) on Booking

• COUNT(numberNights) on Hotel

• MAX(numberNights) on Resort

Multiple Choice

Which of the following queries would correctly display the number of resorts in each resort type?

• SELECT resortType, COUNT(*) FROM Resort GROUP BY resortType;

• SELECT COUNT(resortType) FROM Resort;

• SELECT resortType, SUM(*) FROM Resort;

• SELECT COUNT(*) FROM Resort GROUP BY resortType;

Open Ended

Describe how an alias can improve the readability of a SQL query result and how it is implemented.

Open Ended

Explain how the use of aliases in SQL queries can improve the readability of the resulting tables. Provide an example based on the images.

Multiple Choice

What is the main purpose of using an alias in an SQL SELECT statement, as shown in the examples?

• To make column headings more readable

• To speed up query execution

• To join multiple tables

• To filter records

Multiple Select

Which of the following are benefits of using the GROUP BY clause in SQL?

• It allows grouping of records with identical data in specified columns.

• It ensures one result is returned for each set of grouped records when used with aggregate functions.

• It allows mixing of non-aggregate and aggregate expressions for grouping columns.

• It sorts the records automatically.

Multiple Choice

Which SQL query would you use to display the number of bookings for each meal plan, sorted from least to most popular?

• SELECT Hotel.mealPlan, Count(*) FROM Hotel, Booking GROUP BY Hotel.mealPlan ORDER BY Count(*) ASC;

• SELECT mealPlan, SUM(bookings) FROM Hotel GROUP BY mealPlan;

• SELECT mealPlan, COUNT(*) FROM Booking GROUP BY mealPlan ORDER BY mealPlan;

• SELECT COUNT(mealPlan) FROM Booking ORDER BY mealPlan;

Multiple Choice

Which of the following is NOT typically included in the design of an SQL query?

• The field(s) used to sort the data

• The color scheme of the database interface

• Any search criteria to be applied

• Any field(s) or computed values required

Open Ended

Summarize the key considerations when designing an SQL query as discussed in today's lesson.