Turbo Toolbox

home *** CD-ROM | disk | FTP | other *** search

/ Turbo Toolbox / Turbo_Toolbox.iso / 1990 / 03 / ssql / appendix.shr next >

Wrap

Text File | 1988-02-03 | 11.0 KB | 307 lines

APPENDIX A The following queries will give you an idea of the power of SQL. Registered users not only receive the answers, they also receive detailed explanations of how to arrive at the answers. In some cases there is more than one solution. Most of the answers require knowledge of joining tables and subqueries. The information is divided into six tables. For those of you not familiar with data normalization, it may seem like it would be better to combine the tables. That would produce more problems than it would solve. SALES TABLE date = Date of sale - YYMMDD format bc = Branch Code - corresponds to code in the Branch table cc = Customer Code - corresponds to code in the Cust table sn = Salesperson number - corresponds to enum in the Emp table pc = Product Code - corresponds to code in the Prod table qty = Quantity purchased Primary key is date,bc,cc,sn,pc We are assuming that a customer will not purchase the same product from the same branch from the same salesperson on the same day! BRANCH TABLE code = Branch code st = Branch state MgrNum = Employee number of manager - corresponds to enum in the Emp table Primary key is code EMP TABLE enum = Employee number name = Employee name mgrn = Employee number of employee's manager - corresponds to enum Primary key is enum PROD TABLE code = Product code desc = Product description Primary key is code MANU TABLE (Manufacturing information) code = Product code - corresponds to code in the Prod table mst = State where the product is manufactured defects = percent of defects last time the product was manufactured Primary key is code CUSTOMER TABLE code = Customer code name = Customer name st = Customer state rating = Customer rating Primary key is code APP-1 The tables should already be on your disk with the following data: SALES BRANCH date bc cc sn pc qty code st MgrNum ------ -- -- -- -- --- ---- -- ------ 870401 b1 c3 11 AB 23 b1 AZ 10 870401 b1 c3 11 DD 34 b2 CA 20 870408 b1 c4 12 AB 2 b3 OR 30 870420 b1 c5 13 AC 450 b4 WA 40 870421 b1 c6 11 FA 87 b5 NV 50 870422 b1 c6 12 EC 12 b6 AZ 60 870402 b2 c5 24 EC 55 870408 b2 c5 24 DZ 81 EMP 870410 b2 c2 27 DA 3 enum name mgrn 870412 b2 c2 27 AB 41 ---- ---------------- ---- 870415 b3 c2 33 AC 33 1 Harvey Bigcheese 870418 b3 c1 35 DZ 125 10 Mary Hizzle 1 870420 b3 c1 35 DA 875 11 Marty Nogglater 12 870425 b3 c5 32 AB 674 12 Gary Hoppertalk 10 870430 b3 c5 32 AC 675 13 Kelly Unlucky 12 870401 b4 c1 41 EA 947 14 Bobo Butters 12 870408 b4 c1 41 EC 452 20 Nancy Umlat 1 870415 b4 c1 41 FA 32 22 Larry Moe 20 870402 b5 c6 52 AB 342 24 Curley Moe 22 870408 b5 c4 56 AC 845 27 Quarkly Poslak 20 870420 b5 c4 56 DD 654 30 Vernal Equinox 1 870401 b6 c3 63 DA 45 32 Otto Otter 35 870408 b6 c3 63 DZ 73 33 Yarquark Moon 35 35 Joe Snow 30 37 Iflab Wasmal 35 PROD 40 Telan Rowaq 1 code desc 41 Robby Marslat 43 ---- -------------- 43 Mongus Wombat 40 AB Megawamp 50 Kanga Rat 1 AC Gigasnarf 52 Elark Kaboom 50 DD Decawidgit 54 Agarth Boavlam 50 DA Technowidgit 56 Finster Funankle 52 DZ Electrowidgit 60 Xero Xanadu 1 EA Nanomouse 62 Duncan Donut 60 EC RGBMouse 63 Duncan Bagel 60 FA Dynamic Disk 64 Ticia Splatt 60 MANU CUSTOMER code mst defects code name st rating ---- --- ------- ---- --------------- -- ------ AB WA 3 c1 Compugorp WA 20 AC AZ 0 c2 Techoharps OR 15 DD WA 3 c3 Organomice AZ 34 DA OR 10 c4 QuarkCo AZ 10 DZ AZ c5 Marswarp CA 30 EA AZ 12 c6 Multicrud NV 2 EC ID 2 FA NV 3 AB ID 5 APP-2 1. What is the total number of different products sold to customer c1? 2. Get product numbers for all products supplied to all customers in California. 3. Get customer numbers for customers who have purchased from any branch which sells products manufactured in Arizona. 4. Get the employee name and corresponding manager name for every employee who has a manager. 5. Get the employee name, corresponding manager name, and the manager's manager name for all instances where the three exist. For example, the following would be a row from the result: Duncan Donut Xero Xanadu Harvey Bigcheese This is because Duncan works for Xero and Xero works for Harvey. 6. For sales from 870410 to 870420, print the date, customer name and salesperson name, product name and qty for each sale. 7. Get customer names and product names for customers who have purchased items not manufactured in their own state. Sort it by product description within customer name. 8. Get all columns from the sales table for the customer with the highest rating. 9. Get branch code, customer names and product names for who purchase products manufactured in their own state from a branch in their own state. 10. Get branch numbers for branches who sell to both c1 and c2. 11. Get product codes for products sold to any customer in California. 13. Get product numbers and corresponding customer names for products manufactured in the same state as a customer. 14. Get customer numbers for customers sold at least one product from a branch not in the same state. 15. Get branch codes for branches which sell at least one product sold by at least one branch which sells product DD. 16. Get all pairs of state values such that the branch in the first state sells to a customer in a second state. 17. Get all [State, product code, State] where the branch in the first state sells the product to the customer in the second state. Exclude rows where the states are the same. APP-3 18. What is the average quantity for a purchase of a product that ends in 'A'? 19. Did Yarquark Moon's manager sell anything to customer c1? 20. What are the names of managers who actually sold something? 21. In order to determine the significance of the customer rating, what is the average quantity for each rating, sorted by rating. Just list the rating and the average quantity. 22. How many purchases of Megawamps were sold by a branch in AZ and what was the average quantity sold? 23. What are the names of managers whose salespeople have sold products to Organomice? 24. Get the manager name and the total quantity sold by that manager's immediate subordinates. 25. Get the defects and the average quantity sold for each level of defects. Only include products for which we track defects. 26. Who of Xero Xanadu's subordinates sold Technowidgits and what were the quantities sold? APP-4 APPENDIX B Answers to the questions in the tutorial: 1. List all the data in the table. select * from cust; code name st rating ---- --------------- -- ------ c1 Compugorp WA 20 c2 Techoharps OR c3 Organomice AZ 36 3 rows selected 2. List the customer name and rating for all customers in AZ. select name, rating from cust where st = 'AZ'; name rating --------------- ------ Organomice 36 1 row selected 3. List all the data for ratings from 10 to 35. select * from cust where rating between 10 and 35; code name st rating ---- --------------- -- ------ c1 Compugorp WA 20 1 row selected 4. List the average rating. select avg(rating) from cust; AVG(rating) ----------- 28 3 rows selected 5. How many customers have a rating of less than 25? select count(code) from cust where rating < 25; CNT(code) --------- 1 1 row selected APP-5