SQL Database Basics: Tables, Rows, Columns and Keys

Before writing more queries, it helps to have a clear picture of where your data lives. This lesson explains how a relational database is organised, and introduces the sample database used throughout this course.

The building blocks#

A relational database is organised in layers, a bit like folders and files:

A database server (MySQL, PostgreSQL, SQL Server…) can hold many databases. A shop might have one database containing customers, orders, and products tables.

Every column has a data type#

A column does not accept just anything. When a table is created, each column gets a data type that says what kind of value it holds:

• INTEGER for whole numbers, such as salary or stock
• DECIMAL(10, 2) for exact numbers with decimals, such as prices
• VARCHAR(100) for text up to a length, such as name
• DATE for calendar dates, such as hire_date

Data types protect your data. A database will refuse to store the text 'hello' in an INTEGER column (SQLite is a notable exception that is more relaxed). You will learn them properly in Data Types.

Primary keys: telling rows apart#

Two customers can share the same name, so names cannot reliably identify a row. Instead, almost every table has a primary key: a column whose value is unique and never empty for every row.

Here customer_id is the primary key. If a second customer called Asha signs up, she gets id 5, and the database can still tell them apart.

Foreign keys: connecting tables#

The orders table stores which customer placed each order, but not the customer's name or city. It stores only the customer's id:

orders.customer_id is a foreign key: a column that points to the primary key of another table. Orders 101 and 102 belong to customer 1, Asha.

This is the "relational" in relational database. Each fact is stored once (Asha's city lives only in customers), and tables are linked by ids. When you need the pieces together, you use a JOIN.

Relationships between tables#

One-to-many is by far the most common, and it is always implemented the same way: the "many" side holds a foreign key to the "one" side.

The sample database for this course#

Most lessons use these tables. You do not need to memorise them; each lesson shows the data it uses.

employees

manager_id is a foreign key that points back into the same table: Asha's manager is employee 5, Priya. NULL means "no value"; Meera and Priya have no manager listed.

Together with customers and orders above, plus a products table you will meet later, that is everything you need.

Schema: the shape of the data#

The schema is the design of the database: which tables exist, their columns, data types, and keys. The data inside changes every second; the schema changes rarely and deliberately. In SQL, the schema is defined with statements like CREATE TABLE, and the data is read and changed with SELECT, INSERT, UPDATE and DELETE.

Common mistakes#

Practice#

Practice exercise

Using the sample tables above, answer these questions without writing any SQL:

1. Which column is the primary key of orders?
2. Which column in orders is a foreign key, and which table does it point to?
3. Who is Ravi's manager?
4. What kind of relationship is there between customers and orders?

Show hint

Primary keys uniquely identify rows in their own table. Foreign keys hold the id of a row in another (or the same) table.

Show solution

1. order_id: every order has a unique one.
2. customer_id, which points to customers.customer_id.
3. Ravi's manager_id is 3, so his manager is Meera.
4. One-to-many: one customer can have many orders (Asha has two), but each order belongs to one customer.

Key takeaway#

Next, learn the rules for writing statements in SQL Syntax.