Higher DDD - Wildcards, calculated fields & aliases

• I can design a SQL query making use of the LIKE operator along with a % to represent multiple characters and a _ to represent a single character. 

• I can design a SQL query which includes a calculated field with an associated alias. 

• I can implement SELECT FROM, UPDATE, DELETE FROM and INSERT INTO queries. 

Achievements

• We are learning how to implement SELECT FROM, UPDATE, DELETE FROM and INSERT INTO queries. 

• We are learning how to use wildcards in SQL queries to substitute one or more characters in a string. Specifically, to know how to use the LIKE operator. 

• We are learning how to use calculations in a computed field. 

• We are learning how to use aliases to give a column heading to a computed field.

Aims

• I can implement a SQL query making use of the LIKE operator along with a * to represent multiple characters and a ? To represent a single character. 

• I can implement a SQL query which includes a calculated field with an associated alias. 

Achievements

Aims

Match

Match the following SQL operations

• ORDER BY

• FROM

• SELECT

• WHERE

• JOIN

Sorts the result set in ascending or descending order

Specifies the table to select or delete data from

Specifies the fields to be displayed

Filters records that fulfil a specified condition

Combines rows from two or more tables based on a related column

• Sorts the result set in ascending or descending order

• Specifies the table to select or delete data from

• Specifies the fields to be displayed

• Filters records that fulfil a specified condition

• Combines rows from two or more tables based on a related column

Multiple Choice

Which wildcard character is used in MS Access SQL to match any single character?

• %

• _

• ?

• *

Multiple Choice

What does the LIKE 'a*' expression do in an MS Access SQL query?

• Matches values that start with 'a' and are exactly one character long.

• Matches values that start with 'a' and can be followed by any number of characters.

• Matches values that contain 'a' at any position.

• Matches values that end with 'a'.

Multiple Choice

Which wildcard would you use in MS Access SQL to find values that have "r" as the second character?

• LIKE '*r*'

• LIKE '_r%'

• LIKE '?r?'

• LIKE '?r*'

Fill in the Blanks

A takeaway restaurant is implementing an online ordering system and is using a relational database to store details about the dishes, customers and orders. The restaurant can view order details. Customers are able to place and edit orders. The Dish table is shown below.

The manager wants to increase the price of main dishes that contain chicken by £2.00. Write the SQL statement that would implement this. (3 marks)