The structure and contents of the Ch07_LargeCo database are shown in Figure P7.27. Use this database to answer the following problems.

27. Write a query to display the eight departments in the LGDEPARTMENT table sorted by department name.

28. Write a query to display the SKU (stock keeping unit), description, type, base, category, and price for all products that have a PROD_BASE of Water and a PROD_CATEGORY of Sealer (Figure P7.28).

29. Write a query to display the first name, last name, and email address of employees hired from January 1, 2005, to December 31, 2014. Sort the output by last name and then by first name (Partial results shown in Figure P7.29).

30. Write a query to display the first name, last name, phone number, title, and department number of employees who work in department 300 or have the title CLERK I. Sort the output by last name and then by first name (Partial results shown in Figure P7.30).

31. Write a query to display the employee number, last name, first name, salary from date, salary end date, and salary amount for employees 83731, 83745, and 84039. Sort the output by employee number and salary from date (Figure P7.31).

32. Write a query to display the first name, last name, street, city, state, and zip code of any customer who purchased a Foresters Best brand top coat between 2017-7-15, and 2017-7-31. If a customer purchased more than one such product, display the customers information only once in the output. Sort the output by state, last name, and then first name (Partial results shown in Figure P7.32).

33. Write a query to display the employee number, last name, email address, title, and department name of each employee whose job title ends in the word ASSOCIATE. Sort the output by department name, employee title, and employee number (Partial result shown in Figure P7.33).

34. Write a query to display a brand name and the number of products of that brand that are in the database. Sort the output by the brand name (Figure P7.34).

35. Write a query to display the number of products in each category that have a water base, sorted by category (Figure P7.35).

36. Write a query to display the number of products within each base and type combination, sorted by base and then by type (Figure P7.36).

37. Write a query to display the total inventorythat is, the sum of all products on hand for each brand ID. Sort the output by brand ID in descending order (Figure P7.37).

38. Write a query to display the brand ID, brand name, and average price of products of each brand. Sort the output by brand name. Results are shown with the average price rounded to two decimal places (Figure P7.38).

39. Write a query to display the department number and most recent employee hire date for each department. Sort the output by department number (Figure P7.39).

40. Write a query to display the employee number, first name, last name, and largest salary amount for each employee in department 200. Sort the output by largest salary in descending order, and then by employee number (Partial results shown in Figure P7.40).

41. Write a query to display the customer code, first name, last name, and sum of all invoice totals for customers with cumulative invoice totals greater than $1,500. Sort the output by the sum of invoice totals in descending order (Partial results shown in Figure P7.41).

42. Write a query to display the department number, department name, department phone number, employee number, and last name of each department manager. Sort the output by department name (Figure P7.42).

43. Write a query to display the vendor ID, vendor name, brand name, and number of products of each brand supplied by each vendor. Sort the output by vendor name and then by brand name (Partial results shown in Figure P7.43).

44. Write a query to display the employee number, last name, first name, and sum of invoice totals for all employees who completed an invoice. Sort the output by employee last name and then by first name (Partial results shown in Figure P7.44).

45. Write a query to display the largest average product price of any brand (Figure P7.45).

46. Write a query to display the brand ID, brand name, brand type, and average price of products for the brand that has the largest average product price (Figure P7.46).

47. Write a query to display the manager name, department name, department phone number, employee name, customer name, invoice date, and invoice total for the department manager of the employee who made a sale to a customer whose last name is Hagan on May 18, 2017 (Figure P7.47).

48. Write a query to display the current salary for each employee in department 300. Assume that only current employees are kept in the system, and therefore the most current salary for each employee is the entry in the salary history with a NULL end date. Sort the output in descending order by salary amount (Figure P7.48).

49. Write a query to display the starting salary for each employee. The starting salary would be the entry in the salary history with the oldest salary start date for each employee. Sort the output by employee number (Figure P7.49).

50. Write a query to display the invoice number, line numbers, product SKUs, product descriptions, and brand ID for sales of sealer and top coat 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 (Figure P7.50).

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

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 (Partial results are shown in Figure P7.52).

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

FIGURE P7.27 THE CHOZ_LARGECO ERD LGCUSTOMER PK Cust Code LGBRAND PK Brand ID Brand_Name Brand_Type Cust_Fname Cust_Lname Cust_Street Cust_City Cust_State Cust_ZIP Cust_Balance includes plates LGPRODUCT LGLINE PK Prod SKU LGINVOICE PK Inv Num PK,FK1 Inv_Num | Line Num # contains appears on + 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 LGSALARY HISTORY LGSUPPLIES has # PK,FK1 Emp Num PK Sal From PK,FK1 Vend ID PK,FK2 Prod SKU Emp_Fname Emp_Lname Emp_Email Emp_Phone Emp_Hiredate Emp_Title Emp_Comm FK1 Dept_Num Sal End Sal_Amount provides manages employs I 70 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 FIGURE P7.27 THE CHOZ_LARGECO ERD LGCUSTOMER PK Cust Code LGBRAND PK Brand ID Brand_Name Brand_Type Cust_Fname Cust_Lname Cust_Street Cust_City Cust_State Cust_ZIP Cust_Balance includes plates LGPRODUCT LGLINE PK Prod SKU LGINVOICE PK Inv Num PK,FK1 Inv_Num | Line Num # contains appears on + 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 LGSALARY HISTORY LGSUPPLIES has # PK,FK1 Emp Num PK Sal From PK,FK1 Vend ID PK,FK2 Prod SKU Emp_Fname Emp_Lname Emp_Email Emp_Phone Emp_Hiredate Emp_Title Emp_Comm FK1 Dept_Num Sal End Sal_Amount provides manages employs I 70 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