Answer Happy

3. Change the line size to 300 4. Display the SQL commands using: SET ECHO ON 5. Use the Spool command to create the log file (Mod10LastnameFirstname.txt) 6. To terminate log file use: SPOOL OFF command FTP the Mod10LastnameFirstname.txt file to your local computer and submit on Canvas Using the Event.sql script file provided (same as Module 5 assignment), create the Event schema: Event.sql Athlete (aID NUMBER, aName VARCHAR2 (20), Ranking NUMBER, age NUMBER) Event (EID CHAR(7), eName VARCHAR2(20), eLocation VARCHAR2(20)) Competes ( aID NUMBER, eID CHAR(7), position NUMBER, Sport, VARCHAR2(20), year NUMBER) Equipment (aID NUMBER, modelID NUMBER, Model VARCHAR2(20), Brand VARCHAR2(20)) To see the execution plans for queries use: SET AUTOTRACE TRACEONLY EXPLAIN To turn off explain plan for queries: SET AUTOTRACE OFF (if you want to observe what the resulting tuple set is). Run the following 2 Queries: Insert TWO screen shots of the explain plans, one for each query Then answer questions 1 and 2 Query 1: SELECT A. aName, E. EName FROM Athlete A, Event E, Competes C; Query 2: SELECT A.aName, E. EName FROM Athlete A, Event E, Competes C WHERE A.aID = C.aID AND E.EID = C.eID AND E. elocation = 'Minneapolis';
4. List the following for each of the 2 queries: Look at the top row and compare Query1 and Query 2: SELECT STATEMENT top row d. Number of rows in the result (compare Query 1 and Query 2) e. Total Bytes (compare Query 1 and Query 2) a. Total Cost (compare Query 1 and Query 2) PART 2 Directions - Query Tree Using the following schena: Athlete (aTD, atiane, Ranking, age) Event (eID, ekane, eLocation) Competes (aID, e10, position, sport, year) consider the following query: SELECT A.aName, C.sport, C.Year FROM Athlete A, Event B, Competes C WHERE A.aID = C.aID AND E.EID = C.eID AND E. EName = * X Games' RND A. Ranking =1 AND A.age >18; 5. Draw the Query tree using Selection, Projection, and Cross product (orx). 6. Draw a query tree that optimizes the SQL query. PART 3 Directions - Estimating IOO Cost Using two relations R and S, with the join condition Ri=Sj, and given the following information: - M pages in R with pR tuples per page - N pages in S with pS tuples per page R contains: - 500 pages - 70 tuples per page S contains: - 1000 pages - 60 tuples per page Buffer size is 102 (inclusive of 2 additional buffers) 7. Compute the 1/O cost for R JOIN S using a block nested loop join 8. Compute the 1/0 cost for S JOIN R using a block nested loop join 9. Compute the 1/0 cost for R and S using a hash join.
Note dynanic statistics uted; dyranic caspline \{levelu?\} 1. Look at the EXPLAIN PLAN for each Query and describe what you see regarding the number of rows, the cost and the difference between merge join cartesian and hash join. 2. List the following for each of the 2 queries: Look at the top row and compare Query1 and Query 2: SELECT STATEMENT top row a. Number of rows in the result (compare Query 1 and Query 2) b. Total Bytes (compare Query 1 and Query 2) c. Total Cost (compare Query 1 and Query 2) the following 2 Queries, Insert TWO screen shots of the explain plans, one for each query n answer question 3 and 4 Query 1: SELECT A.aName FroM Athlete A, Competes C1, Competes C2 WHERE A.aID = C1.aID AND A.aID = C2.aID AND C1.eID ∠> C2.eID Query 2: SELECT DISTINCT λ.aName From Athlete λ, Competes Cl. Competes e2 WHERE A.aID = C1.aID AND A.​aID =C2.aID AND C1.eID ∠> C2.eID 3. Look at the EXPLAIN PLAN for each Query and describe what you see regarding the number of rows.