Use one or more criteria to select a subset of records from a database, manipulate the results (sorting, calculations, searching) and present the data in a clear, professional report.
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(30) NOT NULL,
Age TINYINT,
Gender CHAR(1) CHECK (Gender IN ('M','F')),
Score DECIMAL(4,1),
CourseID INT
);
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(20) NOT NULL
);
ALTER TABLE Students
ADD CONSTRAINT FKStudentCourse
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID);
| Data type | Typical use | Range / storage |
|---|---|---|
| INT | Whole numbers, IDs | -2 147 483 648 to 2 147 483 647 (4 bytes) |
| TINYINT | Small whole numbers (e.g., ages) | 0 to 255 (1 byte) |
| DECIMAL(p,s) | Exact numeric values (e.g., marks) | p total digits, s after decimal (storage varies) |
| CHAR(n) | Fixed‑length text (e.g., gender code) | n bytes, always n characters |
| VARCHAR(n) | Variable‑length text (e.g., names) | 1–n+1 bytes, only actual characters stored |
Name VARCHAR(30) – most surnames & first names fit within 30 characters; using VARCHAR saves space.Gender CHAR(1) – only one character needed, fixed length simplifies checks.Score DECIMAL(4,1) – allows values up to 999.9; more than sufficient for a 0‑100 mark system.A simple data‑entry form would display fields for Name, Age, Gender, Score and a drop‑down list of Course (populated from the Courses table). The StudentID can be auto‑generated (e.g., AUTO_INCREMENT) so the user never edits it.
A query requests information from one or more tables. In SQL the most common form is the SELECT statement.
SELECT column1, column2, …
FROM table_name
WHERE condition
ORDER BY column [ASC|DESC];
Tests only one field.
SELECT Name, Score
FROM Students
WHERE Score > 80;
Combine two or more tests with logical operators. Use parentheses to control precedence.
SELECT Name, Age, CourseName
FROM Students s
JOIN Courses c ON s.CourseID = c.CourseID
WHERE (Gender = 'F' AND Age >= 17)
AND (CourseName = 'Mathematics' OR Score >= 90);
AND – both conditions must be true.OR – either condition may be true.NOT – reverses the truth value.=, <>, !=, >, <, >=, <=% (any number of characters) and _ (single character).-- Names that start with “A”
SELECT Name FROM Students WHERE Name LIKE 'A%';
-- Scores between 70 and 85 (inclusive)
SELECT Name, Score FROM Students WHERE Score BETWEEN 70 AND 85;
-- Students in Science or History
SELECT Name FROM Students WHERE CourseID IN (2,3);
SELECT Name, Age, Score
FROM Students
ORDER BY Age ASC, Score DESC; -- youngest first, highest score within each age
SELECT Name,
Score,
Score * 1.10 AS AdjustedScore -- 10 % bonus added
FROM Students;
Most DBMS provide an Import Wizard. In MySQL you can also use:
LOAD DATA INFILE 'students.csv'
INTO TABLE Students
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- skip header line
SELECT *
INTO OUTFILE 'students.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
.xlsx for editing, then export to .pdf for submission.| StudentID | Name | Age | Gender | Score | CourseID |
|---|---|---|---|---|---|
| 101 | Alice | 17 | F | 84 | 1 |
| 102 | Bob | 18 | M | 76 | 2 |
| 103 | Clara | 17 | F | 91 | 1 |
| 104 | David | 19 | M | 68 | 3 |
| 105 | Eve | 18 | F | 73 | 2 |
| CourseID | CourseName |
|---|---|
| 1 | Mathematics |
| 2 | Science |
| 3 | History |
SELECT Name, Score
FROM Students
WHERE Score > 80;
SELECT s.Name, s.Age, c.CourseName
FROM Students s
JOIN Courses c ON s.CourseID = c.CourseID
WHERE s.Gender = 'F'
AND s.Age BETWEEN 17 AND 18
AND c.CourseName = 'Mathematics';
SELECT Name, Score
FROM Students
WHERE Score BETWEEN 70 AND 85;
SELECT Name
FROM Students
WHERE Name LIKE 'A%';
SELECT Name
FROM Students
WHERE Name LIKE '_a%';
SELECT s.Name, s.Score, c.CourseName
FROM Students s
JOIN Courses c ON s.CourseID = c.CourseID
WHERE s.Score > 90 OR c.CourseName = 'History';
SELECT Name, Age, Score
FROM Students
ORDER BY Age ASC, Score DESC;
SELECT clause with the chosen columns.FROM clause (include JOIN if more than one table).WHERE clause using comparison, logical, LIKE, BETWEEN, IN, NOT etc.ORDER BY, calculated fields, or LIMIT.| # | Task | AO Target |
|---|---|---|
| 1 | List StudentID and Name of all male students who scored less than 70. | AO1 – recall syntax; AO2 – apply AND with numeric and text criteria. |
| 2 | Retrieve the names of students whose course is either “Science” or “History”. | AO2 – use IN or OR with a foreign‑key field. |
| 3 | Find all students whose names contain the letter “e” (any position) and whose age is greater than 17. | AO2 – combine LIKE with AND. |
| 4 | Show the Name and Score for students whose scores are not between 75 and 85. | AO2 – use NOT BETWEEN. |
| 5 | List the distinct courses offered in the Students table. | AO2 – use DISTINCT (or SELECT DISTINCT CourseName FROM Courses). |
| 6 | Produce a report showing Name, Age, Score sorted by Score descending, then export it as a CSV file. | AO3 – present data; AO2 – apply ORDER BY and export command. |
WHERE Gender = 'F').AND when OR is required – often leads to an empty result set.LIKE without a wildcard – it behaves exactly like =.BETWEEN excludes the end values – it is inclusive.JOIN Courses to get CourseName).SELECT … FROM … WHERE … to retrieve exactly the rows needed.AND, OR, NOT, and parentheses for precedence.LIKE, BETWEEN, IN – to handle text patterns, ranges, and lists.Your generous donation helps us continue providing free Cambridge IGCSE & A-Level resources, past papers, syllabus notes, revision questions, and high-quality online tutoring to students across Kenya.