SQL Error Detection -- Beginner

1. Find and fix the error in the following query.

Find and fix the error in the following query.

Hint 1: Check the required separator when listing columns in the SELECT clause.

SELECT name, price, brand
FROM products
LIMIT 5;

2. Find and fix the error in the following query.

Find and fix the error in the following query.

Hint 1: Check the type of quotes used for string literals in the SQL standard. It may work in SQLite, but it's problematic from a standard SQL perspective.

SELECT name, price
FROM products
WHERE brand = 'ASUS';

3. Find and fix the error in the following query.

Find and fix the error in the following query.

Hint 1: Check the spelling of the SQL keyword.

SELECT name, email
FROM customers
WHERE grade = 'VIP';

4. Find and fix the error in the following query.

Find and fix the error in the following query.

Hint 1: This query is actually syntactically correct. Think about the default sort direction of ORDER BY price. What if the intent is "most expensive first"?

SELECT name, price
FROM products
WHERE price BETWEEN 100000 AND 500000
ORDER BY price DESC
LIMIT 10;

5. Find and fix the error in the following query.

Find and fix the error in the following query.

Hint 1: Check the exact keyword used for sorting.

SELECT name, email, grade
FROM customers
WHERE grade = 'VIP'
ORDER BY name;

6. Find and fix the error in the following query.

Find and fix the error in the following query.

Hint 1: Check the position of the quotes in the LIKE pattern. The % must be inside the string.

SELECT name, price
FROM products
WHERE name LIKE '%게이밍%';

7. Find and fix the error in the following query.

Find and fix the error in the following query.

Hint 1: Check the order of SQL clauses. Where should WHERE be positioned?

SELECT name, price, stock_qty
FROM products
WHERE price > 100000
  AND stock_qty > 0
  AND is_active = 1
ORDER BY price DESC
LIMIT 10;

8. Find and fix the error in the following query.

Find and fix the error in the following query.

Hint 1: Even when assigning column aliases in the SELECT clause, something is needed between columns.

SELECT name AS 상품명, price AS 가격
FROM products
LIMIT 5;

9. Find and fix the error in the following query.

Find and fix the error in the following query.

Hint 1: Check the separator between values in the IN list.

SELECT name, price
FROM products
WHERE brand IN ('ASUS', 'MSI', 'Dell');

10. Find and fix the error in the following query.

Find and fix the error in the following query.

Hint 1: Check the separator between columns in the SELECT list.

SELECT COUNT(*) AS total,
       AVG(price) AS avg_price,
       SUM(price) AS total_price
FROM products;

11. The following query tries to find customers without a birth

The following query tries to find customers without a birth date. The result returns 0 rows. Why?

Hint 1: In SQL, NULL means "unknown value." What happens when you compare with =? The result is always something specific.

SELECT name, email
FROM customers
WHERE birth_date IS NULL;

12. The following query tries to find the product count per bran

The following query tries to find the product count per brand. It throws an error.

Hint 1: There is a required clause when using an aggregate function alongside a non-aggregate column in SELECT.

SELECT brand, COUNT(*) AS product_count
FROM products
GROUP BY brand;

13. The following query tries to show only brands with 10 or mor

The following query tries to show only brands with 10 or more products. It throws an error.

Hint 1: Can aggregate functions be used in the WHERE clause?

SELECT brand, COUNT(*) AS product_count
FROM products
GROUP BY brand
HAVING COUNT(*) >= 10;

14. The following query tries to find products that are not 'ASU

The following query tries to find products that are not 'ASUS'. However, products with a NULL brand are missing from the results.

Hint 1: What is the result of NULL != 'ASUS'? Comparisons with NULL always return NULL (UNKNOWN).

SELECT name, brand
FROM products
WHERE brand != 'ASUS' OR brand IS NULL;

15. The following query tries to find the top 10 orders by amoun

The following query tries to find the top 10 orders by amount. The ORDER BY placement is wrong.

Hint 1: Think about the execution order of SQL clauses. Which should come first, LIMIT or ORDER BY?

SELECT order_number, total_amount
FROM orders
ORDER BY total_amount DESC
LIMIT 10;

16. The following query sorts the average points per tier in des

The following query sorts the average points per tier in descending order. It uses an alias in ORDER BY, which may not work in some cases.

Hint 1: This query works correctly in SQLite. However, some databases have restrictions on using aliases in ORDER BY. What alternative exists?

SELECT grade,
       AVG(point_balance) AS avg_points
FROM customers
GROUP BY grade
ORDER BY AVG(point_balance) DESC;

17. The following query tries to find only customers where gende

The following query tries to find only customers where gender is not NULL. The logic is wrong.

Hint 1: Same principle as Problem 11. The != comparison with NULL also returns NULL.

SELECT name, gender
FROM customers
WHERE gender IS NOT NULL;

18. The following query finds the count per order status and fil

The following query finds the count per order status and filters with HAVING. But the intent is wrong.

Hint 1: HAVING is meant for filtering aggregate results. What clause should be used for simple column value filtering?

SELECT status, COUNT(*) AS order_count
FROM orders
WHERE status = 'confirmed'
GROUP BY status;

19. The following query finds the count per brand for products p

The following query finds the count per brand for products priced 1M+. There is an issue with GROUP BY.

Hint 1: All non-aggregate columns in SELECT must be included in GROUP BY. What about name?

SELECT brand, COUNT(*) AS cnt
FROM products
WHERE price >= 1000000
GROUP BY brand;

20. The following query finds the order count per year. The resu

The following query finds the order count per year. The results look wrong.

Hint 1: ordered_at contains both date and time. How should you group by year?

SELECT SUBSTR(ordered_at, 1, 4) AS year, COUNT(*) AS order_count
FROM orders
GROUP BY SUBSTR(ordered_at, 1, 4)
ORDER BY year;

21. The following query calculates the margin rate (%) for produ

The following query calculates the margin rate (%) for products. Some products show a margin of 0%.

Hint 1: What happens when you divide two integers in SQLite?

SELECT name, price, cost_price,
       ROUND((price - cost_price) * 100.0 / price, 1) AS margin_pct
FROM products
WHERE is_active = 1
LIMIT 10;

22. The following query has a CASE expression missing the ELSE c

The following query has a CASE expression missing the ELSE clause. What problem can occur?

Hint 1: What value will products priced 1M+ receive?

SELECT name, price,
       CASE
           WHEN price < 100000 THEN '저가'
           WHEN price < 500000 THEN '중저가'
           WHEN price < 1000000 THEN '중가'
           ELSE '고가'
       END AS price_tier
FROM products;

23. The following query uses an alias in HAVING. It may throw an

The following query uses an alias in HAVING. It may throw an error.

Hint 1: SQLite allows using aliases in HAVING, but what about MySQL, PostgreSQL, etc.?

SELECT brand, COUNT(*) AS cnt
FROM products
GROUP BY brand
HAVING COUNT(*) >= 10;

24. The following query calculates the discount rate for orders.

The following query calculates the discount rate for orders. A division-by-zero error may occur.

Hint 1: What happens if total_amount is 0?

SELECT order_number,
       total_amount,
       discount_amount,
       CASE
           WHEN total_amount = 0 THEN 0
           ELSE ROUND(discount_amount * 100.0 / total_amount, 1)
       END AS discount_rate
FROM orders
ORDER BY discount_rate DESC
LIMIT 10;

25. The following query finds monthly statistics for 2024 orders

The following query finds monthly statistics for 2024 orders. The month sorting is wrong.

Hint 1: For "monthly statistics," it's natural to view from January to December in chronological order. What is the current sort criterion?

SELECT SUBSTR(ordered_at, 6, 2) AS month,
       COUNT(*) AS order_count
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY month
ORDER BY month;

26. The following query finds the average and total points per t

The following query finds the average and total points per tier. ROUND is applied to only one place.

Hint 1: The same value (AVG(point_balance)) is queried twice -- one with ROUND, one without. This isn't an error per se, but avg_points_raw will show a long decimal number.

SELECT grade,
       ROUND(AVG(point_balance)) AS avg_points,
       SUM(point_balance) AS total_points,
       COUNT(*) AS customer_count
FROM customers
GROUP BY grade;

27. The following query finds products containing 'Black' or 'Wh

The following query finds products containing 'Black' or 'White' in the name. The results are more than expected.

Hint 1: Check the operator precedence of AND and OR. Which is evaluated first?

SELECT name, price
FROM products
WHERE (name LIKE '%블랙%' OR name LIKE '%화이트%')
  AND price > 500000;

28. The following query calculates the sum of columns that may c

The following query calculates the sum of columns that may contain NULL. The results differ from expectations.

Hint 1: If discount_amount or shipping_fee is NULL, SUM ignores NULLs, but what about discount_amount + shipping_fee?

SELECT COUNT(*) AS total_orders,
       SUM(COALESCE(discount_amount, 0)) + SUM(COALESCE(shipping_fee, 0)) AS extra_charges,
       AVG(COALESCE(discount_amount, 0) + COALESCE(shipping_fee, 0)) AS avg_extra
FROM orders;

29. The following query finds product count by price tier. The C

The following query finds product count by price tier. The CASE condition order is wrong.

Hint 1: CASE evaluates conditions top-to-bottom and stops at the first TRUE condition. All product prices are >= 0.

SELECT CASE
           WHEN price >= 1000000 THEN '고가'
           WHEN price >= 500000 THEN '중가'
           WHEN price >= 100000 THEN '중저가'
           ELSE '저가'
       END AS price_tier,
       COUNT(*) AS cnt
FROM products
GROUP BY price_tier;

30. The following query analyzes customer data by signup year. W

The following query analyzes customer data by signup year. WHERE and HAVING are confused.

Hint 1: HAVING is used twice, and the SQL clause order is also wrong. Distinguish between row-level filtering and group-level filtering.

SELECT SUBSTR(created_at, 1, 4) AS join_year,
       grade,
       COUNT(*) AS customer_count,
       ROUND(AVG(point_balance)) AS avg_points
FROM customers
WHERE grade IN ('GOLD', 'VIP')
GROUP BY join_year, grade
HAVING COUNT(*) >= 50
ORDER BY join_year, grade;