Subqueries with EXISTS in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

PostgreSQL

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

Ccustomer_name

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

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

Not matching the correct customer_id column.

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.

PostgreSQL

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

Bname

Corder_id

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column like id or name 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 match on product_id.

3fill in blank

hard

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

PostgreSQL

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'

Aid

Bemployee_id

Cmanager_id

Ddept_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column like id or manager_id from employees.

Confusing columns from different tables.

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 placed orders with total amount greater than 1000.

PostgreSQL

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

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

Acustomer_id

Border_id

Ctotal_amount

Damount

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong columns like order_id or amount instead of total_amount.

Not matching customer_id correctly.

The subquery checks orders where the customer_id matches the customer and the total_amount is greater than 1000.

5fill in blank

hard

Fill all three blanks to select products that have total ordered quantity greater than 10.

PostgreSQL

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

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

Aproduct_id

Bid

Dorder_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong columns for matching or grouping.

Confusing product_id and id columns.

The subquery matches order_items.product_id to products.product_id, groups by order_items.product_id, and filters groups with total quantity over 10.