Question 1

Choose the best Examine the description of the EMPLOYEES table: Which query is valid?

Accepted Answer

A

Explanation: 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

Question 2

Which three statements are true about single row functions?

Accepted Answer

D, E, F

Explanation: 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.

Question 3

Which statement executes successfully?

Accepted Answer

E

Explanation: 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

Question 4

Which two are true about transactions in the Oracle Database?

Accepted Answer

B, D

Explanation: 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

Question 5

Examine the data in the COLORS table: https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/1Z0-071/page_239_img_1.jpg Examine the data in the BRICKS table: https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/1Z0-071/page_239_img_2.jpg Which two queries return all the rows from COLORS?

Accepted Answer

A, D

Explanation: 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.

Question 6

Which two are true about external tables that use the ORACLE _DATAPUMP access driver?

Accepted Answer

C

Explanation: 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

Question 7

Examine the description of the EMPLOYEES table: https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/1Z0-071/page_271_img_1.jpg Which statement increases each employee's SALARY by the minimum SALARY for their DEPARTM ENT_ID?

Accepted Answer

D

Explanation: 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

Question 8

https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/1Z0-071/page_277_img_1.jpg For each employee in department 90 you want to display: 1. their last name 2. the number of complete weeks they have been employed The output must be sorted by the number of weeks, starting with the longest serving employee first.Which statement will accomplish this?

Accepted Answer

D

Explanation: 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)

Question 9

Which statement will execute successfully?

Accepted Answer

B

Explanation: 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

Question 10

Examine this statement:
CREATE TABTE orders
(sarial_no NUMBER UNIQUE,
order_id NUMBER PRIMARY KEY ,
order_date DATE NOT NULL,
status VARCHAR2 (10) CHECK (status IN ('CREDIT', 'CASH')),
product_id NUMBER REFERENCES products (product_id),
order_ total NUMBER);
On which two columns of the table will an index be created automatically?

Accepted Answer

A, E

Explanation: 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

Question 11

You have the privileges to create any type of synonym. Which stalement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that is accesible to all users?

Accepted Answer

E

Explanation: Synonyms in Oracle are aliases for database objects that can simplify SQL statements for database users. A . The term "GLOBAL" is not used in the creation of synonyms in Oracle. B . The statement without the keyword PUBLIC will create a private synonym that is only accessible to the user creating the synonym, not all users. C . The correct syntax does not include PUBLIC as a prefix to the synonym name itself, making this option incorrect. D . You cannot specify the SYS schema for creating synonyms, as it is reserved for system objects. E . This is the correct syntax to create a public synonym, which makes the underlying object accessible to all users. Reference: Oracle Database SQL Language Reference, 12c Release 1 (12.1): "CREATE SYNONYM"

Question 12

Examine the data in the ENPLOYEES table: https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/1Z0-071/page_284_img_1.jpg Which statement will compute the total annual compensation tor each employee？

Accepted Answer

C

Explanation: The correct statement for computing the total annual compensation for each employee is option C. This is because the monthly commission is a percentage of the monthly salary (indicated by the column name MONTHLY_COMMISSION_PCT). To calculate the annual compensation, we need to calculate the annual salary (which is monthly_salary * 12) and add the total annual commission to it. Here's the breakdown of the correct statement, option C: (monthly_salary * 12) computes the total salary for the year. NVL(monthly_commission_pct, 0) replaces NULL values in the monthly_commission_pct column with 0, ensuring that the commission is only added if it exists. (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) computes the annual commission by first determining the monthly commission (which is a percentage of the monthly salary), and then multiplying it by 12 to get the annual commission. Finally, (monthly_salary * 12) + (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) adds the annual salary and the annual commission to get the total annual compensation. The other options are incorrect: Option A is incorrect because it adds the monthly_commission_pct directly to the monthly_salary, which does not consider that monthly_commission_pct is a percentage. Option B is incorrect because it adds the commission percentage directly without first applying it to the monthly salary. Option D is incorrect because it does not handle the NULL values in the commission column, which would result in a NULL total annual compensation whenever the monthly_comission_pct is NULL. Reference: Oracle Documentation on NVL function: NVL Oracle Documentation on Numeric Literals: Numeric Literals

Question 13

Whith three statements are true about built in data types?

Accepted Answer

B, D, F

Explanation: The true statements about built-in data types in Oracle are: B: A BFILE is a data type in Oracle that allows for a read-only link to binary files stored outside the database in the operating system. This is correct as per Oracle's documentation. D: The default length for a CHAR column, when not specified, is one character. This is according to the Oracle SQL standard. F: A BLOB is used for storing binary data within the Oracle database, allowing for storage of large amounts of unstructured binary data. The incorrect options are: A: A VARCHAR2 column does not blank-pad values; it is CHAR that may blank-pad to the fixed length. C: A CHAR column requires a length specification, although if omitted, the default is one character. E: A VARCHAR2 column requires a length specification; without it, the statement will fail. Reference: Oracle Documentation on Data Types: Data Types Oracle Documentation on LOBs: LOBs Oracle Documentation on Character Data Types: Character Data Types

Question 14

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

Accepted Answer

B, C, F

Explanation: Regarding Oracle join and ANSI join syntax: B . The Oracle join syntax supports the creation of a Cartesian product of two tables. This is true. In Oracle, if you list tables in the FROM clause without a join condition, it creates a Cartesian product. C . The SQL:1999 compliant ANSI join syntax supports natural joins. This is true. ANSI syntax supports natural joins, which join tables based on columns with the same names in the joined tables. F . The SQL:1999 compliant ANSI join syntax supports the creation of a Cartesian product of two tables. This is true. The ANSI standard allows for Cartesian products when tables are listed in the FROM clause without a join condition. Options A, D, E, and G are incorrect: A is incorrect because the Oracle join syntax supports all types of joins, including right outer joins. D is incorrect because Oracle's proprietary join syntax does not use the term "natural join." E is incorrect because there is no inherent performance difference between Oracle join syntax and ANSI join syntax; performance depends on how the query is written and how the database optimizer handles it. G is incorrect for the same reason as E. Reference: Oracle Database SQL Language Reference, 12c Release 1 (12.1)

Question 15

In your session NLS_ DATE_ FORMAT is set to DD–MON_RR. Which two queries display the year as four digits?

Accepted Answer

E, A

Explanation: A . True. The TO_CHAR function will convert SYSDATE to a string using the format 'MM/DD/YYYY', which displays the year as four digits. Then TO_DATE will convert this string back to a date without changing its appearance because the format matches. E . True. The TO_CHAR function with the format 'MM/DD/YYYY' will display the date with the year as four digits, regardless of the NLS_DATE_FORMAT setting. B, C, D, and F are incorrect because they either do not specify the format model needed to display the year as four digits or use TO_DATE which does not format the output but rather interprets the input. Reference: Oracle Database SQL Language Reference 12c, Datetime Functions - TO_DATE, TO_CHAR

Free Oracle 1Z0-071 Actual Exam Questions