[2025年04月] ベスト Oracle PL/SQL Developer Certified Associate 学習ガイドは 1z1-071 試験問題集 [Q134-Q158]

[2025年04月] ベストOracle PL/SQL Developer Certified Associate学習ガイドは1z1-071試験問題集

1z1-071認定ガイド問題と解答トレーニング

Oracle Database SQL試験（1z1-071）は、SQLデータベース管理に特化した専門家向けの認定試験です。この試験は、Oracleデータベースを適切に管理するために必要な基本的なスキルを習得するための重要なステップです。Oracle Database SQL認定（1z1-071）は、高度なSQL機能を利用してOracleデータベースを開発、管理、維持することができることを証明するため、雇用主に高く評価されています。

Oracle 1z0-071 は、オラクルデータベース SQL 資格試験としても知られており、SQL 開発やデータベース管理におけるプロフェッショナルのスキルと知識を検証する業界に認められた認定です。この認定試験は、SQL ステートメントの作成、データベースオブジェクトの管理、およびその他のデータベース関連のタスクを実行する能力を証明したい個人を対象としています。

 

質問 # 134
Examine the description of the EMPLOYEES table:

NLS_DATE FORMAT is DD-MON-RR.
Which two queries will execute successfully?

• A. SELECT dept_ id, AVG (MAX(salary)) FROM employees GROUP By dept_id HAVING hire_date> ' O1-JAN-19';
• B. SELECT dept id, MAX (SUM(salary)) FROM employees GROUP BY dept_id;
• C. SELECT dept_ iD, sum(salary) FROM employees WHERE hire_date > '01-JAN-9' GROUP BY dept_id;
• D. SELECT dept_ id, AVG(MAX(salary)) FROM employees GROUP BY dept_id, salary;
• E. SELECT AVG(MAX(salary)) FROM employees GROUP BY salary;

正解：C、E

質問 # 135
What is true about non-equijoin statement performance?

• A. The between condition always performs better than using the >= and <= conditions.
• B. The join syntax used makes no difference to performance.
• C. The Oracle join syntax performs better than the SQL: 1999 compliant ANSI join syntax.
• D. The between condition always performs less well than using the >= and <= conditions.
• E. Table aliases can improve performance.

正解：B

解説：
When comparing the performance of different SQL join types and conditions, Oracle SQL optimizations generally ensure that performance is consistent across different syntactical forms of expressing the same logic:
Option A: False. The BETWEEN condition does not inherently perform worse than using >= and <=. Oracle's optimizer typically evaluates these conditions similarly, optimizing the underlying execution based on the data distribution and available indexes.
Option B: False. Oracle's optimizer is designed to handle both Oracle-specific join syntax and ANSI join syntax with equal competence. Performance differences would typically be negligible because the optimizer translates both into an optimal execution plan based on the same underlying mechanisms.
Option C: True. The join syntax used (whether Oracle's traditional syntax or ANSI standard syntax) generally does not affect the performance. Oracle's query optimizer is adept at translating different syntaxes into efficient execution plans.
Option D: False. The assertion that BETWEEN always performs better than >= and <= is incorrect. Performance depends more on factors like indexing, the specific data and distribution, and the Oracle optimizer's capabilities than on the mere choice of syntax.
Option E: False. While table aliases help improve query readability and can prevent ambiguity in SQL queries, they do not inherently improve performance. Their use is a best practice for code clarity and maintenance, not performance enhancement.

質問 # 136
Which three statements indicate the end of a transaction? (Choose three.)

• A. after a COMMITis issued
• B. after a ROLLBACKis issued
• C. after a SELECTstatement is issued
• D. after a SAVEPOINTis issued
• E. after a CREATEstatement is issued

正解：A、B、E

質問 # 137
Examine the description of the CUSTOMERS table:

Which three statements will do an implicit conversion?

• A. SELECT FROM customers WHERE insert date = '01-JAN-19';
• B. SELECT * FROM customers WHERE TO_ CHAR (customer_ id) = '0001';
• C. SELECT * FROM customers WHERE customer id = '0001';
• D. SELECT. FRON customers WE TO DATE (Insert _ date) = DATE '2019-01-01';
• E. SELECT * FROM customers WHERE customer_ id = 0001;
• F. SELECT. FROM customers WHERE insert_ date = DATE *2019-01-01';

正解：A、C、D

質問 # 138
Examine this SQL statement:
DELETE FROM employees e
WHERE EXISTS
(SELECT'dummy'
FROM emp_history
WHERE employee_id = e.employee_id)
Which two are true?

• A. The DELETE statement executes successfully even if the subquery selects multiple rows.
• B. The subquery is not a correlated subquery.
• C. The subquery is executed before the DELETE statement is executed.
• D. The subquery is executed for every row in the EMPLOYEES table.
• E. All existing rows in the EMPLOYEE table are deleted.

正解：A、D

解説：
The provided DELETE statement uses a correlated subquery to determine which rows should be deleted from the EMPLOYEES table.
* A. The subquery is indeed executed for every row in the EMPLOYEES table. This is because it references e.employee_id, which is a column from the outer query, making it a correlated subquery.
* B. The subquery is a correlated subquery, as explained above.
* C. The subquery is executed for each row, not before the DELETE statement.
* D. Not all existing rows in the EMPLOYEES table are deleted, only those that have a corresponding employee_id in the EMP_HISTORY table.
* E. The DELETE statement executes successfully even if the subquery selects multiple rows because the EXISTS condition only checks for the presence of rows, not their count.
References:
* Oracle Database SQL Language Reference 12c Release 1 (12.1), DELETE Statement
* Oracle Database SQL Language Reference 12c Release 1 (12.1), Subquery Factoring
* Oracle Database SQL Language Reference 12c Release 1 (12.1), Correlated Subqueries

質問 # 139
Examine this statement:
SELECT 1 AS id, ' John' AS first name
FROM DUAL
UNION
SELECT 1 , ' John' AS name
FROM DUAL
ORDER BY 1;
What is returned upon execution?

• A. 2 rows
• B. 1 row
• C. 0 rows
• D. an error

正解：B

解説：
The statement provided uses the UNION operator, which combines the results of two or more queries into a single result set. However, UNION also eliminates duplicate rows. Both queries in the union are selecting the same values 1 and ' John', thus the result is one row because duplicates will be removed.
SELECT 1 AS id, ' John' AS first name FROM DUAL UNION SELECT 1 , ' John' AS name FROM DUAL ORDER BY 1; Given that both SELECT statements are actually identical in the values they produce, despite the differing column aliases, the UNION will eliminate one of the duplicate rows, resulting in a single row being returned.
References:
* Oracle Documentation on UNION: SQL Language Reference - UNION

質問 # 140
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 Nayer
You want to display the CUST_NAME values where the last name starts with Mc or MC.
Which two WHERE clauses give the required result?

• A. WHERE SUBSTR(cust_name, INSTR(cust_name,' ') + 1) LIKE 'Mc%' OR 'MC%'
• B. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) IN ('MC%','Mc%')
• C. WHERE SUBSTR(cust_name, INSTR(cust_name,' ') + 1) LIKE 'Mc%'
• D. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) LIKE 'Mc%'
• E. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) LIKE UPPER('MC%')

正解：C、E

解説：
To find customer names where the last name starts with Mc or MC, the correct queries are:
* A. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'MC%'This query
* converts the substring of cust_name that comes after the first space (which should correspond to the last name) to uppercase and checks if it starts with 'MC', which will match both 'Mc' and 'MC'.
* E. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1) LIKE 'Mc%'This query checks if the substring of cust_name after the first space starts with 'Mc'. However, this query will only match last names that start with 'Mc' and not 'MC' unless the database is using a case-insensitive collation.
Options B, C, and D are incorrect:
* B is incorrect because the syntax is not valid in Oracle SQL; the LIKE clause cannot be used to match multiple patterns in that way.
* C is incorrect because INITCAP would not only capitalize the first letter of 'mc' or 'Mc' but would lowercase all other letters, which is not the intended action.
* D is incorrect because, like option C, INITCAP is not the appropriate function for this use case and it will not correctly identify names that start with 'MC'.

質問 # 141
Examine the data in the CUST_NAME column of the CUSTOMERS table.

You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows:

Which two queries give the required output?

• A. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ',1,2)<>0;
• B. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ',-1,2)<>0;
• C. SELECT LPAD(SUBSTR(cust_name, INSTR (cust_name ' ')),LENGTH(cust_name) - INSTR(cust_name, ' '), '*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ',1,-2)<>0;
• D. SELECT LPAD(SUBSTR(cust_name, INSTR (cust_name ' ')),LENGTH(cust_name) - INSTR(cust_name, ' '), '*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ',1,2)<>0;

正解：A、B

質問 # 142
Examine the description of the ORDERStable:

Examine the description of the INVOICEStable:

Which three statements execute successfully? (Choose three.)

• A.
• B.
• C.
• D.
• E.
• F.
• G.

正解：B、F、G

質問 # 143
Examine the description of the PRODUCT_DETAILS table:

Which two statements are true?

• A. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.
• B. PRODUCT_ID can be assigned the PRIMARY KEY constraint.
• C. EXPIRY_DATE cannot be used in arithmetic expressions.
• D. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it.
• E. PRODUCT_NAME cannot contain duplicate values.
• F. PRODUCT_PRICE contains the value zero by default if no value is assigned to it.

正解：C、E

質問 # 144
Examine the description of the EMP_DETAILS table given below:

Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL TABLE?

• A. You cannot add a new column to the table with LONG as the data type.
• B. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.
• C. An EMP_IMAGE column can be included in the GROUP BY clause.
• D. An EMP_IMAGE column cannot be included in the ORDER BY clause.

正解：A、D

解説：
Explanation

質問 # 145
Which three statements are true about a self join?

• A. The query must use two different aliases for the table.
• B. The on clause must be used.
• C. It must be an equijoin.
• D. It must be an inner join.
• E. It can be an outer join.
• F. The on clause can be used.

正解：A、E、F

解説：
C). True, when performing a self join, aliases are used to differentiate the same table in different contexts within the same query. This helps in comparing different rows within the same table as if they were in two separate tables.D. True, the ON clause can be used in self joins to specify the conditions on which the join is based. This is a standard method for specifying join conditions in SQL.F. True, a self join can be an outer join, allowing for the inclusion of rows that do not have a matching row in the same table according to the join condition. This enhances the flexibility of data analysis and reporting.
References:
* Oracle documentation on joins: Oracle Database SQL Language Reference
* Details on self joins and outer joins: Oracle Joins Tutorial

質問 # 146
Examine this partial command:

Which two clauses are required for this command to execute successfully?

• A. the DEFAULT DIRECTORY clause
• B. the ACCESS PARAMETERS clause
• C. the LOCATION clause
• D. the REJECT LIMIT clause
• E. the access driver TYPE clause

正解：C、E

解説：
In Oracle Database 12c, when creating an external table using the CREATE TABLE ... ORGANIZATION EXTERNAL statement, there are certain clauses that are mandatory for the command to execute successfully.
Statement C, the LOCATION clause, is required. The LOCATION clause specifies one or more external data source locations, typically a file or a directory that the external table will read from. Without this, Oracle would not know where to find the external data for the table.
Statement E, the access driver TYPE clause, is also required. The access driver tells Oracle how to interpret the format of the data files. The most common access driver is ORACLE_LOADER, which allows the reading of data files in a format compatible with the SQL*Loader utility. Another option could be ORACLE_DATAPUMP, which reads data in a Data Pump format.
Statements A, B, and D are not strictly required for the command to execute successfully, although they are often used in practice:
A, the DEFAULT DIRECTORY clause, is not mandatory if you have specified the full path in the LOCATION clause, but it is a best practice to use it to avoid hard-coding directory paths in the LOCATION clause.
B, the REJECT LIMIT clause, is optional and specifies the maximum number of errors to allow during the loading of data. If not specified, the default is 0, meaning the load will fail upon the first error encountered.
D, the ACCESS PARAMETERS clause, is where one would specify parameters for the access driver, such as field delimiters and record formatting details. While it is common to include this clause to define the format of the external data, it is not absolutely required for the command to execute; defaults would be used if this clause is omitted.
For reference, you can find more details in the Oracle Database SQL Language Reference for version 12c, under the CREATE TABLE statement for external tables.

質問 # 147
You execute this command:
TRUNCATE TABLE dept;
Which two are true?

• A. A ROLLBACK statement can be used to retrieve the deleted data.
• B. It retains the integrity constraints defined on the table.
• C. It retains the indexes defined on the table.
• D. A FLASHBACK TABLE statement can be used to retrieve the deleted data.
• E. It always retains the space used by the removed rows.
• F. It drops any triggers defined on the table.

正解：B、C

解説：
When using the TRUNCATE TABLE command in Oracle, several aspects of the table's structure and associated database objects are impacted. Here's an explanation of each option:
* A: Incorrect. TRUNCATE TABLE does not drop triggers associated with the table; they remain defined.
* B: Correct. Indexes on the table are retained and not dropped when you truncate a table. However, if the index is a domain index, it may be dropped depending on its type.
* C: Correct. Integrity constraints such as primary keys, foreign keys, etc., are retained unless they are on a disabled state where truncation can lead to constraint being dropped.
* D: Incorrect. A TRUNCATE TABLE operation cannot be rolled back. It is a DDL (Data Definition Language) operation and commits automatically.
* E: Incorrect. The TRUNCATE TABLE operation deallocates the space used by the data unless the REUSE STORAGE clause is specified.
* F: Incorrect. TRUNCATE TABLE operation removes all the rows in a table and does not log individual row deletions, thus FLASHBACK TABLE cannot be used to retrieve the data.

質問 # 148
Which statement executes successfully?

• A. SELECT TO_NUMBER(INTERVAL'800' SECOND, 'HH24:MM') FROM DUAL;
• B. SELECT TO_NUWBER(TO_DATE(INTERVAL '800' SECOND)) FROM DUAL;
• C. SELECT TO_CHAR(INTERVAL '800' SECOND, 'HH24:MM') FROM DUAL;
• D. SELECT TO_DATE(TO_NUMBER(INTERVATL '800' SECOND)) FROM DUAL;
• E. SELECT TO_DATE(INTERVAL '800' SECOND,'HH24:MM') FROM DUAL;

正解：C

質問 # 149
Which two statements are true about a full outer join?

• A. It returns matched and unmatched rows from both tables being joined.
• B. The Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause.
• C. It returns only unmatched rows from both tables being joined.
• D. It includes rows that are returned by an inner join.
• E. It includes rows that are returned by a Cartesian product.

正解：A、D

解説：
In Oracle Database 12c, regarding a full outer join:
* A. It includes rows that are returned by an inner join. This is true. A full outer join includes all rows from both joined tables, matching wherever possible. When there's a match in both tables (as with an inner join), these rows are included.
* D. It returns matched and unmatched rows from both tables being joined. This is correct and the essence of a full outer join. It combines the results of both left and right outer joins, showing all rows from both tables with matching rows from the opposite table where available.
Options B, C, and E are incorrect:
* B is incorrect because the Oracle join operator (+) is used for syntax in older versions and cannot implement a full outer join by using (+) on both sides. Proper syntax uses the FULL OUTER JOIN keyword.
* C is incorrect as a Cartesian product is the result of a cross join, not a full outer join.
* E is incorrect because it only describes the scenario of a full anti-join, not a full outer join.

質問 # 150
Which two queries execute successfully?

• A. SELECT NULLIF (100, 'A') FROM DUAL;
• B. SELECT NULLIF(NULL, 100) FROM DUAL;
• C. SELECT COALESCE(100, NULL, 200) FROM DUAL;
• D. SELKCT COALESCE (100, 'A') FROM DUAL;
• E. SELECT NULLIF (100) FROM DUAL;

正解：C、E

質問 # 151
Examine the description of the BOOKS_TRANSACTIONS table:

Examine this partial SQL statement:
SELECT * FROM books_transactions
Which two WHERE conditions give the same result?

• A. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'));
• B. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102');
• C. WHERE (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN ('A101','A102');
• D. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102');
• E. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102');

正解：B、C

質問 # 152
Examine the data in the COLORS table:

Examine the data in the BRICKS table:

Which two queries return all the rows from COLORS?

• A.
• B.
• C.
• D.
• E.

正解：A、B

質問 # 153
View the Exhibit and examine the structure of the BOOKStable.

The BOOKS table contains details of 100 books.
Examine the commands executed and their outcome:

Which statement is true?

• A. The first rollback restores the 101 rows that were deleted and the second rollback causes the row was inserted to be deleted and commits the changes.
• B. The first rollback restores the 100 rows that were deleted and the second rollback commits only the changes.
• C. Both ROLLBACKcommands restore the 101 rows that were deleted.
• D. Both ROLLBACKcommands restore the 100 rows that were deleted.

正解：A

質問 # 154
Which two statements are true about single row functions?

• A. MOD: returns the quotient of a division operation
• B. CONCAT: can be used to combine any number of values
• C. CEIL: can be used for positive and negative numbers
• D. TRUNC: can be used with NUMBER and DATE values
• E. FLOOR: returns the smallest integer greater than or equal to a specified number

正解：C、D

解説：
Single-row functions operate on single rows and return one result per row. Let's look at each option in the context of Oracle 12c SQL:
A . CONCAT: This function can only combine two values at a time. If you need to concatenate more than two values, you have to nest CONCAT functions or use the || operator.
B . MOD: The MOD function returns the remainder of a division operation, not the quotient.
C . CEIL: This function returns the smallest integer that is greater than or equal to a specified number. It works with both positive and negative numbers.
D . FLOOR: It returns the largest integer that is less than or equal to the specified number, not greater than or equal to.
E . TRUNC: This function can indeed be used with both NUMBER and DATE values to truncate them to a specified number of decimal places or to a particular component of a date.
Reference:
Oracle Database SQL Language Reference 12c Release 1 (12.1), Functions
Oracle Database SQL Language Reference 12c Release 1 (12.1), CONCAT Function Oracle Database SQL Language Reference 12c Release 1 (12.1), MOD Function Oracle Database SQL Language Reference 12c Release 1 (12.1), CEIL and FLOOR Functions Oracle Database SQL Language Reference 12c Release 1 (12.1), TRUNC Function

質問 # 155
Evaluate the following query:

What would be the outcome of the above query?

• A. It executes successfully and displays the literal " {'s start date was \> " for each row in the output.
• B. It executes successfully and introduces an 's at the end of each promo_name in the output.
• C. It produces an error because flower braces have been used.
• D. It produces an error because the data types are not matching.

正解：B

質問 # 156
Which two are true?

• A. CONCAT joins two character strings together.
• B. CONCAT joins two or more character strings together.
• C. FLOOR returns the largest integer less than or equal to a specified number.
• D. FLOOR returns the largest positive integer less than or equal to a specified number.
• E. INSTR finds the offset within a string of a single character only.
• F. INSTR finds the offset within a character string, starting from position 0.

正解：B、C

解説：
The CONCAT function and FLOOR function in Oracle SQL have specific behaviors:
A). CONCAT function joins two or more character strings into one string, making this statement true.
B). FLOOR function returns the largest integer that is less than or equal to the specified number, making this statement true.
C). While CONCAT can join two strings together, this statement is incomplete as it can join more than two.
D). INSTR can find the offset of a substring within a string, not just a single character.
E). INSTR starts searching the string from position 1 in Oracle SQL, not position 0.
F). FLOOR does return the largest integer less than or equal to the specified number, but it can be any integer, not just positive ones.
References:
* Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Single-Row Functions"

質問 # 157
Which two are true?

• A. FLOOR returns the largest integer less than or equal to a specified number.
• B. FLOOR returns the largest positive integer less than or equal to a specified number.
• C. INSTR finds the offset within a string of a single character only.
• D. CONCAT joins two or more character strings together.
• E. INSTR finds the offset within a character string, starting from position 0.
• F. CONCAT joins two character strings together.

正解：A、F

質問 # 158
......

ベストOracle 1z1-071学習ガイドと問題集は2025年に更新されました：https://www.passtest.jp/Oracle/1z1-071-shiken.html

1z1-071認定お試しPDF最新1z1-071問題集：https://drive.google.com/open?id=1aBIBMDxKys-9aZwfOh8Kju4yQdqwKPD3