Union, intersection, difference in DBMS Theory - Mini Project: Build & Apply

Union, Intersection, and Difference in SQL

📖 Scenario: You work in a company database where you have two tables: Employees_USA and Employees_Canada. Each table lists employees working in that country.You want to learn how to combine and compare these tables using SQL operations: union, intersection, and difference.

🎯 Goal: Build SQL queries to find:All employees working in either USA or Canada (union)Employees working in both USA and Canada (intersection)Employees working in USA but not in Canada (difference)

📋 What You'll Learn

Create two tables Employees_USA and Employees_Canada with exact employee names

Write a SQL query using UNION to combine all employees from both tables

Write a SQL query using INTERSECT to find employees common to both tables

Write a SQL query using EXCEPT to find employees in USA but not in Canada

💡 Why This Matters

🌍 Real World

Combining and comparing data from different sources or departments is common in business databases.

💼 Career

Knowing how to use UNION, INTERSECT, and EXCEPT helps database analysts and developers write efficient queries for data analysis and reporting.

1

Create the Employees Tables

Create two tables called Employees_USA and Employees_Canada with a single column EmployeeName of type VARCHAR. Insert these exact names into Employees_USA: 'Alice', 'Bob', 'Charlie'. Insert these exact names into Employees_Canada: 'Bob', 'Diana', 'Eve'.

DBMS Theory

-- Create Employees_USA table and insert data
-- Create Employees_Canada table and insert data

Need a hint?

Use CREATE TABLE to make tables and INSERT INTO to add rows.

2

Write the UNION Query

Write a SQL query called AllEmployees that selects EmployeeName from Employees_USA and Employees_Canada combined using UNION to get all unique employee names from both tables.

DBMS Theory

CREATE TABLE Employees_USA (EmployeeName VARCHAR(50));
INSERT INTO Employees_USA (EmployeeName) VALUES ('Alice'), ('Bob'), ('Charlie');

CREATE TABLE Employees_Canada (EmployeeName VARCHAR(50));
INSERT INTO Employees_Canada (EmployeeName) VALUES ('Bob'), ('Diana'), ('Eve');

-- Write a UNION query to get all unique employees
-- Your code here

Need a hint?

Use UNION between two SELECT statements to combine unique rows.

3

Write the INTERSECT Query

Write a SQL query that selects EmployeeName from Employees_USA and Employees_Canada using INTERSECT to find employees who work in both USA and Canada.

DBMS Theory

CREATE TABLE Employees_USA (EmployeeName VARCHAR(50));
INSERT INTO Employees_USA (EmployeeName) VALUES ('Alice'), ('Bob'), ('Charlie');

CREATE TABLE Employees_Canada (EmployeeName VARCHAR(50));
INSERT INTO Employees_Canada (EmployeeName) VALUES ('Bob'), ('Diana'), ('Eve');

SELECT EmployeeName FROM Employees_USA
UNION
SELECT EmployeeName FROM Employees_Canada;

-- Write an INTERSECT query to find employees in both tables
-- Your code here

Need a hint?

Use INTERSECT between two SELECT statements to find common rows.

4

Write the EXCEPT Query

Write a SQL query that selects EmployeeName from Employees_USA but excludes those also in Employees_Canada using EXCEPT to find employees who work only in USA.

DBMS Theory

CREATE TABLE Employees_USA (EmployeeName VARCHAR(50));
INSERT INTO Employees_USA (EmployeeName) VALUES ('Alice'), ('Bob'), ('Charlie');

CREATE TABLE Employees_Canada (EmployeeName VARCHAR(50));
INSERT INTO Employees_Canada (EmployeeName) VALUES ('Bob'), ('Diana'), ('Eve');

SELECT EmployeeName FROM Employees_USA
UNION
SELECT EmployeeName FROM Employees_Canada;

SELECT EmployeeName FROM Employees_USA
INTERSECT
SELECT EmployeeName FROM Employees_Canada;

-- Write an EXCEPT query to find employees only in USA
-- Your code here

Need a hint?

Use EXCEPT to find rows in the first query that are not in the second.