What a Database Is and Your First SELECT
Meet tables, rows, columns and keys through the Fernleaf sample shop database, then run your first queries for real.
In this lesson you will
- Explain tables, rows, columns and keys
- Create the sample database
- Run a first SELECT and a row count
- Know what NULL means
About 25 minutes
Maya has a shop and a question
Maya runs Fernleaf, a small online plant shop. Her sales live in a spreadsheet that has grown into a tangle: one sheet for customers, another for orders, a third for products, and a growing pile of formulas that join them by hand. One morning she asks a simple question: ‘Which customers have never bought anything?’ Answering it takes her an hour of scrolling. Her cousin Dev, who works with data, says: ‘In a database that is one line.’ This course follows Maya as she learns SQL, the language for asking questions of data in a database. All the data in it is made up, in sample money.
What a database is
A database is an organised store of data that many programs and people can use at once. Most databases that you will meet are relational: the data lives in tables, and tables are linked by shared values. A table is like a sheet in a spreadsheet, with strict rules. Each column has a name and a kind of data, such as text, whole numbers or dates. Each row is one record, such as one customer or one product. Maya’s database has four tables: customers (6 rows), products (8 rows), orders and order items. Splitting the data this way means each fact lives in one place. A customer’s city is stored once, not repeated on every order.
Keys connect the tables
Each table has a primary key, a column whose value is different for every row, such as the customer id. Another table can store that value to point at the row. In the orders table, the column customer_id holds a customer’s id. It is called a foreign key, because it refers to a key in another table. This is how SQL later joins an order to the customer who placed it, a topic for lesson 8.
SQL in one sentence
SQL, usually said as ‘ess-queue-ell’ or ‘sequel’, is a language of statements. A statement such as SELECT * FROM customers reads: choose all columns from the customers table. You describe what you want, not how to find it, and the database works out the steps. SQL has been used for decades and is a core skill for data and software work. Keywords such as SELECT and FROM are not case sensitive, but writing them in capitals is a common habit that makes queries easier to read.
The Fernleaf sample database
Every lesson in this course uses the same small database. The script below creates it. You can run it in any SQL tool that accepts standard statements. The examples in these lessons were run for real in SQLite, a small free database engine that is included with many tools and with Python. Other systems, such as MySQL, PostgreSQL and SQL Server, share the same core, but small details differ, and the lessons point out the places where that matters. For anything beyond the lessons, check your own database’s documentation.
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT, joined TEXT);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL, stock INTEGER);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, order_date TEXT, status TEXT);
CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, qty INTEGER);
INSERT INTO customers VALUES
(1, 'Asha', 'Leeds', '2025-03-14'), (2, 'Ben', 'York', '2025-06-02'),
(3, 'Chloe', 'Leeds', '2025-09-21'), (4, 'Dev', 'Bath', '2026-01-10'),
(5, 'Ella', 'York', '2026-02-18'), (6, 'Femi', NULL, '2026-05-30');
INSERT INTO products VALUES
(1, 'Fern', 'plant', 12.50, 14), (2, 'Cactus', 'plant', 6.00, 30),
(3, 'Orchid', 'plant', 18.00, 5), (4, 'Clay pot', 'pot', 8.50, 22),
(5, 'Glass pot', 'pot', 15.00, 0), (6, 'Plant food', 'care', 5.50, 40),
(7, 'Mist spray', 'care', 4.00, NULL), (8, 'Succulent mix', 'plant', 9.00, 12);
INSERT INTO orders VALUES
(1, 1, '2026-06-03', 'shipped'), (2, 2, '2026-06-10', 'shipped'),
(3, 1, '2026-07-01', 'shipped'), (4, 3, '2026-07-15', 'cancelled'),
(5, 4, '2026-08-02', 'shipped'), (6, 2, '2026-08-20', 'shipped'),
(7, 5, '2026-09-05', 'pending'), (8, 1, '2026-09-18', 'shipped'),
(9, 4, '2026-10-02', 'shipped'), (10, 3, '2026-10-20', 'pending');
INSERT INTO order_items VALUES
(1, 1, 2), (1, 4, 2), (2, 2, 3), (2, 6, 1), (3, 3, 1), (3, 5, 1),
(4, 1, 1), (5, 8, 2), (5, 4, 1), (6, 1, 1), (6, 6, 2), (6, 7, 1),
(7, 2, 4), (8, 3, 2), (8, 6, 1), (9, 1, 3), (9, 4, 3), (10, 8, 1), (10, 5, 1);
Notice the word NULL in a few places. It means ‘no value is stored’. Femi has no city, and the mist spray has no stock figure. Lesson 4 is entirely about NULL, because it behaves in ways that surprise everyone at first. Notice too that prices use the REAL type here for simplicity. A real shop should keep money in exact decimal columns, or as whole pence, because floating-point numbers can show tiny errors.
Your first queries
Below are three queries, run for real. The first lists every customer. The star after SELECT means ‘all columns’. The second lists the products. The third counts the rows in each table by stacking four small queries with UNION ALL. The lines starting with two dashes are comments, which the database ignores.
-- 1
SELECT * FROM customers
-- 2
SELECT * FROM products
-- 3
SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
-- 1
id | name | city | joined
1 | Asha | Leeds | 2025-03-14
2 | Ben | York | 2025-06-02
3 | Chloe | Leeds | 2025-09-21
4 | Dev | Bath | 2026-01-10
5 | Ella | York | 2026-02-18
6 | Femi | NULL | 2026-05-30
-- 2
id | name | category | price | stock
1 | Fern | plant | 12.50 | 14
2 | Cactus | plant | 6.00 | 30
3 | Orchid | plant | 18.00 | 5
4 | Clay pot | pot | 8.50 | 22
5 | Glass pot | pot | 15.00 | 0
6 | Plant food | care | 5.50 | 40
7 | Mist spray | care | 4.00 | NULL
8 | Succulent mix | plant | 9.00 | 12
-- 3
table_name | row_count
customers | 6
products | 8
orders | 10
order_items | 19
The customers query returned 6 rows, one for each customer, with their id, name, city and joining date. Femi’s city shows as NULL. The products query returned 8 rows. The last result confirms what the script created: 6 customers, 8 products, 10 orders and 19 order items. Order items is a table of its own because one order can contain several products, so each order line is a row there.
Habits to start with
- Look at a table with a small query before you ask it harder questions.
- Read the column names. They tell you what each value means.
- Run queries on a copy or a sample database while you are learning, never on live data you cannot replace.
- Keep your queries in a text file, so you can run them again.
Thinking a table is a spreadsheet with no rules. A table has fixed columns and kinds of data, and one fact belongs in one place. Repeating the same fact in many rows is how data goes out of step.
The shop, people and figures are all made up, and the prices are in sample money. SQL examples were run in SQLite. Other databases accept the same core statements but differ in details, so check your system’s documentation.
Practice for this lesson
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What is a row in a table?
- A column name
- One record, such as one customer
- A whole database
- A type of key
Each row is one record; each column is one kind of data.
What does a primary key do?
- Hides a column
- Sorts the table
- Deletes old rows
- Gives each row a value that no other row has
A primary key identifies a row, such as the customer id.
What is a foreign key?
- A column that points at a row in another table
- A password
- A column with no values
- A copy of the table
The customer_id in orders points to a row in customers.
What does SELECT * FROM customers ask for?
- Only the first column
- The number of tables
- All columns of every row in customers
- One customer
The star means all columns.
What does NULL mean in a column?
- The number zero
- An empty sentence
- An error
- No value is stored
Femi has no city stored, so it shows as NULL.
Summary
- Relational data lives in tables linked by keys.
- SQL describes what you want, and the database finds it.
- SELECT * FROM table shows every column of every row.
- NULL means no value is stored.
Questions
Which database should I use to practise?
Any that accepts standard SQL. The lessons ran in SQLite, which is free and small. Many free online playgrounds also let you try SQL without installing anything.
Is SQL the same in every database?
The core is shared, so most of this course works everywhere. Details such as date functions and row limits differ, and the lessons point these out.
Is the sample data real?
No. The shop, customers, orders and prices are all made up for teaching.
