Query Design and SQL
Course Content Specification
Exemplify a design of a solution to a query:
tables and queries
fields
search criteria
sort order
calculations
Describe, exemplify and use SQL operations for pre-populated relational databases, with three or more linked tables:
UPDATE, SELECT, DELETE, INSERT statements making use of:
wildcards
aggregate functions (MIN, MAX, AVG, SUM, COUNT)
computed values, alias
GROUP BY
ORDER BY
WHERE
Read and explain code that makes use of the above SQL.
What is in the design of a query?
The design of an SQL query should include:
Any field(s) or computed values required.
The table(s) needed to provide all of the details required.
Any search criteria (if required).
What grouping is needed (if appropriate).
The field(s) used to sort the data and the type(s) of sort required.
Sample Query Design grid as will be used in exam papers.
SQL at Higher
In Higher you are still going to be using SELECT, INSERT, UPDATE and DELETE queries and EQUI-JOINS that were covered at National 5 but we are now also going to introduce:
Wildcards
Aggregate functions
Computed Values
Alias
GROUP BY
UPDATE query to edit more than one field
Query within a query (Nested/Sub queries)
Database to be used for SQL Examples
Sample Database
This image can be printed off as a PDF here
Types of Query
There are a few different types of queries you are expected to use at Higher: