Oca 7

Front 1

1. How do you remove the view USA_STATES from the schema?
A. ALTER VIEW USA_STATES REMOVE;

B. DROP VIEW USA_STATES;

C. DROP VIEW USA_STATES CASCADE;

D. DROP USA_STATES;

Back 1

1. B. A view is dropped using the DROP VIEW view _name; command.

Front 2

2. In a join view, on how many base tables can you perform a DML operation (UPDATE/INSERT/DELETE) in a single step?
A. One

B. The number of base tables in the view definition

C. The number of base tables minus one

D. None

Back 2

2. A. Y ou can perform an INSERT, UPDATE, or DELETE operation on the columns involving only one base table at a time. There are also some restrictions
on the DML operations you perform on a join view.

Front 3

3. The following code is used to define a view. The EMP table does not have a primary key or any other constraints.
CREATE VIEW MYVIEW AS
SELECT DISTINCT ENAME, SALARY
FROM EMP
WHERE DEPT_ID = 10;


Which operation is allowed on the view?
A. SELECT, INSERT, UPDATE, DELETE

B. SELECT, UPDATE

C. SELECT, INSERT, DELETE

D. SELECT

E. SELECT, UPDATE, DELETE

Back 3

3. D. Since the view definition includes a DISTINCT clause, only queries are allowed on the view.

Front 4

4. Which statements are used to modify a view definition? (Choose all that apply.)
A. ALTER VIEW

B. CREATE OR REPLACE VIEW

C. REPLACE VIEW

D. CREATE FORCE VIEW

E. CREATE OR REPLACE FORCE VIEW

Back 4

4. B, E. The OR REPLACE option in the CREATE VIEW statement is used to modify the definition of the view. The FORCE option can be used to create the view
with errors. The ALTER VIEW statement is used to compile a view or to add or modify constraints on the view.

Front 5

5. You create a view based on the EMPLOYEES table using the following SQL.
CREATE VIEW MYVIEW AS SELECT * FROM EMPLOYEES;
You modify the table to add a column named EMP_SSN. What do you need to do to have this new column appear in the view?
A. Nothing. Since the view definition is selecting all columns, the new column will appear in the view automatically.
B. Recompile the view using ALTER VIEW MYVIEW RECOMPILE.
C. Re-create the view using CREATE OR REPLACE VIEW.
D. Add the column to the view using ALTER VIEW MYVIEW ADD EMP_SSN.

Back 5

5. C. When you modify the base table, the view becomes invalid. Oracle will recompile the view the first time it is accessed. Recompiling the view
will make it valid, but the new column will not be available in the view. This is because when you create the view using *, Oracle expands the column
names and stores the column names in the dictionary.

Front 6

6. Which is a valid status of a constraint created on a view?
A. DISABLE VALIDATE

B. DISABLE NOVALIDATE

C. ENABLE NOVALIDATE

D. All of the above

Back 6

6. B. Since the constraints on the view are not enforced by Oracle, the only valid status of a constraint can be DISABLE NOVALIDATE. Y ou must specify this
status when creating constraints on a view.

Front 7

7. The SALARY column of the EMPLOYEE table is defined as NUMBER(8,2), and the COMMISSION_PCT column is defined as NUMBER(2,2). A view is created
with the following code:
CREATE VIEW EMP_COMM AS
SELECT LAST_NAME,
SALARY * NVL(COMMISSION_PCT,0) Commission
FROM EMPLOYEES;


What is the datatype of the COMMISSION column in the view?
A. NUMBER (8,2)

B. NUMBER (10,2)

C. NUMBER

D. FLOAT

Back 7

7. C. When numeric operations are performed using numeric datatypes in the view definition, the resulting column will be a floating datatype, which
is NUMBER without any precision or scale.

Front 8

8. Which clause in the SELECT statement is not supported in a view definition subquery?
A. GROUP BY

B. HAVING
C. CUBE
D. FOR UPDATE OF
E. ORDER BY

Back 8

8. D. The FOR UPDATE OF clause is not supported in the view definition. The FOR UPDATE clause locks the rows, so it is not allowed.

Front 9

9. The EMPLOYEE table has the following columns:
EMP_ID NUMBER (4)
EMP_NAME VARCHAR2 (30)
SALARY NUMBER (6,2)
DEPT_ID VARCHAR2 (2)


Which query will show the top five highest-paid employees?
A. SELECT * FROM(SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY ASC)WHERE ROWNUM <= 5;

B. SELECT EMP_NAME, SALARY FROM(SELECT * FROM EMPLOYEE ORDER BY SALARY DESC)WHERE ROWNUM < 5;

C. SELECT * FROM(SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY DESC)WHERE ROWNUM <= 5;

D. SELECT EMP_NAME, SALARY(SELECT * FROM EMPLOYEE ORDER BY SALARY DESC)WHERE ROWNUM = 5;

Back 9

9. C. Y ou can find the top five salaries using an inline view with the ORDER BY clause. The Oracle 11g Optimizer understands the top-n rows query.
Option B would have been correct if you had ROWNUM <= 5 in the WHERE clause.

Front 10

10. The EMPLOYEE table has the following columns:
EMP_ID NUMBER (4) PRIMARY KEY
EMP_NAME VARCHAR2 (30)
SALARY NUMBER (6,2)
DEPT_ID VARCHAR2 (2)


A view is defined using the following SQL:
CREATE VIEW EMP_IN_DEPT10 AS
SELECT * FROM EMPLOYEE
WHERE DEPT_ID = 'HR';


Which INSERT statement will succeed through the view?
A. INSERT INTO EMP_IN_DEPT10 VALUES (1000, 'JOHN',1500,'HR');

B. INSERT INTO EMP_IN_DEPT10 VALUES (1001, NULL,1700,'AM');

C. INSERT INTO EMP_IN_DEPT10 VALUES (1002, 'BILL',2500,'AC');

D. All of the above

Back 10

10. D. The view is based on a single table, and the only constraint on the table is the primary key. Although the view is defined with a WHERE clause,
you have not enforced that check while using DML statements through the WITH CHECK OPTION clause.

Front 11

11. To be able to modify a join view, the view definition should not contain which of the following in the top-level query? (Choose all that apply.)
A. A DISTINCT operator

B. An ORDER BY clause

C. Aggregate functions such as SUM, AVG, and COUNT

D. A WHERE clause

E. A GROUP BY clause

F. A ROWNUM pseudocolumn

Back 11

11. A, C, E, F. T o be able to update a base table using the view, the view definition should not have a DISTINCT clause, a GROUP BY clause, a START WITH
clause, a CONNECT BY clause, ROWNUM, set operators (UNION, UNION ALL, INTERSECT, or MINUS), or a subquery in the SELECT clause.

Front 12

12. Which statement will create a sequence that starts with 0 and gets smaller one whole number at a time?
A. create sequence desc_seq start with 0 increment by -1 maxvalue 1;

B. create sequence desc_seq increment by -1;

C. create sequence desc_seq start with 0 increment by -1;

D. Sequences can only increase.

Back 12

12. A. For a descending sequence, the default START WITH value is –1, and the default MAXVALUE value is –1. T o start the sequence with 0, you must
explicitly override both of these defaults.

Front 13

13. Which statement is most correct in describing what happens to a synonym when the underlying object is dropped?
A. The synonym’s status is changed to INVALID.

B. You can’t drop the underlying object if a synonym exists unless the CASCADE clause is used in the DROP statement.

C. The synonym is automatically dropped with the underlying object.

D. Nothing happens to the synonym.

Back 13

13. A. When the underlying object is dropped, the synonym will become INVALID. Y ou can see the status of the synonym by querying the USER_OBJECTS
dictionary view.

Front 14

14. There is a public synonym named PLAN_TABLE for SYSTEM.PLAN_TABLE. Which of the following statements will remove this public synonym from the
database?
A. drop table system.plan_table;

B. drop synonym plan_table;

C. drop table system.plan_table cascade;

D. drop public synonym plan_table;

Back 14

14. D. T o remove a public synonym, use the DROP PUBLIC SYNONYM statement. The DROP TABLE statement will remove a table from the database but will not
drop any synonyms on the table. The synonym will become invalid.

Front 15

15. A developer reports that she is receiving the following error:
SELECT key_seq.currval FROM dual;

ERROR at line 1:
ORA-08002: sequence KEY_SEQ.CURRVAL is not yet defined


Which of the following statements does the developer need to run to fix this condition?
A. create sequence key_seq;

B. create synonym key_seq;

C. select key_seq.nextval from dual;

D. grant create sequence to public;

Back 15

15. C. A sequence is not yet initialized if NEXTVAL has not yet been selected from it within the current session. I t has nothing to do with creating a
sequence, creating a synonym, or granting privileges.

Front 16

16. Bitmapped indexes are best suited to which type of environment?
A. High-cardinality columns

B. Online transaction processing (OLTP) applications

C. Full-table scan access

D. Low- to medium-cardinality columns

Back 16

16. D. Bitmapped indexes are not suited for high-cardinality columns (those with highly selective data). OLTP applications tend to need row-level
locking, which is not available with bitmap indexes. Full-table scans do not use indexes. Bitmap indexes are best suited to multiple combinations of
low- to medium-cardinality columns.

Front 17

17. Which clauses in a SELECT statement can an index be used for? (Choose all that apply.)
A. SELECT

B. FROM

C. WHERE

D. HAVING

Back 17

17. A, C. The obvious answer is C, but an index also can be used for the SELECT clause. I f an index contains all the columns needed to satisfy the
query, the table does not need to be accessed.

Front 18

18. Y ou need to generate artificial keys for each row inserted into the PRODUCTS table. Y ou want the first row to use a sequence value of 1000, and you
want to make sure that no sequence value is skipped. Which of the following statements will meet these requirements?
A. CREATE SEQUENCE product_key2START WITH 1000INCREMENT BY 1NOCACHE;

B. CREATE SEQUENCE product_key2START WITH 1000NOCACHE;

C. CREATE SEQUENCE product_key2START WITH 1000NEXTVAL 1NOCACHE;

D. Options A and B meet the requirements.

E. None of the above statements meet all the requirements.

Back 18

18. D. Both options A and B produce identical results, because the INCREMENT BY 1 clause is the default if it is not specified. Option C is invalid
because NEXTVAL is not a valid keyword within a CREATE SEQUENCE statement.

Front 19

19. Which statement will display the last number generated from the EMP_SEQ sequence?
A. select emp_seq.curr_val from dual;

B. select emp_seq.currval from dual;

C. select emp_seq.lastval from dual;

D. select last_number from all_sequences where sequence_name ='EMP_SEQ';

E. You cannot get the last sequence number generated.

Back 19

19. B. Option D is close, but it shows the greatest number in the cache, not the latest generated. The correct answer is from the sequence itself,
using the pseudocolumn CURRVAL.

Front 20

20. Which statement will create a sequence that will rotate through 100 values in a round-robin manner?
A. create sequence roundrobin cycle maxvalue 100;

B. create sequence roundrobin cycle to 100;

C. create sequence max_value 100 roundrobin cycle;

D. create rotating sequence roundrobin min 1 max 100;

Back 20

20. A. The keyword CYCLE will cause the sequence to wrap and reuse numbers. The keyword MAXVALUE will set the largest value the sequence will cycle
to. The name roundrobin is there to confuse to you.