1- 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

  

Salesreps Table: EMPL_NUM NAME 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 Offices Table: OFFICE CITY 22 Denver 11 New York 12 Chicago 13 Atlanta 21 Los Angeles Customers Table: CUST_NUM COMPANY 2111 2102 2103 2123 2107 2115 2101 2112 2121 2114 2124 2108 2117 2122 2120 2106 2119 2118 2113 2109 2105 Smithson Corp. Jones Mfg. Zetacorp JCP Inc. First Corp. Acme Mfg. Carter and Sons Ace International QMA Assoc. Orion Corp. Peter Brothers Holm and Landis J.P. Sinclair Three-Way Lines AGE REP_OFFICE TITLE REGION Rico Enterprises Fred Lewis Corp. 37 31 48 52 Western Eastern Eastern Eastern Western 33 45 41 Solomon Inc. Midwest Systems Ian and Schmidt Chen Associates AAA Investments 62 29 49 CUST_REP 103 101 105 102 110 101 106 108 103 102 107 109 106 HIRE_DATE 13 Sales Rep 11 Sales Rep 21 Sales Rep 11 VP Sales 12 Sales Mgr 12 Sales Rep Sales Rep 21 Sales Mgr 12-FEB-88 12-OCT-89 10-DEC-86 14-JUN-88 19-MAY-87 20-OCT-86 13-JAN-90 12-OCT-89 01-MAR-87 12 Sales Rep 22 Sales Rep 14-NOV-88 105 102 102 109 108 104 103 101 MGR 108 106 104 105 108 50000 65000 108000 CREDIT_LIMIT 40000 35000 20000 65000 108000 45000 43200 40000 55000 35000 30000 50000 65000 25000 60000 20000 TARGET 25000 45000 300000 575000 800000 350000 725000 MANAGER 104 106 108 106 104 101 106 104 108 QUOTA 350000 300000 350000 275000 200000 300000 350000 275000 300000 SALES 186042 692637 735042 367911 835915 SALES 367911 392725 474050 299912 142594 305673 75985 361865 286775 186042