Database Management Systems

ANSWER WITH A QUERY TREE

2) Use query tree to optimize the following query. Use the tables that was provided in previous assignment SELECT cust_num, cust rep, name, rep. office, city FROM Customers, Salesreps, Offices WHERE empl num=cust rep, and rep. office = office AND credit limit >= 5000 And region="western And office in (11, 12) 3) Use query tree to optimize the following query. Use the tables that was provided in previous assignment SELECT Order Num, Name, City FROM Orders, Customers, Salesreps, Offices WHERE Cust = Cust Num AND Cust Rep = Empl Num AND Rep Office=Office AND Amount > 10000 AND region =Western 4) Use query tree to optimize the following query. Use the tables that was provided in previous assignment SELECT order num, mfr, product, rep, price From orders, products Where price > 100 And Qty-On-hand > 3 and mfr = mfr id and product = product_id = Salesreps Table: EMPL_NUM NAME AGE REP_OFFICE TITLE HIRE_DATE MANAGER QUOTA SALES 104 106 108 105 Bill Adams 109 Mary Jones 102 Sue Smith 106 Sam Clark 104 Bob Smith 101 Dan Roberts 110 Tom Synder 108 Larry Fitch 103 Paul Cruz 107 Nacy Angelli 37 31 48 52 33 45 41 62 29 49 13 Sales Rep 12-FEB-88 11 Sales Rep 12-OCT-89 21 Sales Rep 10-DEC-86 11 VP Sales 14-JUN-88 12 Sales Mgr 19-MAY-87 12 Sales Rep 20-OCT-86 Sales Rep 13-JAN-90 21 Sales Mgr 12-OCT-89 12 Sales Rep 01-MAR-87 22 Sales Rep 14-NOV-88 350000 300000 350000 275000 200000 300000 106 104 101 106 104 108 367911 392725 474050 299912 142594 305673 75985 361865 286775 186042 350000 275000 300000 Offices Table: OFFICE CITY REGION MGR TARGET SALES 108 106 22 Denver 11 New York 12 Chicago 13 Atlanta 21 Los Angeles Western Eastern Eastern Eastern Western 104 105 300000 575000 800000 350000 725000 186042 692637 735042 367911 835915 108 Customers Table: CUST_NUM COMPANY CUST_REP CREDIT LIMIT 50000 65000 108000 40000 35000 2111 2102 2103 2123 2107 2115 2101 2112 2121 2114 2124 2108 2117 2122 2120 2106 2119 2118 JCP Inc. First Corp. Acme Mfg. Carter and Sons Ace International Smithson Corp. Jones Mfg. Zetacorp QMA Assoc. Orion Corp. Peter Brothers Holm and Landis J.P. Sinclair Three-Way Lines Rico Enterprises Fred Lewis Corp. Solomon Inc. Midwest Systems lan and Schmidt Chen Associates AAA Investments 103 101 105 102 110 101 106 108 103 102 107 109 106 105 102 102 109 108 104 103 101 20000 65000 108000 45000 43200 40000 55000 35000 30000 50000 65000 25000 60000 20000 25000 45000 2113 2109 2105 Orders Table: ORDER NUM ORDER DAT CUST REP MFR PRODU AMOUNT 31500 17-DEC-89 11-JAN-90 03-JAN-90 10-FEB-90 12-OCT-89 30-JAN-90 02-FEB-90 17-DEC-89 14-JAN-90 23-FEB-90 08-JAN-90 27-DEC-89 20-JAN-90 24-FEB-90 12-OCT-89 22-JAN-90 08-JAN-90 02-MAR-90 29-JAN-90 04-NOV-89 12-OCT-89 15-FEB-90 10-FEB-90 112961 113012 112989 113051 112968 113036 113045 112963 113013 113058 112997 112983 113024 113062 112979 113027 113007 113069 113034 112992 112975 113055 112040 113048 112993 113065 113003 113049 112987 113057 113042 3745 1458 1420 3978 22500 45000 3276 652 1480 652 702 7100 2430 2117 2111 2101 2118 2102 2107 2112 2103 2118 2108 2124 2103 2114 2124 2114 2103 2112 2109 2107 2118 2111 2108 2120 2106 2106 2108 2118 2103 2111 2113 106 105 106 108 101 110 108 105 108 109 107 105 108 107 102 105 108 107 110 108 103 101 102 102 102 109 108 105 103 101 REI ACI FEA QSA ACI ACI REI ACI BIC FEA BIC ACI QSA FEA ACI ACI IMM IMM REI ACI REI ACI IMM REI QSA IMM QSA ACI ACI REI 8 as ez - - s 6 2A44L 41003 114 K47 41004 4100Z 2A44R 41004 41003 112 41003 41004 XK47 114 4100Z 41002 7730 775C 2A45C 09 41002 2A446 4100X 779C 2A45C XK47 779C XK47 4100Y 4100X 2A44R 15000 4104 2925 31350 632 04-JAN-89 27-FEB-90 25-JAN-90 10-FEB-90 31-DEC-89 18-FEB-90 02-FEB-90 24 6 3 3 2 11 24 5 760 2100 150 3750 1896 2130 5625 776 27500 600 22500 Products Table: MFR PRODU DESCRIPTION PRICE QTY ON HAND 2A45C 4100Y XK47 RATCHET LINK WIDGET REMOVER REDUCER PLATE 900-LB BRACE SIZE 3 WIDGET SIZE 4 WIDGET HANDLE BRACE PIN REDUCER REI ACI QSA BIC IMM ACI ACI BIC IMM QSA REI FEA IMM BIC ACI IMM ACI QSA ACI REI IMM ACI FEA IMM REI 41672 779C 41003 41004 41003 887P XK48 2A44L 112 887F 41089 41001 775C 41002 XK48A 41002 2444R 773C 4100X 114 887X 2A44G LEFT HINGE HOUSING BRACE HOLDER RETAINER SIZE 1 WIDGET 500-LB BRACE WIDGET INSTALLER REDUCER SIZE 2 WIDGET RIGHT HINGE 300-LB BRACE WIDGET ADJUSTER MOTOR MOUNT BRACE RETAINER HINGE PIN 79 2750 355 180 1875 107 117 652 250 134 4500 148 54 225 SS 1425 2500 177 76 4500 975 25 243 475 350 210 25 38 0 9 207 139 3 24 203 12 115 223 78 277 5 28 37 167 12 28 37 15 32 14