Query Design (SQA National 5 Computing Science): Revision Note

Query design

What is a query?

• A query is a request for specific information from a database

• Before any SQL is written, students must design the query

• The design is a simple plan that identifies the required:

  • Fields, the data that will appear in the final output

  • Tables, the tables the database must read to obtain the fields

  • Search criteria, the rules that filter the data

  • Sort order, the way the results should be ordered

• These elements must be selected using the ERD and data dictionary provided in the question

• Good design reduces mistakes and helps ensure the final SQL solution meets the requirements

Designing queries with multiple tables and fields

• Some queries require data from more than one table

• In the exam, students must be able to:

  • Identify all required tables

  • Identify all output fields

• If a query uses fields from different tables, the SQL written later will require a join using the primary key and foreign key

• Students do not write the join during the design stage

• They only select the correct tables and fields

Example

• A query asks for hotel name, phone number, town, and train station details

• A correct design would include:

  • Tables: Hotel, Resort

  • Fields: hotelName, phoneNumber, town, trainStation

• This is sufficient for full marks in the design stage

Designing search criteria

• Search criteria determine which records are included in the results

• Students must use correct field names and appropriate operators

• Common operators include:

  • =

  • <

  • >

  • <=

  • >=

  • AND

  • OR

Examples

• town = "Ayr"

• swimmingPool = true AND starRating >= 4

• Both examples show valid criteria based on a data dictionary

Designing sort order

• Sort order controls how the final results are organised

• Students must be able to design:

  • A single-field sort

  • A two-field sort

• Two-field sorting is required knowledge

• The first field is the main sort, the second deals with ties

Example

• The requirement states: “Show hotels with the highest star rating first, then sort towns alphabetically.”

• The correct sort order in the design is:

  • starRating DESC

  • town ASC

Final query design