Description
A. Provide a list all of the Customer ID, Customer Names, and States and sort the list in alphabetical order by Customer Name.
B. Provide a list of all of the Customer ID, Customer Names, and States, and sort the list by state with the Customer Names in alphabetical order within each state.
C. List the customers showing the Customer ID, Customer Name, address, and sales rep name in alphabetical order by customer name
D. Which employees have not completed course ID = 90? Hint: name of employee only, and the best way to determine this is by having a subselect statement to determine the EmployeeIDs that have completed CourseID 90, and then have a the select statement use the output of the subselect to determine which of all of the employees are not in the list provided by the subselect.
E. How many sales reps does PSC have? Hint: I want to know how many, not who they are. Also, realize that all sales reps are employees, but not all employees are sales reps. Also, keep in mind that being a sales rep does not mean that they have actually sold anything.
F. List all of the sales reps sorted by largest commission rate first Hint: name and sales commission rate
G. Who is the manager of the sales reps? Hint: name of the manager only.
H. List the employee names of those that report directly to the manager of the sales reps. Hint: Your SQL statement will need to determine the manager first before it can determine the employees that report to him/her.
I. Who is the manager of the manager of the sales reps? Hint: Show the name of the sales rep’s manager’s manager only, and your single SQL statement will need to determine the sales rep’s manager before it can determine the manager of the sales rep’s manager.
J. List the employee names of those that report directly to the manager of the sales manager. Hint: Your SQL statement must determine the sales manger before it can determine manager of the sales rep’s manager, and then it must determine the names of those that report to the manager of the sales rep’s manager.
K. Provide an inventory report that lists the most costly items first. The inventory report should include product identification numbers, product descriptions, unit prices, supplier names, cost, and quantity supplied. Hint: the most costly item is the one in which the product of cost and quantity yields the largest value. Be careful not to confuse cost with price. Price is the value that the products are sold to the customers, and cost is the value that is paid to purchase the products from the suppliers. Also, be aware that the word “product” above refers to the result of multiplication (i.e., the product of cost and quantity).
L. List all of the employees in alphabetical order and each course they have completed in order of date completed. Hint: some employees might not have taken any courses.
