Databases and SQL

Tables, records, and fields

A database is a structured way to store data so it can be searched, sorted, and updated easily. A single-table database stores everything in one table.

Each row is a record: all the data about one thing (here, one pet). Each column is a field: one piece of data stored for every record (here, the pet's name, type, and so on).

Data types for fields

Each field is given a data type, which also works as validation:

Primary keys

A primary key is a field that holds a unique value for every record, so each record can be identified. Names are not good keys because two pets could share a name; PetID is unique.

SELECT, FROM, WHERE, ORDER BY

SQL (Structured Query Language) is used to search a database. A query names the fields to show (SELECT), the table (FROM), the condition records must meet (WHERE), and how to sort the results (ORDER BY ... ASC or DESC).

In the diagram, the faded rows fail the WHERE condition and the highlighted columns are the ones selected.

SELECT Name, Age
FROM Pets
WHERE Type = 'Dog'
ORDER BY Age DESC;

Combining conditions

Use AND and OR to build more specific conditions, and comparison operators such as <, >, and <> (not equal).

SELECT Name, Owner
FROM Pets
WHERE Age < 4 AND Type <> 'Dog';

SUM and COUNT

COUNT returns how many records match. SUM adds up the values in a numeric field for the matching records.

SELECT COUNT(PetID)
FROM Pets
WHERE Type = 'Cat';
-- Result: 2

SELECT SUM(Age)
FROM Pets
WHERE Owner = 'Chen';
-- Result: 9 (Milo 5 + Rocky 4)

Key terms

Record

A row in a table: all the data about one item.

Field

A column in a table: one category of data.

Primary key

A field that uniquely identifies each record.

SQL

Structured Query Language, used to query databases.

Check yourself