Free Oracle 1z0-071 Exam Dumps Questions & Answers

Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
enameVARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_PK?

• A.It would remain disabled and has to be enabled manually using the ALTER TABLE command.
• B.It would be automatically enabled and immediate.
• C.It would remain disabled and can be enabled only by dropping the foreign key constraint and re- creating it.
• D.It would be automatically enabled and deferred.

Examine the structure of the SALES table.

Examine this statement:

Which two statements are true about the SALES1 table? (Choose two.)

• A.It is created with no rows.
• B.It will not be created because of the invalid WHERE clause.
• C.It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
• D.It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
• E.It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.

Which three statements are true regarding the WHEREand HAVINGclauses in a SQL statement?
(Choose three.)

• A.The WHEREclause is used to exclude rows before the grouping of data.
• B.The HAVINGclause conditions can use aliases for the columns.
• C.WHEREand HAVINGclauses cannot be used together in a SQL statement.
• D.The HAVINGclause conditions can have aggregate functions.
• E.The HAVINGclause is used to exclude one or more aggregated results after grouping data.

Table HR.EMPLOYEEScontains a row where the EMPLOYEE_IDis 109.
User ALICEhas no privileges to access HR.EMPLOYEES.
User ALICEstarts a session.
User HRstarts a session and successfully executes these statements:
GRANT DELETE ON employees TO alice;
UPDATE employees SET salary = 24000 WHERE employee_id = 109;
In her existing session ALICEthen executes:
DELETE FROM hr.employees WHERE employee_id = 109;
What is the result?

• A.The DELETE command will wait for HR'S transaction to end then delete the row.
• B.The DELETE command will immediately delete the row.
• C.The DELETE command will wait for HR'S transaction to end then return an error.
• D.The DELETE command will immediately return an error.

Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?

• A.It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.
• B.It executes successfully and displays rows in the descending order of PROMO_CATEGORY.
• C.It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators.
• D.It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.