A table joined to itself

SQL · Unit 8: More joins

Full plan
SQL
10 exercises

Another database, a company of nine. Its employees table stores each person's name, role, and monthly salary, and a boss_id column. Who is each person's boss?

boss_id is a foreign key, and it points to the same table it lives in. Rachel's boss is 2, and 2 is Mark, two rows up. Carol runs the company and has no boss.

SELECT id, name, role, boss_id
FROM employees
ORDER BY id;

Returns

id	name	role	boss_id
1	Carol	CEO	NULL
2	Mark	Manager	1
3	Paige	Manager	1
4	Rachel	Sales Rep	2
5	Nicole	Sales Rep	2
6	Samuel	Developer	3
7	Lydia	Dev	3
8	Frank	Developer	3
9	Grace	Illustrator	1

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Multiple choice
In Frank's row, boss_id is 3. Where do you look up that 3 to find out who his boss is?
2. Complete the code
Who manages the people who write code? In role, some say Developer and one says Dev, and the LIKE keeps all three. Fill in the ON.
3. Predict the output
The same join answers the question the other way around. Mark is employee 2. His boss is Carol, and he's the boss of Rachel and Nicole. What does this query return?
4. Find the bug
HR compares each sales rep's salary with their boss's. The boss is Mark, who earns 3800. The query runs without an error and says Nicole's boss earns 2100. Which line has the problem?
5. Put the lines in order
Who does each manager manage? Put in order the query that gives the manager's name and the name of each person reporting to them, by manager and, within each one, by employee. Two lines don't belong.
6. Find the bug
This query should list each employee with their boss. Here SQL answers ambiguous column name and points at e.name, which has its alias in front. Which line has the problem?
7. Predict the output
With the bosses' copy in the FROM, the same join counts how many people report to each person. boss_id points to the direct boss. Mark, Paige, and Grace point to Carol. Rachel and Nicole point to Mark. Samuel, Lydia, and Frank point to Paige. What does this query return for employees 1 through 4, who are Carol, Mark, Paige, and Rachel?
8. Put the lines in order
Who doesn't manage anyone? Put in order the query that gives their names, in alphabetical order. Two lines don't belong.
9. Predict the output
A table can go into a query as many times as it takes. Here it goes in three times, and bb is the copy where you read the boss's boss. Mark is employee 2, and his boss is Carol, who has no boss. Rachel is employee 4, and her boss is Mark. What does this query return?
10. Write the code
The company directory. Write a query with three columns, in this order: each person's name, their role, and their boss's name, saying nobody for whoever has none. All nine have to be there, by name in alphabetical order.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →