1. [SQL Week 2 Question 200-502] You
1. [SQL Week 2 Question 200-502] You want to make a report which will give you information about shipments sent by your lower-commission employees. Create a query which will
a. Include only all those shipments which were sent by an employee whose commission is 10% or less (include commissions of exactly 10%)
b. Print the state, company name, total quantity in the shipments for that customer, and the maximum employee commission in that order left to right.
c. Sort it alphabetically by state (ascending). If one state has several companies in it, they should be sorted within the state, with the highest total order quantity on top.
Your data may or may not match the example below.
For example, if your input data looks like this
Shipment
Customer Company Name
Customer State
Quantity
Employee
Employee Commission
1
ABC
MN
1
Sarah
0.05
2
ABC
MN
2
Thomas
0.10
3
Junior
MN
5
Sarah
0.05
4
DEF
GA
1
Thomas
0.10
5
DEF
GA
1
Victor
0.11
6
G
MD
10
Wally
0.01
7
H
MD
20
Xerxes
0.09
Your answer would look like this:
State
Customer CompanyName
Total Quantity shipped to this customer
Maximum Employee Commission in these shipments
GA
DEF
1
0.10
MD
H
20
0.09
MD
G
10
0.01
MN
Junior
5
0.05
MN
ABC
3
0.10
