Sorting and Paging

1. Sort the products in order of highest price and look up the

Sort the products in order of highest price and look up the name (name) and price (price).

Hint 1: Using ORDER BY price DESC sorts in descending order

SELECT name, price
FROM products
ORDER BY price DESC;

2. Sort products in descending order of price and search for na

Sort products in descending order of price and search for name, brand (brand), and price.

Hint 1: Just using ORDER BY price ASC or ORDER BY price will result in ascending order (default)

SELECT name, brand, price
FROM products
ORDER BY price ASC;

3. View the names and prices of the 5 most expensive products.

View the names and prices of the 5 most expensive products.

Hint 1: Sort by ORDER BY and then get only the top N by LIMIT

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

4. View the name, price, and inventory quantity of the five pro

View the name, price, and inventory quantity of the five products with the lowest inventory quantity (stock_qty).

Hint 1: If you sort by ORDER BY stock_qty ASC in ascending order, the inventory is low

SELECT name, price, stock_qty
FROM products
ORDER BY stock_qty ASC
LIMIT 5;

5. Sort customers in order of sign-up date (created_at) to vi

Sort customers in order of sign-up date (created_at) to view the names, emails, ratings (grade), and sign-up dates of the five customers who signed up first.

Hint 1: Use ORDER BY created_at ASC to list the oldest subscription date first

SELECT name, email, grade, created_at
FROM customers
ORDER BY created_at ASC
LIMIT 5;

6. Sort product names in alphabetical/alphabetical order to vie

Sort product names in alphabetical/alphabetical order to view the first 5 items.

Hint 1: When sorted by ORDER BY name, the English alphabet comes before the Korean alphabet

SELECT name, price
FROM products
ORDER BY name
LIMIT 5;

7. View the top 6-10 most expensive products (i.e. skip the top

View the top 6-10 most expensive products (i.e. skip the top 5 and go to the next 5).

Hint 1: OFFSET skips the specified number of rows. Fetch from the 6th with LIMIT 5 OFFSET 5

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5 OFFSET 5;

8. View the names, levels, and points of the 5 customers with t

View the names, levels, and points of the 5 customers with the most points (point_balance).

Hint 1: Sort in descending order with ORDER BY point_balance DESC and then apply LIMIT 5

SELECT name, grade, point_balance
FROM customers
ORDER BY point_balance DESC
LIMIT 5;

9. Look up the order number (order_number), order amount, and

Look up the order number (order_number), order amount, and order date (ordered_at) of the five orders with the largest order amount (total_amount).

Hint 1: Use ORDER BY total_amount DESC LIMIT 5 in table orders

SELECT order_number, total_amount, ordered_at
FROM orders
ORDER BY total_amount DESC
LIMIT 5;

10. View the list of brands registered in the product table in a

View the list of brands registered in the product table in alphabetical order without duplication.

Hint 1: Remove duplicates with SELECT DISTINCT brand and sort with ORDER BY brand

SELECT DISTINCT brand
FROM products
ORDER BY brand;

11. Sort products with a price of 1 million won or more in desce

Sort products with a price of 1 million won or more in descending order of price and search for name, brand, and price. Prints only the top 5.

Hint 1: Filter by WHERE price >= 1000000 and then apply ORDER BY price DESC LIMIT 5

SELECT name, brand, price
FROM products
WHERE price >= 1000000
ORDER BY price DESC
LIMIT 5;

12. Sort Samsung Electronics or LG Electronics brand products in

Sort Samsung Electronics or LG Electronics brand products in descending price order to search for name, brand, and price. Prints only the top 5.

Hint 1: Filter the two brands by WHERE brand IN ('삼성전자', 'LG전자')

SELECT name, brand, price
FROM products
WHERE brand IN ('삼성전자', 'LG전자')
ORDER BY price DESC
LIMIT 5;

13. Among VIP level customers, sort the customers with the last

Among VIP level customers, sort the customers with the last name 'Kim' in order of the highest amount of points and view the names, levels, and points of only 5 people.

Hint 1: Apply both conditions simultaneously with WHERE grade = 'VIP' AND name LIKE '김%'

SELECT name, grade, point_balance
FROM customers
WHERE grade = 'VIP'
  AND name LIKE '김%'
ORDER BY point_balance DESC
LIMIT 5;

14. Sort discontinued products (products where discontinued_at

Sort discontinued products (products where discontinued_at is not NULL) in the most recent order based on the discontinuation date to view only 5 names, prices, and discontinuation dates.

Hint 1: Filter only discontinued products with WHERE discontinued_at IS NOT NULL

SELECT name, price, discontinued_at
FROM products
WHERE discontinued_at IS NOT NULL
ORDER BY discontinued_at DESC
LIMIT 5;

15. Sort reviews with a review rating (rating) of 1 in the mos

Sort reviews with a review rating (rating) of 1 in the most recent order and view the 5 titles (title), rating, and creation date (created_at).

Hint 1: In table reviews, filter by WHERE rating = 1 and sort by ORDER BY created_at DESC

SELECT title, rating, created_at
FROM reviews
WHERE rating = 1
ORDER BY created_at DESC
LIMIT 5;

16. Sort products priced between 500,000 won and 1 million won o

Sort products priced between 500,000 won and 1 million won or less in ascending order of price and search for 5 names, brands, and prices.

Hint 1: Specify the range with WHERE price BETWEEN 500000 AND 1000000

SELECT name, brand, price
FROM products
WHERE price BETWEEN 500000 AND 1000000
ORDER BY price ASC
LIMIT 5;

17. Among card payments (method = 'card'), search for 5 cases

Among card payments (method = 'card'), search for 5 cases where the number of installment months (installment_months) is greater than 0 by sorting them in descending order of the number of installment months and descending order of payment amount (amount). Prints order ID, payment amount, card company (card_issuer), and number of installment months.

Hint 1: Multiple sorting occurs if you separate the columns after ORDER BY with a comma. Sort by the second column when the first column is the same

SELECT order_id, amount, card_issuer, installment_months
FROM payments
WHERE method = 'card'
  AND installment_months > 0
ORDER BY installment_months DESC, amount DESC
LIMIT 5;

18. Sort GOLD level customers alphabetically by name to view the

Sort GOLD level customers alphabetically by name to view the names and emails of customers 11-15.

Hint 1: OFFSET 10 (skip 10) to start with the 11th, LIMIT 5 to get 5 people

SELECT name, email
FROM customers
WHERE grade = 'GOLD'
ORDER BY name
LIMIT 5 OFFSET 10;

19. Check the type of payment method (method) without duplicat

Check the type of payment method (method) without duplicates.

Hint 1: Use SELECT DISTINCT method FROM payments

SELECT DISTINCT method
FROM payments
ORDER BY method;

20. Search 5 items by sorting the product name, price, cost (`co

Search 5 items by sorting the product name, price, cost (cost_price), and margin (price - cost) in order of largest margin. Add the alias margin to the margin column.

Hint 1: You can create a calculated column as price - cost_price AS margin in the SELECT clause

SELECT name, price, cost_price, price - cost_price AS margin
FROM products
ORDER BY margin DESC
LIMIT 5;

21. Out of the canceled orders (status = 'cancelled'), look up

Out of the canceled orders (status = 'cancelled'), look up the order number, order amount, and cancellation date (cancelled_at) of the five orders with the largest order amount.

Hint 1: Filter by WHERE status = 'cancelled' and sort by ORDER BY total_amount DESC

SELECT order_number, total_amount, cancelled_at
FROM orders
WHERE status = 'cancelled'
ORDER BY total_amount DESC
LIMIT 5;

22. For products with less than 10 items in stock, sort them in

For products with less than 10 items in stock, sort them in ascending order of stock or descending price to view the name, price, and quantity in stock.

Hint 1: Filter by WHERE stock_qty <= 10 and multi-sort by ORDER BY stock_qty ASC, price DESC

SELECT name, price, stock_qty
FROM products
WHERE stock_qty <= 10
ORDER BY stock_qty ASC, price DESC;

23. View 10 customers who have never logged in (last_login_at

View 10 customers who have never logged in (last_login_at is NULL) by name, email, and rating by sorting them in order of sign-up date.

Hint 1: NULL is checked with IS NULL, not = NULL

SELECT name, email, grade
FROM customers
WHERE last_login_at IS NULL
ORDER BY created_at ASC
LIMIT 10;

24. Check the product name, price, and price including VAT. Calc

Check the product name, price, and price including VAT. Calculate the price including VAT as price * 1.1 and add the aliases price_with_tax. Only 5 items will be printed in order of highest price including VAT.

Hint 1: You can get neat results by organizing the decimal points with ROUND(price * 1.1)

SELECT name, price, ROUND(price * 1.1) AS price_with_tax
FROM products
ORDER BY price_with_tax DESC
LIMIT 5;

25. Look up the order number, order amount, and order date of th

Look up the order number, order amount, and order date of the five orders with the largest order amount among orders received in 2024 (ordered_at is 2024).

Hint 1: Specify the range to 2024 with WHERE ordered_at >= '2024-01-01' AND ordered_at < '2025-01-01'

SELECT order_number, total_amount, ordered_at
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at < '2025-01-01'
ORDER BY total_amount DESC
LIMIT 5;

26. Order orders with a discount amount (discount_amount) grea

Order orders with a discount amount (discount_amount) greater than 0 in descending order of discount amount, and view 5 order numbers, order amount, discount amount, and order date.

Hint 1: Filter only orders with a discount applied to WHERE discount_amount > 0

SELECT order_number, total_amount, discount_amount, ordered_at
FROM orders
WHERE discount_amount > 0
ORDER BY discount_amount DESC
LIMIT 5;

27. Sort the items paid with Kakao Pay (method = 'kakao_pay')

Sort the items paid with Kakao Pay (method = 'kakao_pay') in order of largest payment amount and search for 5 items including order ID (order_id), payment amount, and payment date (paid_at).

Hint 1: Filter by WHERE method = 'kakao_pay' in table payments

SELECT order_id, amount, paid_at
FROM payments
WHERE method = 'kakao_pay'
ORDER BY amount DESC
LIMIT 5;

28. Find 5 customers who signed up in 2025 who are GOLD or VIP l

Find 5 customers who signed up in 2025 who are GOLD or VIP level in order of highest accumulated points. Prints name, level, points, and membership date.

Hint 1: Combine conditions with WHERE created_at >= '2025-01-01' AND grade IN ('GOLD', 'VIP')

SELECT name, grade, point_balance, created_at
FROM customers
WHERE created_at >= '2025-01-01'
  AND grade IN ('GOLD', 'VIP')
ORDER BY point_balance DESC
LIMIT 5;

29. View 5 non-ASUS brand products (brand != 'ASUS') excluding

View 5 non-ASUS brand products (brand != 'ASUS') excluding Samsung Electronics, LG Electronics, and MSI in descending order of price. Prints name, brand, and price.

Hint 1: NOT IN allows you to exclude multiple values at once

SELECT name, brand, price
FROM products
WHERE brand NOT IN ('ASUS', '삼성전자', 'LG전자', 'MSI')
ORDER BY price DESC
LIMIT 5;

30. Look up the names, weights (grams), and prices of the five h

Look up the names, weights (grams), and prices of the five heaviest products among products with recorded weight (weight_grams). Also print the weight_kg column, which converts the weight to kilograms.

Hint 1: Filter only products with weight by WHERE weight_grams IS NOT NULL. Calculate kilograms with weight_grams / 1000.0

SELECT name,
       weight_grams,
       ROUND(weight_grams / 1000.0, 1) AS weight_kg,
       price
FROM products
WHERE weight_grams IS NOT NULL
ORDER BY weight_grams DESC
LIMIT 5;