Subqueries in WHERE with IN in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all employees who work in departments listed in the subquery.

PostgreSQL

SELECT * FROM employees WHERE department_id [1] (SELECT id FROM departments WHERE location = 'New York');

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

ANOT

BIN

CLIKE

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' instead of 'IN' causes an error because the subquery returns multiple rows.

Using 'LIKE' is for pattern matching, not for lists.

Using 'NOT' alone is incomplete and causes syntax error.

The IN operator is used to check if a value matches any value in a list or subquery result.

2fill in blank

medium

Complete the code to find all products whose category_id is in the list of categories with 'active' status.

PostgreSQL

SELECT product_name FROM products WHERE category_id [1] (SELECT id FROM categories WHERE status = 'active');

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

AIN

B=

CNOT IN

DEXISTS

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' causes an error if the subquery returns multiple rows.

Using 'NOT IN' would select products not in active categories, which is opposite.

Using 'EXISTS' requires a different syntax and correlation.

The IN operator checks if category_id is among the IDs returned by the subquery.

3fill in blank

hard

Fix the error in the query to select customers who have placed orders in 2023.

PostgreSQL

SELECT * FROM customers WHERE customer_id [1] (SELECT customer_id FROM orders WHERE order_date >= '2023-01-01');

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

A=

BLIKE

CNOT IN

DIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' causes a runtime error due to multiple rows returned.

Using 'LIKE' is for pattern matching, not for lists.

Using 'NOT IN' would select customers who did not place orders.

The IN operator is needed because the subquery returns multiple customer IDs.

4fill in blank

hard

Fill both blanks to select all employees whose department is in the list of departments located in 'Chicago' and whose salary is greater than 50000.

PostgreSQL

SELECT name FROM employees WHERE department_id [1] (SELECT id FROM departments WHERE city = 'Chicago') AND salary [2] 50000;

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

AIN

B>

C<

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' instead of 'IN' for department_id causes errors if multiple departments.

Using '<' instead of '>' filters salaries less than 50000, opposite of requirement.

The first blank needs IN to check department membership. The second blank needs > to filter salaries greater than 50000.

5fill in blank

hard

Fill all three blanks to select product names and prices for products in categories that are active and have a discount greater than 10.

PostgreSQL

SELECT product_name, price FROM products WHERE category_id [1] (SELECT id FROM categories WHERE status = [2]) AND discount [3] 10;

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

AIN

B'active'

C>

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' instead of 'IN' for category_id causes errors if multiple categories.

Forgetting quotes around 'active' causes syntax errors.

Using '<' or '=' instead of '>' for discount filters wrong products.

The first blank uses IN to check category membership. The second blank uses 'active' as a string literal for status. The third blank uses > to filter discounts greater than 10.