Grade 12 IT | Advanced SQL Notes | Learning Unit 1
Advanced SQL – Complete Study Notes
Grade 12 | Exploring IT: Java Programming | Learning Unit 1
1. General Form of a SQL SELECT Statement
A SQL statement can have many clauses — only SELECT is compulsory. The full general form is:
MS Access MySQL
SELECT TOP <n> <field(s)> FROM SELECT <field(s)> FROM <table(s)>
<table(s)> WHERE condition(s) GROUP BY WHERE condition(s) GROUP BY expression
expression HAVING condition ORDER BY HAVING condition ORDER BY expression
expression LIMIT <n>
⭐ EXAM TIP
In the exam, make sure you use the correct syntax for the platform specified (MS Access vs
MySQL). TOP n vs LIMIT n is a common mistake!
2. SQL Clause Quick-Reference Table
Clause MS Access / MySQL Purpose
SELECT SELECT field1, field2 Choose which fields to display
FROM FROM tableName Specify the table(s) to query
WHERE WHERE condition Filter rows (before grouping)
GROUP BY GROUP BY field Group rows for aggregate functions
HAVING HAVING condition Filter groups (after GROUP BY)
ORDER BY ORDER BY field Sort results
ASC/DESC
LIMIT (MySQL) LIMIT n Restrict number of rows returned
TOP n (Access) SELECT TOP n ... Restrict number of rows returned
DISTINCT SELECT DISTINCT field Remove duplicate values
AS field AS Alias Rename a field in the result set
3. Joining Tables
In Grade 12 we query multiple tables at once. The link between tables is always a primary/foreign key
relationship.
Advanced SQL Notes | Grade 12 IT
Grade 12 IT | Advanced SQL Notes | Learning Unit 1
3.1 Cartesian Product (no link specified)
If you list two tables in FROM without linking them, every row in table 1 is matched to every row in table 2.
SELECT * FROM tblEntrants, tblActs -- produces rows = 47 × 28 = 1316!
⭐ COMMON MISTAKE
Always provide a WHERE or ON condition when joining tables — otherwise you get a Cartesian
product.
3.2 WHERE Clause Join (older syntax)
Link two tables using a WHERE clause that matches the primary key in one table to the foreign key in the other.
SELECT Firstname, Surname, ActName, Category
FROM tblEntrants, tblActs
WHERE [Link] = [Link]
📝 NOTE
When two tables share a field name, prefix it with the table name: [Link] vs
[Link]
3.3 INNER JOIN (preferred syntax)
Returns only the rows that have a match in BOTH tables. This is the most common join type.
SELECT field1, field2
FROM firstTable
INNER JOIN secondTable ON [Link] = [Link]
General form:
SELECT <table1>.<field1>, <table2>.<field2>, <field3>
FROM <table1>
INNER JOIN <table2> ON <table1>.<primary key> = <table2>.<foreign key>
3.4 Joining Three Tables (Nested JOIN)
FROM tblPoints
INNER JOIN (tblActs INNER JOIN tblEntrants ON [Link] = [Link])
ON [Link] = [Link]
📝 NOTE
The virtual table in brackets is treated as a single table. Three tables need exactly TWO links (ON
conditions).
3.5 LEFT JOIN
Returns ALL rows from the LEFT (first) table. If there is no match in the right table, NULL is placed in those fields.
SELECT [Link], [Link]
FROM Employees
LEFT JOIN Orders ON Employees.Employee_ID = Orders.Employee_ID
Advanced SQL Notes | Grade 12 IT
Grade 12 IT | Advanced SQL Notes | Learning Unit 1
📝 NOTE
Use LEFT JOIN to find rows that have NO match: add WHERE [Link] IS NULL
3.6 RIGHT JOIN
Returns ALL rows from the RIGHT (second) table. NULL appears where there is no match in the left table. A
RIGHT JOIN is the exact opposite of a LEFT JOIN — you can always rewrite one as the other by swapping table
order.
3.7 JOIN Summary Table
Join Type Syntax What it Returns
INNER JOIN FROM t1 INNER JOIN t2 ON [Link] Only matching rows from both
= [Link] tables
LEFT JOIN FROM t1 LEFT JOIN t2 ON [Link] All rows from left table;
= [Link] NULL if no match in right
RIGHT JOIN FROM t1 RIGHT JOIN t2 ON [Link] All rows from right table;
= [Link] NULL if no match in left
WHERE Join FROM t1, t2 WHERE [Link] = Same as INNER JOIN (older
[Link] syntax)
Nested Join FROM t1 INNER JOIN (t2 INNER Join 3+ tables
JOIN t3 ON ...) ON ...
4. Aggregate Functions & GROUP BY
Function Syntax Example What it Does
COUNT(*) SELECT COUNT(*) FROM Count all rows
tbl
COUNT(field) SELECT COUNT(ActNum) Count non-null values
FROM tbl
SUM(field) SUM(Quantity) Total of numeric field
AVG(field) AVG(Judge1) Average of numeric field
ROUND(val, n) ROUND(AVG(Judge1), 1) Round to n decimal places
MAX(field) MAX(Balance) Largest value
MIN(field) MIN(Balance) Smallest value
4.1 GROUP BY
Used with aggregate functions to calculate a result for each group (e.g., each category or salesperson).
SELECT ActName, COUNT(*) AS NumEntrants
FROM tblActs, tblEntrants
WHERE [Link] = [Link]
GROUP BY ActName
Advanced SQL Notes | Grade 12 IT
Grade 12 IT | Advanced SQL Notes | Learning Unit 1
⭐ EXAM TIP
All fields in SELECT that are NOT aggregate functions must appear in GROUP BY.
4.2 HAVING
HAVING filters groups AFTER GROUP BY — it is the equivalent of WHERE but for aggregate results.
GROUP BY Category
HAVING ROUND(AVG(Judge1 + Judge2 + Judge3)/3, 1) >= 8
📝 NOTE
WHERE filters individual rows (before grouping). HAVING filters groups (after grouping).
4.3 ORDER BY
Sorts the final result. Use ASC (default) for ascending or DESC for descending.
ORDER BY Surname ASC, FirstName ASC
ORDER BY SUM(Quantity) DESC
5. NOT IN and Embedded (Sub) Queries
An embedded query (subquery) is a SELECT statement inside another SELECT. It appears in the WHERE
clause.
SELECT Category
FROM tblActs
WHERE Category NOT IN (
SELECT DISTINCT Category
FROM tblActs
WHERE ROUND(AVG(Judge1+Judge2+Judge3)/3,1) >= 8
)
Steps to read this query:
• Inner query runs first — produces a list of category names.
• Outer query returns all categories NOT in that list.
⭐ EXAM TIP
NOT IN excludes values. IN includes only matching values. Both can use a subquery or a literal list:
WHERE Grade IN (10, 11, 12)
6. Using INNER JOIN Instead of Compound WHERE Clauses
INNER JOIN replaces the compound WHERE clause syntax. The tables that provide the foreign key relationships
are connected with ON. All other conditions go in the WHERE clause.
SELECT field1, field2, field3
Advanced SQL Notes | Grade 12 IT
Grade 12 IT | Advanced SQL Notes | Learning Unit 1
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
📝 NOTE
Advantages of INNER JOIN syntax: clearer, more portable, easier to extend to 3+ tables, and
preferred in IEB exams.
7. SQL Statement to Create a Table
MS Access:
CREATE TABLE tblCustomer (
CustID AUTOINCREMENT,
CustName VARCHAR(30),
Branch VARCHAR(20),
PRIMARY KEY (CustID)
)
MySQL:
CREATE TABLE tblCustomer (
CustID INT NOT NULL AUTO_INCREMENT,
CustName VARCHAR(30),
Branch VARCHAR(20),
PRIMARY KEY (CustID)
)
Adding a Foreign Key (MySQL):
CREATE TABLE tblAccount (
AcNo INT,
Type VARCHAR(10),
Balance DOUBLE,
MinBal INT,
CustID INT,
PRIMARY KEY (AcNo),
FOREIGN KEY (CustID) REFERENCES tblCustomer(CustID)
)
⭐ EXAM TIP
AUTOINCREMENT (Access) vs AUTO_INCREMENT (MySQL) — notice the underscore difference!
In MySQL, CustID must also be NOT NULL for AUTO_INCREMENT to work.
8. Data Manipulation Language (DML) Summary
Statement Syntax When to Use
INSERT INTO INSERT INTO tbl (f1,f2) Add a new record
VALUES (v1,v2)
Advanced SQL Notes | Grade 12 IT
Grade 12 IT | Advanced SQL Notes | Learning Unit 1
Statement Syntax When to Use
INSERT...SELECT INSERT INTO tbl (f1) Copy records from another
SELECT f1 FROM tbl2 WHERE table/query
...
UPDATE UPDATE tbl SET Modify existing records
field=value WHERE
condition
DELETE DELETE FROM tbl WHERE Remove records
condition
CREATE TABLE CREATE TABLE tbl (field Create a new table
datatype, PRIMARY KEY(f))
8.1 INSERT INTO ... VALUES
INSERT INTO tblEntrants (FirstName, Surname, Grade, Class, ActNum)
VALUES ('Leda', 'Burnside', '1999-10-15', 12, 'A', 501)
8.2 INSERT INTO ... SELECT (Embedded INSERT)
Inserts records selected from another query — used when you don't know the values in advance.
INSERT INTO tblEntrants (EntrantNum, FirstName, Surname, ActNum)
SELECT EntrantNum + 100, FirstName, Surname, 999
FROM tblEntrants
WHERE ActNum IN (SELECT ActNum FROM ... WHERE avg_score > overall_avg)
⭐ EXAM TIP
The new EntrantNum = old EntrantNum + 100 is a common exam pattern to generate a unique
primary key when AUTOINCREMENT is not allowed.
8.3 UPDATE
UPDATE tblEntrants
SET Grade = 12
WHERE EntrantNum = 16001
📝 NOTE
Always include a WHERE clause in UPDATE and DELETE — without it, EVERY record is
changed/deleted!
9. Advanced Techniques
9.1 TOP / LIMIT
-- MS Access
SELECT TOP 3 SName, SUM(Quantity) AS TotalItemsSold
FROM tblSalesPeople, tblInvoices
WHERE [Link] = [Link]
Advanced SQL Notes | Grade 12 IT
Grade 12 IT | Advanced SQL Notes | Learning Unit 1
GROUP BY SName
ORDER BY SUM(Quantity) DESC
-- MySQL equivalent
... ORDER BY SUM(Quantity) DESC LIMIT 3
9.2 Calculated Fields & ROUND
SELECT clientName, itemCostPrice * 1.35 AS TotalSpend
-- Adds 35% markup as a new calculated column
SELECT SUM(itemCostPrice * Quantity) * 1.35 AS TotalSpend
9.3 HAVING with SUM
HAVING SUM(itemCostPrice * Quantity) >= 100000
⭐ EXAM TIP
You can use an alias (AS) in ORDER BY but NOT in WHERE or HAVING — repeat the full
expression there instead.
10. Keywords & Operators Quick-Reference
Keyword / Operator Example Meaning
AND WHERE Grade=10 AND Both conditions must be true
Class='A'
OR WHERE Cat='Singing' OR Either condition true
Cat='Dancing'
NOT IN WHERE Cat NOT IN (SELECT Exclude values in subquery/list
Cat FROM ...)
IN WHERE Grade IN (10, 11, Match any value in list
12)
LIKE WHERE Name LIKE 'L%' Pattern matching (% = wildcard)
BETWEEN WHERE Balance BETWEEN Range (inclusive)
100 AND 500
IS NULL WHERE Product IS NULL Field has no value
IS NOT NULL WHERE Product IS NOT Field has a value
NULL
= WHERE SPID = clientID Equality (used in JOIN
conditions)
11. General Strategy for Multi-Table Queries
Advanced SQL Notes | Grade 12 IT
Grade 12 IT | Advanced SQL Notes | Learning Unit 1
Follow these steps whenever a query involves more than one table:
• Step 1 – Identify the required fields and which tables contain them.
• Step 2 – Identify the required tables according to the primary/foreign key relationships.
• Step 3 – Identify any intermediate tables that are needed as a 'path' between the required tables.
• Step 4 – Build the FROM / JOIN clause using those tables.
• Step 5 – Add WHERE / ON conditions and any aggregate/filter clauses.
⭐ EXAM TIP
Before writing the query, draw or identify the relationship diagram. Count the number of links
needed (tables − 1 = links required).
12. Common Exam Mistakes to Avoid
• Forgetting to prefix ambiguous field names with the table name (e.g., [Link]).
• Using WHERE instead of HAVING to filter aggregate results.
• Placing a calculated/alias field in a HAVING clause — repeat the full expression.
• Forgetting to include bracket tables correctly in a three-table nested JOIN.
• Using TOP n (Access) instead of LIMIT n (MySQL) or vice versa.
• Missing the semicolon at the end of a MySQL statement.
• Omitting the WHERE clause in UPDATE or DELETE — affects ALL records!
• Using GROUP BY without aggregate functions (or missing a field from GROUP BY).
• Confusing INNER JOIN (matches only) with LEFT JOIN (all from left, NULLs for unmatched).
• Writing a Cartesian product by forgetting the ON / WHERE join condition.
— End of SQL Study Notes —
Advanced SQL Notes | Grade 12 IT