1. An Employee Database Table
Action Products Company's sales department wants to establish a database to track key information about its salespersons. Salespersons are assigned to either industrial or commercial customers and to one of the four sales regions (East
South
Midwest
and West). Actions products wishes to record the following information for each salesperson: Their name
the date they were hired
their sales commission rate
their sales region
and the category of customer served. The table below shows this information for Action's sales staff.
- Using a database software package
create a table to store the preceding information and enter the sample data provided.
- Generate database queries or reports to answer these questions and get a printed listing of your results.
- Get a listing of the name
commission rate
and hire date of all salespersons who sell to commercial customers. Sort your listing in order from the first hired to the most recently hired salesperson.
- Get a listing of the names and commission rates of all salespersons in the eastern region sorted from the lowest to the highest commission rate.
- Get a listing showing the number of salespersons receiving each commission rate.
2. Sales and Commissions at Action Products
Action Products wishes to track its sales using database software. For each sale
the firm wants to record the salesperson's name
the customer's name
the sales date and the dollar amount of the sale. Sample data for recent sales in the east region follow:
- Using database software
create an appropriate table for this sales data. If you completed Application Exercise 7-1
add this new table to the database where you stored the employee table. Enter the sample sales data into your sales database table.
- Using your table of sample data
prepare and get printed listings of the following:
- A report of sales by the salesperson T. Turner showing detailed information (customer name
date
and amount) for each sale and showing the total dollar amount of sales.
- A report grouped by customer name showing detailed information (date and amount) for each sale and showing total dollar amount of all sales to each customer.
- (If you completed Application Exercise 7-1) Join the employee and sales tables appropriately to produce a table showing commissions earned. (The commission earned equals the commission rate for the employee table times the dollar amount from the sales table). Your report should be grouped by Salesperson and should show the customer name
date
sales dollar amount
and commission earned for each sale. It should also show totals for sales dollar amount and commission earned for each salesperson.
|
