Relational databases and SQL · GCSE Computer Science

Databases

A database is an organised collection of data. A relational database stores its data in several linked tables.
Each table holds data about one kind of thing, such as students. Each row is a record, and each column is a field.
A primary key is a field that uniquely identifies each record, such as StudentID. A foreign key is a field in one table that is the primary key of another table, which links the two tables.

Each piece of data is stored only once. This avoids data redundancy (the same data repeated) and data inconsistency (the same data stored differently in different places). A change only needs making in one place.

SELECT Name, Age FROM Students WHERE Age > 15 ORDER BY Name ASC chooses the fields, the table, the condition and the order. ASC means A to Z (smallest first) and DESC means Z to A (largest first).
SELECT * returns every field. Conditions can use =, <>, <, >, <=, >=, AND and OR. You may also see LIKE with a wildcard: WHERE Name LIKE 'A%' finds names beginning with A.
To use two tables, link them in the WHERE clause: SELECT Students.Name, Forms.Room FROM Students, Forms WHERE Students.FormID = Forms.FormID.

INSERT INTO Students (StudentID, Name, Age) VALUES (7, 'Ali', 15) adds a record.
UPDATE Students SET Age = 16 WHERE StudentID = 7 changes a record.
DELETE FROM Students WHERE StudentID = 7 removes a record. Always include WHERE, or every record in the table is changed or deleted.

Database: An organised collection of data.
Relational database: A database that stores data in linked tables.
Record: One row of a table, holding the data about one item.
Field: One column of a table, holding one piece of data about each item.
Primary key: A field that uniquely identifies each record in a table.
Foreign key: A field that is the primary key of another table, linking the two tables.
Data redundancy: The same data being stored more than once.
Data inconsistency: The same data being stored differently in different places.
SQL: Structured query language, used to search and change databases.