SQL handouts
1. Basic SELECT and WHERE
Show all columns for orders from the North region.
List Order_ID and Profit for orders where Profit is greater than 500.
Retrieve all orders for Product_ID = 'P02'.
2. Calculations and Aliases
Select Order_ID, Units_Sold, Unit_Price, and calculate Total_Amount as Units_Sold * Unit_Price.
Find Revenue minus Cost for each order and show it as Calculated_Profit. Check if it matches the Profit column.
3. Filtering by Date and Numeric Ranges
Select all orders placed on or after '2025-02-01'.
Get orders where Units_Sold between 2 and 7 (inclusive).
4. GROUP BY and Aggregation
Find total Revenue per Region.
Find total Profit per Salesperson_ID.
Find total Units_Sold for each Product_ID.
5. HAVING and Aggregated Filters
Show Region and total Profit, but only for regions where total Profit > 1000.
Show Salesperson_ID and total Revenue, but only those with Revenue > 3000.
6. ORDER BY and LIMIT/TOP
List all orders ordered by Profit in descending order.
Show the top 2 most profitable orders.
7. Joins (for later extension)
Suppose you have a separate Product table (Product_ID, Product_Name). Write a query to show Order_ID, Product_Name, Units_Sold, and Profit.
Suppose you have a Salesperson table (Salesperson_ID, Salesperson_Name, Region). Write a query to list Salesperson_Name and total Profit they generated.
Comments
Post a Comment