Data Analyst Program

SQL · Week 1 of 4 · 4–5 hours

Database Basics and SQL Syntax

Understand how a database is organized and how a SQL statement is written.

Learning objectives

By the end of this week you will be able to:

  • Explain what a relational database is and why businesses use one.
  • Identify primary keys and foreign keys.
  • Read the parts of a SQL statement.
  • Run your first query in an online editor.

Lessons

Database Basics

A database is an organized collection of data stored so it can be searched and updated quickly and safely. Most business data lives in a relational database, which stores data in tables. A table looks like a spreadsheet: each column is one attribute (name, price, date) and each row is one record (one customer, one order).

Why not just use spreadsheets? Databases handle millions of rows, let many people use the data at the same time, enforce rules (an order date must be a date), and avoid repeating the same information everywhere.

Keys connect the tables:

  • A primary key uniquely identifies each row, for example customer_id in the customers table. No two customers share one.
  • A foreign key is a column that points to another table's primary key. The orders table has a customer_id column that tells you which customer placed each order.

Our course database has three tables:

customers (customer_id, name, city, signup_date)
products  (product_id, product_name, category, price)
orders    (order_id, customer_id, product_id, quantity, order_date)

Instead of copying the customer's name into every order, the order just stores customer_id. This idea, keeping each fact in one place, is called normalization.

SQL Syntax

SQL is a language made of statements. A statement reads almost like an English sentence:

SELECT name, city
FROM customers
WHERE city = 'Chicago'
ORDER BY name;

Rules to remember:

  • Keywords such as SELECT, FROM and WHERE are not case sensitive, but writing them in capitals makes queries easier to read.
  • Text values go in single quotes: 'Chicago'. Numbers do not: price > 20.
  • End each statement with a semicolon.
  • Line breaks and spaces do not matter to the computer, so put each clause on its own line for readability.
  • Comments start with -- and are ignored: -- customers in Chicago.

The order you write the clauses is always: SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY > LIMIT. You will learn each one over the next three weeks.

SQL commands fall into groups. As an analyst you will mostly read data with SELECT. Commands such as CREATE TABLE and INSERT build and fill tables; you will use them once this week to set up your practice database.

Watch

Practice

Open a free online SQL editor. Paste the course setup script sql-course-setup.sql into the editor and run it once. It creates the three tables and fills them with sample rows. Then run:

SELECT * FROM customers;
SELECT * FROM products;
SELECT * FROM orders;

On paper, draw the three tables and connect each foreign key to the primary key it points to.

Check your understanding

Try each question first, then open it to see the answer.

What is the difference between a primary key and a foreign key?

A primary key uniquely identifies rows in its own table; a foreign key refers to a primary key in another table.

Which is correct: WHERE city = Chicago or WHERE city = 'Chicago'?

WHERE city = 'Chicago'. Text values need single quotes.

Put these clauses in order: ORDER BY, FROM, SELECT, WHERE.

SELECT, FROM, WHERE, ORDER BY.

Assignment

Submit a screenshot of your three tables loaded in the editor, and your diagram showing how the tables are connected by keys.

Course setup script

Paste this into your SQL editor and run it once or download the .sql file. It creates the customers, products and orders practice tables.

-- SQL for Data Analysis: practice database (online store)
-- Paste this whole script into SQLiteOnline, DB Fiddle, MySQL or PostgreSQL and run it once.

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(50),
  city        VARCHAR(30),
  signup_date DATE
);

CREATE TABLE products (
  product_id   INT PRIMARY KEY,
  product_name VARCHAR(50),
  category     VARCHAR(30),
  price        DECIMAL(8,2)
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT REFERENCES customers(customer_id),
  product_id  INT REFERENCES products(product_id),
  quantity    INT,
  order_date  DATE
);

INSERT INTO customers VALUES
(1,'Ava Johnson','Chicago','2025-11-03'),
(2,'Ben Carter','Denver','2025-12-14'),
(3,'Chloe Kim','Chicago','2026-01-09'),
(4,'Diego Lopez','Austin','2026-01-22'),
(5,'Emma Brown','Seattle','2026-02-02'),
(6,'Farah Ali','Denver','2026-02-18'),
(7,'Jack Wilson','Austin','2026-03-01'),
(8,'Jasmine Reed',NULL,'2026-03-11'),
(9,'Liam Turner','Seattle','2026-03-25'),
(10,'Mia Patel','Chicago','2026-04-04');

INSERT INTO products VALUES
(101,'Wireless Mouse','Electronics',24.99),
(102,'USB-C Charger','Electronics',19.50),
(103,'Noise-Cancelling Headphones','Electronics',129.00),
(104,'Data Analysis Handbook','Books',38.00),
(105,'Python Pocket Guide','Books',15.99),
(106,'Desk Lamp','Home',32.50),
(107,'Coffee Mug','Home',9.75),
(108,'Notebook Set','Office',12.00),
(109,'Ergonomic Chair','Office',249.00),
(110,'Phone Stand','Electronics',14.25);

INSERT INTO orders VALUES
(1001,1,101,2,'2026-01-05'),
(1002,2,104,1,'2026-01-12'),
(1003,1,107,4,'2026-01-20'),
(1004,3,103,1,'2026-02-03'),
(1005,4,105,2,'2026-02-10'),
(1006,2,102,3,'2026-02-15'),
(1007,5,109,1,'2026-02-27'),
(1008,1,104,1,'2026-03-04'),
(1009,6,106,2,'2026-03-09'),
(1010,3,108,5,'2026-03-14'),
(1011,7,110,1,'2026-03-18'),
(1012,2,103,1,'2026-03-22'),
(1013,4,101,1,'2026-03-30'),
(1014,8,105,3,'2026-04-02'),
(1015,1,102,2,'2026-04-08'),
(1016,5,107,6,'2026-04-15'),
(1017,6,104,2,'2026-04-21'),
(1018,3,109,1,'2026-04-28'),
(1019,2,108,2,'2026-05-03'),
(1020,7,106,1,'2026-05-09');
-- Customers 9 and 10 have no orders on purpose (useful for LEFT JOIN practice).