Day 3 Practice: Functions & Grouping

Hands-On SQL Assignments

Scenario: You are working with a SalesData table containing columns: SaleID, SalespersonName, Region, SaleAmount, and ItemsSold. Use built-in functions and grouping to generate reports for the management team.

Task 1: Math & String Formatting

Management noticed trailing spaces in the names. Write a query to select SalespersonName, applying both LTRIM and RTRIM to clean the data, and format the names in completely UPPER case.
Find the exact character length (LEN) of each salesperson's name.
For a special promotion, the company is rounding up sales targets. Write a query to find the CEILING value of the SaleAmount.

Task 2: Aggregate Functions (Overall Totals)

Write a query to find the total SUM of all SaleAmount in the entire company.
Write a query to find the MAX (highest) and MIN (lowest) sale amounts recorded.
Write a query to COUNT the total number of sales transactions recorded in the table.

Task 3: Data Grouping & Filtering (GROUP BY & HAVING)

Write a query to find the total sum of SaleAmount per Region. (Hint: Use GROUP BY Region).
Write a query to find the average SaleAmount per SalespersonName.
WHERE vs HAVING Challenge: The company only wants to reward top-performing regions. Write a query that calculates the total sum of sales per Region, but only shows regions where that total sum is greater than 100,000.
Combined Challenge: Write a query to find the average SaleAmount per Region, but exclude any sales where the ItemsSold is less than 5 (filter rows first), and only show regions where the resulting average sale is greater than 1,000 (filter groups second).