String/Math Functions

1. Find the character count of product names. Top 10 longest na

Find the character count of product names. Top 10 longest names.

Hint 1: Get string length with LENGTH(name). ORDER BY LENGTH(name) DESC LIMIT 10.

SELECT
    name,
    LENGTH(name) AS name_length
FROM products
ORDER BY name_length DESC
LIMIT 10;

2. Convert all brand names to uppercase and list unique brands.

Convert all brand names to uppercase and list unique brands.

Hint 1: Convert to uppercase with UPPER(brand), remove duplicates with DISTINCT.

SELECT DISTINCT UPPER(brand) AS brand_upper
FROM products
ORDER BY brand_upper;

3. Remove the brand portion from product names and extract only

Remove the brand portion from product names and extract only the remaining name.

Hint 1: Extract the string after brand name + space with SUBSTR(name, LENGTH(brand) + 2).

SELECT
    brand,
    name,
    SUBSTR(name, LENGTH(brand) + 2) AS model_name
FROM products
WHERE name LIKE brand || ' %'
ORDER BY brand, model_name
LIMIT 15;

4. Extract only the ID portion before '@' from customer emails.

Extract only the ID portion before '@' from customer emails.

Hint 1: SUBSTR(email, 1, INSTR(email, '@') - 1) cuts to before '@'. INSTR returns the position of a specific character in a string.

SELECT
    email,
    SUBSTR(email, 1, INSTR(email, '@') - 1) AS user_id
FROM customers
ORDER BY user_id
LIMIT 15;

5. Remove color information ('블랙', '화이트', '실버') from product na

Remove color information ('블랙', '화이트', '실버') from product names.

Hint 1: Nest REPLACE to remove multiple strings. Clean up trailing spaces with TRIM.

SELECT
    name,
    TRIM(REPLACE(REPLACE(REPLACE(name, '블랙', ''), '화이트', ''), '실버', '')) AS name_no_color
FROM products
WHERE name LIKE '%블랙%' OR name LIKE '%화이트%' OR name LIKE '%실버%'
ORDER BY name
LIMIT 15;

6. Create phone numbers with hyphens (-) removed.

Create phone numbers with hyphens (-) removed.

Hint 1: Replace hyphens with empty string using REPLACE(phone, '-', '').

SELECT
    name,
    phone,
    REPLACE(phone, '-', '') AS phone_no_dash,
    LENGTH(REPLACE(phone, '-', '')) AS digit_count
FROM customers
LIMIT 10;

7. Extract the category abbreviation (first 2 characters) from

Extract the category abbreviation (first 2 characters) from product SKU codes.

Hint 1: Extract the first 2 characters of SKU with SUBSTR(sku, 1, 2). SKU format is like LA-GEN-삼성-00001.

SELECT
    sku,
    SUBSTR(sku, 1, 2) AS category_code,
    name
FROM products
ORDER BY category_code, sku
LIMIT 15;

8. List brands per category, separated by commas.

List brands per category, separated by commas.

Hint 1: Combine unique brands within a category into a comma-separated string with GROUP_CONCAT(DISTINCT brand, ', ').

SELECT
    cat.name AS category,
    COUNT(DISTINCT p.brand) AS brand_count,
    GROUP_CONCAT(DISTINCT p.brand, ', ') AS brands
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE p.is_active = 1
GROUP BY cat.id, cat.name
ORDER BY brand_count DESC
LIMIT 10;

9. Extract the model number after RTX from products containing

Extract the model number after RTX from products containing 'RTX' in their name.

Hint 1: Find the position of 'RTX' with INSTR(name, 'RTX'), then extract the model number with SUBSTR(name, INSTR(...) + 4, 4).

SELECT
    name,
    SUBSTR(name, INSTR(name, 'RTX') + 4, 4) AS rtx_model
FROM products
WHERE name LIKE '%RTX%'
ORDER BY rtx_model DESC;

10. Round order amounts to the nearest 10,000 won. Top 10 by amo

Round order amounts to the nearest 10,000 won. Top 10 by amount.

Hint 1: ROUND(total_amount, -4) rounds to the nearest 10,000. Negative decimal places round the integer part.

SELECT
    order_number,
    total_amount,
    ROUND(total_amount, -4) AS rounded_10k,
    ROUND(total_amount, -4) / 10000 AS in_man_won
FROM orders
WHERE status NOT IN ('cancelled')
ORDER BY total_amount DESC
LIMIT 10;

11. Safely handle products with zero margin. Use NULLIF to preve

Safely handle products with zero margin. Use NULLIF to prevent division by zero.

Hint 1: NULLIF(cost_price, 0) returns NULL if cost_price is 0. Dividing by NULL results in NULL, avoiding errors.

SELECT
    name,
    price,
    cost_price,
    ROUND(100.0 * (price - cost_price) / NULLIF(cost_price, 0), 1) AS margin_pct
FROM products
WHERE is_active = 1
ORDER BY margin_pct DESC NULLS LAST
LIMIT 15;

12. Convert order amounts to text format ('1,234,567원').

Convert order amounts to text format ('1,234,567원').

Hint 1: Add thousands separators with printf('%,d', total_amount). Append the unit with ||.

SELECT
    order_number,
    total_amount,
    printf('%,d', CAST(total_amount AS INTEGER)) || '원' AS formatted_amount
FROM orders
WHERE status NOT IN ('cancelled')
ORDER BY total_amount DESC
LIMIT 10;

13. Classify product prices into range labels. Use IIF or CASE.

Classify product prices into range labels. Use IIF or CASE.

Hint 1: IIF(condition, true, false) is SQLite's simple conditional function. For multiple conditions, CASE WHEN is more suitable.

SELECT
    name,
    price,
    CASE
        WHEN price < 100000 THEN '10만원 미만'
        WHEN price < 500000 THEN '10~50만원'
        WHEN price < 1000000 THEN '50~100만원'
        WHEN price < 2000000 THEN '100~200만원'
        ELSE '200만원 이상'
    END AS price_range,
    IIF(price >= 1000000, '고가', '일반') AS price_class
FROM products
WHERE is_active = 1
ORDER BY price DESC
LIMIT 15;

14. Convert numbers stored as strings to integer/real. Type conv

Convert numbers stored as strings to integer/real. Type conversion of stock quantities.

Hint 1: Convert types with CAST(value AS INTEGER) or CAST(value AS REAL). Check current type with TYPEOF().

SELECT
    name,
    stock_qty,
    TYPEOF(stock_qty) AS original_type,
    CAST(stock_qty AS REAL) AS stock_as_real,
    TYPEOF(CAST(stock_qty AS REAL)) AS converted_type,
    CAST(price AS INTEGER) AS price_int
FROM products
WHERE is_active = 1
ORDER BY stock_qty DESC
LIMIT 10;

15. Create a name list per customer grade. Only first 5 per grad

Create a name list per customer grade. Only first 5 per grade, comma-separated.

Hint 1: Number within each grade with ROW_NUMBER() in a subquery, filter to 5, then combine with GROUP_CONCAT.

SELECT
    grade,
    COUNT(*) AS total_count,
    GROUP_CONCAT(name, ', ') AS sample_names
FROM (
    SELECT
        name,
        grade,
        ROW_NUMBER() OVER (PARTITION BY grade ORDER BY created_at) AS rn
    FROM customers
    WHERE is_active = 1
)
WHERE rn <= 5
GROUP BY grade
ORDER BY total_count DESC;

16. Separate date and sequence number from order numbers. Format

Separate date and sequence number from order numbers. Format: ORD-YYYYMMDD-NNNNN.

Hint 1: Extract the date part (YYYYMMDD) with SUBSTR(order_number, 5, 8) and sequence number with SUBSTR(order_number, 14).

SELECT
    order_number,
    SUBSTR(order_number, 5, 8) AS date_part,
    SUBSTR(order_number, 5, 4) || '-' || SUBSTR(order_number, 9, 2) || '-' || SUBSTR(order_number, 11, 2) AS formatted_date,
    CAST(SUBSTR(order_number, 14) AS INTEGER) AS sequence_no
FROM orders
ORDER BY ordered_at DESC
LIMIT 10;

17. Find only products whose names contain capacity/size info (n

Find only products whose names contain capacity/size info (number+GB/TB/MHz/W).

Hint 1: GLOB is case-sensitive pattern matching. Find patterns with numbers followed by units like *[0-9]GB*, *[0-9]TB*.

SELECT name, price, brand
FROM products
WHERE name GLOB '*[0-9]GB*'
   OR name GLOB '*[0-9]TB*'
   OR name GLOB '*[0-9]MHz*'
   OR name GLOB '*[0-9]W*'
ORDER BY price DESC
LIMIT 15;

18. Product summary card: Format product info into a single-line

Product summary card: Format product info into a single-line string.

Hint 1: Combine || (string concatenation), COALESCE, and printf for formatting. Handle NULL values with COALESCE.

SELECT
    '[' || brand || '] ' || name
    || ' | ' || printf('%,d', CAST(price AS INTEGER)) || '원'
    || ' | 재고: ' || stock_qty || '개'
    || ' | ' || COALESCE('무게: ' || (weight_grams / 1000.0) || 'kg', '무게 미정')
    AS product_card
FROM products
WHERE is_active = 1
ORDER BY price DESC
LIMIT 5;

19. Email domain analysis by signup channel: Cross-tabulation of

Email domain analysis by signup channel: Cross-tabulation of signup channel and email domain.

Hint 1: Extract domain with SUBSTR(email, INSTR(email, '@') + 1). Handle NULL with COALESCE(acquisition_channel, '미확인').

SELECT
    COALESCE(acquisition_channel, '미확인') AS channel,
    SUBSTR(email, INSTR(email, '@') + 1) AS domain,
    COUNT(*) AS customer_count
FROM customers
GROUP BY COALESCE(acquisition_channel, '미확인'),
         SUBSTR(email, INSTR(email, '@') + 1)
ORDER BY customer_count DESC
LIMIT 10;

20. Comprehensive product report: Summarize price range, margin

Comprehensive product report: Summarize price range, margin rate, stock status, and sales activity in one string.

Hint 1: Combine multiple functions (CASE, ROUND, IIF, printf, COALESCE, ||) to generate a comprehensive report.

SELECT
    name,
    CASE
        WHEN price < 100000 THEN '저가'
        WHEN price < 500000 THEN '중가'
        WHEN price < 1000000 THEN '중고가'
        ELSE '고가'
    END AS price_tier,
    printf('%.1f%%', 100.0 * (price - cost_price) / NULLIF(cost_price, 0)) AS margin_pct,
    CASE
        WHEN stock_qty = 0 THEN '품절'
        WHEN stock_qty < 10 THEN '부족'
        WHEN stock_qty < 50 THEN '보통'
        ELSE '충분'
    END AS stock_status,
    IIF(is_active = 1, '판매중', '단종') AS sale_status,
    printf('%,d원', CAST(price AS INTEGER)) AS display_price
FROM products
ORDER BY price DESC
LIMIT 15;