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

Using Subqueries with EXISTS in MySQL

📖 Scenario: You are managing a small online bookstore database. You want to find authors who have written books that are currently in stock.

🎯 Goal: Build a MySQL query using a subquery with EXISTS to find all authors who have at least one book available in stock.

📋 What You'll Learn

Create a table called authors with columns author_id and author_name.

Create a table called books with columns book_id, title, author_id, and in_stock.

Write a query using EXISTS to select authors who have books with in_stock greater than 0.

💡 Why This Matters

🌍 Real World

Bookstores and libraries often need to find authors who have available books for sale or loan. Using EXISTS helps efficiently check related data.

💼 Career

Database developers and analysts use subqueries with EXISTS to filter data based on related tables, a common task in real-world SQL querying.

1

Create the authors table and insert data

Create a table called authors with columns author_id (integer) and author_name (varchar 50). Insert these exact rows: (1, 'Alice Walker'), (2, 'Mark Twain'), (3, 'Jane Austen').

MySQL

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

Hint

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

2

Create the books table and insert data

Create a table called books with columns book_id (integer), title (varchar 100), author_id (integer), and in_stock (integer). Insert these exact rows: (101, 'The Color Purple', 1, 5), (102, 'Adventures of Huckleberry Finn', 2, 0), (103, 'Pride and Prejudice', 3, 3).

MySQL

CREATE TABLE authors (
  author_id INT,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice Walker'),
(2, 'Mark Twain'),
(3, 'Jane Austen');

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

Hint

Define the books table with the specified columns and insert the given rows.

3

Write a query using EXISTS to find authors with books in stock

Write a SELECT query to get author_name from authors where there exists at least one book in books with the same author_id and in_stock greater than 0. Use a subquery with EXISTS.

MySQL

CREATE TABLE authors (
  author_id INT,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice Walker'),
(2, 'Mark Twain'),
(3, 'Jane Austen');

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  author_id INT,
  in_stock INT
);

INSERT INTO books (book_id, title, author_id, in_stock) VALUES
(101, 'The Color Purple', 1, 5),
(102, 'Adventures of Huckleberry Finn', 2, 0),
(103, 'Pride and Prejudice', 3, 3);

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

Hint

Use EXISTS with a subquery that checks for books with matching author_id and in_stock > 0.

4

Complete the query with ordering

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

MySQL

CREATE TABLE authors (
  author_id INT,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice Walker'),
(2, 'Mark Twain'),
(3, 'Jane Austen');

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  author_id INT,
  in_stock INT
);

INSERT INTO books (book_id, title, author_id, in_stock) VALUES
(101, 'The Color Purple', 1, 5),
(102, 'Adventures of Huckleberry Finn', 2, 0),
(103, 'Pride and Prejudice', 3, 3);

SELECT author_name
FROM authors a
WHERE EXISTS (
  SELECT 1
  FROM books b
  WHERE b.author_id = a.author_id
    AND b.in_stock > 0
);

-- Add ORDER BY clause to sort by author_name
-- Your code here

Hint

Use ORDER BY author_name ASC at the end of the query to sort alphabetically.

Practice

1. What does the EXISTS keyword do in a MySQL query?

easy

A. Counts the total number of rows in a table

B. Deletes rows from a table based on a condition

C. Checks if a subquery returns any rows and returns TRUE or FALSE

D. Joins two tables together

Solution

1. Step 1: Understand the purpose of EXISTS

   EXISTS checks if the subquery returns at least one row.

2. Step 2: Compare with other options

   Counting rows, deleting rows, and joining tables are different operations.

3. Final Answer:

   Checks if a subquery returns any rows and returns TRUE or FALSE -> Option C

4. Quick Check:

   EXISTS means row presence check = B [OK]

Hint: EXISTS means "is there at least one row?" [OK]

Common Mistakes:

• Confusing EXISTS with COUNT
• Thinking EXISTS deletes rows
• Mixing EXISTS with JOIN

2. Which of the following is the correct syntax to use EXISTS in a WHERE clause?

easy

A. SELECT * FROM table WHERE EXISTS = (SELECT column FROM table2);

B. SELECT * FROM table WHERE EXISTS SELECT column FROM table2;

C. SELECT * FROM table WHERE EXISTS IN (SELECT column FROM table2);

D. SELECT * FROM table WHERE EXISTS (SELECT column FROM table2);

Solution

1. Step 1: Recall EXISTS syntax

   EXISTS must be followed by a subquery in parentheses.

2. Step 2: Check each option

   SELECT * FROM table WHERE EXISTS (SELECT column FROM table2); uses correct syntax with parentheses; others have syntax errors.

3. Final Answer:

   SELECT * FROM table WHERE EXISTS (SELECT column FROM table2); -> Option D

4. Quick Check:

   EXISTS needs parentheses around subquery = C [OK]

Hint: EXISTS always uses parentheses for the subquery [OK]

Common Mistakes:

• Omitting parentheses after EXISTS
• Using '=' or 'IN' with EXISTS
• Writing subquery without parentheses

3. Given tables customers(id, name) and orders(id, customer_id), what does this query return?

SELECT name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id);

medium

A. An error because of invalid subquery

B. All customers who have placed at least one order

C. All orders with matching customer names

D. All customers including those without orders

Solution

1. Step 1: Understand the EXISTS subquery

   The subquery checks if there is at least one order with customer_id matching customers.id.

2. Step 2: Interpret the WHERE clause effect

   Only customers with matching orders exist, so only those customers are returned.

3. Final Answer:

   All customers who have placed at least one order -> Option B

4. Quick Check:

   EXISTS filters customers with orders = D [OK]

Hint: EXISTS filters rows with matching related data [OK]

Common Mistakes:

• Thinking it returns all customers
• Confusing orders with customers
• Assuming syntax error in subquery

4. Identify the error in this query:

SELECT * FROM employees WHERE EXISTS SELECT * FROM departments WHERE departments.id = employees.dept_id;

medium

A. Missing parentheses around the subquery after EXISTS

B. Using SELECT * inside EXISTS is not allowed

C. The WHERE clause inside subquery is invalid

D. EXISTS cannot be used with multiple tables

Solution

1. Step 1: Check EXISTS syntax

   EXISTS requires the subquery to be enclosed in parentheses.

2. Step 2: Verify other parts

   SELECT * is allowed inside EXISTS; WHERE clause is valid; EXISTS works with multiple tables.

3. Final Answer:

   Missing parentheses around the subquery after EXISTS -> Option A

4. Quick Check:

   EXISTS needs parentheses = A [OK]

Hint: Always put subquery in parentheses after EXISTS [OK]

Common Mistakes:

• Omitting parentheses after EXISTS
• Thinking SELECT * is invalid inside EXISTS
• Misunderstanding WHERE clause usage

5. You want to find all products that have never been ordered. Given tables products(id, name) and orders(id, product_id), which query correctly uses EXISTS to achieve this?

hard

A. SELECT name FROM products WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.product_id = products.id);

B. SELECT name FROM products WHERE EXISTS (SELECT 1 FROM orders WHERE orders.product_id = products.id);

C. SELECT name FROM products WHERE EXISTS (SELECT 1 FROM orders WHERE orders.id = products.id);

D. SELECT name FROM products WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.id = products.id);

Solution

1. Step 1: Understand the goal

   We want products with no matching orders, so use NOT EXISTS with matching product_id.

2. Step 2: Analyze each option

   SELECT name FROM products WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.product_id = products.id); correctly uses NOT EXISTS with orders.product_id = products.id. SELECT name FROM products WHERE EXISTS (SELECT 1 FROM orders WHERE orders.product_id = products.id); finds products with orders. Options C and D compare wrong columns.

3. Final Answer:

   SELECT name FROM products WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.product_id = products.id); -> Option A

4. Quick Check:

   NOT EXISTS filters products without orders = A [OK]

Hint: Use NOT EXISTS to find items with no related rows [OK]

Common Mistakes:

• Using EXISTS instead of NOT EXISTS
• Comparing wrong columns in subquery
• Confusing product_id with order id