INTERSECT for common rows in SQL - Mini Project: Build & Apply

Find Common Customers Using INTERSECT

📖 Scenario: You work for a company that has two sales databases. Each database has a table of customers who made purchases in different regions. You want to find customers who bought products in both regions.

🎯 Goal: Create two tables named region1_customers and region2_customers with customer names. Then write a query using INTERSECT to find customers common to both regions.

📋 What You'll Learn

Create a table called region1_customers with a column customer_name and insert these exact names: 'Alice', 'Bob', 'Charlie', 'Diana'

Create a table called region2_customers with a column customer_name and insert these exact names: 'Bob', 'Diana', 'Eve', 'Frank'

Write a query using INTERSECT to select customer_name from both tables to find common customers

Complete the final query to show only the common customer names

💡 Why This Matters

🌍 Real World

Companies often need to find customers or data points common to multiple datasets or regions to analyze overlapping markets or shared customers.

💼 Career

Knowing how to use INTERSECT helps database analysts and developers write efficient queries to compare datasets and find common records.

1

Create the region1_customers table and insert data

Create a table called region1_customers with a column customer_name of type VARCHAR(50). Insert these exact customer names: 'Alice', 'Bob', 'Charlie', and 'Diana'.

SQL

-- Create table region1_customers and insert customers
-- Your code here

Need a hint?

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

2

Create the region2_customers table and insert data

Create a table called region2_customers with a column customer_name of type VARCHAR(50). Insert these exact customer names: 'Bob', 'Diana', 'Eve', and 'Frank'.

SQL

CREATE TABLE region1_customers (customer_name VARCHAR(50));
INSERT INTO region1_customers (customer_name) VALUES
('Alice'),
('Bob'),
('Charlie'),
('Diana');

-- Create table region2_customers and insert customers
-- Your code here

Need a hint?

Repeat the table creation and insertion steps for the second region.

3

Write the INTERSECT query to find common customers

Write a SQL query that selects customer_name from region1_customers and uses INTERSECT with a select of customer_name from region2_customers to find customers present in both tables.

SQL

CREATE TABLE region1_customers (customer_name VARCHAR(50));
INSERT INTO region1_customers (customer_name) VALUES
('Alice'),
('Bob'),
('Charlie'),
('Diana');

CREATE TABLE region2_customers (customer_name VARCHAR(50));
INSERT INTO region2_customers (customer_name) VALUES
('Bob'),
('Diana'),
('Eve'),
('Frank');

-- Write INTERSECT query to find common customers
-- Your code here

Need a hint?

Use INTERSECT between two SELECT statements to get common rows.

4

Complete the query to show only common customer names

Ensure the final query selects customer_name from region1_customers and uses INTERSECT with the select from region2_customers to display only the customers who appear in both tables.

SQL

CREATE TABLE region1_customers (customer_name VARCHAR(50));
INSERT INTO region1_customers (customer_name) VALUES
('Alice'),
('Bob'),
('Charlie'),
('Diana');

CREATE TABLE region2_customers (customer_name VARCHAR(50));
INSERT INTO region2_customers (customer_name) VALUES
('Bob'),
('Diana'),
('Eve'),
('Frank');

-- Finalize the query to show common customers
-- Your code here

Need a hint?

The query is complete when it uses INTERSECT to find common customer names.