SQL Basics
1. The Foundation Table: Products
We will use this sample table for all SQL examples below:
| ProdID | Name | Category | Price | StockLevel |
|---|---|---|---|---|
| P01 | Laptop | Hardware | 800.00 | 15 |
| P02 | Mouse | Hardware | 25.00 | 50 |
| P03 | Antivirus | Software | 40.00 | 100 |
| P04 | Monitor | Hardware | 150.00 | 5 |
2. Basic Data Retrieval
SELECT and FROM
Retrieves specific columns from a table.
SELECT Name, Price
FROM Products;
FROM Products;
WHERE (Filtering)
Filters records based on a condition.
SELECT *
FROM Products
WHERE Price > 100.00;
FROM Products
WHERE Price > 100.00;
3. Sorting and Multiple Conditions
ORDER BY
Sorts results in ascending (ASC) or descending (DESC) order.
SELECT Name, StockLevel
FROM Products
WHERE Category = 'Hardware'
ORDER BY StockLevel DESC;
FROM Products
WHERE Category = 'Hardware'
ORDER BY StockLevel DESC;
4. SQL Aggregate Functions
These functions perform calculations on a set of values and return a single result.
SUM
Calculates the total of a numeric column.
SELECT SUM(StockLevel)
FROM Products;
FROM Products;
COUNT
Counts the number of records that meet a criteria.
SELECT COUNT(*)
FROM Products
WHERE Price < 50.00;
FROM Products
WHERE Price < 50.00;
5. Combining it All (The Exam Special)
A typical exam question might ask you to find the name and price of Hardware items, sorted by the most expensive first:
SELECT Name, Price
FROM Products
WHERE Category = 'Hardware'
ORDER BY Price DESC;
FROM Products
WHERE Category = 'Hardware'
ORDER BY Price DESC;
⚠️ Exam Note: When writing SQL for IGCSE:
- Use
*to select all columns. - Text values in the
WHEREclause must be in single quotes (e.g.,'Software'). - The order of clauses matters: SELECT ➔ FROM ➔ WHERE ➔ ORDER BY.