Name: _______________________
Homework (100 points)
Part 1
Using the following Data Dictionary views write the statements that will perform the following actions. Be sure to test your statements. (Do not use SELECT *)
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
USER_ROLE_PRIVS
USER_SYS_PRIVS
USER_TAB_PRIVS
|
Question |
SQL statement or Answer |
|
1. Determine what privileges your account has been granted through a role. (10 points) |
|
|
1. Determine what system privileges your account has been granted. (10 points) |
|
|
1. Execute the following statement then determine what table privileges your account has been granted. (15 points) 1. 1.Grant select on student to public;
|
|
|
1. Determine what system privileges the DVONLINE role has. (10 points) |
|
|
1. Analyze the following query and write a description of the output it produces. (15 points)
|
|
|
SELECT COUNT(DECODE(SIGN(total_capacity-20), -1, 1, 0, 1)) "<=20", COUNT(DECODE(SIGN(total_capacity-21), 0, 1, -1, NULL, DECODE(SIGN(total_capacity-30), -1, 1)))"21-30", COUNT(DECODE(SIGN(total_capacity-30), 1, 1)) "31+" FROM (SELECT SUM(capacity) total_capacity, course_no FROM section GROUP BY course_no) |
|
|
1. Determine the top three zip codes where most of the students live. Use an analytical function. The query will product 10 rows. (10 points)
|
|
Part 2
Analyze the file from Doc Share called utlpwdmg.sql and analyze the code in this file. Write a paragraph that describes what the function performs. What are the inputs parameters, the output parameter and what does the function do? (30 points)
Name: _______________________
Homework (100 points)
Part 1
Using the following Data Dictionary views write the statements that will perform the following actions. Be sure to test your statements. (Do not use SELECT *)
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
USER_ROLE_PRIVS
USER_SYS_PRIVS
USER_TAB_PRIVS
|
Attachments
|
