Solving Grouping and Filtering Grouped Data Problems with SQL: Examples from Sales Orders, Entertainment Agency, and School Scheduling Databases
$5.99
Kindly ADD to CART and Purchase the Full Answer at $5.99 ONLY.
BUAN-SQL – Assignment 10
Grouping and Filtering Grouped Data
Directions
For each of the problems below, work out your answer in Cloud9 and then paste your USE statement and your SELECT statement into the box below the problem.
Some of these problems require you to join two or more tables. Please break your SELECT statement onto a new line for each clause for readability. Do not include any SHOW or DESCRIBE statements you use to determine database, table or column names.
Problems
In the Sales Orders database, how many orders are for only one product? (Hint: use a subquery in the FROM clause that lists the order numbers for orders having only one row and then COUNT those rows in the main query.) (1 row, final result should be 133)
Answer
In the Entertainment Agency database, show each Agent’s name, the sum of the contract price for the engagements they booked, and the agent’s total commission for agents whose total commission is more than $1000. Sort the list from highest total contracts to lowest. Make sure the total commission appears with two decimal places. (4 rows)
Answer
Using the School Scheduling Database, provide the full name of every staff member and the number of classes they have taught in order from most classes to fewest. (27 rows)
Assignment Preview:
- In the Sales Orders database, how many orders are for only one product?
USE Sales_Orders_DB;
SELECT COUNT(*)
FROM (SELECT OrderNumber
FROM Orders
GROUP BY OrderNumber
HAVING COUNT(*) = 1) as OrdersWithOneProduct;
- In the Entertainment Agency database, show each Agent’s name, the sum of the contract price for the engagements they booked, and the agent’s total commission for agents whose total commission is more than $1000. Sort the list from highest total contracts to lowest. Make sure the total commission appears with two decimal places.
USE Entertainment_Agency_DB;
SELECT AgentName, SUM(Engagements.ContractPrice) as TotalContracts,
FORMAT(SUM(Engagements.Commission), 2) as TotalCommission
FROM Agents
JOIN Engagements ON Agents.AgentID = Engagements.AgentID
GROUP BY Agents.AgentName
HAVING SUM(Engagements.Commission) > 1000
ORDER BY TotalContracts DESC;
- Using the School Scheduling Database, provide the full name of every staff member and the number of classes they have taught in order from most classes to fewest.
USE School_Scheduling_DB;
SELECT Staff.FullName, COUNT(Schedule.ClassID) as NumClassesTaught
FROM Staff
JOIN Schedule ON Staff.StaffID = Schedule.StaffID
GROUP BY Staff.FullName
ORDER BY NumClassesTaught DESC;
Note: The above solution will work if the relationship between Staff and Schedule is one-to-many, If it’s many-to-many you need to join with a bridge table instead.
