Module 1: SQL Foundations

Databases and Tables Explained

Most business data, from M-Pesa transaction records to supermarket sales and hospital appointments, lives in relational databases. SQL (Structured Query Language) is how you ask those databases questions. Before you write your first query, you need a clear picture of how the data is organised, because almost every SQL mistake comes from misunderstanding the structure of the tables.

What a relational database is

A relational database stores data in tables. Each table holds one kind of thing: customers, orders, products. A table is made of:

Columns (also called fields): the attributes, such as customer_id, name and city. Each column has a data type, such as integer, decimal, text or date.
Rows (also called records): one row per item, such as one customer.

Software that manages databases is called a database management system. Popular ones include PostgreSQL, MySQL, Microsoft SQL Server, Oracle and SQLite. They all use SQL, with small differences in syntax called dialects. This course uses standard SQL that works in PostgreSQL and, with minor changes noted where relevant, in the others.

Keys and relationships

Tables are connected through keys.

A primary key uniquely identifies each row in a table, for example customer_id in the customers table. No two rows share it, and it cannot be empty.
A foreign key is a column in one table that refers to the primary key of another. The customer_id column in the orders table tells you which customer placed each order.

Splitting data into related tables avoids repetition. Instead of typing the customer's name and phone number on every order, you store them once in the customers table and refer to them by ID. This design approach is called normalisation.

The sample database for this course

Throughout the course you will use a small database for a fictional Nairobi online shop. All figures are hypothetical.

Table	Columns	One row represents
customers	customer_id, name, city, signup_date	A customer
products	product_id, product_name, category, cost_kes	A product
orders	order_id, customer_id, order_date, status, total_kes	An order
order_items	order_id, product_id, quantity, unit_price_kes	One product line within an order

The relationships are: one customer has many orders; one order has many order items; one product appears in many order items. Knowing which table holds which grain (what one row means) is essential. For example, summing total_kes from orders gives revenue; summing quantity * unit_price_kes from order_items should give the same figure if the data is consistent.

Worked example: creating and exploring the tables

You can practise free with SQLite (using a tool such as DB Browser for SQLite) or by installing PostgreSQL. Here is how the customers and orders tables could be created:

CREATE TABLE customers (
    customer_id  INTEGER PRIMARY KEY,
    name         TEXT NOT NULL,
    city         TEXT,
    signup_date  DATE
);

CREATE TABLE orders (
    order_id     INTEGER PRIMARY KEY,
    customer_id  INTEGER REFERENCES customers(customer_id),
    order_date   DATE NOT NULL,
    status       TEXT,
    total_kes    NUMERIC(12,2)
);

INSERT INTO customers VALUES
 (1, 'Achieng Otieno', 'Nairobi', '2025-01-14'),
 (2, 'Brian Kamau', 'Nakuru', '2025-02-03'),
 (3, 'Fatuma Hassan', 'Mombasa', '2025-02-20');

INSERT INTO orders VALUES
 (101, 1, '2025-03-01', 'delivered', 4500.00),
 (102, 1, '2025-03-15', 'delivered', 12800.00),
 (103, 3, '2025-03-18', 'cancelled', 2300.00);

Then look at the data:

SELECT * FROM customers;
SELECT * FROM orders;

Notice that Brian (customer 2) has no orders yet, and that order 103 was cancelled. These details matter later when you count customers or sum revenue. As an analyst you will mostly read data with SELECT, rather than create tables, and in many workplaces you will only have read access. That is good practice: it prevents accidental changes to live data.

Common mistakes to avoid

Not knowing the grain. Before any analysis, state what one row in each table represents.
Assuming IDs are meaningful numbers. Customer IDs are labels. Never add or average them.
Running changes on live data. Statements such as UPDATE, DELETE and DROP change or remove data. Practise on a copy.
Ignoring the dialect. If a query fails, check whether your database uses a different function name or syntax.
Trusting the data blindly. Missing values, duplicates and cancelled records are normal. You will learn to handle them.

Exercise

Install DB Browser for SQLite or PostgreSQL, create the customers and orders tables from this lesson and insert at least five customers and ten orders, including one customer with no orders and one cancelled order. Run SELECT * on each table. Then write one sentence for each table stating what one row represents and which column is its primary key.

Key points

Relational databases store data in tables of rows and typed columns.
Primary keys identify rows uniquely; foreign keys link tables together.
Always know the grain: what one row in each table represents.
Analysts mostly read data with SELECT; practise changes only on copies.