Oracle 1z0-071 Exam Dumps

Oracle Database 12c SQL

646 Reviews

Exam Code	1z0-071
Exam Name	Oracle Database 12c SQL
Questions	326 Questions Answers With Explanation
Update Date	06, 11, 2026
Price	Was : $81 Today : $45 Was : $99 Today : $55 Was : $117 Today : $65

Add To Cart

Demo Questions

Question # 1

An Oracle database server session has an uncommitted transaction in progress which updated 5000 rows in a table.In which three situations does the transactions complete thereby committing the updates? (Choose three.)

A. when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT
B. when a CREATE INDEX statement is executed successfully in the same session
C. when a COMMIT statement is issued by the same user from another session in the same database instance
D. when the session logs out successfully
E. when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT
F. when a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

Show Answer

Question # 2

Which three actions can you perform by using the ORACLE_DATAPUMP access driver? (Choose three.)

A. Read data from an external table and load it into a table in the database
B. Create a directory object for an external table
C. Execute DML statements on an external table
D. Query data from an external table
E. Read data from a table in the database and insert it into an external table
F. Create a directory object for a flat file

Show Answer

Question # 3

Which three statements are true about performing Data Manipulation Language (DML) operations on a view in an Oracle Database?

A. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.
B. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
C. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
D. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
E. Insert statements can always be done on a table through a view.
F. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains aggregating functions.

Show Answer

Question # 4

Which three statements are true about the Oracle join and ANSI join syntax? 

A. The Oracle join syntax supports natural joins.
B. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.
C. The Oracle join syntax supports creation of a Cartesian product of two tables.
D. The SQL:1999 compliant ANSI join syntax supports natural joins.
E. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
F. The Oracle join syntax only supports right outer joins.
G. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.

Show Answer

Question # 5

Which two are true about granting privileges on objects? 

A. An object privilege can be granted to other users only by the owner of that object.
B. An object privilege can be granted to a role only by the owner of that object.
C. A table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table.
D. The owner of an object acquires all object privileges on that object by default.
E. The WITH GRANT OPTION clause can be used only by DBA users.

Show Answer

Question # 6

Which three statements are true regarding the SQL WHERE and HAVING clauses? (Choose three.)

A. The HAVING clause conditions can have aggregating functions.
B. The HAVING clause conditions can use aliases for the columns.
C. The WHERE and HAVING clauses cannot be used together in a SQL statement.
D. The WHERE clause is used to exclude rows before grouping data.
E. The HAVING clause is used to exclude one or more aggregated results after grouping data.

Show Answer

Question # 7

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

A. A Main query can have many subqueries.
B. A subquery can have more than one main query.
C. The subquery and main query must retrieve date from the same table.
D. The subquery and main query can retrieve data from different tables.
E. Only one column or expression can be compared between the subquery and main query.
F. Multiple columns or expressions can be compared between the subquery and main query.

Show Answer

Question # 8

Examine the data in the CUST_NAME column of the CUSTOMERS table.CUST_NAME-------------------Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni Julia NayerYou need to display customers' second names where the second name starts with "Mc" or "MC". Which query gives the required output?

A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');

Show Answer

Question # 9

Which statement adds a column called SALARY to the EMPLOYEES table having 100 rows, which cannot contain null?

A. ALTER TABLE EMPLOYEESADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL;
B. ALTER TABLE EMPLOYEESADD SALARY NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;
C. ALTER TABLE EMPLOYEESADD SALARY NUMBER(8,2) DEFAULT NOT NULL;
D. ALTER TABLE EMPLOYEESADD SALARY NUMBER(8,2) NOT NULL;

Show Answer

Question # 10

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.

Show Answer

Question # 11

Which statement is true regarding the SESSION_PRIVS dictionary view?

A. It contains the object privileges granted to other users by the current user session.
B. It contains the system privileges granted to other users by the current user session.
C. It contains the current object privileges available in the user session.
D. It contains the current system privileges available in the user session.

Show Answer

Question # 12

Which statement is true regarding the USING clause in table joins? (Choose two.) 

A. It can be used to join a maximum of three tables.
B. It can be used to access data from tables through equijoins as well as nonequijoins.
C. It can be used to join tables that have columns with the same name and compatible data types.
D. It can be used to restrict the number of columns used in a NATURAL join.

Show Answer

Question # 13

You must create a table for a banking application.One of the columns in the table has these requirements: A column to store the duration of a short team loanThe data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions.The maximum loan period is 30 days.Interest must be calculated based on the number of days for which the loan remains unpaid.Which data type would you use?

A. DATE
B. NUMBER
C. TIMESTAMP
D. INTERVAL DAY TO SECOND
E. INTERVAL YEAR TO MONTH

Show Answer

Question # 14

Which two statements are true regarding the execution of the correlated subqueries? (Choose two.) 

A. The nested query executes after the outer query returns the row.
B. The nested query executes first and then the outer query executes.
C. The outer query executes only once for the result returned by the inner query.
D. Each row returned by the outer query is evaluated for the results returned by the inner query.

Show Answer

Question # 15

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

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

Show Answer

Question # 16

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

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.

Show Answer

Question # 17

Which statement is true about the INTERSECT operator used in compound queries?

A. INTERSECT is of lower precedence than UNION or UNION ALL.
B. Multiple INTERSECT operators are not possible in the same SQL statement.
C. It ignores NULLs.
D. It processes NULLs in the selected columns.

Show Answer

Question # 18

On your Oracle 12c database, you invoked SQL *Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command: $> sqlldr hr/hr@pdb table=employees Which two statements are true regarding the command? (Choose two.)

A. It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database.
B. It fails because no SQL *Loader data file location is specified.
C. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.
D. It fails because no SQL *Loader control file location is specified.

Show Answer

Question # 19

The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER. Which two queries execute successfully?

A. SELECT NVL (cust_credit_limit * .15, ‘Not Available’) FROM customers;
B. SELECT TO_CHAR(NVL(cust_credit_limit * .15, ‘Not Available’)) FROM customers;
C. SELECT NVL(TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;
D. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;
E. SELECT NVL2 (cust_credit_limit * .15, ‘Not Available’) FROM customers;

Show Answer

Question # 20

Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.) 

A. During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.
B. During execution, the Oracle server may read data from storage if the required data is not already in memory.
C. During row source generation, rows that satisfy the query are retrieved from the database and stored in memory
D. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution

Show Answer

Question # 21

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

A. The ORDER BY Clause can be used in a subquery.
B. A subquery can be used in the FROM clause of a SELECT statement.
C. If a subquery returns NULL, the main query may still return rows.
D. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

Show Answer

Question # 22

In the PROMOTIONS table, the PROMO_BEGIN_DATE column is of data type DATE and the default date format is DD-MON-RR. Which two statements are true about expressions using PROMO_BEGIN_DATE contained a query?

A. PROMO_BEGIN_DATE – 5 will return a date.
B. PROMO_BEGIN_DATE – SYSDATE will return a number.
C. TO_NUMBER(PROMO_BEGIN_DATE) – 5 will return a number.
D. TO_DATE(PROMO_BEGIN_DATE * 5) will return a date.
E. PROMO_BEGIN_DATE – SYSDATE will return an error.

Show Answer

Question # 23

These are the steps for a correlated subquery, listed in random order:The WHERE clause of the outer query is evaluated.A candidate row is fetched from the table specified in the outer query.This is repeated for the subsequent rows of the table, until all the rows are processed.Rows are returned by the inner query, after being evaluated with the value from the candidate row in theouter query.Which is the correct sequence in which the Oracle server evaluates a correlated subquery?

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

Show Answer

Question # 24

Which two statements are true about date/time functions in a session where NLS_DATE_FORMAT is set to DD-MON-YYYY HH24:MI:SS? 

A. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.
B. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of fractional seconds
C. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server.
D. SYSDATE can be used in expressions only if the default date format is DD-MON-RR.
E. SYSDATE can be queried only from the DUAL table.
F. CURRENT_DATE returns the current date and time as per the session time zone.

Show Answer

Question # 25

Which two are true about the WITH GRANT OPTION clause? 

A. The grantee must have the GRANT ANY OBJECT PRIVILEGE system privilege to use this option.
B. It can be used when granting privileges to roles.
C. It cannot be used to pass on privileges to PUBLIC by the grantee.
D. It can be used for system and object privileges.
E. It can be used to pass on privileges to other users by the grantee.
F. The grantee can grant the object privilege to any user in the database, with or without including this option.

Show Answer