01. Which statement correctly differentiates a system privilege from an object privilege?
a) System privileges can be granted only by the DBA whereas object privileges can be granted by DBAs or the owner of the object.
b) System privileges give the rights to only create user schemas whereas object privileges give rights to manipulate objects in a schema.
c) Users require system privileges to gain access to the database whereas they require object privileges to create objects in the database.
d) A system privilege is the right to perform specific activities in a database whereas an object privilege is a right to perform activities on a specific object in the database.
02. Which statement is true regarding the SESSION_PRIVS dictionary view?
a) It contains the current object privileges available in the user session.
b) It contains the current system privileges available in the user session.
c) It contains the object privileges granted to other users by the current user session.
d) It contains the system privileges granted to other users by the current user session.
03. Evaluate the following DELETE statement:
DELETE FROM orders;
There are no other uncommitted transactions on the ORDERS table. Which statement is true about the DELETE statement?
a) It removes all the rows in the table and allows ROLLBACK
b) It would not remove the rows if the table has a primary key.
c) It removes all the rows as well as the structure of the table.
d) It removes all the rows in the table and does not allow ROLLBACK
04. Which three statements indicate the end of a transaction? (Choose three.)
a) after a COMMIT is issued
b) after a ROLLBACK is issued
c) after a SAVEPOINT is issued
d) after a SELECT statement is issued
e) after a CREATE statement is issued
05. Which two statements are true regarding operators used with subqueries? (Choose two.)
a) The NOT IN operator is equivalent to IS NULL
b) The <ANY operator means less than the maximum.
c) =ANY and =ALL operators have the same functionality.
d) The IN operator cannot be used in single-row subqueries.
e) The NOT operator can be used with IN, ANY and ALL operators.
06. Which statements are correct regarding indexes? (Choose all that apply.)
a) When a table is dropped, the corresponding indexes are automatically dropped.
b) For each DML operation performed, the corresponding indexes are automatically updated.
c) Indexes should be created on columns that are frequently referenced as part of an expression.
d) A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
07. Which statements are true regarding the hierarchical query in Oracle Database 10g? (Choose all that apply.)
a) It is possible to retrieve data only in top-down hierarchy.
b) It is possible to retrieve data in top-down or bottom-up hierarchy.
c) It is possible to remove an entire branch from the output of the hierarchical query.
d) You cannot specify conditions when you retrieve data by using a hierarchical query.
08. The first DROP operation is performed on PRODUCTS table using the following command:
DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?
a) It recovers only the table structure.
b) It recovers the table structure, data, and the indexes.
c) It recovers the table structure and data but not the related indexes.
d) It is not possible to recover the table structure, data, or the related indexes.
09. Evaluate the following command:
CREATE TABLE employees (employee_id NUMBER(2) PRIMARY KEY, last_name VARCHAR2(25) NOT NULL, department_id NUMBER(2), job_id VARCHAR2(8), salary NUMBER(10,2));
You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization:
CREATE OR REPLACE VIEW sales_staff_vu AS SELECT employee_id, last_name job_id
WHERE job_id LIKE 'SA_%' WITH CHECK OPTION;
Which statements are true regarding the above view? (Choose all that apply.)
a) It allows you to insert details of all new staff into the EMPLOYEES table.
b) It allows you to delete the details of the existing sales staff from the EMPLOYEES table.
c) It allows you to update the job ids of the existing sales staff to any other job id in the EMPLOYEES table.
d) It allows you to insert the IDs, last names and job ids of the sales staff from the view if it is used in multitable INSERT statements.
10. Evaluate the following CREATE TABLE command:
CREATE TABLE order_item (order_id NUMBER(3), item_id NUMBER(2),
CONSTRAINT ord_itm_id_pk PRIMARY KEY (order_id item_id) USING INDEX
(CREATE INDEX ord_itm_idx
Which statement is true regarding the above SOL statement?
a) It would execute successfully and only ORD_ITM_IDX index would be created.
b) It would give an error because the USING INDEX clause cannot be used on a composite primary key.
c) It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be created.
d) It would give an error because the USING INDEX clause is not permitted in the CREATE TABLE command.