SQL · Lesson 1 of 7

Databases and SQL Basics

Tables, rows, keys and how relational databases organize data.

• Beginner
• 10 min read
• 3 objectives

A relational database stores data in tables, like spreadsheets with strict rules. Each row is one record and each column is one attribute with a fixed type. SQL (Structured Query Language) is the standard language for asking these databases questions and changing their data. It works, with small dialect differences, in PostgreSQL, MySQL, SQLite, SQL Server and Oracle.

Why databases exist

A spreadsheet works for a small list, but imagine an online shop with millions of customers, orders and products being read and changed by thousands of people at the same moment. You need something faster, safer and more organised. That is a database, and SQL (Structured Query Language) is the language you use to talk to it. It is over 40 years old and still the most important data skill in software, used by analysts, backend developers, data scientists and product managers alike.

SQL is declarative: you describe what you want ("the names of customers over 30") and the database works out how to find it. That makes simple questions very short to write.

A sample table

Throughout this course we use two tables. customers holds people, and orders holds purchases, each pointing at a customer.

-- customers
-- id | name  | country | age
--  1 | Ada   | UK      | 36
--  2 | Linus | FI      | 28
--  3 | Grace | US      | 45

-- orders
-- id | customer_id | total | status
-- 10 |           1 | 120.0 | paid
-- 11 |           1 |  35.5 | paid
-- 12 |           3 |  80.0 | refunded

Keys

• A primary key uniquely identifies each row (customers.id). It can never be duplicated or null.
• A foreign key is a column that points at another table's primary key (orders.customer_id). This is how tables relate.

Your first query

SELECT name, country
FROM customers;

name   | country
Ada    | UK
Linus  | FI
Grace  | US

A query names what you want (SELECT) and where it lives (FROM). SQL is declarative: you describe the result, and the database engine works out the fastest way to produce it. Keywords are case-insensitive, but writing them in capitals and ending statements with a semicolon is the convention.

Trying it

The easiest way to practice is SQLite, which needs no server: install it, run sqlite3 practice.db and paste the statements. Online playgrounds such as SQLite Fiddle or DB Fiddle work too.

sqlite3 practice.db
sqlite> CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT, age INTEGER);
sqlite> .tables

The vocabulary, with a picture

Think of a table as a spreadsheet tab with strict rules. Every column has a name and a type (text, number, date), and every row is one record. A database is a collection of related tables. A query is a question you ask about them.

• Table: a set of rows about one kind of thing, such as customers.
• Row (record): one customer.
• Column (field): one attribute, such as country.
• Primary key: a column that uniquely identifies each row, like id.
• Foreign key: a column that points at a row in another table, like orders.customer_id.

Ask your first three questions

Every example on this page runs against the same two small tables, so you can follow along. Here is everything in customers:

SELECT * FROM customers

id | name  | country | age
---+-------+---------+----
1  | Ada   | UK      | 36
2  | Linus | FI      | 28
3  | Grace | US      | 45

Now only two columns. The database returns exactly the columns you list, in the order you list them:

SELECT name, country FROM customers

name  | country
------+--------
Ada   | UK
Linus | FI
Grace | US

And a question that needs a decision: which customers are older than 30?

SELECT name, age FROM customers WHERE age > 30

name  | age
------+----
Ada   | 36
Grace | 45

Reading a query in plain English

Read SELECT name, age FROM customers WHERE age > 30 aloud in a different order and it becomes a sentence: "From the customers table, keep the rows where age is over 30, and show me the name and age." SQL is written in a slightly unusual order but read it as that sentence and it always makes sense.

Two tables, one relationship

Orders live in their own table and refer to customers by id instead of repeating the customer's details. This is the core idea of relational databases: store each fact once, and link rows together. A quick look at orders:

SELECT * FROM orders

id | customer_id | total | status
---+-------------+-------+---------
10 | 1           | 120.0 | paid
11 | 1           | 35.5  | paid
12 | 3           | 80.0  | refunded

Customer 1 (Ada) appears twice because she placed two orders, and there is nothing for Linus (id 2) at all. The joins lesson shows how to combine the two tables into one answer.

Key takeaways

• A database holds tables; a table holds rows; each row has the same columns.
• A primary key uniquely identifies a row; a foreign key points at a row in another table.
• SELECT columns FROM table WHERE condition is the shape of most questions.
• SQL is declarative: say what you want, not how to get it.

-- Write your solution here