← Data Analytics from Scratch: SQL, Spreadsheets and Metrics
Lesson
The relational model and your first SELECT
Learner can describe tables as rows and columns with keys and can write a simple SELECT … FROM to return chosen columns.
Tables, keys and the SELECT statement
Tables, keys and the SELECT statement
A relational database stores data in tables. Each table is a grid: rows (also called records) run horizontally and represent individual items, while columns (also called fields) run vertically and represent attributes. For example, an orders table might have columns order_id, city, and amount, and each row is one order.
Every table has a primary key — one column (or a combination of columns) whose value uniquely identifies each row. No two rows can share the same primary key. In the orders table below, order_id is the primary key.
orders table:
order_id | city | amount
---------|--------|-------
1 | London | 50
2 | Paris | 40
3 | London | 70
4 | Paris | 20
5 | Paris | 30
To retrieve data from a table you write a SELECT statement. The basic form is: SELECT column1, column2 FROM table_name. SQL then returns a result set — a temporary view of the data matching your query; the stored table itself is unchanged. You can also write SELECT * to return every column. Example: SELECT city, amount FROM orders returns only the city and amount columns for all five rows.
Lesson notes
Tables, keys and the SELECT statement
A relational database stores data in tables. Each table is a grid: rows (also called records) run horizontally and represent individual items, while columns (also called fields) run vertically and represent attributes. For example, an orders table might have columns order_id, city, and amount, and each row is one order.
Every table has a primary key — one column (or a combination of columns) whose value uniquely identifies each row. No two rows can share the same primary key. In the orders table below, order_id is the primary key.
orders table:
order_id | city | amount
---------|--------|-------
1 | London | 50
2 | Paris | 40
3 | London | 70
4 | Paris | 20
5 | Paris | 30
To retrieve data from a table you write a SELECT statement. The basic form is: SELECT column1, column2 FROM table_name. SQL then returns a result set — a temporary view of the data matching your query; the stored table itself is unchanged. You can also write SELECT * to return every column. Example: SELECT city, amount FROM orders returns only the city and amount columns for all five rows.