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.