1) Use the following figure to solve the following queries using MySQL (Cengage)

A) 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

B) 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 brands average price. Sort the result by brand name

FIGURE P7.27 THE CH07_LARGECO ERD LGCUSTOMER LGBRAND PK Cust Code PK Brand ID Brand Name Brand_Type Cust_Fname Cust_Lname Cust_Street Cust_City Cust_State Cust_ZIP Cust_Balance includes places LGPRODUCT LGLINE | Prod SKU LGINVOICE PK InvNum PK,FK1 InvNum PK Line Num contains appears on HE # FK2 FK1 Cust_Code Inv_Date Inv_Total FK2 Employee_ID Prod SKU Line Qty Line_Price Prod_Descript Prod Type Prod_Base Prod_Category Prod_Price Prod_QOH Prod_Min FK1 Brand_ID completes provided by LGEMPLOYEE PK Emp Num LGSUPPLIES LGSALARY_HISTORY PK,FK1 Emp Num PK Sal From PK,FK1 Vend_ID PK,FK2 Prod SKU Emp_Fname Emp_Lname has Emp_Email + Emp_Phone Emp_Hiredate Emp_Title Emp_Comm FK1 Dept_Num Sal End Sal_Amount provides employs mankages $ LGVENDOR PK Vend ID LGDEPARTMENT PK Dept Num Vend_Name Vend_Street Vend_City Vend_State Vend_ZIP Dept_Name Dept_Mail_Box Dept_Phone FK1 Emp_Num