Scalar and Aggregate Functions Coding Practice Challenges | CodeNivo

Question 1

18. Practical Example: Search for a Substring

Accepted Answer

SELECT
  product_id,
  product_name
FROM products
WHERE POSITION('pro' IN product_name) > 0;

Answer

-- Practice query for String Functions

Question 2

13. Calculating a Future Deadline

Accepted Answer

SELECT
    task_id,
    created_at,
    created_at + INTERVAL '7 days' AS deadline
FROM tasks;

Answer

-- Practice query for Date and Time Functions

Question 3

15. Real-World Example: Product Pricing

Accepted Answer

SELECT
    product_id,
    price,
    discount_percent,
    ROUND(
        (price * discount_percent / 100)::numeric,
        2
    ) AS discount_amount,
    ROUND(
        (price - price * discount_percent / 100)::numeric,
        2
    ) AS final_price
FROM products
ORDER BY product_id;

Answer

-- Practice query for Numeric Functions

Question 4

16. Finding Recent Records with NOW()

Accepted Answer

SELECT order_id, created_at
FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days'
  AND created_at <= NOW()
ORDER BY created_at DESC;

Answer

-- Practice query for Date and Time Functions

Question 5

18. Grouping Data by Month

Accepted Answer

SELECT
    DATE_TRUNC('month', created_at) AS month_start,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month_start;

Answer

-- Practice query for Date and Time Functions

Question 6

17. Practical Example: Normalize Email Text

Accepted Answer

SELECT
  customer_id,
  email AS original_email,
  LOWER(TRIM(email)) AS normalized_email
FROM customers
WHERE email IS NOT NULL;

Answer

-- Practice query for String Functions

Question 7

12. Practical Example: Even and Odd Numbers

Accepted Answer

SELECT
    value,
    CASE
        WHEN MOD(value, 2) = 0 THEN 'Even'
        ELSE 'Odd'
    END AS number_type
FROM numbers
ORDER BY value;

Answer

-- Practice query for Numeric Functions

Question 8

10. Practical Example: Finding Large Differences

Accepted Answer

SELECT
    product_id,
    expected_stock,
    actual_stock,
    ABS(expected_stock - actual_stock) AS stock_difference
FROM inventory
ORDER BY stock_difference DESC;

Answer

-- Practice query for Numeric Functions

Question 9

16. Practical Example: Clean Customer Names

Accepted Answer

SELECT
  customer_id,
  full_name AS original_name,
  INITCAP(TRIM(full_name)) AS cleaned_name
FROM customers
ORDER BY customer_id;

Answer

-- Practice query for String Functions

order_id	created_at
203	2026-10-18 09:30:00+05:30
201	2026-10-15 14:20:00+05:30

month_start	order_count
2026-08-01 00:00:00+05:30	120
2026-09-01 00:00:00+05:30	145
2026-10-01 00:00:00+05:30	98

product_id	price	discount_percent	discount_amount	final_price
1	100.00	10	10.00	90.00
2	249.99	15	37.50	212.49

Scalar and Aggregate Functions Problems