Database Concepts

A worked example: a students table

In this table:

• The fields (columns) are StudentID, FirstName, LastName, MarkSubmitted, Percentage. There are 5 fields.
• Each row is a record for one student. There are 6 records.
• The primary key is StudentID because every student has a unique seven-digit ID.

3. Data types for database fields

When a table is created, each field is given a data type that says what kind of value it can hold. Choosing the right type makes the data smaller, faster and safer.

Choosing the right type

For each field, ask three short questions in order:

1. Does it ever need decimals? Yes → Real. No → keep going.
2. Is it a count or whole-number ID? Yes → Integer (an ID that contains letters, such as AB123, is text). No → keep going.
3. Is it just true or false? Yes → Boolean. Is it a date or timestamp? Yes → Date/Time. Is it exactly one character (a code or flag)? Yes → Character. Otherwise → Text / Alphanumeric.

Two common slips to avoid. Phone numbers and postcodes are stored as text, not integers: they may start with a leading zero, contain spaces or letters, and never have arithmetic done on them. Calendar dates are stored as a Date type, not as text: only a true date field can sort chronologically and reject impossible values like 2024-02-30.

Why the right data type matters

• Memory efficiency: an integer takes less space than the same number stored as text.
• Correct comparisons: > and < compare numbers by value but text character by character, so as text "10" comes before "9".
• Validation: a date field rejects a value like "purple" automatically.
• Sorting: a properly-typed date field sorts chronologically; the same date as text would sort alphabetically.

4. Validation at the database level

When a field is defined, validation rules can be attached so that bad data is rejected before it ever reaches the table.

Validation at the database level is a safety net: even if the application code forgets to check, the database itself will reject bad data.

5. SQL: the language for querying databases

SQL (Structured Query Language) is the standard language used to ask questions of a database and to update its contents.

Almost every real database system understands SQL: MySQL, PostgreSQL, Microsoft SQL Server, SQLite, Oracle and many others. The dialects differ slightly, but the core syntax is shared.

The focus is on just enough SQL to retrieve records from a single table using the SELECT statement.

6. The SELECT statement

A typical SQL query has up to three clauses:

SELECT  field1, field2, ...
FROM    TableName
WHERE   condition;

Each line has a specific job:

The semicolon at the end is the SQL "end of statement" marker.

SELECT every field

To return every column for every record, use the wildcard * after SELECT:

SELECT *
FROM Customers;

This returns the whole table.

SELECT specific fields

To return only certain columns, list them by name:

SELECT FirstName, LastName, Age
FROM Customers;

Only those three columns appear in the result, even if the table has many more.

SELECT with a WHERE filter

To return only certain rows, add a WHERE clause with a condition:

SELECT *
FROM Customers
WHERE Age > 25;

Only customers older than 25 are returned.

A customers table to practise on

Consider this Customers table:

Query 1: SELECT * FROM Customers; returns all five fields for all four records (the whole table).

Query 2: SELECT ID, Name, Age FROM Customers WHERE Age > 25;

(Liam O'Brien is excluded because his age is exactly 25, while the query asked for strictly greater than 25. Priya Gupta is excluded because 19 < 25.)

7. WHERE clauses: comparison operators

Conditions inside WHERE use comparison operators, much like the ones in topic 21.

Text values are wrapped in quotes, single or double ('London', "London"), as the exam papers use both; numbers are written without quotes (30, 40000).

Common slip: writing WHERE City = London (without quotes) makes the database look for a column called London, which does not exist, and the query fails. Always quote text values.

8. Combining conditions: AND and OR

When a WHERE clause needs to test more than one condition, AND and OR combine them, exactly like in topic 21.

AND: every condition must be true

SELECT *
FROM Customers
WHERE Age > 18
  AND City = 'New York';

Returns only customers who are both older than 18 and living in New York.

OR: at least one condition must be true

SELECT *
FROM Customers
WHERE City = 'London'
   OR City = 'Paris';

Returns customers living in either London or Paris.

Multiple combined conditions

SELECT *
FROM Customers
WHERE (Age > 30 AND Country = 'USA')
   OR Country = 'France';

Returns customers who are either over-30 USA residents, or any customer from France. Brackets make the meaning unambiguous.

9. LIKE and the % wildcard

A WHERE condition often needs to match a pattern in text rather than an exact value. SQL uses the LIKE keyword with the % wildcard, which stands for "any sequence of characters" (including no characters at all).

Pattern: countries starting with C

SELECT Name, Country
FROM Customers
WHERE Country LIKE 'C%';

For the table from section 6, this returns:

The other three records (Ireland, Japan, India) are excluded because their country names do not start with C.

Pattern: names containing "as"

SELECT Name
FROM Customers
WHERE Name LIKE '%as%';

Returns Maya Hassan. The % on both sides means "as" can appear anywhere in the name.

10. Determining the records output by a query

A common exam question gives a table and a SELECT query and asks which records the query returns. The approach:

1. Look at the FROM clause to find the table.
2. Look at the WHERE clause and identify the condition each record must satisfy.
3. Check each record in the table against the condition.
4. Keep only the records where every condition is true.
5. Look at the SELECT clause and write down only the columns asked for.

Walkthrough: a Dublin or Osaka query

Using the Customers table from section 6:

SELECT *
FROM Customers
WHERE City = 'Dublin' OR City = 'Osaka';

Step by step:

• Maya Hassan: City = 'Toronto' → does not match either → excluded.
• Liam O'Brien: City = 'Dublin' → matches → included.
• Hiroshi Tanaka: City = 'Osaka' → matches → included.
• Priya Gupta: City = 'Mumbai' → does not match either → excluded.

Result:

Walkthrough: an animal breeding query

Consider the tbl_zoo table:

A keeper wants the names of every species that is held in captivity and eats more than 10 kg of food a day:

SELECT Species
FROM tbl_zoo
WHERE InCaptivity = 'Yes'
  AND DailyFeedKg > 10;

Working through each record against both conditions:

• Giraffe: in captivity and 35 > 10 → both true → included.
• Cheetah: in captivity but 3 is not > 10 → excluded.
• Tortoise: in captivity but 2 is not > 10 → excluded.
• Anaconda: not in captivity → excluded.
• Macaw, Pelican, Komodo Dragon: in captivity but their feed amounts (1, 2, 4) are not > 10 → excluded.
• Bison: in captivity and 25 > 10 → included.
• Tarantula: not in captivity → excluded.
• Gorilla: in captivity and 20 > 10 → included.

Result:

11. Sorting results with ORDER BY

ORDER BY sorts the records in the result by a chosen field, either ascending or descending.

ORDER BY is added after the WHERE clause (or after FROM if there is no WHERE). The direction is set with ASC (ascending: A→Z, smallest first) or DESC (descending: Z→A, largest first). If neither is written, the default is ascending.

SELECT Species, DailyFeedKg
FROM tbl_zoo
WHERE InCaptivity = 'Yes'
ORDER BY DailyFeedKg DESC;

This lists the captive species from the largest daily feed to the smallest, so Giraffe (35) appears first, then Bison (25), then Gorilla (20), and so on down to Macaw (1).

You can sort on text too. ORDER BY Species ASC would list the species in alphabetical order.

12. Summarising data with SUM and COUNT

Two functions calculate a single value from a whole column of results rather than listing the rows.

SUM

SUM(field) adds up all the values in a numeric field across the records the query selects.

SELECT SUM(DailyFeedKg)
FROM tbl_zoo
WHERE InCaptivity = 'Yes';

This adds the daily feed for every captive species (35 + 3 + 2 + 1 + 25 + 2 + 20 + 4 = 92), so the query returns a single value: 92.

COUNT

COUNT(field) returns how many records the query selects, rather than the records themselves.

SELECT COUNT(Species)
FROM tbl_zoo
WHERE InCaptivity = 'No';

Two species are not in captivity (Anaconda and Tarantula), so this returns 2. Using COUNT(*) counts every selected record in the same way.