Assignment #3 Solution

Write the queries necessary to obtain the required information. Make sure all columns you return have descriptive column headings.







Return the Minimum and Maximum sales for all offices.
 

Determine how many orders were made in 1989. Return the number of rows that meet this condition.



List the number of different titles in the sales reps table. Only list each title once and unknown titles should be ignored.



List the average quota for salesreps in office 21.



List the average sale amount for all sale reps in each office.



For each salesrep that has made an order, list the minimum, maximum and average order amount for all their orders. Include only those orders made anytime from 1990-1999. Omit from the list any salesrep that has only made 1 order in this time frame. Sort the results by Empl_Num.



Use a sub-query to list the Customer number; Name and Credit Limit of any customers who have exceeded their credit limit (amount credit limit) on any order.



Use a subquery and using the “all” keyword to find the customer number, Salesrep id, and CreditLimit of every customer whose CreditLimit is larger than the CreditLimit of all of the customers of sales rep number 109.



Do question 8, still using the subquery but do not use the “all” keyword.



Use sub query and “in” keyword to print the salesreps (ids) who have taken order for the companies ‘Zetacorp’ or ‘JCP Inc.’ . Duplicate rows is not allowed



Use sub query to find the id and the name of every sales rep that represents at least one customer with a credit limit of greater than $5000.



Use sub query and keyword “exists” to list the id and the name of the salesreps in which some customers have orders some products in their hiredate.



List all the products (Mfr_ID and Product_ID) that have never been sold. Use the ‘Exists’ clause.





Updates Questions:
Insert the following information into the OFFICES table:
Office: 39 City: Miami Region: Southern Manager: 106 Target: 1000000 Sales: 0



Write an insert statement to add Your Name as Empl_Num 772. Use the date the insert is run as the Hire date (sysdate). Sales are zero. Other column remain NULL;



Write an insert statement to add 'Tom Sawyer' Empl_Num 814. Use the date the insert is run as the Hire date (sysdate). Sales are zero. Use implicit null values for columns that are not mentioned.



Delete all orders for employees 108, 101, 102.


Delete all sales reps that have no orders and were hired before Jan 1 1987.


Update your employee record with the following:
Age: 37 Rep_Office:39 Title: Senior VP Manager: NULL Quota: 100000

Increase customers credit limit by 20% for all customers that have 2 or more orders in which each order is more than 25,000.



Increase the credit limit of any customer who has any order that exceeds their credit limit. The new credit limit should be set to their maximum order amount plus $1,000. This must be done in 1 SQL statement.



Views and Security Questions
Create a view to show the Sales rep Name, and city that the Sales rep works in.
 

Grant select access of the view created in question 22 to your Database instructors: Ahmad R. Hadaegh (with user id ahadaegh).



Create a view to show the customer name, product, description, quantity ordered and value of parts ordered. The column heading for the customers name should be ‘CustName’ and the column heading for value of parts ordered should be ‘Value’.
 

Grant select access of the view created in question 24 to public



Revoke access on view created in question 24 from Public.



Using the view created in question 24 above, list all information for product ‘L14’.




Create a view called TheManagers to list the name of all sales reps that manage some office. Along with the managers name, list the office number and city for each office.



Grant all privileges on the view created in question 28 to your instructor.



Grant Select, Insert and Update on the Offices table to userids ‘jschmidt’ and ‘kmart’.