1Z0-061 Free Practice Test

- (Topic 3)
Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30)
JOB_ID NUMBER\ SAL NUMBER
MGR_ID NUMBER . References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER . Foreign key to DEPARTMENT_ID column of the. . DEPARTMENTS table
You created a sequence called EMP_ID_SEQ in order to populate sequential values for the EMPLOYEE_ID column of the EMPLOYEES table.
Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)

1. A. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.
2. B. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.
3. C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.
4. D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
5. E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.
6. F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.

Correct Answer: CD
the EMP_ID_SEQ sequence is not affected by modification to the EMPLOYEES table. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
Incorrect
A- EMP_ID_SEQ sequence can be use to populate JOB_ID
B- EMP_ID_SEQ sequence will not be invalidate when column in EMPLOYEE_ID is modify.
E- EMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEES table.
F- EMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEE_ID column.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 12-4

- (Topic 3)
What is true regarding sub queries?

1. A. The inner query always sorts the results of the outer query
2. B. The outer query always sorts the results of the inner query
3. C. The outer query must return a value to the outer query
4. D. The inner query returns a value to the outer query
5. E. The inner query must always return a value or the outer query will give an error

Correct Answer: D
The inner query returns a value to the outer query. If the inner query does not return a
value, the outer query does not return a result

- (Topic 2)
< >>>< ><>

1. A. The BETWEEN clause cannot be used for the CHECK constraint
2. B. SYSDATE cannot be used with the CHECK constraint
3. C. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY
4. D. The CHECK constraint cannot be placed on columns having the DATE data type

Correct Answer: B
CHECK Constraint
The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows
A single column can have multiple CHECK constraints that refer to the column in its definition.
There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level.
CREATE TABLE employees (...
salary NUMBER(8, 2) CONSTRAINT emp_salary_min CHECK (salary > 0),

- (Topic 4)
What is true about sequences?

1. A. Once created, a sequence belongs to a specific schema.
2. B. Once created, a sequence is linked to a specific table.
3. C. Once created, a sequence is automatically available to all users.
4. D. Only the DBA can control which sequence is used by a certain table.
5. E. Once created, a sequence is automatically used in all INSERT and UPDATE statements.

Correct Answer: A

- (Topic 1)
Examine the structure of the employees table:
< ><>>>>< >

1. A. Option A
2. B. Option B
3. C. Option C
4. D. Option D

Correct Answer: B
We should use LEFT OUTER JOIN as we want to display employees which have no (have NULL values for) managers.