Subqueries with EXISTS in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all customers who have at least one order.

MySQL

SELECT customer_id FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.[1]);

Drag options to blanks, or click blank then click option'

Aid

Border_id

Corder_date

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column like order_id or order_date in the WHERE clause.

Confusing the table aliases or column names.

The subquery checks if there is any order with the same customer_id as the customer. So, the correct column to compare is customer_id.

2fill in blank

medium

Complete the code to find products that have never been ordered.

MySQL

SELECT product_id FROM products WHERE NOT EXISTS (SELECT 1 FROM order_items WHERE order_items.product_id = products.[1]);

Drag options to blanks, or click blank then click option'

Aproduct_id

Border_id

Cid

Dquantity

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column like order_id or quantity in the WHERE clause.

Forgetting to use NOT EXISTS to find products never ordered.

The subquery checks if there is any order item with the same product_id as the product. To find products never ordered, we use NOT EXISTS and compare product_id.

3fill in blank

hard

Fix the error in the query to select employees who manage at least one department.

MySQL

SELECT employee_id FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE departments.manager_id = employees.[1]);

Drag options to blanks, or click blank then click option'

Adepartment_id

Bemployee_id

Cmanager_id

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong employee column like id or manager_id.

Confusing the columns between employees and departments.

The subquery checks if the employee's employee_id matches the manager_id in departments. So, the employee column to compare is employee_id.

4fill in blank

hard

Fill both blanks to select customers who have placed orders with total amount greater than 1000.

MySQL

SELECT customer_id FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.[1] = customers.[2] AND orders.total_amount > 1000);

Drag options to blanks, or click blank then click option'

Acustomer_id

Border_id

Cid

Dtotal_amount

Attempts:

3 left

💡 Hint

Common Mistakes

Using order_id or total_amount in the join condition.

Mixing up customer_id with id.

The subquery matches orders.customer_id with customers.customer_id to find orders by the customer. So both blanks use customer_id.

5fill in blank

hard

Fill all three blanks to select products that appear in orders with quantity greater than 5.

MySQL

SELECT product_id FROM products WHERE EXISTS (SELECT 1 FROM order_items WHERE order_items.[1] = products.[2] AND order_items.[3] > 5);

Drag options to blanks, or click blank then click option'

Aproduct_id

Bid

Cquantity

Dorder_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using order_id instead of product_id for the join.

Using id instead of product_id for products.

Checking order_id > 5 instead of quantity > 5.

The subquery links order_items.product_id to products.product_id and checks if quantity is greater than 5.

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