Sorting and Limiting Results Coding Practice Challenges | CodeNivo

Question 1

22. Practice Solutions

Accepted Answer

-- Task 1
SELECT employee_id, salary
FROM employees
WHERE salary IS NULL;

-- Task 2
SELECT customer_id, phone
FROM customers
WHERE phone IS NOT NULL;

-- Task 3
SELECT
  customer_id,
  COALESCE(city, 'Unknown') AS city_display
FROM customers;

-- Task 4
SELECT
  product_id,
  price - COALESCE(discount_amount, 0) AS final_price
FROM products;

-- Task 5
SELECT
  order_id,
  revenue / NULLIF(quantity, 0) AS revenue_per_unit
FROM order_items;

-- Task 6
SELECT
  customer_id,
  COALESCE(NULLIF(email, ''), 'No Email') AS email_display
FROM customers;

-- Task 7
SELECT
  COUNT(*) AS total_employees,
  COUNT(salary) AS employees_with_salary
FROM employees;

-- Task 8
SELECT c.customer_id, c.first_name
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Answer

-- Practice query for Working with NULL Values

Question 2

11. Formatting a More Complex Query

Accepted Answer

-- Report completed and processing orders for review.
SELECT
  o.order_id,
  c.company_name,
  c.contact_email,
  o.total_amount,
  o.status
FROM orders AS o
INNER JOIN customers AS c
  ON o.customer_id = c.customer_id
WHERE o.order_date >= DATE '2026-01-01'
  AND o.status IN ('completed', 'processing')
ORDER BY
  o.total_amount DESC,
  o.order_id ASC
LIMIT 25;

Answer

-- Practice query for SQL Comments and Query Formatting

Question 3

5. Practical Example: Incomplete User Profiles

Accepted Answer

SELECT
  user_id,
  full_name,
  email,
  phone_number
FROM users
WHERE phone_number IS NULL
ORDER BY user_id ASC;

Answer

-- Practice query for Using IS NULL and IS NOT NULL

Question 4

21. Guided Practice

Accepted Answer

-- Task 1:
-- Find employees whose salary is NULL.

-- Task 2:
-- Find customers whose phone is not NULL.

-- Task 3:
-- Display 'Unknown' when a customer's city is NULL.

-- Task 4:
-- Calculate price minus discount_amount,
-- treating a NULL discount as zero.

-- Task 5:
-- Calculate revenue per order only when quantity
-- is not zero, returning NULL when quantity is zero.

-- Task 6:
-- Display 'No Email' for both NULL and empty
-- email strings.

-- Task 7:
-- Count all employees and employees whose
-- salary is not NULL.

-- Task 8:
-- Find customers with no matching orders
-- using a LEFT JOIN.

Answer

-- Practice query for Working with NULL Values

Question 5

18. Guided Practice

Accepted Answer

-- Task 1:
-- Find users whose email_verified_at is NULL.

-- Task 2:
-- Find products whose description is not NULL.

-- Task 3:
-- Find active articles where deleted_at is NULL.

-- Task 4:
-- Find customers whose email is either NULL
-- or an empty string.

-- Task 5:
-- Compare old_data.status and new_data.status
-- and return rows where the values differ,
-- including differences involving NULL.

-- Task 6:
-- Find employees without a department assignment.

-- Task 7:
-- Write a query that matches two nullable
-- optional_code columns using null-safe equality.

-- Task 8:
-- Create a partial index for active articles
-- ordered by published_at and article_id.

Answer

-- Practice query for Using IS NULL and IS NOT NULL

Question 6

20. Guided Practice

Accepted Answer

-- Task 1:
-- Return the first 5 employees ordered by emp_id.

-- Task 2:
-- Return page 4 of products with 10 products per page.

-- Task 3:
-- Return the next 10 products after product_id 250
-- using keyset pagination.

-- Task 4:
-- Return the next 5 highest-paid employees after
-- skipping the first 5, with emp_id as the tie-breaker.

-- Task 5:
-- Write a query using FETCH FIRST to return
-- at most 3 employees ordered by salary descending.

Answer

-- Practice query for Limiting Results with LIMIT and OFFSET

Question 7

19. Practice Solutions

Accepted Answer

-- Task 1
SELECT user_id, email
FROM users
WHERE email_verified_at IS NULL;

-- Task 2
SELECT product_id, description
FROM products
WHERE description IS NOT NULL;

-- Task 3
SELECT article_id, title
FROM articles
WHERE deleted_at IS NULL
ORDER BY published_at DESC, article_id DESC;

-- Task 4
SELECT customer_id, email
FROM customers
WHERE email IS NULL
   OR email = '';

-- Task 5
SELECT o.record_id, o.status, n.status
FROM old_data AS o
JOIN new_data AS n
  ON n.record_id = o.record_id
WHERE o.status IS DISTINCT FROM n.status;

-- Task 6
SELECT employee_id, full_name
FROM employees
WHERE department_id IS NULL;

-- Task 7
SELECT a.record_id, b.record_id
FROM table_a AS a
JOIN table_b AS b
  ON a.optional_code IS NOT DISTINCT FROM b.optional_code;

-- Task 8
CREATE INDEX idx_articles_active_published_at
ON articles (published_at DESC, article_id DESC)
WHERE deleted_at IS NULL;

Answer

-- Practice query for Using IS NULL and IS NOT NULL

Question 8

21. Practice Solutions

Accepted Answer

-- Task 1: First 5 employees
SELECT emp_id, full_name
FROM employees
ORDER BY emp_id ASC
LIMIT 5;

-- Task 2: Page 4, 10 products per page
-- OFFSET = (4 - 1) * 10 = 30
SELECT product_id, title
FROM products
ORDER BY product_id ASC
LIMIT 10 OFFSET 30;

-- Task 3: Next 10 products after product_id 250
SELECT product_id, title
FROM products
WHERE product_id > 250
ORDER BY product_id ASC
LIMIT 10;

-- Task 4: Employees ranked by salary
SELECT emp_id, full_name, salary
FROM employees
ORDER BY salary DESC, emp_id ASC
LIMIT 5 OFFSET 5;

-- Task 5: Standard FETCH syntax
SELECT emp_id, full_name, salary
FROM employees
ORDER BY salary DESC, emp_id ASC
FETCH FIRST 3 ROWS ONLY;

Answer

-- Practice query for Limiting Results with LIMIT and OFFSET

Sorting and Limiting Results Problems