LGCUSTOMER PK Cust Code Cust Frame Cust_Lname Cust Street Cust City Cust State PK FK1 Cust_ZIP...

   

Transcribed Image Text:

LGCUSTOMER PK Cust Code Cust Frame Cust_Lname Cust Street Cust City Cust State PK FK1 Cust_ZIP Cust Balance places LGINVOICE Inv Num Cust Code Inv_Date Inv Total FK2 Employee ID completes LGEMPLOYEE PK Emp Num PK Emp_Fname Emp_Lname Emp_Email Emp_Phone Emp Hiredate Emp Title Emp_Comm FK1 Dept Num employs manbges LGDEPARTMENT Dept Num Dept Name Dept Mail Box Dept Phone FK1 Emp Num contains has LGLINE PK,FK1 InvNum PK Line Num FK2 Prod_SKU Line Qty Line Price LGSALARY HISTORY PK,FK1 Emp Num Sal From PK Sal End Sal Amount appears on HH LGBRAND PK Brand ID Brand Name Brand Type includes 8 LGPRODUCT PK Prod SKU Prod Descript Prod Type Prod Base Prod_Category Prod Price Prod_QOH Prod_Min FK1 Brand ID provided by LGSUPPLIES PK,FK1 Vend ID PK,FK2 Prod SKU provides LGVENDOR PK Vend ID Vend Name Vend Street Vend_City Vend State Vend ZIP 50. Write a query to display the invoice number, line numbers, product SKUs, product descriptions, and brand ID for sales of sealer and topcoat products of the same brand on the same invoice. Sort the results by invoice number in ascending order, first line number in ascending order, and then by second line number in descending order. INV NUM 115 118 135 153 222 LINE_ NUM 2 N. 2 5 2 1 PROD_ SKU 5140- RTG 5140- RTG 3036- PCT 3701- YAW 1336- FVM PROD_DESCRIPT Fire Resistant Sealer, for Exterior Wood (ULC Approved) Fire Resistant Sealer, for Exterior Wood (ULC Approved) Sealer, for Knots Sealer, Solvent Based, for Concrete Floors Alkyd, Sanding Sealer, Clear LINE_ NUM 1 5 2 1 3 PROD SKU 1203-AIS 5046- TTC 1074-VVJ 3955- NWD 8199- YRF PROD_ DESCRIPT Fire Retardant Coating, Latex, Interior, Flat (ULC Approved) Aluminum Paint, Heat Resistant (Up to 427C - 800F) (eggshell-like - MPI Gloss Level 3) Light Industrial Coating, 25 Exterior, Water Based Water Repellant, Clear (Not Paintable) BRAND_ ID 35 Varnish, Exterior, Water Based, (Satin-Like) MPI Gloss Level 4 35 30 33 52. Write a query to display the customer code, first name, and last name of all customers who have had at least one invoice completed by employee 83649 and at least one invoice completed by employee 83677. Sort the output by customer last name and then first name. CUST CODE 684 684 340 211 903 643 295 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 O. Sort the results by customer last name and then first name. sust code sustiname sust.Iname sust street ADDISON 323 LORETTA PLACE 680 RED TALON DRIVE 829 BIG BEND LOOP 393 853 ROBIN NINA DORTHY FOSTER ALLEN AUSTIN BERNAL CUST FNAME WENDI WENDI GAYLORD BOLTON MARCIA GERALD 1299 EAST 3RD AVENUE 1069 LUGENE LANE sust fitx Mobile Robertsdale AL sust state sust.zie inx.date AL CUST LNAME BEAN BEAN Diamond Shamrock Birmingham AL Montgomery AL BURRIS CAUDILL AL 36693 36574 2017-08-26 35280 2017-06-21 36614 2017-04-24 NULL Largest Invoice 230.63 11.99 589.75 0.00 36131 2017-11-25 372.68 LGCUSTOMER PK Cust Code Cust Frame Cust_Lname Cust Street Cust City Cust State PK FK1 Cust_ZIP Cust Balance places LGINVOICE Inv Num Cust Code Inv_Date Inv Total FK2 Employee ID completes LGEMPLOYEE PK Emp Num PK Emp_Fname Emp_Lname Emp_Email Emp_Phone Emp Hiredate Emp Title Emp_Comm FK1 Dept Num employs manbges LGDEPARTMENT Dept Num Dept Name Dept Mail Box Dept Phone FK1 Emp Num contains has LGLINE PK,FK1 InvNum PK Line Num FK2 Prod_SKU Line Qty Line Price LGSALARY HISTORY PK,FK1 Emp Num Sal From PK Sal End Sal Amount appears on HH LGBRAND PK Brand ID Brand Name Brand Type includes 8 LGPRODUCT PK Prod SKU Prod Descript Prod Type Prod Base Prod_Category Prod Price Prod_QOH Prod_Min FK1 Brand ID provided by LGSUPPLIES PK,FK1 Vend ID PK,FK2 Prod SKU provides LGVENDOR PK Vend ID Vend Name Vend Street Vend_City Vend State Vend ZIP 50. Write a query to display the invoice number, line numbers, product SKUs, product descriptions, and brand ID for sales of sealer and topcoat products of the same brand on the same invoice. Sort the results by invoice number in ascending order, first line number in ascending order, and then by second line number in descending order. INV NUM 115 118 135 153 222 LINE_ NUM 2 N. 2 5 2 1 PROD_ SKU 5140- RTG 5140- RTG 3036- PCT 3701- YAW 1336- FVM PROD_DESCRIPT Fire Resistant Sealer, for Exterior Wood (ULC Approved) Fire Resistant Sealer, for Exterior Wood (ULC Approved) Sealer, for Knots Sealer, Solvent Based, for Concrete Floors Alkyd, Sanding Sealer, Clear LINE_ NUM 1 5 2 1 3 PROD SKU 1203-AIS 5046- TTC 1074-VVJ 3955- NWD 8199- YRF PROD_ DESCRIPT Fire Retardant Coating, Latex, Interior, Flat (ULC Approved) Aluminum Paint, Heat Resistant (Up to 427C - 800F) (eggshell-like - MPI Gloss Level 3) Light Industrial Coating, 25 Exterior, Water Based Water Repellant, Clear (Not Paintable) BRAND_ ID 35 Varnish, Exterior, Water Based, (Satin-Like) MPI Gloss Level 4 35 30 33 52. Write a query to display the customer code, first name, and last name of all customers who have had at least one invoice completed by employee 83649 and at least one invoice completed by employee 83677. Sort the output by customer last name and then first name. CUST CODE 684 684 340 211 903 643 295 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 O. Sort the results by customer last name and then first name. sust code sustiname sust.Iname sust street ADDISON 323 LORETTA PLACE 680 RED TALON DRIVE 829 BIG BEND LOOP 393 853 ROBIN NINA DORTHY FOSTER ALLEN AUSTIN BERNAL CUST FNAME WENDI WENDI GAYLORD BOLTON MARCIA GERALD 1299 EAST 3RD AVENUE 1069 LUGENE LANE sust fitx Mobile Robertsdale AL sust state sust.zie inx.date AL CUST LNAME BEAN BEAN Diamond Shamrock Birmingham AL Montgomery AL BURRIS CAUDILL AL 36693 36574 2017-08-26 35280 2017-06-21 36614 2017-04-24 NULL Largest Invoice 230.63 11.99 589.75 0.00 36131 2017-11-25 372.68

Answer rating: 100% (QA)

1 SELECT COUNT AS Brands FROM LGBRAND 2 SELECT DeptNum AS Department Number C... View the full answer