ds_bos_mysql_tutorial_Keith_and_Pam

ds_bos_mysql_tutorial_Keith_and_Pam.sql

ds_bos_mysql_tutorial_Keith_and_Pam

What customers are from the UK
What is the name of the customer who has the most orders?
What supplier has the highest average product price?
What category has the most orders?

5. What employee made the most sales (by number of sales)?
** 6. What employee made the most sales (by value of sales)?

** 7. What Employees have BS degrees? (Hint: Look at LIKE operator)

** 8. What supplier has the highest average product price assuming they have at least 2 products (Hint: Look at the HAVING operator)

What category has the most orders?

  SELECT CategoryName, COUNT(*) FROM Categories 
JOIN Products on (Categories.CategoryID = Products.CategoryID)  
JOIN OrderDetails on (Products.ProductID = OrderDetails.ProductID)  
 GROUP BY Categories.CategoryID  ORDER BY COUNT(*)DESC

1. What Employees have BS degrees? (Hint: Look at LIKE operator)
   SELECT FirstName,LastName,Notes LIKE '% BS %' FROM Employees

What supplier has the highest average product price assuming they have at least 2 products (Hint: Look at the HAVING operator)

SELECT Suppliers.SupplierName, AVG(Products.Price) FROM Suppliers 
JOIN Products ON(Suppliers.SupplierID = Products.SupplierID) 
JOIN OrderDetails ON(OrderDetails.ProductID = Products.ProductID)
GROUP BY Suppliers.SupplierID  HAVING COUNT(OrderDetails.ProductID) > 2
ORDER BY AVG(Products.Price) DESC