Oracle 1Z0-071 Exam Real Questions

Prepare and pass your Oracle Database 12c SQL with free 1Z0-071 exam questions.

Page: 1 / 109

Total 544 Questions | Updated On: Sep 25, 2024

Add To Cart

Question 1

The ORDERS table a primary key constrain on the ORDER_ID column.

The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the

primary key of the ORDERS table.

The constraint is defined with ON DELETE CASCADS.

There are rows in the ORDERS table with an ORDER_TOTAL of less than 1000.

Which three DELETE statements execute successfully?

A : LETE * FROM orders WHERE order_total < 1000;

B : LETE FROM orders;

C : LETE order_id FROM orders WHERE order_total < 1000;

D : LETE orders WHERE order_total < 1000;

E : LETE FROM orders WHERE order_total < 1000;

Answer: A,B,D

Question 2

Examine the data in the EMP table:

You execute this query:

SELECT deptno AS 'Department', AVG(sal) AS AverageSalary, MAX(sal) AS 'Max Salary'

FROM emp

WHERE sal >= 12000

GROUP BY 'Department '

ORDER BY AverageSalary;

Why does an error occur?

A : alias name must not be used in an ORDER BY clause

B : allas name must not contain space characters.

C : alias name must not be used in a GROUP BY clause.

D : alias name must always be specified in quotes.

Answer: C

Question 3

Which three statements are true about inner and outer joins? (Choose three.)

A : full outer join must use Oracle syntax

B : inner join returns matched rows

C : left or right outer join returns only unmatched rows

D : full outer join returns matched and unmatched rows

E : ter joins can only be used between two per query

F : ter joins can be used when there are multiple join conditions on two tables

Answer: B,C,D

Question 4

View the exhibit for the structure of the STUDENT and FACULTY tables.

You need to display the faculty name followed by the number of students handled by the faculty at

the base location.

Examine the following two SQL statements:

Statement 1

SQL>SELECT faculty_name, COUNT(student_id)

FROM student JOIN faculty

USING (faculty_id, location_id)

GROUP BY faculty_name;

Statement 2

SQL>SELECT faculty_name, COUNT(student_id)

FROM student NATURAL JOIN faculty

GROUP BY faculty_name;

Which statement is true regarding the outcome?

A : ly statement 2 executes successfully and gives the required result.

B : ly statement 1 executes successfully and gives the required result.

C : th statements 1 and 2 execute successfully and give different results.

D : th statements 1 and 2 execute successfully and give the same required result.

Answer: B

Question 5

Examine the data in the INVOICES table:

Examine the data in the CURRENCIES table:

CURRENCY_CODE

-------------

JPY

GPB

CAD

EUR

USD

Which query returns the currencies in CURRENCIES that are not present in INVOICES?

A : SELECT currency_ code FROM currenciesMINUSSELECT currency_ code FROM invoices;

B : SELECT * FROM currenciesWHERE NOT EXISTS (SELECT NULL FROM invoices WHERE currency_ code = currency_ code);

C : SELECT currency_ code FROM currenciesINTERSECTSELECT currency_ code FROM invoices;

D : SELECT * FROM currenciesMINUSSELECT * FROM invoices;

Answer: A

Page: 1 / 109

Total 544 Questions | Updated On: Sep 25, 2024

Add To Cart