Page no:57
11. Display sales persons number wise maximum amt from order table. 12. Display the largest order taken by each salesperson on each date. 13. Display the details of maximum orders above 3000. 14. Display details of orders order number & date wise 15. Display customer’s highest ratings in each city. 16. Write a query that totals the orders for each day & places the results in descending order. Exercise 4 1. Add a column curr_bal in orders table for current balance 2. Increase commission of all sales persons by 200. 3. Delete all orders where odate is less than 5-2-05 Exercise 5 1. Display names of all customers matched with the salespeople serving them. 2. Find all orders by customers not located in same cities as their Salespersons. 3. Display each order number followed by the name of customer who made it. 4. Calculate the amount of salespersons commissions on each order by a customer with a rating above 100. 5. Display the pairs of salespeople who are living in the same city. Exclude combinations of sales people with themselves as well as duplicate rows with the order reversed. 6. Display the names & cities of all customers with same rating as Hoffman. Exercise 6 1. Write a query that uses a sub-query to obtain all orders for the customer named ‘Gopal’. Assume you do not know the customer number. 2. Write a query that produces the names & ratings of all customers who have above-average orders. 3. Write a query that selects the total amt in orders for each salesperson for whom this total is greater than the amount of the largest order in table. Exercise 7 1. Create a union of two queries that shows the names, cities 7 ratings of all customers. Those with a rating of 200 or greater will also have ratings “high rating”, while the others will have the words “low rating”. 2. Write a command that produces the name & number of each salesperson & each customer with more than one current order. Put results in alphabetical order. Exercise 8 1. Create an index that would permit each salesperson to retrieve his or her orders grouped by date quickly. 2. Create a view that shows all of the customers who have highest ratings.