1.       [SQL Week 2 Question 200-503] You

1.       [SQL Week 2 Question 200-503] You suspect certain employees are not servicing very many shipments, and some may not have any shipments at all. 


Create SQL to run a report which will

a.       List all your employees and the customers they serviced along with the number of shipments for each customer. Include all employees, including those who may not have serviced any customers.

b.       Note you want the number of shipments, not the sum of shipment quantity.  For example, if you have one shipment of 10 units, and another shipment of 20 units, our number of shipments here is 2.

c.       Sort the list ascending on number of shipments (so the smallest numbers of shipments are at the top), and then on employee id.  If an employee has not serviced any customers, display that employee at the very top.



For the Question 4, you may want to review the SQL Aliases functionality (“select X from Y as Z”).  There is a section in WWW3 SQL schools on this.


12-09-20 | 15:11:08

Select idemployee AS Employee_ID, EmpFirstName AS First_Name, EmpLastName as Last_Name,idCustomer as Customer_ID,CompanyName AS Company_Name, count(Quantity) as total_shipment FROM shipment s
join Customer c
On c.idCustomer=s.Customer_idCustomer
right Join employee e
On s.employee_idEmployee=e.idEmployee group by EmpFirstName order by count(quantity) desc, idEmployee asc;

Login to answer this question!