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)
select username, granted_role from user_role_privs
2. Determine what system privileges your account has been granted. (10 points) select * from user_sys_privs
3. Execute the following statement then determine what table privileges your account has been granted. (15 points)
Grant select on student to public;
select table_name, privilege from user_tab_privs;
4. Determine what system privileges the DVONLINE role has. (10 points) ROLE_SYS_PRVS
5. 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)
6. 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?