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.

Practice

1. What does the IN keyword do in a SQL query when used with a subquery in the WHERE clause?

easy

A. It filters rows where a column's value matches any value returned by the subquery.

B. It joins two tables together based on matching columns.

C. It updates rows in the table with values from the subquery.

D. It deletes rows that do not exist in the subquery.

Solution

1. Step 1: Understand the role of IN with subqueries

   The IN keyword checks if a value exists in a list of values returned by the subquery.

2. Step 2: Identify the filtering effect

   Rows are kept only if their column value matches any value from the subquery's result.

3. Final Answer:

   It filters rows where a column's value matches any value returned by the subquery. -> Option A

4. Quick Check:

   IN filters rows by subquery values [OK]

Hint: Think of IN as 'is in this list' from the subquery [OK]

Common Mistakes:

• Confusing IN with JOIN
• Thinking IN updates or deletes rows
• Assuming IN returns a single value only

2. Which of the following is the correct syntax to use a subquery with IN in PostgreSQL?

easy

A. SELECT * FROM employees WHERE department_id IN (SELECT * FROM departments);

B. SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments);

C. SELECT * FROM employees WHERE department_id IN SELECT id FROM departments;

D. SELECT * FROM employees WHERE department_id = IN (SELECT id FROM departments);

Solution

1. Step 1: Check the correct use of parentheses

   The subquery must be enclosed in parentheses after IN.

2. Step 2: Verify the subquery returns a single column

   The subquery should return one column of values to compare against.

3. Final Answer:

   SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments); -> Option B

4. Quick Check:

   Subquery in parentheses with single column [OK]

Hint: Always put subquery inside parentheses after IN [OK]

Common Mistakes:

• Using = IN instead of just IN
• Omitting parentheses around subquery
• Selecting multiple columns in subquery

3. Given the tables:
employees(id, name, department_id)
departments(id, name)
What will this query return?

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales');

medium

A. An error because subquery returns multiple rows.

B. Names of all employees regardless of department.

C. Names of employees who do not work in Sales.

D. Names of employees who work in the Sales department.

Solution

1. Step 1: Understand the subquery filtering departments

   The subquery selects department IDs where the department name is 'Sales'.

2. Step 2: Filter employees by matching department IDs

   The main query returns employee names whose department_id matches those IDs.

3. Final Answer:

   Names of employees who work in the Sales department. -> Option D

4. Quick Check:

   IN filters employees by Sales department IDs [OK]

Hint: Subquery filters departments, main query filters employees [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing NOT IN with IN
• Assuming subquery causes error with multiple rows

4. Identify the error in this query:

SELECT * FROM employees WHERE department_id IN SELECT id FROM departments WHERE name = 'HR';

medium

A. Missing parentheses around the subquery after IN.

B. Using IN instead of EXISTS.

C. Subquery returns multiple columns.

D. department_id cannot be compared with id.

Solution

1. Step 1: Check syntax for subquery with IN

   The subquery must be enclosed in parentheses after the IN keyword.

2. Step 2: Identify missing parentheses causing syntax error

   The query lacks parentheses around the subquery, causing invalid syntax.

3. Final Answer:

   Missing parentheses around the subquery after IN. -> Option A

4. Quick Check:

   Subquery must be in parentheses after IN [OK]

Hint: Always wrap subqueries in parentheses after IN [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Confusing IN with EXISTS syntax
• Assuming subquery returns multiple columns

5. You want to find all customers who have placed orders for products in the 'Electronics' category. Given tables:
customers(id, name)
orders(id, customer_id, product_id)
products(id, name, category)
Which query correctly uses a subquery with IN to get these customers?

hard

A. SELECT name FROM customers WHERE id IN (SELECT id FROM products WHERE category = 'Electronics');

B. SELECT name FROM customers WHERE id = IN (SELECT customer_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE category = 'Electronics'));

C. SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE category = 'Electronics'));

D. SELECT name FROM customers WHERE id IN (SELECT product_id FROM orders WHERE customer_id IN (SELECT id FROM products WHERE category = 'Electronics'));

Solution

1. Step 1: Identify the correct subquery for products in 'Electronics'

   The innermost subquery selects product IDs where category is 'Electronics'.

2. Step 2: Use these product IDs to find matching orders

   The middle subquery selects customer IDs from orders with those product IDs.

3. Step 3: Select customer names whose IDs match those customer IDs

   The main query selects customer names where their ID is in the list from the orders subquery.

4. Final Answer:

   SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE category = 'Electronics')); -> Option C

5. Quick Check:

   Nested IN subqueries filter customers by product category [OK]

Hint: Use nested IN subqueries to filter step-by-step [OK]

Common Mistakes:

• Using = IN instead of IN
• Mixing up customer_id and product_id columns
• Selecting wrong columns in subqueries