Higher - Database query design

• I can create a design for a database query which includes the following:

  • Field names

  • Computed fields (with aliases)

  • Table(s)

  • Search criteria

  • Grouping

  • Sort order

Achievements

• We are learning how to design database queries, specifically detailing fields, tables, search criteria, grouping and sort order.

Aims

Match

Match the query design rows with the SQL implementation.

• Field(s) and calculations(s)

• Table(s) and query

• Search criteria

• Grouping

• Sort order

SELECT

FROM

WHERE

GROUP BY

ORDER BY

• SELECT

• FROM

• WHERE

• GROUP BY

• ORDER BY

Multiple Choice

Which aggregate function should be used for finding the highest value in a column?

• MAX

• MIN

• COUNT

• SUM

• AVERAGE

Multiple Choice

Which aggregate function should be used for totalling the values in a column?

• MAX

• MIN

• COUNT

• SUM

• AVERAGE

Multiple Choice

Which aggregate function should be used for returning the number of records which meet the search criteria?

• MAX

• MIN

• COUNT

• SUM

• AVERAGE

Multiple Select

Which of the following answers would be selected if the wildcard ‘McN%’ was used in the WHERE LIKE clause?

• McNichol

• McKenney

• McNesbitt

• McKenzie

• Macneil

Multiple Select

Which of the following answers would be selected if the wildcard ‘_o%’ was included in the WHERE LIKE clause?

WHERE surname LIKE ‘_o_%’

• Yo

• Bolton

• Nolan

• Moore

• Opgaard

Multiple Choice

When specifying a calculated field an alias should be detailed in

• Round brackets ( )

• Square brackets [ ]

• Quotation marks " "

• Apostrophes ' '

Open Ended

Which two fields has this table been sorted on and in which order?