SQL

Learn it by playing

Answer these questions to earn energy, then fish and explore. No account needed.

For teachers: ready-to-use lesson slides, revision notes, diagrams for SQL (Computer Science, CIE) — use them in your lesson, or run the topic as a live class game.

Notes

What is SQL?

**SQL** (Structured Query Language) is a programming language used to interact with a **DBMS**.
SQL allows users to **select**, **order**, **sum**, and **count** data.

SELECT and FROM

**SELECT** retrieves data from a database table.
Use `SELECT×` to retrieve **all columns** from a table.
**FROM** specifies the table(s) to retrieve data from.
Example: `SELECT name, age FROM users;` retrieves names and ages from the 'users' table.

WHERE Clause

**WHERE** filters data based on a specified condition.
Example: `SELECT×FROM users WHERE age > 30;` retrieves users older than 30.
Conditions can use comparison operators: `=`, `>`, `<`, `≥`, `≤`, `<>`.

AND and OR Operators

**AND** combines multiple conditions – all must be true.
Example: `SELECT×FROM users WHERE age > 18 AND city = 'New York';`
**OR** retrieves data when at least one condition is true.
Example: `SELECT×FROM users WHERE age < 18 OR city = 'New York';`

Wildcards and LIKE

**Wildcard** `%` matches any sequence of characters.
Use **LIKE** with wildcards for pattern matching.
Example: `SELECT×FROM users WHERE name LIKE 'J%';` finds names starting with 'J'.
`×` selects all columns; `%` is used only with LIKE.

ORDER BY

**ORDER BY** sorts results in ascending (**ASC**) or descending (**DESC**) order.
Default order is ascending.
Example: `SELECT Forename, Lastname FROM Students ORDER BY Lastname ASC;`
Multiple columns can be specified, e.g., `ORDER BY Lastname, Forename ASC`.

SUM and COUNT

**SUM** adds up and outputs the sum of a numeric field.
Example: `SELECT SUM(Salary) FROM tbl_people;`
**COUNT** counts the number of rows that match the criteria.
Example: `SELECT COUNT(×) FROM tbl_people WHERE Salary > 50000;`

Worked Example

Given table `tbl_animals` with fields: Animal, Breeding, Number of Young.
To display animals that are breeding and have exactly 1 young:
`SELECT Animal FROM tbl_animals WHERE Breeding = 'Yes' AND Number of Young = 1;`

Structure of a SQL SELECT query showing the order of clauses.

Practice questions

Free preview — 8 of 40 questions. Sign up to see them all.

1.What does SQL stand for?
Easy
- AStructured Query Language
- BSimple Query Language
- CStandard Query Language
- DSequential Query Language
2.Which SQL command is used to retrieve data from a database table?
Easy
- ASELECT
- BFROM
- CWHERE
- DGET
3.In SQL, which keyword is used to filter records based on a condition?
Easy
- AWHERE
- BIF
- CFILTER
- DHAVING
4.What does the '×' symbol represent in SQL?
Easy
- AAll columns
- BAll rows
- CA wildcard character
- DMultiplication
5.Which SQL statement retrieves the name and age of all customers from the 'Customers' table?
Medium
- ASELECT name, age FROM Customers;
- BSELECT name AND age FROM Customers;
- CSELECT×FROM Customers;
- DGET name, age FROM Customers;
6.What is the purpose of the ORDER BY clause in SQL?
Medium
- ATo sort the result set
- BTo filter records
- CTo group records
- DTo count records
7.Which SQL function is used to calculate the total of a numeric column?
Medium
- ASUM
- BCOUNT
- CTOTAL
- DADD
8.Given a table 'Students' with columns 'Name' and 'Age', which SQL statement selects students older than 18?
Medium
- ASELECT×FROM Students WHERE Age > 18;
- BSELECT×FROM Students IF Age > 18;
- CSELECT×FROM Students HAVING Age > 18;

More questions

9.What does the COUNT function do in SQL?
Medium
- ACounts the number of rows that match a condition
- BCounts the number of columns in a table
- CCounts the sum of a numeric column
- DCounts the number of distinct values
10.Which SQL statement would retrieve all records from the 'Orders' table where the 'Status' is either 'Pending' or 'Shipped'?
Hard
- ASELECT×FROM Orders WHERE Status = 'Pending' OR Status = 'Shipped';
- BSELECT×FROM Orders WHERE Status = 'Pending' AND Status = 'Shipped';
- CSELECT×FROM Orders WHERE Status IN ('Pending', 'Shipped');
- DSELECT×FROM Orders WHERE Status LIKE 'Pending%' OR 'Shipped%';
11.Given a table 'Employees' with columns 'Name', 'Department', and 'Salary', which SQL statement lists all employees from the 'Sales' department with a salary greater than 50000?
Hard
- ASELECT×FROM Employees WHERE Department = 'Sales' AND Salary > 50000;
- BSELECT×FROM Employees WHERE Department = 'Sales' OR Salary > 50000;
- CSELECT×FROM Employees WHERE Department = 'Sales' AND Salary < 50000;
12.Which SQL statement correctly uses a wildcard to find customers whose names start with 'J'?
Hard
- ASELECT×FROM Customers WHERE Name LIKE 'J%';
- BSELECT×FROM Customers WHERE Name = 'J%';
- CSELECT×FROM Customers WHERE Name LIKE '%J';
- DSELECT×FROM Customers WHERE Name = 'J×';
13.Given a table 'Products' with columns 'ProductID', 'Name', 'Price', and 'Quantity', which SQL statement calculates the total value of all products in stock?
Medium
- ASELECT SUM(Price×Quantity) FROM Products;
- BSELECT SUM(Price) FROM Products;
- CSELECT COUNT(Price×Quantity) FROM Products;
- DSELECT TOTAL(Price×Quantity) FROM Products;
14.Consider the table 'Orders' with columns 'OrderID', 'CustomerID', 'OrderDate', and 'Amount'. Which SQL statement retrieves the total amount spent by each customer, sorted by total amount in descending order?
Hard
- ASELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID ORDER BY SUM(Amount) DESC;
- BSELECT CustomerID, SUM(Amount) FROM Orders ORDER BY SUM(Amount) DESC;
- CSELECT CustomerID, SUM(Amount) FROM Orders GROUP BY Amount ORDER BY SUM(Amount) DESC;
- DSELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID ORDER BY Amount DESC;
15.Given a table 'Students' with columns 'StudentID', 'Name', 'Subject', and 'Score', which SQL statement finds the average score for each subject?
Medium
- ASELECT Subject, AVG(Score) FROM Students GROUP BY Subject;
- BSELECT Subject, AVG(Score) FROM Students;
- CSELECT Subject, SUM(Score) FROM Students GROUP BY Subject;
- DSELECT Subject, COUNT(Score) FROM Students GROUP BY Subject;
16.A table 'Sales' has columns 'SaleID', 'Product', 'Quantity', and 'Date'. Which SQL statement finds the product with the highest total quantity sold?
Hard
- ASELECT Product, SUM(Quantity) AS TotalQty FROM Sales GROUP BY Product ORDER BY TotalQty DESC LIMIT 1;
- BSELECT Product, SUM(Quantity) FROM Sales GROUP BY Product ORDER BY Quantity DESC LIMIT 1;
- CSELECT Product, SUM(Quantity) FROM Sales ORDER BY SUM(Quantity) DESC LIMIT 1;
- DSELECT Product, MAX(Quantity) FROM Sales GROUP BY Product ORDER BY MAX(Quantity) DESC LIMIT 1;
17.In SQL, which clause is used to combine rows from two or more tables based on a related column?
Easy
- AJOIN
- BMERGE
- CCOMBINE
- DLINK
18.Given a table 'Employees' with columns 'EmployeeID', 'Name', 'Department', and 'Salary', which SQL statement lists departments with an average salary greater than 50000?
Medium
- ASELECT Department, AVG(Salary) FROM Employees GROUP BY Department HAVING AVG(Salary) > 50000;
- BSELECT Department, AVG(Salary) FROM Employees WHERE AVG(Salary) > 50000 GROUP BY Department;
- CSELECT Department, AVG(Salary) FROM Employees GROUP BY Department WHERE AVG(Salary) > 50000;
- DSELECT Department, AVG(Salary) FROM Employees HAVING AVG(Salary) > 50000;
19.A table 'Books' has columns 'BookID', 'Title', 'Author', and 'YearPublished'. Which SQL statement counts the number of books published after the year 2000?
Medium
- ASELECT COUNT(×) FROM Books WHERE YearPublished > 2000;
- BSELECT COUNT(YearPublished) FROM Books WHERE YearPublished > 2000;
- CSELECT SUM(×) FROM Books WHERE YearPublished > 2000;
- DSELECT COUNT(×) FROM Books HAVING YearPublished > 2000;
20.Given two tables: 'Customers' (CustomerID, Name, City) and 'Orders' (OrderID, CustomerID, Amount). Which SQL statement retrieves the names of customers who have placed at least one order?
Hard
- ASELECT Name FROM Customers WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders);
- BSELECT Name FROM Customers WHERE CustomerID = ANY (SELECT CustomerID FROM Orders);
- CSELECT Name FROM Customers WHERE CustomerID EXISTS (SELECT CustomerID FROM Orders);
- DSELECT Name FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders);
21.In SQL, which keyword is used to remove duplicate rows from the result set?
Easy
- ADISTINCT
- BUNIQUE
- CDIFFERENT
- DREMOVE
22.Given a table 'Inventory' with columns 'ItemID', 'ItemName', 'Category', and 'Stock', which SQL statement finds the total stock for each category, but only for categories where total stock is less than 100?
Medium
- ASELECT Category, SUM(Stock) FROM Inventory GROUP BY Category HAVING SUM(Stock) < 100;
- BSELECT Category, SUM(Stock) FROM Inventory WHERE SUM(Stock) < 100 GROUP BY Category;
- CSELECT Category, SUM(Stock) FROM Inventory GROUP BY Category WHERE SUM(Stock) < 100;
- DSELECT Category, SUM(Stock) FROM Inventory HAVING SUM(Stock) < 100;
23.A database has two tables: 'Students' (StudentID, Name) and 'Enrollments' (EnrollmentID, StudentID, CourseID). Which SQL statement finds the names of students who are not enrolled in any course?
Hard
- ASELECT Name FROM Students WHERE StudentID NOT IN (SELECT DISTINCT StudentID FROM Enrollments);
- BSELECT Name FROM Students WHERE StudentID NOT EXISTS (SELECT StudentID FROM Enrollments);
- CSELECT Name FROM Students WHERE StudentID <> ALL (SELECT StudentID FROM Enrollments);
- DSELECT Name FROM Students MINUS SELECT Name FROM Enrollments;
24.Given a table 'Transactions' with columns 'TransactionID', 'AccountID', 'Type' (either 'Deposit' or 'Withdrawal'), and 'Amount'. Which SQL statement calculates the net balance change for each account?
Medium
- ASELECT AccountID, SUM(CASE WHEN Type='Deposit' THEN Amount ELSE -Amount END) FROM Transactions GROUP BY AccountID;
- BSELECT AccountID, SUM(Amount) FROM Transactions GROUP BY AccountID;
- CSELECT AccountID, SUM(Amount) FROM Transactions WHERE Type='Deposit' GROUP BY AccountID;
- DSELECT AccountID, SUM(Amount) FROM Transactions WHERE Type='Withdrawal' GROUP BY AccountID;
25.Which SQL statement would retrieve all records from the 'Students' table where the 'Grade' is 'A'?
Easy
- ASELECT×FROM Students WHERE Grade = 'A';
- BSELECT×FROM Students IF Grade = 'A';
- CSELECT×FROM Students HAVING Grade = 'A';
- DSELECT×FROM Students WHEN Grade = 'A';
26.What does the '%' symbol represent when used with the LIKE operator in SQL?
Easy
- AA wildcard that matches any sequence of characters
- BA wildcard that matches a single character
- CA placeholder for a numeric value
- DA symbol to indicate a comment
27.Given a table 'Products' with columns 'ProductID', 'Name', 'Price', and 'Category', which SQL statement finds the average price of products in the 'Electronics' category?
Medium
- ASELECT AVG(Price) FROM Products WHERE Category = 'Electronics';
- BSELECT AVERAGE(Price) FROM Products WHERE Category = 'Electronics';
- CSELECT SUM(Price) / COUNT(Price) FROM Products HAVING Category = 'Electronics';
- DSELECT AVG(Price) FROM Products HAVING Category = 'Electronics';
28.Consider a table 'Orders' with columns 'OrderID', 'CustomerID', 'OrderDate', and 'Amount'. Which SQL statement retrieves the total amount spent by each customer, sorted by total in descending order?
Medium
- ASELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID ORDER BY SUM(Amount) DESC;
- BSELECT CustomerID, SUM(Amount) FROM Orders ORDER BY SUM(Amount) DESC GROUP BY CustomerID;
- CSELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID ORDER BY Amount DESC;
- DSELECT CustomerID, SUM(Amount) FROM Orders ORDER BY Amount DESC;
29.Given two tables: 'Students' (StudentID, Name, Class) and 'Enrollments' (EnrollmentID, StudentID, CourseID, Grade). Which SQL statement finds the names of students who have a grade of 'A' in any course?
Hard
- ASELECT Name FROM Students WHERE StudentID IN (SELECT StudentID FROM Enrollments WHERE Grade = 'A');
- BSELECT Name FROM Students, Enrollments WHERE Students.StudentID = Enrollments.StudentID AND Grade = 'A';
- CSELECT Name FROM Students INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID WHERE Grade = 'A';
- DAll of the above
30.A table 'Transactions' has columns 'TransactionID', 'AccountID', 'Type' ('Deposit' or 'Withdrawal'), and 'Amount'. Which SQL statement calculates the current balance for each account (assuming starting balance 0)?
Hard
- ASELECT AccountID, SUM(CASE WHEN Type = 'Deposit' THEN Amount WHEN Type = 'Withdrawal' THEN -Amount END) AS Balance FROM Transactions GROUP BY AccountID;
- BSELECT AccountID, SUM(Amount) AS Balance FROM Transactions GROUP BY AccountID;
- CSELECT AccountID, SUM(Amount) FROM Transactions WHERE Type = 'Deposit' GROUP BY AccountID;
- DSELECT AccountID, SUM(Amount) - SUM(Amount) AS Balance FROM Transactions GROUP BY AccountID;
31.Which SQL clause is used to sort the result set?
Easy
- AORDER BY
- BSORT BY
- CGROUP BY
- DARRANGE BY
32.Given a table 'Employees' with columns 'EmployeeID', 'Name', 'Department', and 'Salary', which SQL statement lists departments where the average salary is greater than 50000?
Medium
- ASELECT Department, AVG(Salary) FROM Employees GROUP BY Department HAVING AVG(Salary) > 50000;
- BSELECT Department, AVG(Salary) FROM Employees WHERE AVG(Salary) > 50000 GROUP BY Department;
- CSELECT Department, AVG(Salary) FROM Employees GROUP BY Department WHERE AVG(Salary) > 50000;
- DSELECT Department, AVG(Salary) FROM Employees HAVING AVG(Salary) > 50000;
33.Given a table 'Inventory' with columns 'ItemID', 'ItemName', 'Category', and 'Stock', which SQL statement finds the total stock for each category, but only includes categories with total stock greater than 100?
Hard
- ASELECT Category, SUM(Stock) FROM Inventory GROUP BY Category HAVING SUM(Stock) > 100;
- BSELECT Category, SUM(Stock) FROM Inventory WHERE SUM(Stock) > 100 GROUP BY Category;
- CSELECT Category, SUM(Stock) FROM Inventory GROUP BY Category WHERE SUM(Stock) > 100;
- DSELECT Category, SUM(Stock) FROM Inventory HAVING SUM(Stock) > 100;
34.A database has two tables: 'Customers' (CustomerID, Name, City) and 'Orders' (OrderID, CustomerID, Amount). Which SQL statement retrieves the names of customers who have placed at least one order with amount greater than 100?
Hard
- ASELECT Name FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE Amount > 100);
- BSELECT Name FROM Customers WHERE EXISTS (SELECT×FROM Orders WHERE Orders.CustomerID = Customers.CustomerID AND Amount > 100);
- CSELECT Name FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Amount > 100;
- DAll of the above
35.Given a table 'Orders' with columns 'OrderID', 'CustomerID', 'OrderDate', and 'Amount', which SQL statement retrieves the total amount spent by each customer?
Hard
- ASELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID;
- BSELECT CustomerID, SUM(Amount) FROM Orders;
- CSELECT CustomerID, TOTAL(Amount) FROM Orders GROUP BY CustomerID;
- DSELECT CustomerID, SUM(Amount) FROM Orders ORDER BY CustomerID;
36.Given the table 'Products' with columns 'ProductID', 'Name', 'Price', and 'Quantity', which SQL statement calculates the total value of all products in stock?
Medium
- ASELECT SUM(Price×Quantity) FROM Products;
- BSELECT SUM(Price)×SUM(Quantity) FROM Products;
- CSELECT Price×Quantity FROM Products;
- DSELECT COUNT(Price×Quantity) FROM Products;
37.Consider the table 'Orders' with columns 'OrderID', 'CustomerID', 'OrderDate', and 'Amount'. Which SQL statement retrieves the total amount spent by each customer, only including customers who have spent more than $500 in total?
Hard
- ASELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID HAVING SUM(Amount) > 500;
- BSELECT CustomerID, SUM(Amount) FROM Orders WHERE SUM(Amount) > 500 GROUP BY CustomerID;
- CSELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID WHERE SUM(Amount) > 500;
- DSELECT CustomerID, SUM(Amount) FROM Orders HAVING SUM(Amount) > 500;
38.Given the table 'Students' with columns 'StudentID', 'Name', 'Subject', and 'Score', which SQL statement finds the average score for each subject?
Medium
- ASELECT Subject, AVG(Score) FROM Students GROUP BY Subject;
- BSELECT Subject, AVG(Score) FROM Students;
- CSELECT AVG(Score) FROM Students GROUP BY Subject;
- DSELECT Subject, AVG(Score) FROM Students ORDER BY Subject;
39.Given a table 'Employees' with columns 'EmployeeID', 'Name', 'Department', and 'Salary', which SQL statement lists departments with an average salary greater than $50,000?
Medium
- ASELECT Department, AVG(Salary) FROM Employees GROUP BY Department HAVING AVG(Salary) > 50000;
- BSELECT Department, AVG(Salary) FROM Employees WHERE AVG(Salary) > 50000 GROUP BY Department;
- CSELECT Department, AVG(Salary) FROM Employees GROUP BY Department WHERE AVG(Salary) > 50000;
- DSELECT Department, AVG(Salary) FROM Employees HAVING AVG(Salary) > 50000;
40.Given a table 'Inventory' with columns 'ItemID', 'ItemName', 'Category', and 'Stock', which SQL statement finds the total stock for each category, but only including categories that have more than 10 items in total?
Hard
- ASELECT Category, SUM(Stock) FROM Inventory GROUP BY Category HAVING SUM(Stock) > 10;
- BSELECT Category, SUM(Stock) FROM Inventory WHERE SUM(Stock) > 10 GROUP BY Category;
- CSELECT Category, SUM(Stock) FROM Inventory GROUP BY Category WHERE SUM(Stock) > 10;
- DSELECT Category, SUM(Stock) FROM Inventory HAVING SUM(Stock) > 10;

Slides

What is SQL?

SQL stands for Structured Query Language.
It is used to interact with a Database Management System (DBMS).
SQL allows you to select, order, sum, and count data.

What does SQL stand for?

Structured Query Language
Simple Query Language
Standard Query Language
Structured Question Language

SELECT and FROM

SELECT retrieves data from a database table.
FROM specifies which table to retrieve data from.
Example: SELECT×FROM users; retrieves all columns from the users table.
You can select specific columns: SELECT name, age FROM users;

To retrieve all columns from a table called 'Students', you write: SELECT ____ FROM Students;

WHERE Clause

WHERE filters data based on a condition.
Example: SELECT×FROM users WHERE age > 30; retrieves users older than 30.
You can use comparison operators: =, >, <, ≥, ≤, <> (not equal).

Which SQL statement retrieves all columns from the 'Customers' table where the city is 'London'?

SELECT×FROM Customers WHERE City = 'London';
SELECT×FROM Customers WHERE City = London;
SELECT City FROM Customers WHERE×= 'London';
SELECT×FROM Customers WHERE City = 'London'

AND and OR Operators

AND combines multiple conditions – all must be true.
OR returns data when at least one condition is true.
Example: SELECT×FROM users WHERE age > 18 AND city = 'New York';
Example: SELECT×FROM users WHERE age < 18 OR city = 'New York';

Which of these are true about AND and OR?

AND requires all conditions to be true.
OR requires all conditions to be true.
OR returns data if at least one condition is true.

LIKE and Wildcards

LIKE is used with wildcards to search for patterns.
'%' matches any sequence of characters.
Example: SELECT×FROM users WHERE name LIKE 'J%'; finds names starting with 'J'.
'×' selects all columns (not used with LIKE).

To find customers whose country starts with 'U', you write: SELECT×FROM Customers WHERE Country ____ 'U%';

ORDER BY

ORDER BY sorts the result set.
Use ASC for ascending order (default) or DESC for descending.
Example: SELECT×FROM Students ORDER BY Lastname ASC;
You can sort by multiple columns: ORDER BY Lastname, Forename ASC;

Which clause is used to sort data in SQL?

ORDER BY
SORT BY
GROUP BY
ARRANGE BY

SUM and COUNT

SUM adds up the values in a numeric column.
Example: SELECT SUM(Salary) FROM tbl_people;
COUNT counts the number of rows that match a condition.
Example: SELECT COUNT(×) FROM tbl_people WHERE Salary > 50000;

To count the number of customers older than 30, you write: SELECT ____(×) FROM Customers WHERE Age > 30;

Worked Example

Given a table 'tbl_animals' with columns: Animal, Breeding, Number of Young.
Task: Display animals that are breeding and have exactly 1 young.
SQL: SELECT Animal FROM tbl_animals WHERE Breeding = 'Yes' AND Number of Young = 1;

Using the table tbl_animals, which SQL statement retrieves all animals that are breeding and have more than 3 young?

SELECT×FROM tbl_animals WHERE Breeding = 'Yes' AND Number of Young > 3;
SELECT×FROM tbl_animals WHERE Breeding = 'Yes' OR Number of Young > 3;
SELECT×FROM tbl_animals WHERE Breeding = 'Yes' AND Number of Young > 3
SELECT Animal FROM tbl_animals WHERE Breeding = 'Yes' AND Number of Young > 3;

Unlock all 40 questions, slides & more

Create a free account to see every question, the slides, flashcards and revision notes for this topic.

Past papers

Past-paper practice for this topic is coming soon.

🗂️ Coming soon