Subqueries with EXISTS in PostgreSQL - Mini Project: Build & Apply

Find Customers with Orders Using EXISTS Subquery

📖 Scenario: You work at a small online store. You have two tables: customers and orders. You want to find which customers have placed at least one order.

🎯 Goal: Build a SQL query using a subquery with EXISTS to list all customers who have made orders.

📋 What You'll Learn

Create a customers table with columns customer_id and customer_name.

Create an orders table with columns order_id and customer_id.

Insert the exact customers and orders data as specified.

Write a SQL query using EXISTS to find customers with orders.

💡 Why This Matters

🌍 Real World

Stores and businesses often need to find customers who have made purchases to send promotions or analyze sales.

💼 Career

Knowing how to use EXISTS subqueries is important for database querying tasks in data analysis, backend development, and reporting.

1

Create the customers table and insert data

Write SQL statements to create a table called customers with columns customer_id (integer) and customer_name (text). Then insert these exact rows: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie').

PostgreSQL

-- Create customers 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 orders table and insert data

Write SQL statements to create a table called orders with columns order_id (integer) and customer_id (integer). Then insert these exact rows: (101, 1), (102, 1), (103, 3).

PostgreSQL

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT
);

INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

-- Create orders table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE and INSERT INTO like before, matching the exact columns and values.

3

Write the query using EXISTS to find customers with orders

Write a SQL query that selects customer_id and customer_name from customers where there exists at least one row in orders with the same customer_id. Use a subquery with EXISTS.

PostgreSQL

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT
);

INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER
);

INSERT INTO orders (order_id, customer_id) VALUES
(101, 1),
(102, 1),
(103, 3);

-- Write SELECT query using EXISTS
-- Your code here

Need a hint?

Use WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id) to check if the customer has any orders.

4

Complete the query with ordering by customer_name

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

PostgreSQL

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT
);

INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER
);

INSERT INTO orders (order_id, customer_id) VALUES
(101, 1),
(102, 1),
(103, 3);

SELECT customer_id, customer_name
FROM customers
WHERE EXISTS (
    SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id
);

-- Add ORDER BY customer_name
-- Your code here

Need a hint?

Use ORDER BY customer_name ASC after the WHERE EXISTS clause.