Page no:56
Lab Exercises Exercise1 1. Create table Salespeople with fields snum, sname, city, commission 2. Orders table with field’s onum, odate, snum, amt 3. Customers table with field’s cnum, cname, city, rating, snum Exercise 2 1. Add at least 10 records 2. Display all the records with all sales peoples information. 3. Display the details of fields sname, commission 4. Display the odate, snum, onum, amt from orders table. 5. Display snum from orders table without duplications. 6. Display name & city of salesman where city is “Pune 7. Display all details of customer where rating is 100. 8. Display all details from customer table where salespersons number is 1001. 9. Display the numbers of sales persons, with orders currently in the orders table without any repeats. 10. Display all customers where rating is more than 200 11. Display all customers where city is ‘Mumbai’ rating is more than 100. 12. Display all customers where city is either ‘Pune’ or ‘Mumbai’ 13. List all customers not having city ‘Pune’ or rating more than 100 14. List all orders between order dates 10/03/05 to 30/3/05 15. Display all orders more that 1000 amt. 16. Display names & cities of all salespeople in ‘Pune’ with a commission above 10. 17. Display all customers excluding those, with rating less than equal to 100, unless they are located in ‘Nagar’ 18. Display all sales persons names starting with character ‘G’ 19. Display all sales persons names starting with character ‘G’, the 4th character is ‘A’ & the rest of characters will be any. 20. Find all records from customers table where city is not known i.e. NULL. 21. Display all the customer’s names begins with a letter A to G. 22. Assume each salesperson has a 12% commission on order amt. Display orderno, snum, commission for that order. Exercise 3 1. Display all the customers’ records, arranged on name. 2. Display all customers records arranged on rating in desc. Order. 3. Display all sales persons records arranged on snum 4. Display the count for total number of customers in customers table. 5. Display the count of snum in order table without duplication of snum. 6. Display the counts of all orders for Feb05 7. Display the count of different non-NULL city values in the customer’s table. 8. Display the maximum outstanding amount as blnc+amt 9. Display the minimum rating within customers table. 10. Display average of amt.