EXCEPT equivalent in MySQL - Mini Project: Build & Apply

Using EXCEPT Equivalent in MySQL

📖 Scenario: You work in a company database where you have two tables: employees and managers. You want to find all employees who are not managers.

🎯 Goal: Build a query that finds all employees who are not listed as managers using MySQL's equivalent of the EXCEPT operation.

📋 What You'll Learn

Create a table called employees with columns id and name and insert the exact data given.

Create a table called managers with columns id and name and insert the exact data given.

Write a query that selects all employees who are not managers using a LEFT JOIN and WHERE clause.

Use the exact table and column names as specified.

💡 Why This Matters

🌍 Real World

Finding differences between two lists of data is common in business, such as identifying customers who have not made purchases or employees who are not assigned to projects.

💼 Career

Database developers and analysts often need to write queries that compare tables and find unmatched records, especially when the EXCEPT keyword is not supported.

1

Create the employees table and insert data

Create a table called employees with columns id (integer) and name (varchar 50). Insert these rows exactly: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'), (4, 'Diana').

MySQL

-- Create the employees table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create the managers table and insert data

Create a table called managers with columns id (integer) and name (varchar 50). Insert these rows exactly: (2, 'Bob'), (4, 'Diana').

MySQL

CREATE TABLE employees (id INT, name VARCHAR(50));
INSERT INTO employees (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'Diana');
-- Create the managers table and insert data
-- Your code here

Need a hint?

Use the same approach as for employees but with the smaller data set.

3

Write a query to find employees who are not managers

Write a SELECT query that uses a LEFT JOIN between employees and managers on id. Select employee id and name where the manager id is NULL. Use the exact table and column names.

MySQL

CREATE TABLE employees (id INT, name VARCHAR(50));
INSERT INTO employees (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'Diana');

CREATE TABLE managers (id INT, name VARCHAR(50));
INSERT INTO managers (id, name) VALUES
(2, 'Bob'),
(4, 'Diana');

-- Write the query to find employees who are not managers
-- Your code here

Need a hint?

Use LEFT JOIN to keep all employees and filter out those who have matching managers.

4

Complete the query with ordering

Add an ORDER BY clause to the query to sort the results by employees.name in ascending order.

MySQL

CREATE TABLE employees (id INT, name VARCHAR(50));
INSERT INTO employees (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'Diana');

CREATE TABLE managers (id INT, name VARCHAR(50));
INSERT INTO managers (id, name) VALUES
(2, 'Bob'),
(4, 'Diana');

SELECT employees.id, employees.name
FROM employees
LEFT JOIN managers ON employees.id = managers.id
WHERE managers.id IS NULL
-- Add ORDER BY clause here
-- Your code here

Need a hint?

Use ORDER BY employees.name ASC to sort the output alphabetically by employee name.