Skip to main content
ScotlandComputer ScienceSyllabus dot point

How do you create, query and update a relational database in SQL?

Implement and query a relational database in SQL, using data definition (CREATE, ALTER) and data manipulation (SELECT, INSERT, UPDATE, DELETE) with joins, aggregate functions, GROUP BY and HAVING, subqueries, wildcards and computed columns.

A focused answer to the SQA Advanced Higher Computing Science content on SQL, covering data definition and data manipulation, multi-table joins, aggregate functions with GROUP BY and HAVING, subqueries, wildcards and computed columns.

Generated by Claude Opus 4.814 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 dot point is asking
  2. Data definition: building the structure
  3. Data manipulation: changing data
  4. Querying with SELECT and joins
  5. Aggregates, grouping and HAVING
  6. Subqueries, wildcards and computed columns
  7. Try this

What this dot point is asking

The SQA wants you to read and write SQL: create tables, change them, insert, update and delete data, and above all query data with multi-table joins, aggregate functions, grouping, subqueries, wildcards and computed columns. SQL is examined in the question paper and is the implementation language for a database project.

Data definition: building the structure

Data definition language (DDL) creates and changes the database structure. CREATE TABLE defines a table with its columns, data types and constraints, including the primary key and any foreign keys; ALTER TABLE changes an existing table, for example adding a column or a constraint.

CREATE TABLE Customer (
    customerID INTEGER PRIMARY KEY,
    name       VARCHAR(40),
    town       VARCHAR(30)
);

Data manipulation: changing data

Data manipulation language (DML) works with the rows. INSERT adds a row, UPDATE changes existing rows matched by a WHERE clause, and DELETE removes matched rows. Always include a WHERE clause on UPDATE and DELETE unless you really mean to change every row.

INSERT INTO Customer VALUES (1, 'Ali', 'Perth');
UPDATE Customer SET town = 'Dundee' WHERE customerID = 1;
DELETE FROM Customer WHERE customerID = 1;

Querying with SELECT and joins

A SELECT lists the columns to return, the tables in FROM, a WHERE condition, and an optional ORDER BY. To combine related tables you join them on the foreign-key match, so a sale is paired with the customer it belongs to.

Aggregates, grouping and HAVING

Aggregate functions summarise many rows into one value: SUM, COUNT, AVG, MIN and MAX. GROUP BY splits rows into groups so the aggregate is computed per group, and HAVING filters those groups (unlike WHERE, which filters individual rows before grouping).

Subqueries, wildcards and computed columns

A subquery is a SELECT nested inside another query, often in the WHERE clause, to compare against a value the outer query cannot name directly (for example sales above the average). Wildcards with LIKE match text patterns, where % stands for any sequence of characters. A computed column calculates a value in the SELECT list, such as price * quantity AS lineTotal.

SELECT name FROM Customer WHERE town LIKE 'P%';
SELECT name FROM Sale WHERE amount > (SELECT AVG(amount) FROM Sale);

Try this

Q1. Name the SQL clause used to filter groups created by GROUP BY. [1 mark]

  • Cue. HAVING.

Q2. State which aggregate function counts the number of rows in a group. [1 mark]

  • Cue. COUNT.

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.

AH style: join + aggregate5 marksTables Customer(customerID, name) and Sale(saleID, customerID, amount) are linked by customerID. Write an SQL query to list each customer's name and their total sales, for customers whose total exceeds 1000, ordered by total descending.
Show worked answer →

SELECT name, SUM(amount) AS total (1 mark for the aggregate with an alias) FROM Customer, Sale (or with an explicit JOIN) WHERE Customer.customerID = Sale.customerID (1 mark for the join condition) GROUP BY name (1 mark, grouping by the non-aggregated column) HAVING SUM(amount) > 1000 (1 mark, filtering the grouped totals with HAVING not WHERE) ORDER BY total DESC (1 mark). Markers reward the join, the SUM, the GROUP BY, the HAVING for the aggregated condition, and the correct ORDER BY.

AH style: WHERE vs HAVING2 marksExplain the difference between WHERE and HAVING in an SQL query that uses GROUP BY.
Show worked answer →

WHERE filters individual rows before they are grouped, so it cannot refer to an aggregate (1 mark). HAVING filters the groups after aggregation, so it is the clause used to test an aggregate such as SUM or COUNT against a condition (1 mark). For example, WHERE amount > 0 removes rows first; HAVING SUM(amount) > 1000 removes whole groups whose total is too small.

Related dot points

Sources & how we know this