[2025年06月23日]1z0-071試験問題集PDF正確率保証と更新された問題
合格させる1z0-071試験にはリアルテストエンジンPDFには325問題あります
質問 # 21
View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.
Which DELETE statement would execute successfully?
- A. DELETE orders o, order_items IWHERE o.order_id = i.order_id;
- B. DELETEFROM ordersWHERE (SELECT order_idFROM order_items);
- C. DELETE order_idFROM ordersWHERE order_total < 1000;
- D. DELETE ordersWHERE order_total < 1000;
正解：B
質問 # 22
Which three statements are correct regarding indexes? (Choose three.)
- A. Indexes should be created on columns that are frequently referenced as part of any expression.
- B. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically attempts to create a unique index.
- C. For each DML operation performed on a table, the corresponding indexes are automatically updated if required.
- D. When a table is dropped, corresponding indexes are automatically dropped.
正解：B、C、D
解説：
http://viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/
質問 # 23
Which three statements are true about inner and outer joins? (Choose three.)
- A. A left or right outer join returns only unmatched rows
- B. Outer joins can be used when there are multiple join conditions on two tables
- C. A full outer join must use Oracle syntax
- D. An inner join returns matched rows
- E. Outer joins can only be used between two per query
- F. A full outer join returns matched and unmatched rows
正解：A、D、F
質問 # 24
View the Exhibit and examine the structure of the ORDER_ITEMS table.
You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.
Which query would produce the desired result?
- A. SELECT order_id
FROM order_items
WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)
FROM order_items
GROUP BY order_id) - B. SELECT order_id
FROM order_items
GROUP BY order_id
HAVING SUM(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity)) FROM order_items GROUP BY order_id); - C. SELECT order_id
FROM order_items
WHERE(unit_price*quantity) = MAX(unit_price*quantity)
GROUP BY order_id); - D. SELECT order_id
FROM order_items
WHERE(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity)
FROM order_items) GROUP BY order_id);
正解：B
質問 # 25
Which three statements are true about sequences in a single instance Oracle database? (Choose three.)
- A. A sequence can issue duplicate values
- B. A sequence's unallocated cached value are lost if the instance shuts down
- C. Two or more tables cannot have keys generated from the same sequence
- D. A sequence can only be dropped by a DBA
- E. Sequences can always have gaps
- F. A sequence number that was allocated can be rolled back if a transaction fails
正解：B、E、F
解説：
Explanation/Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6015.htm
質問 # 26
Examine the business rule:
Each student can take up multiple projects and each project can have multiple students.
You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true in this scenario?
- A. STUDENT_IDmust be the primary key in the STUDENTSentity and foreign key in the PROJECTSentity.
- B. PROJECT_IDmust be the primary key in the PROJECTSentity and foreign key in the STUDENTSentity.
- C. The ERD must have a M:M relationship between the STUDENTSand PROJECTSentities that must be resolved into 1:M relationships.
- D. The ERD must have a 1:M relationship between the STUDENTSand PROJECTSentities.
- E. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.
正解：C、E
解説：
Explanation/Reference:
References:
http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61sql-512018.html
質問 # 27
You need to calculate the number of days from 1st January 2019 until today.
Dates are stored in the default format of DD-MON-RR.
Which two queries give the required output?
- A. SELECT ROUND (SYSDATE- '01-JAN-2019') FROM DUAL:
- B. SELECT SYSDATE-TO_DATE ('01-JANUARY-2019') FROM DUAL;
- C. SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY')-'01-JAN-2019' FROM DUAL;
- D. SELECT ROUND (SYSDATE-TO_DATE ('01/JANUARY/2019')) FROM DUAL;
- E. SELECT TO_DATE (SYSDATE, 'DD/MONTH/YYYY')-'01/JANUARY/2019' FROM DUAL;
正解：B、D
解説：
To calculate the number of days between two dates in Oracle SQL, you can simply subtract the earlier date from the later date, and Oracle will return the number of days between them.
Option A uses SYSDATE to get the current date and TO_DATE to convert the string '01-JANUARY-2019' to a date using the default date format, which matches 'DD-MON-RR'. The subtraction here will correctly give the number of days between these two dates.
Option C is similar to A but includes the ROUND function, which is used to round the number to the nearest whole number. Since the difference between two dates in Oracle SQL is a decimal value representing the number of days and fractional days, rounding will give us the whole number of days, which is what we want.
Options B, D, and E are incorrect because they either use an invalid date format (the default format is 'DD-MON-RR', not 'DD/MONTH/YYYY' or 'DD-MON-YYYY'), perform invalid operations (such as subtracting a string from a date), or misuse SYSDATE.
質問 # 28
Which two statements are true about substitution variables? (Choose two.)
- A. A substitution variable used to prompt for a column name must be enclosed in double quotation marks
- B. A substitution variable used to prompt for a column name must be enclosed in a single quotation marks
- C. A substitution variable can be used only in a SELECT statement
- D. A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session
- E. A substitution variable prefixed with & always prompts only once for a value in a session
- F. A substitution variable can be used with any clause in a SELECT statement
正解：B、E
解説：
Explanation/Reference: https://blogs.oracle.com/opal/sqlplus-101-substitution-variables
質問 # 29
Which two tasks can be performed by using Oracle SQL statements? (Choose two.)
- A. querying data from tables in different databases
- B. starting up a database instance
- C. changing the password for an existing database user
- D. connecting to a database instance
- E. executing operating system (OS) commands in a session
正解：A、C
解説：
References:
http://www.techonthenet.com/oracle/password.php
https://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm
質問 # 30
Which two statements are true about a full outer join?
- A. It returns only unmatched rows from both tables being joined.
- B. It returns matched and unmatched rows from both tables being joined.
- C. The Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause.
- D. It includes rows that are returned by an inner join.
- E. It includes rows that are returned by a Cartesian product.
正解：B、D
質問 # 31
Which two statements are true about Entity Relationships?
- A. A Relationship can be mandatory for both entities
- B. A one-to-one relationship is always a self-referencing relationship
- C. A many-to-many relationship can be implemented only by using foreign keys
- D. A one-to-many relationship in one direction is a one-to-one relationship in the other direction
- E. A table name can be specified just once when selecting data from a table having a selfreferencing relationship
正解：A
解説：
A relationship in an Entity-Relationship (ER) model can indeed be mandatory for both entities, meaning that an instance of one entity must relate to one and only one instance of another entity, and vice versa. This is commonly seen in a one-to-one relationship where both sides are mandatory.
Option B is incorrect; a one-to-one relationship does not have to be self-referencing. Self-referencing (or recursive) relationships occur when an entity has a relationship with itself.
Option C is incorrect; a many-to-many relationship typically requires a join table or associative entity with foreign keys that reference the primary keys of the two entities it connects.
Option D is incorrect; in the case of a self-referencing relationship, you may need to use aliases to specify the table more than once to differentiate between the self-referenced columns.
Option E is incorrect because a one-to-many relationship in one direction does not equate to a one-to-one relationship in the opposite direction.
References:
* Entity-Relationship Model Concepts: ER Model
質問 # 32
You have the privileges to create any type of synonym.
Which statement will create a synonym called EMPfor the HCM.EMPLOYEE_RECORDS table that is accessible to all users?
- A. CREATE SYNONIM emp FOR hcm.employee_records;
- B. CREATE SYNONIM PUBLIC.emp FOR hcm.employee_records;
- C. CREATE GLOBAL SYNONIM emp FOR hcm.employee_records;
- D. CREATE SYNONIM SYS.emp FOR hcm.employee_records;
- E. CREATE PUBLIC SYNONIM emp FOR hcm.employee_records;
正解：E
解説：
CREATE PUBLIC SYNONYM emp_table
Reference: https://docs.oracle.com/database/121/SQLRF/statements_7001.htm#SQLRF01401
質問 # 33
Which two statements are true about conditional INSERT ALL?
- A. Each WHEN condition is tested for each row returned by the subquery.
- B. It cannot have an ELSE clause.
- C. Each row returned by the subquery can be inserted into only a single target table.
- D. A single WHEN condition can be used for multiple INTO clauses.
- E. The total number of rows inserted is always equal to the number of rows returned by the subquery.
正解：B、D
質問 # 34
Choose two
Examine the description of the PRODUCT DETALS table:
- A. PRODUCT_NAME cannot contain duplicate values.
- B. PRODUCT_ID can be assigned the PEIMARY KEY constraint.
- C. PRODUCT_PRICE contains the value zero by default if no value is assigned to it.
- D. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it
- E. EXPIRY_DATE cannot be used in arithmetic expressions.
- F. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it
正解：B、D
解説：
A . PRODUCT_ID can be assigned the PRIMARY KEY constraint.
In Oracle Database 12c, a PRIMARY KEY constraint is a combination of a NOT NULL constraint and a unique constraint. It ensures that the data contained in a column, or a group of columns, is unique among all the rows in the table and not null. Given the PRODUCT_ID is marked as NOT NULL, it is a candidate for being a primary key because we can assume that it is intended to uniquely identify each product in the table.
Reference:
B . EXPIRY_DATE cannot be used in arithmetic expressions. (Incorrect)
This statement is not necessarily true. Dates in Oracle can be used in arithmetic expressions, typically to add or subtract days from a date.
C . EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it. (Incorrect) Unless explicitly specified, a date column does not default to SYSDATE. A default value must be set using the DEFAULT clause during the table creation or altered later.
D . PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.
This is correct. In Oracle, if a numeric column like PRODUCT_PRICE has a NULL value (meaning no value stored in it), it can still be used in an arithmetic expression. In such expressions, NULL is typically treated as a zero, but the result of any arithmetic with NULL is also NULL.
E . PRODUCT_PRICE contains the value zero by default if no value is assigned to it. (Incorrect) Unless a default value is explicitly specified during the table creation or altered later, a numeric column like PRODUCT_PRICE does not automatically have a default value of zero.
F . PRODUCT_NAME cannot contain duplicate values. (Incorrect)
There is no constraint indicated that would prevent PRODUCT_NAME from containing duplicate values. Without a UNIQUE or PRIMARY KEY constraint, a column can contain duplicates.
The correct answers are A and D. PRODUCT_ID can be the primary key because it's specified as NOT NULL, thus it can uniquely identify each row in the table. PRODUCT_PRICE can be used in an arithmetic expression with the understanding that if it's NULL, the result of the expression would be NULL as well.
質問 # 35
You want to display the date for the first Monday of the next month and issue the following command:
What is the outcome?
- A. In generates an error because rrrrshould be replaced by rrin the format string.
- B. It executes successfully but does not return the correct result.
- C. In generates an error because fmand double quotation marks should not be used in the format string.
- D. It executes successfully and returns the correct result.
- E. In generates an error because TO_CHARshould be replaced with TO_DATE.
正解：D
質問 # 36
Examine this partial command:
CREATE TABLE cust(
cust_id NUMBER(2),
credit_limit NUMBER(10)
ORGANIZATION EXTERNAL
Which two clauses are required for this command to execute successfully?
- A. the access driver TYPE clause
- B. the ACCESS PARAMETERS clause
- C. the LOCATION clause
- D. the REJECT LIMIT clause
- E. the DEFAULT DIRECTORY clause
正解：C、E
質問 # 37
......
Oracle 1Z0-071試験では、データ操作、データ検索、データ制御、トランザクションコントロールなど、SQLプログラミング言語の基本概念をカバーしています。候補者は、基本的なSQL構文に精通しており、SQL関数、条項、および演算子に関する実用的な知識を持っていることが期待されています。この試験では、テーブル、インデックス、ビュー、シーケンスなどのデータベースオブジェクトに関する候補者の理解も評価しています。
最新をゲットせよ！1z0-071認定練習テスト問題試験問題集：https://www.passtest.jp/Oracle/1z0-071-shiken.html
リアル1z0-071試験問題集解答で有効な1z0-071問題集PDF：https://drive.google.com/open?id=1KGCx4lWHy2-Vz0Xk5_mu9Lu-fGOKlvfu