Oracle Database 12c SQL 1Z0-071 Exam, Exams of Database Programming

Download Oracle Database 12c SQL 1Z0-071 Exam and more Exams Database Programming in PDF only on Docsity!

ORACLE

Number: 1z0- Passing Score: 800 Time Limit: 120 min

Oracle Database 12c SQL

1Z0-071 Exam

Exam A

QUESTION 1

You issue the following command to drop the PRODUCTS table:

SQL > DROP TABLE products;

Which three statements are true about the implication of this command?

A. All data along with the table structure is deleted.

B. A pending transaction in the session is committed.

C. All indexes on the table remain but they are invalidated.

D. All views and synonyms on the table remain but they are invalidated.

E. All data in the table is deleted but the table structure remains.

Correct Answer: ABD Section: (none) Explanation

Explanation/Reference:

QUESTION 2

You execute the following commands:

SQL > DEFINE hiredate = '01-APR-2011'

SQL >SELECT employee_id, first_name, salary FROM employees WHERE hire_date > '&hiredate' AND manager_id > &mgr_id;

For which substitution variables are you prompted for the input?

A. none, because no input required

B. both the substitution variables ''hiredate' and 'mgr_id'.

C. only hiredate'

D. only 'mgr_id'

Correct Answer: D

A. DELETE orders o, order_items I WHERE o.order_id = i.order_id;

B. DELETE FROM orders

WHERE (SELECT order_id FROM order_items);

C. DELETE orders WHERE order_total < 1000;

D. DELETE order_id FROM orders WHERE order_total < 1000;

Correct Answer: B Section: (none) Explanation

Explanation/Reference:

QUESTION 4

View the Exhibit and examine the structure of CUSTOMERS table.

Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.

Which SQL statement would produce the required result?

View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.

EMPLOYEES

Name Null?^ Type

EMPLOYEE_ID FIRST_NAME LAST_NAME

NOT NULL

NOT NULL

NUMBER(6)

VARCHAR2(20)

VARCHAR2(25)

HIRE_DATE NOT NULL DATE

JOB_ID

SALARY

COMMISSION

MANAGER_ID

DEPARTMENT_ID

NOT NULL VARCHAR2(10)

NUMBER(10,2)

NUMBER(6,2)

NUMBER(6)

NUMBER(4)

DEPARTMENTS

Name Null? Type

DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME MANAGER_ID LOCATION_ID

NOT NULL VARCHAR2(30)

NUMBER(6)

NUMBER(4)

You want to update EMPLOYEES table as follows: Update only those employees who work in Boston or Seattle (locations 2900 and 2700). Set department_id for these employees to the department_id corresponding to London (location_id 2100). Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department. Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department.

You issue the following command:

SQL> UPDATE employees SET department_id = (SELECT department_id FROM departments WHERE location_id = 2100),

(salary, commission) = (SELECT 1.1AVG(salary), 1.5AVG(commission) FROM employees, departments WHERE departments.location_id IN(2900, 2700, 2100)) WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700;

What is outcome?

A. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.

B. It generates an error because a subquery cannot have a join condition in a UPDATE statement.

C. It executes successfully and gives the correct result.

D. It executes successfully but does not give the correct result.

Correct Answer: D Section: (none) Explanation

Explanation/Reference:

QUESTION 6

Evaluate the following two queries:

SQL> SELECT cust_last_name, cust_city FROM customers WHERE cust_credit_limit IN (1000, 2000, 3000);

SQL> SELECT cust_last_name, cust_city FROM customers WHERE cust_credit_limit = 1000 or cust_credit_limit = 2000 or cust_credit_limit = 3000

Which statement is true regarding the above two queries?

A. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.

B. There would be no change in performance.

C. Performance would degrade in query 2.

Inkjet C/4 12 102090 LaserPro 600/6/BW 12 102087 LaserPro 1200/8/BW 12 102099 Inkjet B/6 12 102096 Industrial 700/ID 12 102086 Industrial 600/DQ 12 102088 Compact 400/LQ 12 102087 Compact 400/DQ 12 102088 HD 12GB /R 13 102090 HD 10GB /I 13 102071 HD 12GB @7200 /SE 13 102057 HD 18.2GB @10000 /E 13 102078 HD 18.2GB @10000 /I 13 102050 HD 18GB /SE 13 102083 HD 6GB /I 13 102072 HD 8.2GB@5400 13 102093

You have the requirement to display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13 , and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:

SELECT product_name FROM product_information WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;

Which statement is true regarding the execution of the query?

A. It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.

B. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.

C. It would execute and the output would display the desired result.

D. It would execute but the output would return no rows.

Correct Answer: D Section: (none) Explanation

Explanation/Reference:

QUESTION 9

Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

A. The outer query stops evaluating the result set of the inner query when the first value is found.

B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.

C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.

D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Correct Answer: AC Section: (none) Explanation

Explanation/Reference: References: http://www.techonthenet.com/oracle/exists.php

QUESTION 10 View the exhibit and examine the structure of the STORES table.

STORES table Name Null? Type

STORE_ID NUMBER NAME VARCHAR2(100) ADDRESS VARCHAR2(200) CITY VARCHAR2(100) COUNTRY VARCHAR2(100) START_DATE DATE END_DATE DATE PROPERTY_PRICE NUMBER

You want to display the NAME of the store along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price. The stores displayed must have START_DATE in the range of 36 months starting from 01-Jan-2000 and above. Which SQL statement would get the desired output?

Correct Answer: D Section: (none) Explanation

Explanation/Reference:

QUESTION 12

Examine the command:

SQL> ALTER TABLE books_transactions ADD CONSTRAINT fk_book_id FOREIGN KEY (book_id) REFERENCES books (book_id) ON DELETE CASCADE;

What does ON DELETE CASCADE imply?

A. When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.

B. When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted but the table structure is retained.

C. When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted.

D. When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column.

Correct Answer: C Section: (none) Explanation

Explanation/Reference:

QUESTION 13

View the exhibit and examine the structure of the EMPLOYEES table.

You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.

Which SQL statement would you execute?

A. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON m.employee_id = e.manager_id WHERE m.manager_id = 100;

B. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON m.employee_id = e.manager_id WHERE e.manager_id = 100;

C. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON e.employee_id = m.manager_id WHERE m.manager_id = 100;

D. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e WHERE m.employee_id = e.manager_id and AND e.manager_id = 100

Correct Answer: B

There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID. You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID is 123. Which query provides the correct output?

A. SELECT e.last_name, m.manager_id FROM employees e RIGHT OUTER JOIN employees m on (e.manager_id = m.employee_id) AND e.employee_id = 123;

B. SELECT e.last_name, m.manager_id FROM employees e RIGHT OUTER JOIN employees m on (e.employee_id = m.manager_id) WHERE e.employee_id = 123;

C. SELECT e.last_name, e.manager_id FROM employees e RIGHT OUTER JOIN employees m on (e.employee_id = m.employee_id) WHERE e.employee_id = 123;

D. SELECT m.last_name, e.manager_id FROM employees e LEFT OUTER JOIN employees m on (e.manager_id = m.manager_id) WHERE e.employee_id = 123;

Correct Answer: B Section: (none) Explanation

Explanation/Reference:

QUESTION 16

Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?

A. second normal form

B. first normal form

C. third normal form

D. fourth normal form

Correct Answer: A Section: (none) Explanation

Explanation/Reference: References: https://blog.udemy.com/database-normal-forms/

QUESTION 17 Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1 table, which are not present in the SALES2 table.

SALES1 table Name Null^ Type

SALES_ID STORE_ID ITEMS_ID QUANTITY SALES_DATE

NUMBER

NUMBER

NUMBER

NUMBER

DATE

SALES2 table Name Null Type

SALES_ID STORE_ID ITEMS_ID QUANTITY SALES_DATE

NUMBER

NUMBER

NUMBER

NUMBER

DATE

Which set operator generates the required output?

A. INTERSECT

B. UNION

C. PLUS

D. MINUS

E. SUBTRACT

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 can be enabled only by dropping the foreign key constraint and recreating it.

B. It would remain disabled and has to be enabled manually using the ALTER TABLE command.

C. It would be automatically enabled and immediate.

D. It would be automatically enabled and deferred.

Correct Answer: B Section: (none) Explanation

Explanation/Reference:

QUESTION 20

Which three statements are true regarding the data types?

A. The minimum column width that can be specified for a VARCHAR2 data type column is one.

B. Only one LONG column can be used per table.

C. A TIMESTAMP data type column stores only time values with fractional seconds.

D. The BLOB data type column is used to store binary data in an operating system file.

E. The value for a CHAR data type column is blank-padded to the maximum defined column width.

Correct Answer: ABE Section: (none) Explanation

Explanation/Reference:

QUESTION 21

Which three statements are true regarding subqueries?

A. Multiple columns or expressions can be compared between the main query and subquery.

B. Subqueries can contain ORDER BY but not the GROUP BY clause.

C. Main query and subquery can get data from different tables.

D. Subqueries can contain GROUP BY and ORDER BY clauses.

E. Main query and subquery must get data from the same tables.

F. Only one column or expression can be compared between the main query and subquery.

Correct Answer: ACD Section: (none) Explanation

Explanation/Reference: References: http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html

QUESTION 22 Which statement is true regarding the default behavior of the ORDER BY clause?

A. In a character sort, the values are case-sensitive.

B. NULL values are not considered at all by the sort operation.

C. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause.

D. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.

Correct Answer: A Section: (none) Explanation

Explanation/Reference: