BUAN-SQL – Assignment 10
Grouping and Filtering Grouped Data
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.
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)
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)
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)