1z0-071 by Oracle Actual Free Exam Questions And Answers

Question 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?

A. Disable the primary key in the STORES table.

B. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.

C. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.

D. Disable the FOREIGN KEY in SALES table and then delete the rows.

E. Use CASCADE keyword with DELETE statement.

Discussion 0

Correct Answer: B,C,D Vote an answer

Question 2

Examine the structure of the EMPLOYEES table.

You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.
Which query would retrieve the required result?

A. SELECT last_name, hire_date, salary
FROM employees
WHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100) UNION ALL (SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180 INTERSECT SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000);

B. SELECT last_name, hire_date, salary
FROM employees
WHERE salary > 10000
UNION ALL
SELECT last_name, hire_date, salary
FROM employees
WHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100) INTERSECT SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE- 180;

C. SELECT last_name, hire_date, salary
FROM employees
WHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100') UNION SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180 INTERSECT SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000;

D. (SELECT last_name, hire_date, salary
FROM employees
WHERE salary > 10000
UNION ALL
SELECT last_name, hire_date, salary
FROM employees
WHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)) UNION SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180;

Discussion 0

Correct Answer: C Vote an answer

Question 3

Which two statements are true about the SET VERIFY ON command? (Choose two.)

A. It displays values for variables created by the DEFINE command

B. It can be used only in SQL*Plus

C. It displays values for variables prefixed with &&

D. It can be used in SQL Developer and SQL*Plus

E. It displays values for variables used only in the WHERE clause of a query

Discussion 0

Correct Answer: A,D Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 4

You must create a table EMPLOYEES in which the values in the columns EMPLOYEES_ID and LOGIN_ID must be unique and not null.
Which two SQL statements would create the required table? (Choose two.)

A. CREATE TABLE employees
(employee_id NUMBER CONSTRAINT emp_id_nn NOT NULL,
login_id NUMBER CONSTRAINT login_id_nn NOT NULL,
employee_name VARCHAR2(100),
hire_date DATE,
CONSTRAINT emp_num_id_uk UNIQUE (employee_id, login_id));

B. CREATE TABLE employees
(employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY,
login_id NUMBER UNIQUE,
employee_name VARCHAR2(25),
hire_date DATE);

C. CREATE TABLE employees
(employee_id NUMBER,
login_id NUMBER,
employee_name VARCHAR2(100),
hire_date DATE,
CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id));

D. CREATE TABLE employees
(employee_id NUMBER,
login_id NUMBER,
employee_name VARCHAR2(100),
hire_date DATE,
CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id);
CONSTRAINT emp_id_nn NOT NULL (employee_id, login_id));

E. CREATE TABLE employees
(employee_id NUMBER,
login_id NUMBER,
employee_name VARCHAR2(25),
hire_date DATE,
CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, login_id));

Discussion 0

Correct Answer: A,E Vote an answer

Question 5

View the Exhibit and examine the description of the EMPLOYEEStable.

Evaluate the following SQL statement:
SELECT first_name, employee_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1)
"Review" FROM employees;
The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID,and review date for employees. The review date is the firsts Monday after the completion of six months of the hiring.
The NLS_TERRITORYparameter is set to AMERICAin the session.
Which statement is true regarding this query?

A. The query would not execute because the NEXT_DAY function accepts a string as argument.

B. The query would execute but the output would give review dates that are Sundays.

C. The query would not execute because date functions cannot be nested.

D. The query would execute to give the desired output.

Discussion 0

Correct Answer: B Vote an answer

Question 6

Which two statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

A. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.

B. The HAVING clause can be used with aggregate functions in subqueries.

C. The WHERE clause can be used to exclude rows before dividing them into groups.

D. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.

E. The WHERE clause can be used to exclude rows after dividing them into groups.

Discussion 0

Correct Answer: B,C Vote an answer

Question 7

Examine the description of the TRANSACTIONS table:

Which two SQL statements execute successfully? (Choose two.)

A. SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100
"DUES AMOUNT" FROM transactions;

B. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount + 100 DUES FROM transactions;

C. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount + 100 "DUES" FROM transactions;

D. SELECT customer_id AS `CUSTOMER-ID', transaction_date AS DATE, amount + 100 `DUES' FROM transactions;

E. SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount +100 DUES FROM transactions;

Discussion 0

Correct Answer: B,E Vote an answer

Question 8

Which three statements indicate the end of a transaction? (Choose three.)

A. after a SELECT statement is issued

B. after a SAVEPOINT is issued

C. after a COMMIT is issued

D. after a ROLLBACK is issued

E. after a CREATE statement is issued

Discussion 0

Correct Answer: C,D,E Vote an answer

Question 9

Examine the structure of the BOOKS_TRANSACTIONStable.

You want to update this table such that BOOK_IDis set to 'INVALID'for all rows where no MEMBER_ID has been entered.
Examine this partial SQL statement:

Which condition must be used in the WHEREclause to perform the required update?

A. MEMBER_ID = "";

B. MEMBER_ID = NULL;

C. MEMBER_ID = '';

D. MEMBER_ID IS NULL;

Discussion 0

Correct Answer: D Vote an answer

Oracle Database SQL - 1z0-071 Exam Practice Test

Contact Us

Useful Links

Latest Updated