Querying Data with SELECT Coding Practice Challenges | CodeNivo

Question 1

Guided Practice: Build a Simple Report

Accepted Answer

-- Write a SELECT query that:
-- 1. Returns emp_id.
-- 2. Combines first_name and last_name into full_name.
-- 3. Returns department.
-- 4. Calculates salary / 12 as monthly_salary.
-- 5. Sorts by salary descending.
-- 6. Uses emp_id as a tie-breaker.

Answer

-- Practice query for Retrieving Data with SELECT

Question 2

Guided Practice: Filter an Orders Table

Accepted Answer

-- Write a SELECT query that:
-- 1. Returns order_id, customer_id, total_amount, order_date.
-- 2. Includes pending OR processing orders.
-- 3. Requires total_amount >= 100.
-- 4. Limits order_date to calendar year 2026 in UTC.
-- 5. Uses explicit columns rather than SELECT *.
-- 6. Uses parentheses to make the status condition clear.

Answer

-- Practice query for Filtering Data with WHERE

Question 3

Guided Practice: Create a Product Report

Accepted Answer

-- Write a SELECT query that:
-- 1. Returns product_name as item_title.
-- 2. Returns unit_price and unit_cost.
-- 3. Calculates unit_price - unit_cost as gross_profit.
-- 4. Calculates gross margin percentage.
-- 5. Uses NULLIF to avoid division by zero.
-- 6. Sorts by gross_profit descending.

Answer

-- Practice query for Selecting Specific Columns

Question 4

Guided Practice: Unique Product Categories

Accepted Answer

-- Return the category column only.
-- Include active products.
-- Remove repeated categories.
-- Sort the result alphabetically.

Answer

-- Practice query for Removing Duplicate Results with DISTINCT

Question 5

Guided Practice: Latest Order per Customer

Accepted Answer

-- Return customer_id, order_id, order_date, total_amount.
-- Return one order per customer.
-- Prefer the latest order_date.
-- Use order_id DESC to break timestamp ties.

Answer

-- Practice query for Removing Duplicate Results with DISTINCT

Querying Data with SELECT Problems