1Z0-061 Free Practice Test

- (Topic 4)
Which three SQL statements would display the value 1890.55 as $1, 890.55? (Choose three.)

1. A. SELECT TO_CHAR(1890.55, \'$0G000D00\')FROM DUAL;
2. B. SELECT TO_CHAR(1890.55, \'$9, 999V99\')FROM DUAL;
3. C. SELECT TO_CHAR(1890.55, \'$99, 999D99\')FROM DUAL;
4. D. SELECT TO_CHAR(1890.55, \'$99G999D00\')FROM DUAL;
5. E. SELECT TO_CHAR(1890.55, \'$99G999D99\')FROM DUAL;

Correct Answer: ADE

- (Topic 2)
Which view should a user query to display the columns associated with the constraints on a table owned by the user?

1. A. USER_CONSTRAINTS
2. B. USER_OBJECTS
3. C. ALL_CONSTRAINTS
4. D. USER_CONS_COLUMNS
5. E. USER_COLUMNS

Correct Answer: D
view the columns associated with the constraint names in the USER_CONS_COLUMNS view.
Incorrect:
A- table to view all constraints definition and names
B- show all object name belong to user
C- does not display column associated
E- no such view
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25

- (Topic 2)
Which object privileges can be granted on a view?

1. A. none
2. B. DELETE, INSERT, SELECT
3. C. ALTER, DELETE, INSERT, SELECT
4. D. DELETE, INSERT, SELECT, UPDATE

Correct Answer: D
Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE.
Incorrect
A- Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE
B- Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE
C- Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-12

- (Topic 1)
There is a simple view SCOTT.DEPT_VIEW on the table SCOTT.DEPT. This insert fails with an error:
SQL> insert into dept_view values('SUPPORT','OXFORD'); insert into dept_view values('SUPPORT','OXFORD')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."DEPT"."DEPTNO")
What might be the problem?

1. A. The INSERT violates a constraint on the detail table.
2. B. The INSERT violates a constraint on the view.
3. C. The view was created as WITH READ ONLY.
4. D. The view was created as WITH CHECK OPTION.

Correct Answer: A
B is incorrect because constraints are enforced on detail tables, not on views. C and D are incorrect because the error message would be different.

- (Topic 1)
Examine the create table statements for the stores and sales tables.
SQL> CREATE TABLE stores (store_id NUMBER(4)CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12, store_address VARCHAR2(20, start_date DATE);
SQL> CREATE TABLE sales (sales_id NUMBER(4)CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4,quantity NUMBER(10), sales_date DATE, store_id NUMBER(4, CONSTRAINT store_id_fk FOREIGN KEY(store_id)REFERENCES stores(store_id));
You executed the following statement: SQL> DELETE from stores
WHERE store_id=900;
The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
Which three options ensure that the statement will execute successfully? (Choose three.)

1. A. Disable the primary key in the STORES table.
2. B. Use CASCADE keyword with DELETE statement.
3. C. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
4. D. Disable the FOREIGN KEY in SALES table and then delete the rows.
5. E. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.

Correct Answer: ACD