Theory Notes/🗄️ Topic 4: Data & Information Management, Solution Development/10.4.11

10.4.11Grade 10

Single-Table Database & SQL

Your first real SQL — creating a table and writing queries to select, filter, and modify data in a single table.

Creating a single-table database, choosing appropriate field types and settings:

Field types — text/string, integer, real/decimal, date, Boolean, matched appropriately to what the field stores.
Default values — a value automatically used if none is provided.
Autonumber primary key — a field that automatically increments a unique value for each new record, commonly used as the primary key.
NOT NULL — a constraint ensuring a field can never be left empty.
Indexed fields — fields marked for faster searching/sorting by the database engine.

Basic SELECT queries:

Clause	Purpose	Example
SELECT … FROM	Choose which fields to retrieve, from which table	SELECT Name, Grade FROM Learners
WHERE	Filter which records to include	SELECT * FROM Learners WHERE Grade = 11
DISTINCT	Remove duplicate values from the result	SELECT DISTINCT Grade FROM Learners
LIMIT / TOP	Restrict the number of rows returned	SELECT * FROM Learners LIMIT 5
ORDER BY ASC/DESC	Sort the results	SELECT * FROM Learners ORDER BY Name ASC

Combining conditions with logical operators in WHERE (from 10.4.4): NOT, AND, OR, and IN (checks if a value matches any in a given list).

Example

SELECT * FROM Learners WHERE Grade IN (10, 11);

Special operators:

Operator	Purpose	Example
BETWEEN	Value falls within a range (inclusive)	WHERE Age BETWEEN 15 AND 18
LIKE	Pattern matching using wildcards (% for any characters, _ for a single character)	WHERE Name LIKE 'A%' — starts with A
IS NULL	Checks if a field has no value	WHERE Email IS NULL

Altering data — modifying the actual records in a table:

Statement	Purpose	Example
INSERT	Add a new record	INSERT INTO Learners (Name, Grade) VALUES ('Sipho', 12);
UPDATE … WHERE	Modify existing records that match a condition	UPDATE Learners SET Grade = 12 WHERE Name = 'Sipho';
DELETE … WHERE	Remove records that match a condition	DELETE FROM Learners WHERE Grade = 10;

💡 Exam Tip

Always include a WHERE clause with UPDATE and DELETE unless you genuinely intend to affect every single record in the table — a missing WHERE clause is one of the most common and costly real-world SQL mistakes.