Problem 51 The Binder Prime Company wants to recognize the employee who sold the most of its products during a specified period. Write a query to display the employee number, employee first name, employee last name, email address, and total units sold for the employee who sold the most Binder Prime brand products between November 1, 2017, and December 5, 2017. If there is a tie for most units sold, sort the output by employee last name (Figure P7.51). EMP_NUM 84134 ROSALIE EMP_FNAME EMP_LNAME EMP_EMAIL TOTAL GARLAND G.ROSALI98@LGCOMPANY.COM 23 83850 RUSTY MILES M.RUSTY95@LGCOMPANY.COM 23 Problem 53 LargeCo is planning a new promotion in Alabama (AL) and wants to know about the largest purchases made by customers in that state. Write a query to display the customer code, customer first name, last name, full address, invoice date, and invoice total of the largest purchase made by each customer in Alabama. Be certain to include any customers in Alabama who have never made a purchase; their invoice dates should be NULL and the invoice totals should display as 0. Sort the results by customer last name and then first name (Partial result are shown in Figure P7.53). CUST_CODE CUST_FNAME CUST_LNAME CUST_STREET CUST_CITY CUST_STATE CUST ZIP INV_DATE Largest Invoice 903 ROBIN ADDISON 323 LORETTA PLACE Mobile AL 36693 2017-08-26 230.63 643 NINA ALLEN 680 RED TALON DRIVE Robertsdale AL 36574 2017-06-21 11.99 295 DORTHY AUSTIN 829 BIG BEND LOOP Diamond Shamrock AL 36614 2017-04-24 589.75 393 FOSTER BERNAL 1299 EAST 3RD AVENUE Birmingham AL 35280 NULL 0.00 853 GAYLORD BOLTON 1069 LUGENE LANE Montgomery AL 36131 2017-11-25 372.68 925 ALANA BOOKER 1874 I STREET Mccullough AL 36502 2017-12-12 208.85 1248 LISA BRADY 491 LOWLAND AVENUE Daphne AL 36577 2017-12-05 414.47 538 CHIQUITA CALDWELL 1501 BRIGGS COURT Normal AL 35762 2017-05-26 143.90 89 MONICA CANTRELL 697 ADAK CIRCLE Loachapoka AL 36865 2017-03-31 516.58 Problem 54 One of the purchasing managers is interested in the impact of product prices on the sale of products of each brand. Write a query to display the brand name, brand type, average price of products of each brand, and total units sold of products of each brand. Even if a product has been sold more than once, its price should only be included once in the calculation of the average price. However, you must be careful because multiple products of the same brand can have the same price, and each of those products must be included in the calculation of the brand's average price. Sort the result by brand name (Figure P7.54). Ren BRAND NAME BRAND_TYPE Average Price Units Sold BINDER PRIME PREMIUM 16.12 3753 BUSTERS VALUE 22.59 3727 FORESTERS BEST VALUE 20.94 2086 HOME COMFORT CONTRACTOR 21.80 4842 LE MODE PREMIUM 19.22 5284 LONG HAUL OLDE TYME QUALITY STUTTENFURST VALU-MATTE CONTRACTOR 20.12 5728 CONTRACTOR 18.33 3614 CONTRACTOR 16.47 3671 VALUE 16.84 2485 Figure P7.54 Problem 55 The purchasing manager is still concerned about the impact of price on sales. Write a query to display the brand name, brand type, product SKU, product description, and price of any products that are not a premium brand, but that cost more than the most expensive premium brand products (Figure P7.55). BRAND NAME BRAND_TYPE PROD_SKU PROD_DESCRIPT PROD_PRICE LONG HAUL CONTRACTOR 1964-OUT Fire Resistant Top Coat, for Interior Wood 78.49 Figure P7.55