EXISTS

SQL · Unit 9: Subqueries

Full plan
SQL
10 exercises

Another database, the nine-person company. You already know employees. What you hadn't seen is that each person's row stores their department's number in dept_id, and that the departments live in their own table, each with its budget.

Carol, the CEO, doesn't belong to any of them, and her dept_id is empty.

SELECT id, name, budget
FROM departments;
SELECT name, salary, dept_id
FROM employees;

Returns

id	name	budget
1	Sales	50000
2	Tech	80000
3	Design	30000
4	Legal	20000

Returns

name	salary	dept_id
Carol	5200	NULL
Mark	3800	1
Paige	4100	2
Rachel	1900	1
Nicole	2100	1
Samuel	3000	2
Lydia	3000	2
Frank	2600	2
Grace	2400	3

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. Predict the output
The company is going to pay a bonus to the departments where nobody earns 3000 or more. First, the ones that miss out. Which departments have someone who earns 3000 or more? In Sales, Mark does. In Tech, Paige, Samuel, and Lydia do. Grace, the only one in Design, earns 2400. Carol earns 5200 and has no department. What does this query return?
2. Put the lines in order
Who has people reporting to them? The b copy goes outside, and the e copy inside. Put the query in order. Two lines don't belong.
3. Predict the output
The same question, which department has nobody working in it, written with NOT IN. The inner list has nine values, one per employee, and one of them is Carol's NULL. What does it return?
4. Put the lines in order
Which departments get the bonus, because nobody in them earns 3000 or more? Put the query in order. Two lines don't belong.
5. Predict the output
Which departments have a CEO? The company has only one, Carol, and she doesn't belong to any department. This query is also missing the condition that ties it to each one. What does it return?
6. Find the bug
Which departments have someone who earns less than 2500? They're Sales, because of Rachel and Nicole, and Design, because of Grace. The query says Sales and Tech, and in Tech the lowest earner is Frank, at 2600. Which line has the problem?
7. Multiple choice
In the query for the departments where someone works, a coworker changes SELECT 1 to SELECT e.name, e.salary. What changes in the result?
8. Complete the code
Are there two people who earn exactly the same? For each employee, the subquery looks in the x copy for someone else with the same salary. Fill in the blank in the condition that keeps each person from finding themselves.
9. Find the case that fails
HR is looking for the managers whose whole team earns less than 3000. A manager with nobody reporting to them has no team, and doesn't count. Today the query returns Mark, who manages Rachel and Nicole. Which of these changes makes the query let in someone who shouldn't be there?
10. Write the code
Who doesn't share a role with anyone? Write a query with two columns, in this order: the name and the role of whoever doesn't share a role with anyone, by name in alphabetical order. The inner copy of employees is called x.

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 →