Section 17 Quiz Database Programming With SQL

1.         The following table shows some of the output from one of the data dictionary views. Which view is being queried?
USERNAME    PRIVILEGE     ADMIN_OPTION
USCA_ORACLE_SQL01_S08            CREATE          VIEW NO
USCA_ORACLE_SQL01_S08            CREATE          TABLE NO
USCA_ORACLE_SQL01_S08            CREATE          SYNONYM NO
USCA_ORACLE_SQL01_S08            CREATE          TRIGGER NO
USCA_ORACLE_SQL01_S08            CREATE          SEQUENCE NO
USCA_ORACLE_SQL01_S08            CREATE          DATABASE NO
 Mark for Review
(1) Points
            role_sys_privs (lists system privileges granted to roles)
            user_sys_privs (lists system privileges granted to the user) (*)
            role_tab_privs (lists table privileges granted to roles)
            user_tab_privs_recd (lists object privileges granted to the user)

4.         What system privilege must be held in order to login to an Oracle database?  Mark for Review
(1) Points
            CREATE LOGIN
            CREATE SESSION (*)
            CREATE LOGON
            No special privilege is needed; if your username exists in the database, you can login.

5.         Which of the following best describes a role in an Oracle database?   Mark for Review
(1) Points
            A role is an object privilege which allows a user to update a table.
            A role is a name for a group of privileges. (*)
            A role is a type of system privilege.
            A role is the part that a user plays in querying the database.

6.         Which of the following Object Privileges can be granted on an individual column on a table? (Choose two)     Mark for Review
(1) Points
                                    (Choose all correct answers)  
            Delete
            Select
            Update (*)
            References (*)

7.         REGULAR EXPRESSIONS can be used on CHAR, CLOB, and VARCHAR2 datatypes? (True or False)            Mark for Review
(1) Points
            True (*)
            False

8.         Parentheses are not used to identify the sub expressions within the expression. True or False?           Mark for Review
(1) Points
            True
            False (*)

9.         REGULAR EXPRESSIONS does exactly the same as LIKE--no more and no less. (True or False?)   Mark for Review
(1) Points
            True
            False (*)

10.       Which of the following best describes the purpose of the REFERENCES object privilege on a table?            Mark for Review
(1) Points
            It allows a user to create foreign key constraints on the table. (*)
            It allows a user to refer to the table in a SELECT statement.
            It allows a user's session to read from the table but only so that foreign key constraints can be checked.
            It allows the user to create new tables which contain the same data as the referenced table.

11.       User CRAIG creates a view named INVENTORY_V, which is based on the INVENTORY table. CRAIG wants to make this view available for querying to all database users. Which of the following actions should CRAIG perform?         Mark for Review
(1) Points
            He should assign the SELECT privilege to all database users for the INVENTORY table.
            He is not required to take any action because, by default, all database users can automatically access views.
            He must grant each user the SELECT privilege on both the INVENTORY table and INVENTORY_V view.
            He should assign the SELECT privilege to all database users for INVENTORY_V view. (*)
1.         Regular expressions used as check constraints are another way to ensure data is formatted correctly prior to being written into the database table. True or False?            Mark for Review
(1) Points
            True (*)
            False

2.         Regular expressions are a method of describing both simple and complex patterns for searching and manipulating. True or False?    Mark for Review
(1) Points
            True (*)
            False

3.         REGULAR EXPRESSIONS can be used as part of a contraint definition. (True or False?)     Mark for Review
(1) Points
            True (*)
            False

4.         A role can be granted to another role. True or False?   Mark for Review
(1) Points
            True (*)
            False

5.         Which of the following simplifies the administration of privileges?    Mark for Review
(1) Points
            A role (*)
            A trigger
            An index
            A view

6.         Which keyword would you use to grant an object privilege to all database users?       Mark for Review
(1) Points
            ADMIN
            PUBLIC (*)
            ALL
            USERS

7.         User CRAIG creates a view named INVENTORY_V, which is based on the INVENTORY table. CRAIG wants to make this view available for querying to all database users. Which of the following actions should CRAIG perform?         Mark for Review
(1) Points

            He is not required to take any action because, by default, all database users can automatically access views.

            He must grant each user the SELECT privilege on both the INVENTORY table and INVENTORY_V view.

            He should assign the SELECT privilege to all database users for INVENTORY_V view. (*)

            He should assign the SELECT privilege to all database users for the INVENTORY table.

8.         If you are granted privileges to your friend's object, by default you may also grant access to this same object to other users. True or False?    Mark for Review
(1) Points
            True
            False (*)

9.         Which data dictionary view shows which system privileges have been granted to a user?       Mark for Review
(1) Points

            USER_SYSTEM_PRIVS
            USER_TAB_PRIVS
            USER_SYS_PRIVS (*)
            USER_SYSTEM_PRIVILEGES

10.       A Schema is a collection of Objects such as Tables, Views, and Sequences. True or False?   Mark for Review
(1) Points
            True (*)
            False

11.       The database administrator wants to allow user Marco to create new tables in his own schema. Which privilege should be granted to Marco? Mark for Review
(1) Points
            CREATE OBJECT
            CREATE ANY TABLE
            CREATE TABLE (*)
            SELECT

12.       You are the database administrator. You want to create a new user JONES with a password of MARK, and allow this user to create his own tables. Which of the following should you execute?      Mark for Review
(1) Points

            CREATE USER jones IDENTIFIED BY mark;
GRANT CREATE SESSION TO jones;

            CREATE USER jones IDENTIFIED BY mark;
GRANT CREATE SESSION TO jones;
GRANT CREATE TABLE TO jones;
(*)

            CREATE USER jones IDENTIFIED BY mark;
GRANT CREATE TABLE TO jones;

            GRANT CREATE SESSION TO jones;
GRANT CREATE TABLE TO jones;

13.       Which Object Privilege (other than Alter) can be granted to a Sequence?        Mark for Review
(1) Points
            INSERT
            DELETE
            UPDATE
            SELECT (*)

14.       Object privileges are:   Mark for Review
(1) Points
            Required to gain access to the database.
            Required to manipulate the content of objects in the database. (*)
            Named groups of related privileges given to a user.
            A collection of objects, such as tables, views, and sequences.

15.       The following table shows some of the output from one of the data dictionary views. Which view is being queried?
USERNAME    PRIVILEGE     ADMIN_OPTION
USCA_ORACLE_SQL01_S08            CREATE          VIEW NO
USCA_ORACLE_SQL01_S08            CREATE          TABLE NO
USCA_ORACLE_SQL01_S08            CREATE          SYNONYM NO
USCA_ORACLE_SQL01_S08            CREATE          TRIGGER NO
USCA_ORACLE_SQL01_S08            CREATE          SEQUENCE NO
USCA_ORACLE_SQL01_S08            CREATE          DATABASE NO
 Mark for Review
(1) Points
            user_tab_privs_recd (lists object privileges granted to the user)
            role_sys_privs (lists system privileges granted to roles)
            role_tab_privs (lists table privileges granted to roles)
            user_sys_privs (lists system privileges granted to the user) (*)

1.         Which of the following best describes a role in an Oracle database?   Mark for Review
(1) Points
            A role is a type of system privilege.
            A role is a name for a group of privileges. (*)
            A role is the part that a user plays in querying the database.
            A role is an object privilege which allows a user to update a table.

2.         A schema is:    Mark for Review
(1) Points
            A named group of related privileges given to a user.
            A collection of objects, such as tables, views, and sequences. (*)
            Required to gain access to the database.
            Required to manipulate the content of objects in the database.

3.         System privileges are:  Mark for Review
(1) Points
            Required to manipulate the content of objects in the database.
            Named groups of related privileges given to a user.
            Required to gain access to the database. (*)
            A collection of objects, such as tables, views, and sequences.

4.         You grant user AMY the CREATE SESSION privilege. Which type of privilege have you granted to AMY?  Mark for Review
(1) Points
            A user privilege
            An object privilege
            An access privilege
            A system privilege (*)

5.         User ADAM has successfully logged on to the database in the past, but today he receives an error message stating that (although he has entered his password correctly) he cannot log on. What is the most likely cause of the problem? Mark for Review
(1) Points
            One or more object privileges have been REVOKEd from Adam.
            ADAM's user account has been removed from the database.
            ADAM's CREATE USER privilege has been revoked.
            ADAM's CREATE SESSION privilege has been revoked. (*)

6.         Which of the following are system privileges?
(Choose two)   Mark for Review
(1) Points
                                    (Choose all correct answers)  
            INDEX
            UPDATE
            CREATE TABLE (*)
            CREATE SYNONYM (*)

7.         _________________ are special characters that have a special meaning, such as a wildcard character, a repeating character, a non-matching character, or a range of characters. You can use several of these symbols in pattern matching.         Mark for Review
(1) Points
            Reference checks
            Clip Art
            Alphanumeric values
            Meta characters (*)

8.         Regular expressions used as check constraints are another way to ensure data is formatted correctly prior to being written into the database table. True or False?            Mark for Review
(1) Points
            True (*)
            False

9.         REGULAR EXPRESSIONS does exactly the same as LIKE--no more and no less. (True or False?)   Mark for Review
(1) Points
            True
            False (*)

10.       A role can be granted to another role. True or False?   Mark for Review
(1) Points
            True (*)
            False

11.       Which of the following best describes the purpose of the REFERENCES object privilege on a table?            Mark for Review
(1) Points
            It allows the user to create new tables which contain the same data as the referenced table.
            It allows a user's session to read from the table but only so that foreign key constraints can be checked.
            It allows a user to refer to the table in a SELECT statement.
            It allows a user to create foreign key constraints on the table. (*)

12.       When a user is logged into one database, he is restricted to working with objects found in that database. True or False?  Mark for Review
(1) Points
            True
            False (*)

13.       What Oracle feature simplifies the process of granting and revoking privileges?         Mark for Review
(1) Points
            Object
            Data dictionary
            Role (*)
            Schema

14.       Which of the following statements about granting object privileges is false?   Mark for Review
(1) Points
            Object privileges can only be granted through roles. (*)

            An object owner can grant any object privilege on the object to any other user or role of the database.

            To grant privileges on an object, the object must be in your own schema, or you must have been granted the object privileges WITH GRANT OPTION.

            The owner of an object automatically acquires all object privileges on that object.

15.       When granting an object privilege, which option would you include to allow the grantee to grant the privilege to another user?         Mark for Review
(1) Points
            WITH GRANT OPTION (*)
            WITH ADMIN OPTION
            PUBLIC
            FORCE

1.         Which of the following best describes a role in an Oracle database?   Mark for Review
(1) Points
            A role is a name for a group of privileges. (*)
            A role is a type of system privilege.
            A role is an object privilege which allows a user to update a table.
            A role is the part that a user plays in querying the database.

2.         Which of the following are system privileges?
(Choose two)   Mark for Review
(1) Points
                                    (Choose all correct answers)  
            CREATE SYNONYM (*)
            UPDATE
            CREATE TABLE (*)
            INDEX

3.         User ADAM has successfully logged on to the database in the past, but today he receives an error message stating that (although he has entered his password correctly) he cannot log on. What is the most likely cause of the problem? Mark for Review
(1) Points
            ADAM's CREATE USER privilege has been revoked.
            One or more object privileges have been REVOKEd from Adam.
            ADAM's user account has been removed from the database.
            ADAM's CREATE SESSION privilege has been revoked. (*)

4.         Which of the following are object privileges? (Choose two)    Mark for Review
(1) Points
                                    (Choose all correct answers)  
            CREATE TABLE
            INSERT (*)
            SELECT (*)
            DROP TABLE

5.         By Controlling User Access with Oracle Database Security, you can give access to specific Objects in the Database. True or False?          Mark for Review
(1) Points
            True (*)
            False

6.         User JAMES has created a CUSTOMERS table and wants to allow all other users to SELECT from it. Which command should JAMES use to do this?          Mark for Review
(1) Points
            CREATE PUBLIC SYNONYM customers FOR james.customers;
            GRANT SELECT ON customers TO ALL;
            GRANT SELECT ON customers TO PUBLIC; (*)
            GRANT customers(SELECT) TO PUBLIC;

7.         Parentheses are not used to identify the sub expressions within the expression. True or False?           Mark for Review
(1) Points
            True
            False (*)

8.         REGULAR EXPRESSIONS can be used on CHAR, CLOB, and VARCHAR2 datatypes? (True or False)            Mark for Review
(1) Points
            True (*)
            False

9.         REGULAR EXPRESSIONS does exactly the same as LIKE--no more and no less. (True or False?)   Mark for Review
(1) Points
            True
            False (*)

10.       Roles are:         Mark for Review
(1) Points
            Required to gain access to the database.
            Named groups of related privileges given to a user or another role. (*)
            A collection of objects, such as tables, views, and sequences.
            Required to manipulate the content of objects in the database.

11.       Which data dictionary view shows which system privileges have been granted to a user?       Mark for Review
(1) Points
            USER_SYS_PRIVS (*)
            USER_SYSTEM_PRIVS
            USER_SYSTEM_PRIVILEGES
            USER_TAB_PRIVS

12.       Which statement would you use to remove an object privilege granted to a user?       Mark for Review
(1) Points
            ALTER USER
            REMOVE
            DROP
            REVOKE (*)

13.       You need to grant user BOB SELECT privileges on the EMPLOYEES table. You want to allow BOB to grant this privileges to other users. Which statement should you use? Mark for Review
(1) Points

            GRANT SELECT ON employees TO bob;
            GRANT SELECT ON employees TO bob WITH ADMIN OPTION;
            GRANT SELECT ON employees TO bob WITH GRANT OPTION; (*)
            GRANT SELECT ON employees TO PUBLIC WITH GRANT OPTION;

14.       If you are granted privileges to your friend's object, by default you may also grant access to this same object to other users. True or False?    Mark for Review
(1) Points
            True
            False (*)

15.       User BOB's schema contains an EMPLOYEES table. BOB executes the following statement:
GRANT SELECT ON employees TO mary WITH GRANT OPTION;

Which of the following statements can MARY now execute successfully? (Choose two)

 Mark for Review
(1) Points
                                    (Choose all correct answers)  
            DROP TABLE bob.employees;
            GRANT SELECT ON bob.employees TO PUBLIC; (*)
            REVOKE SELECT ON bob.employees FROM bob;
            SELECT FROM bob.employees; (*)

1.         REGULAR EXPRESSIONS can be used as part of a contraint definition. (True or False?)     Mark for Review
(1) Points
            True (*)
            False

2.         Select the correct REGULAR EXPRESSION functions: (Choose two) Mark for Review
(1) Points
                                    (Choose all correct answers)  
            REGEXP_INSTR, REGEXP_SUBSTR (*)
            REGEXP_LIKE, REGEXP_NEAR
            REGEXP_LIKE, REGEXP_REPLACE (*)
            REGEXP_REPLACE, REGEXP_REFORM

3.         Which of these SQL functions used to manipulate strings is NOT a valid regular expression function ?            Mark for Review
(1) Points
            REGEXP_LIKE
            REGEXP (*)
            REGEXP_REPLACE
            REGEXP_SUBSTR

4.         By Controlling User Access with Oracle Database Security, you can give access to specific Objects in the Database. True or False?          Mark for Review
(1) Points
            True (*)
            False

5.         You want to grant privileges to user CHAN that will allow CHAN to update the data in the EMPLOYEES table. Which type of privileges will you grant to CHAN?        Mark for Review
(1) Points
            User privileges
            Administrator privileges
            System privileges
            Object privileges (*)

6.         Which of the following privileges must be assigned to a user account in order for that user to connect to an Oracle database?     Mark for Review
(1) Points
            ALTER SESSION
            OPEN SESSION
            CREATE SESSION (*)
            RESTRICTED SESSION

7.         The following table shows some of the output from one of the data dictionary views. Which view is being queried?
USERNAME    PRIVILEGE     ADMIN_OPTION
USCA_ORACLE_SQL01_S08            CREATE          VIEW NO
USCA_ORACLE_SQL01_S08            CREATE          TABLE NO
USCA_ORACLE_SQL01_S08            CREATE          SYNONYM NO
USCA_ORACLE_SQL01_S08            CREATE          TRIGGER NO
USCA_ORACLE_SQL01_S08            CREATE          SEQUENCE NO
USCA_ORACLE_SQL01_S08            CREATE          DATABASE NO
 Mark for Review
(1) Points
            user_sys_privs (lists system privileges granted to the user) (*)
            role_tab_privs (lists table privileges granted to roles)
            user_tab_privs_recd (lists object privileges granted to the user)
            role_sys_privs (lists system privileges granted to roles)

8.         Which of the following best describes a role in an Oracle database?   Mark for Review
(1) Points
            A role is the part that a user plays in querying the database.
            A role is a type of system privilege.
            A role is an object privilege which allows a user to update a table.
            A role is a name for a group of privileges. (*)

9.         You create a view named EMPLOYEES_VIEW on a subset of the EMPLOYEES table. User AUDREY needs to use this view to create reports. Only you and Audrey should have access to this view. Which of the following actions should you perform?           Mark for Review
(1) Points
            GRANT SELECT ON employees AND employees_view TO audrey;
            GRANT SELECT ON employees_view TO public;
            GRANT SELECT ON employees_view TO audrey; (*)
            Do nothing. As a database user, Audrey's user account has automatically been granted the SELECT privilege for all database objects.

10.       User CRAIG creates a view named INVENTORY_V, which is based on the INVENTORY table. CRAIG wants to make this view available for querying to all database users. Which of the following actions should CRAIG perform?         Mark for Review
(1) Points
            He should assign the SELECT privilege to all database users for the INVENTORY table.
            He should assign the SELECT privilege to all database users for INVENTORY_V view. (*)
            He is not required to take any action because, by default, all database users can automatically access views.
            He must grant each user the SELECT privilege on both the INVENTORY table and INVENTORY_V view.

11.       Scott King owns a table called employees. He issues the following statement:
GRANT select ON employees TO PUBLIC;
Allison Plumb has been granted CREATE SESSION by the DBA. She logs into the database and issues the following statement:
GRANT ﾠselect ON ﾠscott_king.employees TO jennifer_cho;

True or False: Allison's statement will fail.

 Mark for Review
(1) Points
            True (*)
            False

12.       A role can be granted to another role. True or False?   Mark for Review
(1) Points
            True (*)
            False

13.       Which of the following statements about granting object privileges is false?   Mark for Review
(1) Points
            Object privileges can only be granted through roles. (*)
            An object owner can grant any object privilege on the object to any other user or role of the database.
            To grant privileges on an object, the object must be in your own schema, or you must have been granted the object privileges WITH GRANT OPTION.
            The owner of an object automatically acquires all object privileges on that object.

14.       Which keyword would you use to grant an object privilege to all database users?       Mark for Review
(1) Points
            ALL
            PUBLIC (*)
            ADMIN
            USERS

15.       When a user is logged into one database, he is restricted to working with objects found in that database. True or False?  Mark for Review
(1) Points
            True
            False (*)

1.         A Schema is a collection of Objects such as Tables, Views, and Sequences. True or False?   Mark for Review
(1) Points
            True (*)
            False

2.         Which Object Privilege (other than Alter) can be granted to a Sequence?        Mark for Review
(1) Points
            INSERT
            UPDATE
            DELETE
            SELECT (*)

12.       Which statement would you use to remove an object privilege granted to a user?       Mark for Review
            DROP
            REMOVE
            REVOKE (*)
            ALTER USER

13.       Roles are:         Mark for Review
(1) Points
            Named groups of related privileges given to a user or another role. (*)
            A collection of objects, such as tables, views, and sequences.
            Required to gain access to the database.
            Required to manipulate the content of objects in the database.

14.       Which of the following statements about granting object privileges is false?   Mark for Review
(1) Points
                                 
            An object owner can grant any object privilege on the object to any other user or role of the database.
            The owner of an object automatically acquires all object privileges on that object.
            Object privileges can only be granted through roles. (*)
            To grant privileges on an object, the object must be in your own schema, or you must have been granted the object privileges WITH GRANT OPTION.

15.       Which statement would you use to grant a role to users?         Mark for Review
(1) Points
            ASSIGN
            ALTER USER
            CREATE USER

            GRANT (*)
1. User CHANG has been granted SELECT, UPDATE, INSERT, and DELETE privileges on the EMPLOYEES table. You now want to prevent Chang from adding or deleting rows from the table, while still allowing him to read and modify existing rows. Which statement should you use to do this? Mark for Review
(1) Points
REVOKE INSERT AND DELETE ON employees FROM chang;
REMOVE INSERT, DELETE ON employees FROM chang;
REVOKE ALL ON employees FROM chang;
REVOKE INSERT, DELETE ON employees FROM chang; (*)

2. Which of the following is NOT a database object? Mark for Review
(1) Points
Table
Sequence
View
Subquery (*)

3.Which statement would you use to add privileges to a role? Mark for Review
(1) Points
ASSIGN
ALTER ROLE
GRANT (*)
CREATE ROLE

4.Which of the following statements is true? Mark for Review
(1) Points
Database Links allow users to work on remote database objects without having to log into the other database. (*)
Database Links can be created by any user of a database. You do not need any special privileges to create them.
Database Links are pointers to another schema in the same database.
Database Links are never used in the real world.

1.Which of the following are object privileges? (Choose two) Mark for Review
(1) Points
(Choose all correct answers)
CREATE TABLE
INSERT (*)
DROP TABLE
SELECT (*)

2.What Oracle feature simplifies the process of granting and revoking privileges? Mark for Review
(1) Points
Role (*)
Schema
Data dictionary