Real-World SQL Queries – Solve Business Reporting Problems
Quick Answer: Real-world SQL queries help businesses turn stored records into useful reports. We use filtering, grouping, joins and aggregate functions to answer business questions and support decisions.
1. Why Do We Need Real-World SQL Queries?
Imagine Meena manages a restaurant with 500 daily customer orders. She needs to know which food category generates more sales.
Every order contains an item, quantity and price. She needs daily totals and category-wise sales reports.
How can she generate these reports without calculating every order manually?
The problem without business reporting queries
Meena can retrieve every order and calculate totals manually. This works for a few records, but becomes difficult as orders increase.
Example 1: Retrieve restaurant sales without grouping
Run this script in a SQL Server query window to create sample sales.
CREATE TABLE RestaurantSales
(
SaleId INT PRIMARY KEY,
ItemName VARCHAR(50),
Category VARCHAR(30),
Quantity INT,
UnitPrice DECIMAL(10, 2)
);
INSERT INTO RestaurantSales
VALUES
(101, 'Idli', 'Breakfast', 20, 10),
(102, 'Dosa', 'Breakfast', 15, 40),
(103, 'Tea', 'Drinks', 30, 10),
(104, 'Coffee', 'Drinks', 10, 20),
(105, 'Dosa', 'Breakfast', 5, 40);
SELECT Category, ItemName, Quantity, UnitPrice
FROM RestaurantSales
ORDER BY Category, ItemName;
Expected output
Category ItemName Quantity UnitPrice Breakfast Dosa 15 40.00 Breakfast Dosa 5 40.00 Breakfast Idli 20 10.00 Drinks Coffee 10 20.00 Drinks Tea 30 10.00
This query displays all five order records. However, the design has problems.
- Meena must calculate category totals manually.
- Finding the highest-selling category takes extra work.
- Repeated calculations become difficult with many records.
- Business reports need additional filtering and sorting.
Now imagine the restaurant handles thousands of orders. Manual calculations become difficult to maintain.
We need SQL queries that calculate totals and organize business information automatically.
The solution: Business reporting queries
Aggregate function = a function that calculates a result from multiple rows.
SUM() = adds numeric values together.
COUNT() = counts matching records.
GROUP BY = combines rows with matching values into groups.
HAVING = filters groups after aggregate calculations.
ORDER BY = sorts the final query results.
We can group restaurant orders by category. SQL Server calculates sales totals for each group.
Animation 1: How a business sales report works
Watch Meena convert individual restaurant orders into category-wise sales totals.
Meena groups five sales records and calculates category totals. Breakfast generates 1,000 and Drinks generates 500 in sample data.
2. Implement Real-World SQL Reporting Queries
Let's generate Meena's category-wise restaurant sales report.
SUM(Quantity * UnitPrice) = calculates total sales for each order group.
COUNT(*) = counts the number of order records in each group.
Example 2: Generate category-wise sales reports
Run this query after creating the RestaurantSales table above.
SELECT
Category,
SUM(Quantity * UnitPrice) AS TotalSales,
SUM(Quantity) AS ItemsSold,
COUNT(*) AS OrderLines
FROM RestaurantSales
GROUP BY Category
HAVING SUM(Quantity * UnitPrice) > 400
ORDER BY TotalSales DESC;
The GROUP BY clause combines records with matching categories. The SUM() function calculates the sales amount.
The HAVING clause keeps categories with sales above 400. The ORDER BY clause sorts higher sales first.
Expected output
Category TotalSales ItemsSold OrderLines --------- ---------- --------- ---------- Breakfast 1000.00 40 3 Drinks 500.00 40 2
Both categories satisfy the sales condition. Breakfast appears first because its total is higher.
Business tip: Always check whether a report needs individual rows or grouped totals. Choose WHERE for individual rows and HAVING for grouped results.
3. Quick Summary
Without business reporting vs with business reporting
| Without reporting queries | With reporting queries |
|---|---|
| Read every order separately. | Group orders by business category. |
| Calculate sales manually. | Calculate totals using SUM(). |
| Check every record manually. | Filter records using WHERE. |
| Sort reports manually. | Sort results using ORDER BY. |
The important terms to remember
- Aggregate functions: Calculate values from multiple records.
- GROUP BY: Groups records using selected columns.
- HAVING: Filters grouped results.
- WHERE: Filters individual records before grouping.
- ORDER BY: Sorts query results.
4. Common Mistakes and Best Practices
1. Using WHERE with aggregate conditions
WHERE cannot directly filter an aggregate result such as SUM().
Better approach: Use HAVING when filtering grouped aggregate results.
2. Forgetting columns in GROUP BY
Selected non-aggregated columns generally need to appear in GROUP BY.
Better approach: Match selected category columns with the grouping columns.
3. Using COUNT(*) when counting distinct customers
COUNT(*) counts rows, not unique customers.
Better approach: Use COUNT(DISTINCT CustomerId) for unique customer counts.
5. How Real-World SQL Reporting Comes Up in Interviews
Question 1: What is the difference between WHERE and HAVING?
What the interviewer expects: Explain when each clause filters data.
Strong answer: "WHERE filters individual rows before grouping. HAVING filters grouped results after aggregate calculations."
Question 2: How do you calculate department-wise or category-wise sales?
What the interviewer expects: Explain GROUP BY and SUM.
Strong answer: "I use GROUP BY on the required category. Then I use SUM to calculate its total sales."
Question 3: How do you find the top-selling category?
What the interviewer expects: Explain aggregation and sorting.
Strong answer: "I group sales by category and calculate each total. Then I sort the totals in descending order and select the highest result."
6. Hands-On Practice
Beginner: School examination report
Help Lakshmi generate student-wise total marks and average marks. Group records by StudentId.
Expected result: Show each student's total and average marks.
Intermediate: Hospital billing report
Generate department-wise billing totals for a hospital. Filter departments with total billing above 50,000.
Hint: Use GROUP BY, SUM() and HAVING.
Advanced: E-commerce sales report
Build a report showing category-wise revenue and unique customer counts.
- Join Orders and OrderItems tables.
- Calculate revenue using quantity and unit price.
- Count unique customers in each category.
- Display the highest revenue category first.
Challenge: Add a date filter and compare sales across different months.
7. Next Steps and Community
Previous article: SQL Server Database Design – Build an Employee Management Database
Next article: SQL Server Integration with .NET Applications Using ADO.NET
For more details, read the official Microsoft documentation:
Try Meena's restaurant reporting example and add more sales records. Share your queries and reporting solutions with other learners.