Related Tables and Keys
In a relational database, data is stored in several related tables. Each table has a primary key that uniquely identifies every record. A foreign key is a field in one table that refers to the primary key of another table, and this is what links the tables together.
Key idea
Data integrity means the data stays accurate, consistent and valid. Referential integrity ensures every foreign key really matches an existing primary key in another table.
Advanced SQL
The SELECT statement retrieves data. Some advanced features include:
- JOIN — combines rows from two or more tables based on a related field.
- ORDER BY — sorts the results in ascending (ASC) or descending (DESC) order.
- Aggregate functions — such as
COUNT(number),SUM(total),AVG(average),MAXandMIN. - GROUP BY — groups rows that share the same value for a summary.
Example
To list student names together with their class names from the student and class tables:
SELECT student.name, class.class_name FROM student JOIN class ON student.class_id = class.class_id ORDER BY student.name ASC;
To count the number of students in each class:
SELECT class_id, COUNT(*) AS total FROM student GROUP BY class_id;
Remember
JOIN links tables through keys, ORDER BY sorts the results, and aggregate functions summarise data. Primary keys and foreign keys protect the integrity of the relationships between tables.