Accounting

Topics: SQL, Decibel Pages: 8 (809 words) Published: September 10, 2014

Name:

Homework (50 points)
Using the student schema from week 2, provide answers to the following questions. Question
SQL statement or Answer
1. Generate statistics for the student, enrollment, grade, and zipcode tables (15 pts)

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('D02246617', 'STUDENT');

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('D02246617', 'ENROLLMENT');

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('D02246617', 'GRADE');

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('D02246617', 'ZIPCODE');

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS('D02246617', 'STUDENT');
3 END;
4 /

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS('D02246617', 'ENROLLMENT'); 3 END;
4 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS('D02246617', 'GRADE');
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS('D02246617', 'ZIPCODE');
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> DESCRIBE USER_TABLES;

SQL> SELECT TABLE_NAME, NUM_ROWS FROM USER_TABLES;
2. Write queries that performs a join, a subquery, a correlated subquery using the student, enrollment, grade, and zipcode tables. Execute each query to show that it produces the same results. (15 pts) SQL> SELECT S.LAST_NAME, Z.ZIP

2 FROM STUDENT S JOIN ZIPCODE Z
3 ON Z.ZIP = S.ZIP
4 WHERE STATE = 'MI';

SQL> SELECT LAST_NAME, ZIP
2 FROM STUDENT S
3 WHERE ZIP IN (SELECT ZIP
4 FROM ZIPCODE Z WHERE STATE = 'MI');

SQL> SELECT LAST_NAME, ZIP
2 FROM STUDENT S
3 WHERE ZIP = (SELECT ZIP
4 FROM ZIPCODE Z
5 WHERE S.ZIP = Z.ZIP
6 AND STATE = 'MI');

3. Below is the autotrace output and execution plan for each query (join, subquery and correlated subquery). Analyze the results and state which performs best and why. Write an analysis of what operations are being performed for each query. Determine which query is the most efficient and explain why (20 pts)

ANSWER:
During JOIN query, there were total of 5 operation steps; Subquery had 4 steps and correlated query 5 steps. For most of the steps, the Cost for CPU was less during correlated query compared to the other two queries. Further, according to the statistics, correlated query has 164 consistent gets compared to the other two which ad 277. In the overall performance, the correlated query is more efficient. Below is the stat comparison:

STATISTICS
Join query
Subquery
Correlated
Recursive calls
0
0
0
Db block gets
0
0
0
Consistent gets
277
277
164
Physical reads
0
0
0
Redo size
0
0
0
Bytes sent via SQL*Net to client
800
800
800
Bytes received via SQL*Net from client
345
345
345
SQL*Net roundtrips to/from client
3
3
3
Sorts (Memory)
1
1
1
Sorts (disk)
0
0
0
Rows Processed
18
18
18

-----JOIN QUERY-----
SQL> SET AUTOTRACE ON
SQL> SELECT LAST_NAME, S.ZIP
2 FROM STUDENT S JOIN ZIPCODE Z
3 ON S.ZIP = Z.ZIP
4 WHERE STATE = 'CT'
5 ORDER BY LAST_NAME;

LAST_NAME ZIP
------------------------- -----
Allende 06850

18 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 775439700
----------------------------------------------------------------------------- | Id| Operation | Name | Rows| Bytes |Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 35 | 805 | 6 (17)| 00:00:01 | | 1 | SORT ORDER BY | | 35 | 805 | 6 (17)| 00:00:01 | | 2 | NESTED LOOPS | | 35 | 805 | 5 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | STUDENT | 268 | 3752 | 3 (0)| 00:00:01 | |*4 | TABLE ACCESS BY INDEX ROWID| ZIPCODE | 1 | 9 | 1 (0)| 00:00:01 | |*5 | INDEX UNIQUE SCAN |...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Accounting Essay
  • accounting fraud Essay
  • Essay on Accounting
  • Management Accounting Essay
  • Accounting Essay
  • management accounting Essay
  • Accounting Essay
  • cost accounting Essay

Become a StudyMode Member

Sign Up - It's Free