Skip to main content
ScotlandComputer ScienceSyllabus dot point

How do you ask a relational database questions across linked tables using SQL?

Querying a relational database with SQL SELECT, including WHERE, joining multiple tables, ORDER BY, aliases, wildcards, computed values, aggregate functions and GROUP BY.

An SQA Higher Computing Science answer on querying a relational database with SQL, covering SELECT with WHERE, joining multiple tables, ORDER BY, aliases, wildcards, computed values, aggregate functions and GROUP BY.

Generated by Claude Opus 4.813 min answer

Reviewed by: AI editorial process; not yet individually human-reviewed

Have a quick question? Jump to the Q&A page

Jump to a section
  1. What this key area is asking
  2. The shape of a SELECT query
  3. Joining multiple tables
  4. ORDER BY, wildcards and aliases
  5. Computed values
  6. Aggregate functions and GROUP BY
  7. Examples in context
  8. Try this

What this key area is asking

The SQA wants you to write SQL to query a relational database: SELECT with WHERE, joining multiple tables, ORDER BY, aliases, wildcards, computed values, aggregate functions (MIN, MAX, AVG, SUM, COUNT) and GROUP BY. You should read and write queries on a pre-populated database of three or more linked tables.

The shape of a SELECT query

SELECT name, price
FROM Product
WHERE price > 10;

This shows the name and price of every product costing more than 10. Conditions can be combined with AND, OR and NOT, just as in program selection.

Joining multiple tables

Real data is spread across linked tables, so most queries join them. You join by matching a foreign key to the primary key it refers to.

SELECT Customer.name, Order.orderDate
FROM Customer, Order
WHERE Customer.customerID = Order.customerID;

Prefixing fields with the table name (Customer.name) avoids ambiguity when both tables share a field name.

ORDER BY, wildcards and aliases

  • ORDER BY sorts the results by a field, ascending (ASC, the default) or descending (DESC): ORDER BY price DESC.
  • A wildcard with LIKE matches text patterns: % stands for any sequence of characters, so name LIKE 'S%' matches every name starting with S.
  • An alias with AS renames a column in the output, which is essential for naming a computed or aggregate column: ... AS total.

Computed values

A computed value calculates a new column from existing fields in the SELECT, rather than storing it.

SELECT name, price * quantity AS lineTotal
FROM OrderLine;

Here price * quantity is computed for each row and the alias lineTotal names it. Computed values keep the database free of redundant stored data.

Aggregate functions and GROUP BY

SELECT category, COUNT(*) AS numProducts, AVG(price) AS meanPrice
FROM Product
GROUP BY category;

This returns one row per category, with how many products it has and their average price. GROUP BY is what turns a per-row table into per-group summaries.

Examples in context

This is exactly how data-driven applications work behind the scenes. A shop's "top customers" report is a join with SUM and GROUP BY; a "books currently on loan" list is a join with a WHERE filter; a dashboard's "average order value" is AVG. The same SELECT skills scale from a Higher coursework database to systems holding millions of rows, which is why SQL is one of the most widely used languages in industry. The querying you learn here pairs with the data-manipulation key area (INSERT, UPDATE, DELETE) to cover the full database language.

Try this

Q1. State the SQL clause used to filter which records a query returns. [1 mark]

  • Cue. WHERE.

Q2. State what name LIKE 'M%' matches. [1 mark]

  • Cue. Any name beginning with the letter M.

Q3. State the purpose of GROUP BY when used with an aggregate function. [2 marks]

  • Cue. It collapses rows into groups so the aggregate (such as COUNT or SUM) is calculated separately for each group.

Exam-style practice questions

Practice questions written in the style of SQA exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.

SQA Higher (style)5 marksA database has tables Member(memberID, name) and Loan(loanID, memberID, dateOut). Write an SQL query to list each member's name and the number of loans they have, ordered from most loans to fewest.
Show worked answer →

This needs a join between the two tables, an aggregate COUNT grouped by member, and an ORDER BY.

SELECT Member.name, COUNT(*) AS loanCount
FROM Member, Loan
WHERE Member.memberID = Loan.memberID
GROUP BY Member.name
ORDER BY loanCount DESC;

The WHERE links each loan to its member (the join condition), COUNT(*) counts the loans, GROUP BY collapses the rows to one per member, the alias loanCount names the computed column, and ORDER BY ... DESC sorts most loans first.

Markers reward the correct join condition, COUNT with GROUP BY on the member, and ORDER BY DESC. An explicit JOIN ... ON form is equally acceptable.

SQA Higher (style)4 marksFrom a Product(name, category, price) table, write a query to display the name and price of all products in the 'Toys' category whose name begins with 'S', cheapest first.
Show worked answer →

This combines a WHERE with two conditions (one using a wildcard) and an ORDER BY.

SELECT name, price
FROM Product
WHERE category = 'Toys' AND name LIKE 'S%'
ORDER BY price ASC;

The LIKE 'S%' uses the % wildcard to match any name starting with S, the AND combines it with the category condition, and ORDER BY price ASC sorts cheapest first (ASC is the default and may be omitted).

Markers reward selecting the right fields, the correct WHERE with the wildcard pattern 'S%', and ordering by price ascending.

Related dot points

Sources & how we know this