Assignment Homework

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', '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
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 | ZIP_PK | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("Z"."STATE"='CT') 5 - access("S"."ZIP"="Z"."ZIP")

Statistics
----------------------------------------------------------
0 recursive calls 0 db block