Databases
Single-table databases: fields, records, data types and primary keys, and using SQL to search, sort and total data.
📌 Key points
- A database is a structured collection of data. A single-table database is made of records (rows, one per item, e.g. one student) and fields (columns, one per piece of data, e.g. Name).
- Each field has a data type: text/alphanumeric, character, Boolean, integer, real or date/time.
- A primary key is a field that uniquely identifies each record, so its values can never be repeated, e.g. StudentID.
- Validation rules can be set on fields (e.g. range, presence, length, format) to reduce data entry errors.
- SQL (Structured Query Language) is used to search a database: SELECT lists the fields to show, FROM names the table, WHERE gives the search condition.
- ORDER BY sorts the results by a field, in ascending order unless DESC is added for descending order.
- AND means both conditions must be true; OR means at least one condition must be true.
- SUM(Field) adds up the values in a numeric field and COUNT(*) counts the records that match the query.
📖 Key terms
- Field
- One item of data stored about every record, shown as a column, e.g. Surname.
- Record
- All the data about one item or person, shown as a row.
- Primary key
- A field that uniquely identifies each record in a table.
- SQL
- Structured Query Language, used to search and manage data in a database.
- Query
- A request to search a database for records that meet given conditions.
- Data type
- Defines the kind of data a field can hold, e.g. integer, real, Boolean.
✏️ Worked example
Table Students has fields ID, Name, Form, Age. Write SQL to show the Name and Age of every student in form 10A, sorted by Name.
- Choose the fields to display: SELECT Name, Age
- Name the table: FROM Students
- Add the condition: WHERE Form = '10A'
- Sort the results: ORDER BY Name;
- Full query: SELECT Name, Age FROM Students WHERE Form = '10A' ORDER BY Name;
Only list the fields the question asks you to display in SELECT. A field can be used in WHERE or ORDER BY without being shown, and adding extra fields can lose the mark.
Test yourself8 questions from a bank of 14. A new mix every time.
Start the quiz →