Ch07 LargeCo database

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

Database Schema

The schema for the Ch07_LargeCo database is shown below. Click this image to view it in its own tab.

Figure P7.27

This figure is for visual aide and may not match the database schema exactly.

The Ch07_LargeCo database stores data for a company that sells paint products. The company tracks the sale of products to customers. The database keeps data on customers (LGCUSTOMER), sales (LGINVOICE), products (LGPRODUCT), which products are on which invoices (LGLINE), employees (LGEMPLOYEE), the salary history of each employee (LGSALARY_HISTORY), departments (LGDEPARTMENT), product brands (LGBRAND), vendors (LGVENDOR), and which vendors supply each product (LGSUPPLIES). Some of the tables contain only a few rows of data, while other tables are quite large; for example, there are only eight departments, but more than 3,300 invoices containing over 11,000 invoice lines. For Problems 28–55, a figure of the correct output for each problem is provided. If the output of the query is very large, only the first several rows of the output are shown.

Instructions

Given the structure of the Ch07_LargeCo database shown above, use SQL commands to answer the problems in the following steps.

Write your SQL statement in the editor on the right, then click the Run Query button to execute your statement in the interactive MySQL shell.

Click Next Step to get started!

Grading

After you have completed a problem and clicked the Run Query button, mark the task as complete. Checks will run to verify your work.

When all problems are completed and you are satisfied with the results, use the Submit button to record your score.

Problem 27

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

Problem 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 customer’s information only once in the output. Sort the output by state, last name, and then first name (Partial results shown in Figure P7.32).

Figure P7.32

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

Figure P7.33

Problem 45

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

Figure P7.45

Task

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

Figure P7.46

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

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

Figure P7.50

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

Figure P7.51

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