Amazon
Avaya
Checkpoint
Cisco®
CompTIA
CrowdStrike
EC-Council
EXIN
Fortinet
Google
ISC
ISC2
ITIL®
Juniper
Microsoft
Okta
Oracle
Palo Alto Networks
All Vendors
  1. Home
  2. Oracle
  3. 1Z0-071

Oracle 1Z0-071 Exam (page: 8)
Oracle Database SQL
Updated on: 28-Nov-2025

Viewing Page 8 of 49
1Z0-071 PDF 380 Questions

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


Which two tasks would require subqueries or joins to be executed in a single statement?

  1. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
  2. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
  3. listing of customers who do not have a credit limit and were born before 1980
  4. finding the number of customers, in each city, whose marital status is 'married'
  5. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.

Answer(s): A,E



Which statement is true about transactions?

  1. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.
  2. Each Data Definition Language (DDL) statement executed forms a single transaction.
  3. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
  4. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.

Answer(s): B


Reference:

https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT038



View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.


You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.

Which CREATE VIEW statement would create the view successfully?

  1. Option A
  2. Option B
  3. Option C
  4. Option D

Answer(s): C



Which statement is true about an inner join specified in the WHERE clause of a query?

  1. It must have primary-key and foreign-key constraints defined on the columns used in the join condition.
  2. It requires the column names to be the same in all tables used for the join conditions.
  3. It is applicable for equijoin and nonequijoin conditions.
  4. It is applicable for only equijoin conditions.

Answer(s): C



Which statement is true regarding the INTERSECT operator?

  1. The names of columns in all SELECT statements must be identical.
  2. It ignores NULL values.
  3. Reversing the order of the intersected tables alters the result.
  4. The number of columns and data types must be identical for all SELECT statements in the query.

Answer(s): D

Explanation:

INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.
The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.



Examine the following query:

SQL> SELECT prod_id, amount_sold
FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY;

What is the output of this query?

  1. It displays 5 percent of the products with the highest amount sold.
  2. It displays the first 5 percent of the rows from the SALES table.
  3. It displays 5 percent of the products with the lowest amount sold.
  4. It results in an error because the ORDER BY clause should be the last clause.

Answer(s): C


Reference:

https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1



The first DROP operation is performed on PRODUCTS table using this command:
DROP TABLE products PURGE;

Then a FLASHBACK operation is performed using this command:
FLASHBACK TABLE products TO BEFORE DROP;

Which is true about the result of the FLASHBACK command?

  1. It recovers only the table structure.
  2. It recovers the table structure, data, and the indexes.
  3. It recovers the table structure and data but not the related indexes.
  4. It is not possible to recover the table structure, data, or the related indexes.

Answer(s): D


Reference:

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



These are the steps for a correlated subquery, listed in random order:

1. The WHERE clause of the outer query is evaluated.
2. A candidate row is fetched from the table specified in the outer query.
3. This is repeated for the subsequent rows of the table, until all the rows are processed.
4. Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.

Which is the correct sequence in which the Oracle server evaluates a correlated subquery?

  1. 2,1,4,3
  2. 4,1,2,3
  3. 4,2,1,3
  4. 2,4,1,3

Answer(s): D


Reference:

http://rajanimohanty.blogspot.co.uk/2014/01/correlated-subquery.html



Viewing Page 8 of 49



Share your comments for Oracle 1Z0-071 exam with other users:

Saravana Kumar TS 12/8/2023 9:49:00 AM

question: 93 which statement is true regarding the result? sales contain 6 columns and values contain 7 columns so c is not right answer.
INDIA