1.  Which option would you use when creating a view to ensure that no DML operations occur on the view?  Mark for Review 
(1) Points 
      
    FORCE 
  
    NOFORCE 
  
    WITH READ ONLY (*) 
  
    WITH ADMIN OPTION 
  
      
      Incorrect. Refer to Section 10  
  
      
  2.  For a View created using the WITH CHECK OPTION keywords, which of the following statements are true?  Mark for Review 
(1) Points 
      
    The view will allow the user to check it against the data dictionary 
  
    Prohibits changing rows not returned by the subquery in the view definition. (*) 
  
    Prohibits DML actions without administrator CHECK approval 
  
    Allows for DELETES from other tables, including ones not listed in subquery 
  
      
      Incorrect. Refer to Section 10  
  
      
  3.  You can create a view if the view subquery contains an inline view. True or False?  Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Incorrect. Refer to Section 10  
  
      
  4.  What is the purpose of including the WITH CHECK OPTION clause when creating a view?  Mark for Review 
(1) Points 
      
    To make sure that the parent table(s) actually exist 
  
    To keep views form being queried by unauthorized persons 
  
    To make sure that data is not duplicated in the view 
  
    To make sure no rows are updated through the view that will hinder those rows from being returned by the view. (*) 
  
      
      Incorrect. Refer to Section 10  
  
      
  5.  Which statement about performing DML operations on a view is true?  Mark for Review 
(1) Points 
      
    You can perform DML operations on simple views. (*) 
  
    You cannot perform DML operations on a view that contains the WITH CHECK OPTION clause. 
  
    You can perform DML operations on a view that contains the WITH READ ONLY option. 
  
    You can perform DML operations on a view that contains columns defined by expressions, such as COST + 1. 
  
      
      Incorrect. Refer to Section 10  
  
      
  6.  Which action can be performed by using DML statements?  Mark for Review 
(1) Points 
      
    Deleting records in a table (*) 
  
    Creating PRIMARY KEY constraints 
  
    Disabling an index 
  
    Altering a table 
  
      
      Incorrect. Refer to Section 10  
  
      
  7.  You need to create a new view on the EMPLOYEES table to update salary information for employees in Department 50. You need to ensure that DML operations through the view do not change the result set of the view. Which clause should include in the CREATE VIEW statement?  Mark for Review 
(1) Points 
      
    FORCE 
  
    OR REPLACE 
  
    WITH READ ONLY 
  
    WITH CHECK OPTION (*) 
  
      
      Incorrect. Refer to Section 10  
  
      
  8.  You administer an Oracle database, which contains a table named EMPLOYEES. Luke, a database user, must create a report that includes the names and addresses of all employees. You do not want to grant Luke access to the EMPLOYEES table because it contains sensitive data. Which of the following actions should you perform first?  Mark for Review 
(1) Points 
      
    Create the report for him. 
  
    Create a view. (*) 
  
    Create a subquery. 
  
    Create an index. 
  
      
      Incorrect. Refer to Section 10  
  
      
  9.  You need to create a view on the SALES table, but the SALES table has not yet been created. Which statement is true?  Mark for Review 
(1) Points 
      
    You must create the SALES table before creating the view. 
  
    By default, the view will be created even if the SALES table does not exist. 
  
    You can create the table and the view at the same time using the FORCE option. 
  
    You can use the FORCE option to create the view before the SALES table has been created. (*) 
  
      
      Incorrect. Refer to Section 10  
  
      
  10.  In order to query a database using a view, which of the following statements applies?  Mark for Review 
(1) Points 
      
    Use special VIEW SELECT keywords. 
  
    You can retrieve data from a view as you would from any table. (*) 
  
    You can never see all the rows in the table through the view. 
  
    The tables you are selecting from can be empty, yet the view still returns the original data from those tables. 
  
      
      Incorrect. Refer to Section 10  
 
11.  You need to create a view that when queried will display the name, employee identification number, first and last name, salary, and department identification number. When queried, the display should be sorted by salary from lowest to highest, then by last name and first name alphabetically. The view definition should be created regardless of the existence of the EMPLOYEES table. No DML may be performed when using this view. Evaluate these statements: 
CREATE OR REPLACE NOFORCE VIEW EMP_SALARY_V 
AS SELECT employee_id, last_name, first_name, salary, department_id 
FROM employees WITH READ ONLY; 

SELECT * 
FROM emp_salary_v 
ORDER BY salary, last_name, first_name; 

Which statement is true? 
 Mark for Review 
(1) Points 
      
    When both statements are executed all of the desired results are achieved. 
  
    The CREATE VIEW statement will fail if the EMPLOYEES table does not exist. (*) 
  
    The statements will NOT return all of the desired results because the WITH CHECK OPTION clause is NOT included in the CREATE VIEW statement. 
  
    To achieve all of the desired results this ORDER ON clause should be added to the CREATE VIEW statement: 'ORDER ON salary, last_name, first_name' 
  
      
      Incorrect. Refer to Section 10  
  
      
  12.  Evaluate this CREATE VIEW statement: 
CREATE VIEW pt_view AS 
   (SELECT first_name, last_name, status, courseid, subject, term 
    FROM faculty f, course c 
    WHERE f.facultyid = c.facultyid); 

Which type of view will this statement create? 
 Mark for Review 
(1) Points 
      
    Nested 
  
    Simple 
  
    Inline 
  
    Complex (*) 
  
      
      Incorrect. Refer to Section 10  
  
      
  13.  Which keyword(s) would you include in a CREATE VIEW statement to create the view regardless of whether or not the base table exists?  Mark for Review 
(1) Points 
      
    FORCE (*) 
  
    NOFORCE 
  
    OR REPLACE 
  
    WITH READ ONLY 
  
      
      Incorrect. Refer to Section 10  
  
      
  14.  Which of the following keywords cannot be used when creating a view?  Mark for Review 
(1) Points 
      
    HAVING 
  
    WHERE 
  
    ORDER BY 
  
    They are all valid keywords when creating views. (*) 
  
      
      Incorrect. Refer to Section 10  
  
      
  15.  Evaluate this view definition: 
CREATE OR REPLACE VIEW part_name_v 
AS SELECT DISTINCT part_name 
FROM parts 
WHERE cost >= 45; 

Which of the following statements using the PART_NAME_V view will execute successfully? 
 Mark for Review 
(1) Points 
      
    SELECT * 
FROM part_name_v; 
(*)
 
  
    UPDATE part_name_v 
SET cost = cost * 1.23 
WHERE part_id = 56990; 
 
  
    DELETE FROM part_name_v 
WHERE part_id = 56897; 
 
  
    INSERT INTO part_name_v (part_id, part_name, product_id, cost) 
VALUES (857986, 'cylinder', 8790, 3.45); 
 
  
      
      Incorrect. Refer to Section 10  
  
      
  16.  The CUSTOMER_FINANCE table contains these columns: 
CUSTOMER_ID NUMBER(9) 
NEW_BALANCE NUMBER(7,2) 
PREV_BALANCE NUMBER(7,2) 
PAYMENTS NUMBER(7,2) 
FINANCE_CHARGE NUMBER(7,2) 
CREDIT_LIMIT NUMBER(7) 

You created a Top-n query report that displays the account numbers and new balance of the 800 accounts that have the highest new balance value. The results are sorted by payments value from highest to lowest. Which SELECT statement clause is included in your query? 
 Mark for Review 
(1) Points 
      
    Inner query: ORDER BY new_balance DESC (*) 
  
    Inner query: WHERE ROWNUM = 800 
  
    Outer query: ORDER BY new_balance DESC 
  
    Inner query: SELECT customer_id, new_balance ROWNUM 
  
      
      Incorrect. Refer to Section 10  
  
      
  17.  The CUSTOMER_FINANCE table contains these columns: 
CUSTOMER_ID NUMBER(9) 
NEW_BALANCE NUMBER(7,2) 
PREV_BALANCE NUMBER(7,2) 
PAYMENTS NUMBER(7,2) 
FINANCE_CHARGE NUMBER(7,2) 
CREDIT_LIMIT NUMBER(7) 

You execute this statement:

SELECT ROWNUM "Rank", customer_id, new_balance 
FROM (SELECT customer_id, new_balance FROM customer_finance) 
WHERE ROWNUM <= 25 
ORDER BY new_balance DESC;

What statement is true? 
 Mark for Review 
(1) Points 
      
    The statement failed to execute because an inline view was used. 
  
    The statement will not necessarily return the 25 highest new balance values, as the inline view has no ORDER BY. (*) 
  
    The 25 greatest new balance values were displayed from the highest to the lowest. 
  
    The statement failed to execute because the ORDER BY does NOT use the Top-n column. 
  
      
      Incorrect. Refer to Section 10  
  
      
  18.  Which of the following describes a top-N query?  Mark for Review 
(1) Points 
      
    A top-N query returns the bottom 15 records from the specified table. 
  
    A top-N query returns the top 15 records from the specified table. 
  
    A top-N query returns a result set that is sorted according to the specified column values. 
  
    A top-N query returns a limited result set, returning data based on highest or lowest criteria. (*) 
  
      
      Incorrect. Refer to Section 10  
  
      
  19.  The EMPLOYEES table contains these columns: 
EMPLOYEE_ID NUMBER 
LAST_NAME VARCHAR2(25) 
FIRST_NAME VARCHAR2(25) 
DEPARTMENT_ID NUMBER 
JOB_ID VARCHAR(25) 
MANAGER_ID NUMBER 
SALARY NUMBER(9,2) 
COMMISSOIN NUMBER(7,2) 
HIRE_DATE DATE 

Which SELECT statement could be used to display the 10 lowest paid clerks that belong to department 70? 
 Mark for Review 
(1) Points 
      
    SELECT ROWNUM "Ranking", last_name||' ,'||first_name "Employee", salary "Salary" 
FROM 
    (SELECT last_name, first_name, salary 
    FROM employees 
    ORDER BY salary) 
WHERE ROWNUM <=10 AND job_id LIKE 'CLERK' AND department_id = 70; 
 
  
    SELECT ROWNUM "Ranking",last_name||','||first_name "Employee", salary "Salary" 
FROM 
    (SELECT last_name, first_name, salary, job_id 
    FROM employees 
    WHERE job_id LIKE 'CLERK' AND department_id = 70 
    ORDER BY salary) 
WHERE ROWNUM <=10; 
(*)
 
  
    SELECT ROWNUM "Ranking", last_name||' ,'||first_name "Employee", salary "Salary" 
FROM 
    (SELECT last_name, first_name, salary, job_id, dept_id 
    FROM employees 
    WHERE ROWNUM <=10 
    ORDER BY salary) 
WHERE job_id LIKE 'CLERK' AND department_id = 70; 
 
  
    The only way is to use the data dictionary. 
  
      
      Incorrect. Refer to Section 10  
  
      
  20.  Which statement about an inline view is true?  Mark for Review 
(1) Points 
      
    An inline view is a schema object. 
  
    An inline view is a subquery in the FROM clause, often named with an alias. (*) 
  
    An inline view is a complex view. 
  
    An inline view can be used to perform DML operations. 
  
      
      Incorrect. Refer to Section 10  
 
21.  Evaluate this statement: 
CREATE SEQUENCE sales_item_id_seq 
START WITH 101 MAXVALUE 9000090 CYCLE; 

Which statement about this CREATE SEQUENCE statement is true? 
 Mark for Review 
(1) Points 
      
    The sequence will reuse numbers and will start with 101. (*) 
  
    The sequence will generate decrementing sequence numbers starting at 101. 
  
    The statement fails because no INCREMENT BY value is specified. 
  
    The sequence will generate sequence numbers starting with 101, but will not reuse numbers. 
  
      
      Incorrect. Refer to Section 11  
  
      
  22.  When creating a sequence, which keyword or option specifies the minimum sequence value?  Mark for Review 
(1) Points 
      
    MAXVALUE 
  
    MINVALUE (*) 
  
    NOMAXVALUE 
  
    CYCLE 
  
      
      Incorrect. Refer to Section 11  
  
      
  23.  Evaluate this CREATE SEQUENCE statement: 
CREATE SEQUENCE line_item_id_seq CYCLE; 

Which statement is true? 
 Mark for Review 
(1) Points 
      
    The sequence cannot be used with more than one table. 
  
    The sequence preallocates values and retains them in memory. 
  
    The sequence cannot generate additional values after reaching its maximum value. 
  
    The sequence will continue to generate values after the maximum sequence value has been generated. (*) 
  
      
      Incorrect. Refer to Section 11  
  
      
  24.  Which of the following best describes the function of the CURRVAL virtual column?  Mark for Review 
(1) Points 
      
    The CURRVAL virtual column will display the integer that was most recently supplied by a sequence. (*) 
  
    The CURRVAL virtual column will increment a sequence by a specified value. 
  
    The CURRVAL virtual column will display either the physical locations or the logical locations of the rows in the table. 
  
    The CURRVAL virtual column will return a value of 1 for a parent record in a hierarchical result set. 
  
      
      Incorrect. Refer to Section 11  
  
      
  25.  When used in a CREATE SEQUENCE statement, which keyword specifies that a range of sequence values will be preloaded into memory?  Mark for Review 
(1) Points 
      
    LOAD 
  
    MEMORY 
  
    CACHE (*) 
  
    NOCACHE 
  
    NOCYCLE 
  
      
      Incorrect. Refer to Section 11  
  
      
  26.  Which statement about an index is true?  Mark for Review 
(1) Points 
      
    An index can only be created on a single table column. 
  
    Creating an index will always improve query performance. 
  
    Creating an index reorders the data in the underlying table. 
  
    An index created on multiple columns is called a composite or concatenated index. (*) 
  
      
      Incorrect. Refer to Section 11  
  
      
  27.  You want to create a composite index on the FIRST_NAME and LAST_NAME columns of the EMPLOYEES table. Which SQL statement will accomplish this task?  Mark for Review 
(1) Points 
      
    CREATE INDEX fl_idx 
ON employees(first_name || last_name); 
 
  
    CREATE INDEX fl_idx 
ON employees(first_name), employees(last_name); 
 
  
    CREATE INDEX fl_idx 
ON employees(first_name,last_name); 
(*)
 
  
    CREATE INDEX fl_idx ON employees(first_name); 
CREATE INDEX fl_idx ON employees(last_name); 
 
  
      
      Incorrect. Refer to Section 11  
  
      
  28.  What is the correct syntax for creating a private synonym d_sum for the view DEPT_SUM_VU?  Mark for Review 
(1) Points 
      
    CREATE SYNONYM d_sum 
ON dept_sum_vu; 
 
  
    CREATE d_sum SYNONYM 
FOR dept_sum_vu; 
 
  
    UPDATE dept_sum_vu 
ON SYNONYM d_sum; 
 
  
    CREATE SYNONYM d_sum 
FOR dept_sum_vu; 
(*)
 
  
      
      Incorrect. Refer to Section 11  
  
      
  29.  You create a table named CUSTOMERS and define a PRIMARY KEY constraint on the CUST_ID column. Which actions occur automatically?  Mark for Review 
(1) Points 
      
    A CHECK constraint is defined on the CUST_ID column. 
  
    A trigger is created that will prevent NULL values from being accepted in the CUST_ID column. 
  
    A unique index is created on the CUST_ID column, if one does not already exist. (*) 
  
    A sequence is created that will generate a unique value in the CUST_ID column for each row that is inserted into the CUSTOMERS table. 
  
      
      Incorrect. Refer to Section 11  
  
      
  30.  As user Julie, you issue this statement: 
CREATE SYNONYM emp FOR sam.employees; 

Which task was accomplished by this statement? 
 Mark for Review 
(1) Points 
      
    You created a public synonym on the EMP table owned by user Sam. 
  
    You created a private synonym on the EMPLOYEES table that you own. 
  
    You created a public synonym on the EMPLOYEES table owned by user Sam. 
  
    You created a private synonym on the EMPLOYEES table owned by user Sam. (*) 
  
      
      Incorrect. Refer to Section 11  
 
31.  User Mary's schema contains an EMPLOYEES table. Mary has Database Administrator privileges and executes the following statement: 
CREATE PUBLIC SYNONYM employees FOR mary.employees; 

User Susan now needs to SELECT from Mary's EMPLOYEES table. Which of the following SQL statements can she use? (Choose two) 
 Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    CREATE SYNONYM marys_employees FOR mary(employees); 
  
    SELECT * FROM employees; (*) 
  
    SELECT * FROM employees.mary; 
  
    SELECT * FROM mary.employees; (*) 
  
      
      Incorrect. Refer to Section 11  
  
      
  32.  Which statement would you use to remove the LAST_NAME_IDX index on the LAST_NAME column of the EMPLOYEES table?  Mark for Review 
(1) Points 
      
    DROP INDEX last_name_idx;
(*)
 
  
    DROP INDEX last_name_idx(last_name);
 
  
    DROP INDEX last_name_idx(employees.last_name);
 
  
    ALTER TABLE employees
DROP INDEX last_name_idx;
 
  
      
      Incorrect. Refer to Section 11  
  
      
  33.  For which column would you create an index?  Mark for Review 
(1) Points 
      
    A column which has only 4 distinct values. 
  
    A column that is updated frequently 
  
    A column with a large number of null values (*) 
  
    A column that is infrequently used as a query search condition 
  
      
      Incorrect. Refer to Section 11  
  
      
  34.  The following indexes exist on the EMPLOYEES table: 

A unique index on the EMPLOYEE_ID primary key column 
A non-unique index on the JOB_ID column 
A composite index on the FIRST_NAME and LAST_NAME columns. 
If the EMPLOYEES table is dropped, which indexes are automatically dropped at the same time? 
 Mark for Review 
(1) Points 
      
    EMP_ID only 
  
    JOB_ID only 
  
    DEPT_ID only 
  
    EMP_ID and JOB_ID 
  
    All Indexes (*) 
  
      
      Incorrect. Refer to Section 11  
  
      
  35.  The CLIENTS table contains these columns: 
CLIENT_ID NUMBER(4) NOT NULL PRIMARY KEY 
LAST_NAME VARCHAR2(15) 
FIRST_NAME VARCHAR2(10) 
CITY VARCHAR2(15) 
STATE VARCHAR2(2) 

You want to create an index named ADDRESS_INDEX on the CITY and STATE columns of the CLIENTS table. You execute this statement: 

CREATE INDEX clients 
ON address_index (city, state); 

Which result does this statement accomplish? 
 Mark for Review 
(1) Points 
      
    An index named ADDRESS_INDEX is created on the CITY and STATE columns. 
  
    An index named CLIENTS is created on the CITY and STATE columns. 
  
    An index named CLIENTS_INDEX is created on the CLIENTS table. 
  
    An error message is produced, and no index is created. (*) 
  
      
      Incorrect. Refer to Section 11  
  
      
 
      
 Section 12 
      
  36.  Evaluate this statement: 
ALTER USER bob IDENTIFIED BY jim; 

Which statement about the result of executing this statement is true? 
 Mark for Review 
(1) Points 
      
    A new password is assign to user BOB. (*) 
  
    A new user JIM is created from user BOB's profile. 
  
    The user BOB is assigned the same privileges as user JIM. 
  
    The user BOB is renamed and is accessible as user JIM. 
  
      
      Incorrect. Refer to Section 12  
  
      
  37.  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 the part that a user plays in querying the database. 
  
    A role is a name for a group of privileges. (*) 
  
    A role is an object privilege which allows a user to update a table. 
  
      
      Incorrect. Refer to Section 12  
  
      
  38.  User SUSAN creates an EMPLOYEES table, and then creates a view EMP_VIEW which shows only the FIRST_NAME and LAST_NAME columns of EMPLOYEES. User RUDI needs to be able to access employees' names but no other data from EMPLOYEES. Which statement should SUSAN execute to allow this?  Mark for Review 
(1) Points 
      
    SELECT * FROM emp_view FOR rudi; 
  
    CREATE SYNONYM emp_view FOR employees; 
  
    GRANT SELECT ON emp_view TO rudi; (*) 
  
    GRANT SELECT ON emp_view ONLY TO rudi; 
  
      
      Incorrect. Refer to Section 12  
  
      
  39.  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 
      
    GRANT customers(SELECT) TO PUBLIC; 
  
    GRANT SELECT ON customers TO ALL; 
  
    GRANT SELECT ON customers TO PUBLIC; (*) 
  
    CREATE PUBLIC SYNONYM customers FOR james.customers; 
  
      
      Incorrect. Refer to Section 12  
  
      
  40.  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 
  
    CREATE SESSION (*) 
  
    OPEN SESSION 
  
    RESTRICTED SESSION 
  
      
      Incorrect. Refer to Section 12  
 
41.  You grant user AMY the CREATE SESSION privilege. Which type of privilege have you granted to AMY?  Mark for Review 
(1) Points 
      
    A system privilege (*) 
  
    An object privilege 
  
    A user privilege 
  
    An access privilege 
  
      
      Incorrect. Refer to Section 12  
  
      
  42.  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 ALL ON employees FROM chang; 
  
    REVOKE INSERT, DELETE ON employees FROM chang; (*) 
  
    REMOVE INSERT, DELETE ON employees FROM chang; 
  
    REVOKE INSERT AND DELETE ON employees FROM chang; 
  
      
      Incorrect. Refer to Section 12  
  
      
  43.  Which statement would you use to grant a role to users?  Mark for Review 
(1) Points 
      
    GRANT (*) 
  
    ALTER USER 
  
    CREATE USER 
  
    ASSIGN 
  
      
      Incorrect. Refer to Section 12  
  
      
  44.  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'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. 
  
    It allows the user to create new tables which contain the same data as the referenced table. 
  
      
      Incorrect. Refer to Section 12  
  
      
  45.  Which statement would you use to add privileges to a role?  Mark for Review 
(1) Points 
      
    CREATE ROLE 
  
    ALTER ROLE 
  
    GRANT (*) 
  
    ASSIGN 
  
      
      Incorrect. Refer to Section 12  
  
      
  46.  Which statement would you use to remove an object privilege granted to a user?  Mark for Review 
(1) Points 
      
    ALTER USER 
  
    REVOKE (*) 
  
    REMOVE 
  
    DROP 
  
      
      Incorrect. Refer to Section 12  
  
      
  47.  Which keyword would you use to grant an object privilege to all database users?  Mark for Review 
(1) Points 
      
    ADMIN 
  
    ALL 
  
    PUBLIC (*) 
  
    USERS 
  
      
      Incorrect. Refer to Section 12  
  
      
  48.  Granting an object privilege WITH GRANT OPTION allows the recipient to grant other object privileges on the table to other users.  Mark for Review 
(1) Points 
      
    True 
  
    False (*) 
  
      
      Incorrect. Refer to Section 12  
  
      
 
      
 Section 14 
      
  49.  Examine the following statements: 
UPDATE employees SET salary = 15000; 
SAVEPOINT upd1_done; 
UPDATE employees SET salary = 22000; 
SAVEPOINT upd2_done; 
DELETE FROM employees; 

You want to retain all the employees with a salary of 15000; What statement would you execute next? 
 Mark for Review 
(1) Points 
      
    ROLLBACK; 
  
    ROLLBACK TO SAVEPOINT upd1_done; (*) 
  
    ROLLBACK TO SAVEPOINT upd2_done; 
  
    ROLLBACK TO SAVE upd1_done; 
  
    There is nothing you can do, either all changes must be rolled back, or none of them can be rolled back. 
  
      
      Incorrect. Refer to Section 14  
  
      
  50.  If a database crashes, all uncommitted changes are automatically rolled back. True or False?  Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Incorrect. Refer to Section 14