Question-1: Display the list of all departments and their information in the company. The output should be sorted by department names.

Question-2: Generate the list of all products and their information that have solvent as product base and are of either filler or cleaner category. Display the result in the sorted order of product type and product category. The columns should include product brand name, SKU, description, type, category, unit price, quantity on hand, reorder level (i.e., prod_min).

Question-3: The HR manager wants to review employee performance. Generate annual sales handled by each employee who were hired during the period April 2003 to March 2007 in terms of the number and total value of invoices handled by them respectively in each year of their employment. Employee name should be displayed in a single column called “FullName” (use the convention last name, first name). Sort the output by year of sales, and descending order of the total value of invoices handled. The result should include columns employee’s full name, title, year of hire, year of sales, number of invoices, and total value of invoices.

Question-4: The chief marketing officer want to know sales performance of paint products in every market (i.e., states) to determine next year’s discount program for different brand names in each product type and category. Generate annual sales (in $) of all paint product in each state by Brand name, paint product’s type, and category. Display state, purchase year, brand name, product type, product category, annual sales. [Hint: product level annual sales need to be calculated using appropriate formula and tables.]

The ERD for this database is shown below.

Description:

The database provided to you stores data for the LG Paints Company’s Sales and Inventory Management. This company sells paint products across all states in the United States. The company tracks the sale of paint products to customers. The database keeps data on customers (LGCUSTOMER), sales details (LGINVOICE contains overall information of transaction; and LGLINE contains list of products purchased in each transaction), product details and inventory level (LGPRODUCT), product brands (LGBRAND), vendors (LGVENDOR), which vendors supply each product (LGSUPPLIES), employees details (LGEMPLOYEE), the salary history of each employee (LGSALARY_HISTORY), and department details (LGDEPARTMENT).

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.

lgsales.db