Free Download 1z0-071 Examp Dump: Oracle.1z0-071.TestKing.2018-10-23.112q.vcex

File Info

Exam	Oracle Datbase 12c SQL
Number	1z0-071
File Name	Oracle.1z0-071.TestKing.2018-10-23.112q.vcex
Size	10 MB
Posted	Oct 23, 2018
Download	Oracle.1z0-071.TestKing.2018-10-23.112q.vcex

How to open VCEX & EXAM Files?

Files with VCEX & EXAM extensions can be opened by ProfExam Simulator.

Download
ProfExam Simulator

Purchase

Coupon: MASTEREXAM
With discount: 20%

Demo Questions

Question 1

Examine the structure of the EMPLOYEES table.

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?

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;
SELECT e.last_name, m.manager_id
FROM employees e LEFT OUTER JOIN employees m
on (e.employee_id = m.manager_id)
WHERE e.employee_id = 123;
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;
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: D

Question 2

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

second normal form
first normal form
third normal form
fourth normal form

Correct answer: A

Explanation:

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

References:

https://blog.udemy.com/database-normal-forms/

Question 3

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.

Which set operator generates the required output?

INTERSECT
UNION
PLUS
MINUS
SUBTRACT

Correct answer: D

Explanation:

References:https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm

References:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm

Question 4

Evaluate the following ALTER TABLE statement:

ALTER TABLE orders

SET UNUSED (order_date);

Which statement is true?

After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.
The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully.
ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
The DESCRIBE command would still display the ORDER_DATE column.

Correct answer: A

Question 5

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,

ename VARCHAR2(15),

salary NUMBER (8,2),

mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp_no));

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?

It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.
It would remain disabled and has to be enabled manually using the ALTER TABLE command.
It would be automatically enabled and immediate.
It would be automatically enabled and deferred.

Correct answer: B

Question 6

Which three statements are true regarding the data types? (Choose three.)

The minimum column width that can be specified for a VARCHAR2 data type column is one.
Only one LONG column can be used per table.
A TIMESTAMP data type column stores only time values with fractional seconds.
The BLOB data type column is used to store binary data in an operating system file.
The value for a CHAR data type column is blank-padded to the maximum defined column width.

Correct answer: ABE

Question 7

Which three statements are true regarding subqueries? (Choose three.)

Multiple columns or expressions can be compared between the main query and subquery.
Subqueries can contain ORDER BY but not the GROUP BY clause.
Main query and subquery can get data from different tables.
Subqueries can contain GROUP BY and ORDER BY clauses.
Main query and subquery must get data from the same tables.
Only one column or expression can be compared between the main query and subquery.

Correct answer: ACD

Explanation:

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

References:

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

Question 8

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

In a character sort, the values are case-sensitive.
NULL values are not considered at all by the sort operation.
Only those columns that are specified in the SELECT list can be used in the ORDER BY clause.
Numeric values are displayed from the maximum to the minimum value if they have decimal positions.

Correct answer: A

Question 9

Examine the structure of the MEMBERS table.

Which query can be used to display the last names and city names only for members from the states MO and MI?

SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';
SELECT last_name, city FROM members WHERE state LIKE 'M%';
SELECT last_name, city FROM members WHERE state IN ('MO', 'MI');
SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';

Correct answer: C

Question 10

Which two statements are true regarding the COUNT function?

A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.
COUNT (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
COUNT (*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns.
The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.

Correct answer: BD