Subquery with EXISTS operator in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

SQL

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_date

Ccustomer_id

Dorder_id

Attempts:

3 left

💡 Hint

Common Mistakes

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

Not using the EXISTS operator properly.

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

2fill in blank

medium

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

SQL

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'

Aquantity

Border_id

Cid

Dproduct_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using a 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 exists any order item with the same product_id as in the outer query. 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.

SQL

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'

Aemployee_id

Bdept_id

Cmanager_id

Ddepartment_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using manager_id from employees which may not exist.

Using department-related columns instead of employee ID.

The subquery checks if the manager_id in departments matches the employee_id in employees. So, the correct column to compare in employees is employee_id.

4fill in blank

hard

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

SQL

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

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

Acustomer_id

Border_id

Ctotal_amount

Dorder_date

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong columns like order_id or order_date for the amount comparison.

Not matching customer_id correctly.

The subquery checks for orders where the customer_id matches and the total_amount is greater than 100.

5fill in blank

hard

Fill all three blanks to select products that have been ordered more than 10 times in total.

SQL

SELECT product_id FROM products WHERE EXISTS (SELECT 1 FROM order_items WHERE order_items.product_id = products.[1] GROUP BY order_items.[2] HAVING SUM(order_items.[3]) > 10);

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

Aproduct_id

Cquantity

Dorder_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using order_id instead of product_id in GROUP BY.

Using wrong column for summing like order_id.

The subquery groups order items by product_id and sums the quantity ordered. It checks if the total quantity is greater than 10 for the product matching the outer query.