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

Need a 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

Need a 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

Need a 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

Need a hint?

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