Module 1 · SQL for Data Analysis
Introduction to databases
What a database is, how tables connect, and your first query against a real one.
About 20 minutes
The problem
You've just joined Harbourline Freight, a logistics company in Lagos that moves containers for its customers by sea, air and road. On your first morning, Kemi, the head of operations, asks a simple question:
"Can you pull up our list of routes? I want to see where we ship to."
The answer isn't in a spreadsheet on someone's desktop. It lives in the company's database, and the way you ask a database a question is with SQL.
The concept
A database is an organised store of data that many people and systems can use at once. The kind you'll use in this course is a relational database, which keeps data in tables.
A table looks a lot like a spreadsheet:
- Each column holds one kind of information, such as a company name or a booking date.
- Each row is one record: one customer, one shipment, one payment.
- Every table has a primary key, a column whose value is unique for each row. In
customers, that'scustomer_id.
Tables connect to each other through foreign keys. Every shipment belongs to a customer, so the shipments table has a customer_id column that points back to a row in customers. That's what makes the database relational.
Harbourline's database has five tables:
| Table | One row is… | Key columns |
|---|---|---|
customers | a company that ships with Harbourline | customer_id, company_name, industry, city, account_manager_id |
shipments | one booking to move goods | shipment_id, customer_id, route_id, booking_date, status, containers, freight_charge |
routes | a lane Harbourline operates | route_id, origin, destination, mode, target_transit_days |
payments | money received for a shipment | payment_id, shipment_id, payment_date, amount, method |
employees | a member of staff | employee_id, full_name, role, team, manager_id |
Example
Here is the query that answers Kemi's question. Press Run to try it.
SELECT * FROM routes;Walkthrough
SELECTtells the database you want to read data.*means "every column".FROM routessays which table to read from.- The semicolon
;marks the end of the statement. Many tools don't require it, but it's a good habit.
The result is every row and column of the routes table: 30 routes, from sea lanes like Shanghai to Lagos (Apapa) to road routes like Lagos to Kano.
Practice
Practice
Kemi wants to know who works at Harbourline. Show every column of the employees table.
Ctrl + Enter to run. Tab indents; press Esc then Tab to leave the editor.
Challenge
Challenge · optional
Now show every column of the customers table. Look through the result: which columns connect a customer to another table?
Ctrl + Enter to run. Tab indents; press Esc then Tab to leave the editor.
Check your understanding
Answer every question to check.