Techie BabajiLearn to Code. Build Real Skills.
SQL Server · Practice

Practical Exercises

Time to combine everything. These exercises use the ShopDB sample from earlier lessons. Try each one before opening the solution.

Advanced 5 min Lesson 30 of 31Examples run on SQL Server 2022 and Azure SQL Database.
Before you start:HAVINGWindow Functions
0/31 in SQL Server
On this page · 5 min · Advanced

On this page

  1. In simple words
  2. Learning objectives
  3. Explanation
  4. Code example
  5. Step by step
  6. Real-world use
  7. Common mistakes
  8. Best practices
  9. Remember this
  10. Practice exercise
  11. Mini quiz
  12. Related topics
  13. My notes
Reading time
5 min
Difficulty
Advanced
Updated
Oct 27, 2026

Related tutorials

Interview questions

In simple words#

Like solving previous years’ exam papers: practising real queries is what makes SQL stick.

Read the question
Identify tables, filters and grouping.
Build step by step
FROM → WHERE → GROUP BY → SELECT → ORDER BY.
Check results
Verify counts with a simple query.

Learning objectives#

  • Combine SELECT, joins and grouping to answer business questions
  • Use window functions in realistic reports
  • Modify data safely
  • Build confidence for interviews

Explanation#

Each exercise is a question a manager might ask. Read it, decide which tables you need, then build the query one clause at a time.

Check your result against the expected output. If it differs, run parts of the query separately to see where it goes wrong.

Exercises: (1) revenue per category from delivered orders; (2) each customer’s most recent order; (3) products never ordered; (4) monthly revenue; (5) safely increase Furniture prices by 5% inside a transaction.

Code example: Worked exercise 1 — revenue per category (delivered orders only)#

SQLWorked exercise 1 — revenue per category (delivered orders only) Illustrative
SELECT p.Category, SUM(oi.Qty * oi.UnitPrice) AS Revenue
FROM OrderItems AS oi
JOIN Orders   AS o ON o.OrderId   = oi.OrderId
JOIN Products AS p ON p.ProductId = oi.ProductId
WHERE o.Status = 'Delivered'
GROUP BY p.Category
ORDER BY Revenue DESC;
Expected output
Category     Revenue
Electronics  56400.00
Furniture    12000.00

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

  1. Start from OrderItems because revenue lives on order lines.
  2. Join Orders to filter by status and Products to get the category.
  3. Only orders 101 and 102 are delivered: 55,000 + 1,400 Electronics and 12,000 Furniture.
  4. Group by category and sort by revenue.

Real-world use case#

These are the same patterns used in real sales dashboards and in SQL interview tests.

Common mistakes#

Summing UnitPrice instead of Qty × UnitPrice

Revenue must include quantity.

Filtering after grouping

Status is a row condition, so it belongs in WHERE.

Best practices#

  • Write the FROM and JOINs first
  • Add WHERE, then GROUP BY, then SELECT columns
  • Verify totals with a small manual calculation
  • Save useful queries as views

Remember this#

Read the question → find tables → filters → grouping → sorting.

Check results with a quick COUNT.

Practice daily — SQL is learned by writing it.

Practice exercise#

Your task: Exercise 2: return each customer’s most recent order (customer name, order id, order date).

Need a hint?
Use ROW_NUMBER() partitioned by customer, ordered by date descending, in a CTE.
Show solution
SQL Illustrative
WITH Ranked AS (
  SELECT c.Name, o.OrderId, o.OrderDate,
         ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY o.OrderDate DESC) AS rn
  FROM Customers AS c JOIN Orders AS o ON o.CustomerId = c.CustomerId)
SELECT Name, OrderId, OrderDate FROM Ranked WHERE rn = 1;
-- Asha 103 2026-02-10 | Ravi 102 2026-01-07 | Meera 104 2026-02-12

Mini quiz#

Practical Exercises quizMultiple choice Question 1 of 1

Which query pattern finds products that were never ordered?

Score so far: 0/0

Want more? Practise all SQL Server MCQs · SQL Server interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.