1Z0-071 Practice Questions

In Oracle 12c SQL, the GROUP BY clause is used to arrange identical data into groups with the GROUP BY expression followed by the SELECT statement. The SUM() function is then used to calculate the sum for each grouped record on a specific column, which in this case is the salary column. Option A is valid because it correctly applies the GROUP BY clause. Both dept_id and join_date are included in the SELECT statement, which is a requirement when using these columns in conjunction with the GROUP BY clause. This means that the query will calculate the sum of salaries for each combination of dept_id and join_date. It adheres to the SQL rule that every item in the SELECT list must be either an aggregate function or appear in the GROUP BY clause. Option B is invalid due to a typo in SELECT depe_id and also because it ends with a colon rather than a semicolon. Option C is invalid because you cannot nest aggregate functions like MAX(AVG(salary)) without a subquery. Option D is invalid for the same reason as option C, where it tries to nest aggregate functions AVG(MAX(salary)), which is not allowed directly in SQL without a subquery. For further reference, you can consult the Oracle 12c documentation, which provides comprehensive guidelines on how to use the GROUP BY clause and aggregate functions like SUM(): Oracle Database SQL Language Reference, 12c Release 1 (12.1): GROUP BY Clause Oracle Database SQL Language Reference, 12c Release 1 (12.1): Aggregate Functions

Single-row functions in SQL operate on each row independently and can modify the returned value:

Option A: Incorrect. Single row functions can be used in multiple parts of a SELECT statement,

including SELECT, WHERE, and ORDER BY clauses.

Option B: Incorrect. Single row functions can accept more than one argument, such as the CONCAT

function, which can accept multiple string arguments.

Option C: Incorrect. They return one result for each row processed, not per table.

Option D: Correct. Single row functions can take various types of arguments including column names,

literals, variables, and other expressions.

Option E: Correct. Functions can be nested within other functions, allowing complex expressions and

calculations.

Option F: Correct. The data type of the result can differ from the arguments’ data types, such as the

SUBSTR function returning a VARCHAR2 even when used on a number after converting it to a string.

Oracle's interval data types store a period of time. When you want to format an interval as a string,

you can use the TO_CHAR function.

A . This statement will not execute successfully because TO_DATE expects a string representing a

date, not a number derived from an interval.

B . This statement will not execute successfully because TO_NUMBER does not support interval data

types as an input.

C . This statement will not execute successfully because TO_DATE does not support interval data

types as an input.

D . This statement will not execute successfully because TO_NUMBER expects a number or a string

representing a number as an argument, not a date.

E . This statement will execute successfully. TO_CHAR function can be used to convert interval values

to a formatted string, such as 'HH24:MM'.

Reference:

Oracle Documentation on Datetime Functions:

https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF00654

B . True. DDL (Data Definition Language) statements in Oracle Database are auto-commit statements.

This means that if a DDL statement is issued, any uncommitted transactions in the same session will

automatically be committed.

D . False. This is a common misconception. DML (Data Manipulation Language) statements do not

automatically start new transactions. In Oracle, a transaction begins when the first DML statement

(INSERT, UPDATE, DELETE, MERGE, SELECT FOR UPDATE) is encountered after a previous transaction

has been committed or rolled back.

A is incorrect because DDL statements automatically commit the entire transaction, not just the data

dictionary updates. C is incorrect because an uncommitted transaction is not automatically

committed when a user exits SQL*Plus; instead, it is rolled back. E is incorrect because a session

cannot see uncommitted updates made by other sessions.

Reference: Oracle Database SQL Language Reference 12c, Transaction Control - COMMIT

In SQL, to return all rows from one table in a join regardless of whether they match rows in the other

table, a LEFT JOIN is used when we want all rows from the left table, or a RIGHT JOIN when we want

all rows from the right table. Here’s an explanation for each query provided in the context of the

COLORS and BRICKS tables:

A . This query uses a LEFT JOIN and will return all the rows from the COLORS table (c) because it is on

the left side of the join. Additionally, it joins the BRICKS table (b) on the condition that the

color_rgb_hex_value matches. The condition in the WHERE clause (b.brick_id > 0) will not exclude

any COLORS rows because it only filters the rows from the right table (BRICKS) after the join.

B . This query uses a RIGHT JOIN, and while it does return all rows from the COLORS table, it doesn't

place it on the left side of the join, which is not what is typically expected from a RIGHT JOIN to

return all rows from the right side table.

C . This query is a plain JOIN (also known as an inner join), which only returns rows that have

matching values in both tables, so it will not return all the rows from the COLORS table if there is no

match in the BRICKS table.

D . This query uses a FULL JOIN, which returns all rows when there is a match in one of the tables.

Hence, it will return all the rows from both the COLORS and BRICKS tables. While this does return all

rows from the COLORS table, it also includes all rows from the BRICKS table, which may not be

desired if the requirement was only to return all rows from COLORS.

E . This query is similar to option A and uses the USING clause to specify the join condition. The

USING clause is used when both tables have a column with the same name and is meant to simplify

the syntax. However, in this case, the columns do not have the same name in both tables

(RGB_HEX_VALUE in COLORS and COLOR_RGB_HEX_VALUE in BRICKS), so this query will not execute

successfully.

Reference:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Joins"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "LEFT OUTER JOIN"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "RIGHT OUTER JOIN"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "FULL OUTER JOIN"

Based on the explanations above, the correct answers that return all rows from the COLORS table are

A and D. However, D includes all rows from BRICKS as well, which may not be the intended

requirement if the question specifies only rows from COLORS should be returned. Thus, A is the most

accurate answer to the question as it adheres to the standard expectation of a LEFT JOIN.

External tables using the ORACLE_DATAPUMP access driver have specific characteristics:

C . Creating an external table using the ORACLE_DATAPUMP access driver allows you to select data

from other tables, including another external table or a regular table whose rows are stored in

database blocks.

A, B, D, and E are incorrect. Specifically:

A is incorrect because creating an external table does not automatically create a directory object; the

directory object must exist prior to or be created separately.

B is incorrect as it limits the creation to tables stored in database blocks, which is not a restriction.

D is incorrect because creating an external table does not create a dump file; it reads from an existing

dump file created by Data Pump.

E is also incorrect because the dump file is not created as part of the external table creation process.

Reference:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "CREATE TABLE" for External Tables

The correct statement to increase each employee's salary by the minimum salary for their

department is:

D . This option correctly increases each employee's salary by the minimum salary in their

department. The subquery calculates the minimum salary for the department of the current row

being updated (e1) in the outer UPDATE statement. The subquery does not use GROUP BY since it

returns a single value - the minimum salary for the matching department.

A, B, and C are incorrect. Specifically:

A uses a subquery with an aggregate function combined with GROUP BY, which is not needed and

will result in an error.

B incorrectly

MIN(e1.salary) within the subquery, which is incorrect since e1 is the alias

for the outer query's table and should not be used within the subquery.

C sets the salary to itself plus the minimum salary across all employees, not filtered by the

department.

Reference:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "UPDATE"

D . True. The TRUNC function, when applied to the difference between SYSDATE and hire_date

divided by 7, will return the number of complete weeks of employment. The ORDER BY tenure DESC

will sort the result in descending order, starting with the longest-serving employee.

A is incorrect because it does not include the DESC keyword necessary to start with the longest-

serving employee. B is incorrect because ROUND could result in rounding up to the next week, which

does not give the number of complete weeks. C is incorrect because the division is done by 17

instead of 7, which is likely a typo, and ROUND is used instead of TRUNC.

Reference: Oracle Database SQL Language Reference 12c, Single-Row Functions - ROUND and TRUNC

(Date)

B . True. This statement will execute successfully because it has a single column in the SELECT

statements combined with UNION, and the ORDER BY clause is referencing a valid column in the

result set.

A is incorrect because it uses an ORDER BY clause with two columns, which is not allowed when the

SELECT statements have only one column each. C is incorrect for the same reason as A; it

columns that do not exist in the result set. D is incorrect because it attempts to ORDER BY a second

column, which does not exist in the result of the union.

Reference: Oracle Database SQL Language Reference 12c, Set Operators - The UNION [ALL],

INTERSECT, MINUS Operators

An index is created automatically on columns that are defined with a UNIQUE constraint and a

PRIMARY KEY constraint.

A . True. Since the serial_no column is defined with a UNIQUE constraint, Oracle will automatically

create an index for this column.

E . True. The order_id column is defined with a PRIMARY KEY constraint, and Oracle automatically

creates a unique index on the primary key column.

Reference: Oracle Database Concepts 12c, Indexes - Overview of Indexes