Subquery vs JOIN performance trade-off in SQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all customers who have placed an order using a subquery.

SQL

SELECT customer_id FROM orders WHERE customer_id IN (SELECT [1] FROM customers);

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

Acustomer_id

Border_id

Corder_date

Dproduct_id

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting order_id instead of customer_id in the subquery.

Using a column that does not exist in the customers table.

The subquery must select customer_id from the customers table to match the outer query's customer_id.

2fill in blank

medium

Complete the code to join customers and orders tables to get customer names and their order dates.

SQL

SELECT c.name, o.order_date FROM customers c [1] orders o ON c.customer_id = o.customer_id;

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

ALEFT JOIN

BRIGHT JOIN

CINNER JOIN

DFULL JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using LEFT JOIN which includes customers without orders.

Using FULL JOIN which includes unmatched rows from both tables.

An INNER JOIN returns only customers who have orders, matching the requirement.

3fill in blank

hard

Fix the error in the query to find customers who have not placed any orders using a subquery.

SQL

SELECT name FROM customers WHERE customer_id [1] (SELECT customer_id FROM orders);

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

AIN

BNOT EXISTS

CEXISTS

DNOT IN

Attempts:

3 left

💡 Hint

Common Mistakes

Using IN instead of NOT IN, which returns customers with orders.

Using EXISTS incorrectly without a correlated subquery.

To find customers without orders, use NOT IN to exclude those with matching orders.

4fill in blank

hard

Fill both blanks to write a query that lists customers and their order counts, including customers with zero orders.

SQL

SELECT c.name, COUNT(o.order_id) AS order_count FROM customers c [1] JOIN orders o ON c.customer_id [2] o.customer_id GROUP BY c.name;

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

ALEFT

BRIGHT

C=

D<>

Attempts:

3 left

💡 Hint

Common Mistakes

Using RIGHT JOIN which may exclude some customers.

Using <> instead of = in the join condition.

A LEFT JOIN includes all customers even if they have no orders. The join condition uses = to match customer IDs.

5fill in blank

hard

Fill all three blanks to write a query that selects customers with orders after 2023-01-01 using a JOIN and a WHERE filter.

SQL

SELECT c.name, o.order_date FROM customers c [1] JOIN orders o ON c.customer_id [2] o.customer_id WHERE o.order_date [3] '2023-01-01';

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

AINNER

B=

C>

DLEFT

Attempts:

3 left

💡 Hint

Common Mistakes

Using LEFT JOIN which includes customers without orders.

Using < or = in the date filter instead of >.

An INNER JOIN returns customers with matching orders. The join condition uses = to match IDs. The WHERE clause filters orders after 2023-01-01 using >.