Oracle 1z0-071日本語問題集で必ず試験合格させる [Q168-Q184]

Oracle 1z0-071日本語問題集で必ず試験合格させる

1z0-071日本語試験問題（更新されたのは2025年）100%リアル問題解答

質問 # 168
PRODUCTSテーブルの次の説明を調べます。

このテーブルには行が存在し、すべての列にデータがあります。 PRODUCTSテーブルを読み取り専用モードにします。 PRODUCTSで正常に実行される3つのコマンドはどれですか？

A. 商品のCREATE INDEX price_idx（価格）;
B. ALTERTABLE製品SETUNUSED（expiry_date）;
C. DROPTABLE製品
D. ALTERTAELE製品DROPCOLUMN expiry_date;
E. ALTERTABLE製品は未使用の列を削除します
F. TRUNCATETABLE製品;

正解：A、C、E

質問 # 169
CUSTOMERSテーブルの説明を調べます。

暗黙の変換を行う3つのステートメントはどれですか？

A. SELECT FROM Customers WHERE insert date = '01 -JAN-19 ';
B. SELECT * FROM Customers WHERE customer_ id = 0001;
C. SELECT。 FROMの顧客WHEREinsert_ date = DATE * 2019-01-01 ';
D. SELECT。 FRONのお客様WETO DATE（Insert _ date）= DATE '2019-01-01';
E. SELECT * FROM Customers WHERE TO_ CHAR（customer_ id）= '0001';
F. SELECT * FROM Customers WHERE customer id = '0001';

正解：A、D、F

質問 # 170
ORDERSテーブルの説明を調べます。

どの3つのステートメントが正常に実行されますか？

A. SELECT * FROM注文ORDERBY order_ id
連合
SELECT * FROM請求書;
B. SELECT order_ id、order_ data FROMorders
UNION ALL
SELECT invoice_ id、invoice_ data FROM invoices ORDER BY order_ id;
C. （SELECT * FROM注文
UNION ALL
SELECT * FROM invoices）ORDER BY order _id;
D. SELECT order_ id、invoice_ data order_ date FROMorders
マイナス
SELECT invoice_ id、invoice_ data FROM invoices ORDER BY invoice_ id;
E. SELECE order _id、order _ dateFRON注文
LNTERSECT
SELECT invoice_ id、invoice_ id、order_ date FROMorders
F. SELECT * FROM注文ORDERBY order_ id
インターシート
SELECT * FROM請求書ORDERBY invoice_ id;
G. SELECT * FROM注文
マイナス
SELECT * FROM INVOICES ORDER BY 1

正解：A、B、C

解説：
In Oracle SQL, set operations like UNION, UNION ALL, INTERSECT, and MINUS can be used to combine results from different queries:
* Option A:
* Combining results using UNION ALL followed by ORDER BY will execute successfully because UNION ALL allows duplicate rows and ORDER BY can be used to sort the combined result set.
* Option E:
* Similar to option A, UNION ALL combines all rows from the two selects and allows ordering of the results.
* Option G:
* UNION combines the results from two queries and removes duplicates, and ORDER BY can be used to sort the final result set.
Options B, C, D, and F are incorrect because:
* Option B: You cannot intersect different columns (ORDER_ID with INVOICE_ID).
* Option C: Incorrect column names and syntax with ORDER BY.
* Option D: ORDER BY cannot be used before a set operator like INTERSECT.
* Option F: ORDER BY cannot be used directly after a MINUS operator without wrapping the MINUS operation in a subquery.

質問 # 171
PROD_ID列は、SALESテーブルの外部キーです。これはPRODUCTSテーブルを参照します。
同様に、CUST_ID列とTIME_ID列も、それぞれCUSTOMERSテーブルとTIMESテーブルを参照するSALESテーブルの外部キーです。
次のCREATETABLEコマンドを評価します。
CREATE TABLE new_sales（prod_id、I cust_id、order_date DEFAULT SYSDATE）
AS SELECT I prod_id、cust_id、time_id FROMsales。
上記のコマンドに関して正しい説明はどれですか？

A. CREATE TABLEコマンドの列名とSELECT句が一致しないため、NEW_SALESテーブルは作成されませんでした。
B. NEW_SALESテーブルが作成され、指定された列に定義されたすべてのNOTNULL制約が新しいテーブルに渡されます。
C. NEW_SALESテーブルが作成され、指定された列に定義されたすべてのFOREIGNKEY制約が新しいテーブルに渡されます。
D. 列定義でDEFAULT値を指定できないため、NEW_SALESテーブルは作成されませんでした。

正解：A

解説：
The statement true regarding the CREATE TABLE command:
* C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match: The SQL command tries to create a table with columns prod_id, cust_id, and order_date, but the SELECT statement specifies columns prod_id
, cust_id, and time_id. The mismatch in column names and the number of columns specified will prevent the table from being created.
Incorrect options:
* A: It is possible to specify a DEFAULT value in the column definition when creating a table with the CREATE TABLE AS SELECT syntax.
* B: Not all NOT NULL constraints (or any other constraints, for that matter) are automatically passed to the new table unless explicitly stated in the CREATE TABLE statement.
* D: FOREIGN KEY constraints are not automatically included when creating a table using the CREATE TABLE AS SELECT syntax; they would need to be added explicitly afterwards.

質問 # 172
EMPLOYEESテーブルの説明を調べます。

マネージャーが別の部門で働いている従業員の行を返す2つのクエリはどれですか？

A. SELECTemp。*
従業員従業員から
LEFTJOIN従業員マネージャー
ON emp.manager_ id = mgr.employee_ id
ANDemp。部門ID <>マネージャー。 department_ id;
B. SELECTemp。 *
従業員従業員から
従業員マネージャーに参加する
ONemp。 manager_ id = mgr。従業員ID
ANDemp。 department_ id <> mgr.department_ id;
C. SELECTemp。*
従業員従業員から
存在しない場所（
SELECT NULL
FROM従業員マネージャー
WHERE emp.manager id = mgr.employee_ id
AND emp.department_id <> mgr.department_id
）;
D. SELECTemp。 *
従業員従業員から
RIGHTJOIN従業員マネージャー
ON emp.manager_ id = mgr。従業員ID
ANDemp。部門ID <> mgr.department_ id
WHEREemp。 employee_idはNULLではありません。
E. SELECTemp。 *
従業員従業員から
WHERE manager_ id NOT IN（
SELECT mgr.employee_ id
FROM従業員マネージャー
WHEREemp。 department_ id <> mgr.department_ id
）;

正解：B、C

解説：
To find employees whose manager works in a different department, you can use a subquery or a join that compares the DEPARTMENT_ID of the employee with the DEPARTMENT_ID of their manager.
A . This query is incorrect because the NOT IN subquery incorrectly attempts to compare EMPLOYEE_ID with MANAGER_ID, and the correlation condition inside the subquery is incorrect.
B . This query is correct. The NOT EXISTS clause correctly identifies employees whose MANAGER_ID matches the EMPLOYEE_ID of another employee (mgr) and where the DEPARTMENT_ID differs from that manager's DEPARTMENT_ID.
C . This query is incorrect because the LEFT JOIN will return all employees, and there is no WHERE clause to filter out those employees whose managers are in the same department.
D . This query is incorrect. The RIGHT JOIN does not ensure that the resulting rows are for employees whose manager works in a different department. It also returns all managers, which is not the requirement.
E . This query is correct. The JOIN ensures that the returned rows are for employees (emp) whose MANAGER_ID matches the EMPLOYEE_ID of managers (mgr), and it correctly filters to include only those employees whose DEPARTMENT_ID is different from their manager's DEPARTMENT_ID.
Reference:
Oracle Documentation on Joins: https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52358

質問 # 173
このSQLステートメントを調べてください。
従業員から削除e
存在する場所
（「ダミー」を選択
FROM emp_history
WHERE employee_id = e.employee_id）
どちらが正しいですか？

A. サブクエリは相関サブクエリではありません。
B. サブクエリはDELETEステートメントが実行される前に実行されます。
C. EMPLOYEEテーブルの既存の行はすべて削除されます。
D. サブクエリはEMPLOYEESテーブルのすべての行に対して実行されます。
E. サブクエリが複数の行を選択した場合でも、DELETEステートメントは正常に実行されます。

正解：D、E

解説：
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.
Reference:
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

質問 # 174
サブクエリが必要な2つのタスクはどれですか？

A. PROD_LIST_PRICEが1000未満のサプライヤーを表示します。
B. PROD_MIN_PRICEがすべての製品の平均PROD_LIST_PRICEを超えており、ステータスが注文可能である製品を表示します。
C. 製品ステータスが廃止された、サプライヤ102によって供給された製品の総数を表示します。
D. 各製品ステータスの最小PROD_LIST_PRICEを表示します。
E. PROD_LIST_PRICEが平均PROD_LIST_PRICEよりも多い製品の数を表示します。

正解：B、E

質問 # 175
BOOKS_TRANSACTIONSテーブルの説明を調べます。

この部分的なSQLステートメントを調べてください。
SELECT * FROM books_transactions
どの2つのWHERE条件が同じ結果をもたらしますか？

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'）;

正解：D、E

解説：
When writing SQL statements with multiple conditions in the WHERE clause, it's important to understand how logical operators like AND and OR work. These operators are used to filter records based on more than one condition:
* The AND operator displays a record if all the conditions separated by AND are TRUE.
* The OR operator displays a record if any of the conditions separated by OR is TRUE.
The precedence of these operators is also important: AND operations are always evaluated before OR operations unless parentheses are used to explicitly define the order of operations. Therefore, conditions enclosed in parentheses are evaluated first as per the standard SQL operator precedence.
Given this understanding, let's evaluate the options:
* Option A uses parentheses to group the borrowed_date and transaction_type conditions together, ensuring these are evaluated first before the OR operator. This means the query will return records that meet both of these conditions or records with member_id 'A101' or 'A102', regardless of other conditions.
* Option D does not use parentheses around the borrowed_date and transaction_type conditions but does use them to group the member_id conditions. Since the AND operator has higher precedence than the OR, this query will first evaluate the borrowed_date and transaction_type conditions, then evaluate the grouped member_id conditions. The outcome is records that have a borrowed_date of SYSDATE, a transaction_type of 'RM', and a member_id of either 'A101' or 'A102'.
The results of Options A and D are effectively the same, even though the use of parentheses is different. This is because in both cases, the evaluation of the borrowed_date and transaction_type conditions will be performed first due to their grouping by parentheses in Option A and the precedence of AND over OR in Option D. Therefore, they will both return all records where borrowed_date equals SYSDATE and transaction_type equals 'RM', plus any records where member_id is either 'A101' or 'A102'.
For further details on SQL operator precedence and logical operators, you can refer to Oracle Database SQL Language Reference 12c documentation, specifically the sections on conditional expressions.

質問 # 176
PRODUCT_DETAILSテーブルの説明を調べます。

正しい2つのステートメントはどれですか？

A. 日付が割り当てられていない場合、EXPIRY_DATEにはデフォルトでSYSDATEが含まれます。
B. PRODUCT_IDにPRIMARYKEY制約を割り当てることができます。
C. PRODUCT_ PRICEは、値が格納されていない場合でも算術式で使用できます。
D. EXPIRY_DATEは算術式では使用できません。
E. PRODUCT_ PRICEには、値が割り当てられていない場合、デフォルトで値ゼロが含まれます。
F. PRODUCT_NAMEに重複する値を含めることはできません。

正解：B、D

解説：
In reference to the attributes of the PRODUCT_DETAILS table:
A). This statement is false. If PRODUCT_PRICE has no value (i.e., it is NULL), it cannot be used directly in arithmetic expressions because any operation with NULL results in NULL.
B). This statement is true. PRODUCT_ID has the NOT NULL constraint and is of type NUMBER, making it eligible to be a PRIMARY KEY. Primary keys require all values to be unique and not null.
C). This statement is true. EXPIRY_DATE is of type DATE, and it cannot be used in arithmetic expressions directly without a date function that operates on dates.
D). This statement is false. By default, columns of type DATE do not have a default value unless explicitly assigned using the DEFAULT keyword in the column definition.
E). This statement is false. By default, numeric columns do not have a default value unless specified with the DEFAULT keyword.
F). This statement is false. The statement about PRODUCT_NAME not containing duplicate values would be true if it were a UNIQUE or PRIMARY KEY, but there is no such constraint indicated in the provided table description.

質問 # 177
ORDER BY句について正しい2つのステートメントはどれですか？

A. NULLSはソート操作に含まれていません。
B. SELECTリストで指定された列のみがORDERBY原因で使用できます。
C. 文字ソートでは、値は大文字と小文字が区別されます。
D. 列エイリアスはORDERBY原因で使用できます。
E. 小数点以下の桁数の場合は降順で表示されます。

正解：C、D

解説：
The ORDER BY clause in Oracle SQL is used to sort the result set of a query by one or more columns, and it comes with its own set of rules and behaviors:
* D. Column aliases can be used In the ORDER BY clause: True, column aliases that are specified in the SELECT clause can be used in the ORDER BY clause to refer to the columns to sort the results by.
* E. In a character sort, the values are case-sensitive: Oracle sorts results in case-sensitive order by default when using the ORDER BY clause with character columns, unless otherwise specified by the NLS_SORT parameter.
References:
* Oracle Database SQL Language Reference 12c, which provides details on sorting query results using ORDER BY.

質問 # 178
テーブル、ビュー、およびシーケンスに対するオブジェクト特権の付与について正しい2つはどれですか。

A. ALTERはテーブルとシーケンスにのみ付与できます。
B. DELETEは、テーブル、ビュー、およびシーケンスに付与できます。
C. INSERTは、テーブルとシーケンスにのみ付与できます。
D. REFERENCESはテーブルでのみ付与できます。
E. SELECTは、テーブル、ビュー、およびシーケンスに付与できます。

正解：D、E

解説：
Object privileges applicable to tables, views, and sequences in Oracle are:
B . REFERENCES can be granted only on tables. It allows the grantee to create a foreign key that refers to the table.
D . SELECT can be granted on tables and views. This allows the grantee to perform a SELECT on the table or view.
A, C, and E are incorrect. Specifically:
A is incorrect because DELETE cannot be granted on sequences; it is a privilege applicable to tables and views.
C is incorrect because INSERT cannot be granted on sequences; INSERT is applicable only to tables and views.
E is incorrect because ALTER is a privilege applicable to tables and indexes, not sequences.
Reference:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "GRANT"

質問 # 179
説明製品テーブルを調べます。

new_projects テーブルの説明を調べてください。

正常に実行される 2 つのクエリはどれですか?

正解：A、D

解説：
To determine which queries will execute successfully, we need to consider the compatibility of the column definitions and the structure of the SELECT statements:
* Option A uses the MINUS set operator, which subtracts rows returned by the second SELECT statement from the rows returned by the first. For MINUS to work, the number and the order of columns and their data types must be the same in both queries. This query will not execute successfully because the second SELECT statement does not include all columns from the first SELECT statement, and the data types and sizes of PROD_ID do not match (CHAR(2) vs CHAR(4)).
* Option B uses the UNION ALL set operator, which appends the results of the second SELECT statement to the results of the first. Unlike UNION, UNION ALL does not eliminate duplicate rows.
This query will execute successfully because UNION ALL does not require the same data types or sizes, and the result will contain all columns from the first SELECT statement filled with NULL for non-matching columns from the second SELECT statement.
* Option C uses the UNION set operator, which requires the same number of columns and compatible data types. This query will not execute successfully because PROD_NAME has different data types (CHAR(4) vs VARCHAR2(10)), and the result of a UNION must have the same number of columns with compatible data types in the two SELECT statements.
* Option D uses the UNION set operator as well, but unlike Option C, it does not require a specific data type match because both SELECT statements include all columns and UNION is used (which will automatically handle type conversion where necessary). This query will execute successfully.
* Option E uses the INTERSECT set operator, which requires the same number and order of columns and their data types to be identical or compatible. This query will not execute successfully because the data types and sizes of PROD_ID do not match (CHAR(2) vs CHAR(4)).
References:
* Oracle Documentation on Set Operators: SQL Language Reference - Set Operators
* Oracle Documentation on Data Type Precedence: SQL Language Reference - Data Type Precedence In conclusion, only Option B and Option D will execute successfully because they adhere to the rules of the UNION ALL and UNION operators respectively, regarding column count and data type compatibility.

質問 # 180
テーブル結合のUSING句とON句に関して正しいものはどれですか。