Meet the company database
Before you can ask good questions, you need to know what data you have. Throughout this course every query runs against the same small but realistic company database. It models a business with three concerns: the teams it is organised into, the people it employs, and the projects it runs.
departments— the organisational units: Engineering, Marketing, Sales, Support, and Research.employees— the people, with their roles, salaries, and hire dates.projects— the initiatives each department runs, with a status and a budget.
The data is deliberately small so results fit on a screen, but it has real shape: relationships to follow, duplicates to count, and a few missing values that trip up careless queries.
flowchart LR D["departments<br/>id, name, city"] -->|"department_id"| E["employees<br/>id, name, role, salary"] D -->|"department_id"| P["projects<br/>id, name, status, budget"] style D fill:#0e7490,color:#fff
What each table holds
A table's columns tell you what you can ask about. employees has name, role, salary, hired_on, and a department_id that points at its team. projects has name, status, budget, and its own department_id. departments is the smallest — just name and city.
Notice that name appears in every table. It is not one shared column; each table has its own independent name. What links the tables is not a shared column but a key: the department_id foreign key in employees and projects that references departments.id. You will follow those keys with joins later. For now, treat each table as a self-contained list of facts.
SELECT id, name, role, salary FROM employees LIMIT 5;
Two ways to explore: peek and count
When you meet an unfamiliar table, two questions come first: what does the data look like, and how much of it is there? Peeking means reading a few rows to see the values and their shape. Counting means measuring the size of the table without listing every row.
SELECT * FROM employees LIMIT 5 shows five complete rows — the fastest way to learn what columns exist and what kind of values live in them. SELECT COUNT(*) FROM employees returns a single number, the row count, which tells you the scale of the data at a glance. Use peek when you need to understand the content; use count when you only need the size.
flowchart LR P["Peek<br/>SELECT * ... LIMIT 5"] --> C["Count<br/>COUNT(*)"] C --> D["Distinct values<br/>SELECT DISTINCT"] D --> U["Understand<br/>follow the keys"] style P fill:#0e7490,color:#fff style U fill:#0e7490,color:#fff
SELECT COUNT(*) AS employee_count FROM employees;
Seeing the distinct values
Sometimes you do not want every row — you want the set of different values a column holds. SELECT DISTINCT role FROM employees lists each role once, no matter how many people share it. It answers "what kinds of thing are in here?" rather than "show me everything."
DISTINCT is how you discover the categories in your data: the statuses a project can have, the roles people hold, the cities departments sit in. It collapses repeats into a clean list, which is often the first step before you group or filter.
SELECT DISTINCT role FROM employees;
Real data has gaps
The seed data is realistic, which means it is imperfect. One employee, Omar, has no department_id — he is a contractor who does not belong to a team. The Research department has no city recorded. These NULLs are not mistakes in the lesson; they are exactly the kind of gaps every real database contains.
A query that ignores NULLs can quietly return the wrong answer, because NULL does not behave like an ordinary value. You will meet that trap formally soon. For now, just notice where the gaps are, so they do not surprise you when a count comes up short or a join drops a row.
Following the keys
Each row in employees and projects carries a department_id — a pointer to the departments table. Reading a single table shows you one slice of the business; following those pointers shows you how the slices connect.
That connection is what the rest of the course builds on. A join reads across the pointer to combine an employee with their department in one row. An aggregate counts or sums rows that share the same pointer. Once you can see the keys, you can see the shape of every question the database can answer — and the tour is over.
How many departments are there? Return the count with the alias department_count.
SELECT COUNT(*) AS department_count FROM departments ;
Use
COUNT(*)to count all rows.The table you need is
departments.
SELECT COUNT(*) AS department_count FROM departments;
How many projects have a status of 'active'? Alias the result as active_projects.
SELECT COUNT(*) AS active_projects FROM projects -- add your filter here ;
Filter with
WHERE status = 'active'.Wrap the text
'active'in single quotes.
SELECT COUNT(*) AS active_projects FROM projects WHERE status = 'active';
Return the name and budget of every project with a budget greater than 500000, sorted by budget from highest to lowest.
SELECT name, budget FROM projects -- filter and sort here ;
Filter with
WHERE budget > 500000.Highest first means
ORDER BY budget DESC.
SELECT name, budget FROM projects WHERE budget > 500000 ORDER BY budget DESC;
What are the distinct project statuses? Return each status value once, with no duplicates.
SELECT status FROM projects -- collapse to distinct values here ;
Add the keyword
DISTINCTafter SELECT.No WHERE or ORDER BY is needed.
SELECT DISTINCT status FROM projects;
You want to see the set of different job titles people hold, such as Engineer or Sales Rep. Which table and column hold that information?
A person's job title lives in the role column of the employees table. SELECT DISTINCT role FROM employees would list each title once.
Recap
- The company database has three tables:
departments,employees, andprojects. - Peek with
SELECT * ... LIMIT nto see values; count withCOUNT(*)to measure size. SELECT DISTINCTcollapses a column down to its set of unique values.- Tables link through foreign keys like
department_id, not through shared column names. - Real data has NULLs — Omar has no department, Research has no city — and they affect every query.
Next you will shape and filter these rows with SELECT and WHERE.