[2026年09月05日] 最新の1z0-071試験の的確なOracle Database SQLのPDF問題 [Q104-Q126]

[2026年09月05日] 最新の1z0-071試験の的確なOracle Database SQLのPDF問題

1z0-071試験問題を練習するならJPNTest顕著なOracle Database SQL試験練習問題集

Oracle 1z0-071認定試験は、データベース管理者、開発者、またはデータアナリストとして働きたい個人に最適です。この試験は、SQLの基礎、データモデリング、データベース設計、およびデータベースセキュリティなど、さまざまなトピックをカバーしています。SQLコンセプトに関する堅固な理解力が必要な難しい試験であり、候補者はSQLプログラミングにおける実践的なスキルを証明することが求められます。試験に合格することで、個人は就職市場で競争優位性を獲得することができ、また、より高い給与やキャリアアップの機会を得ることができます。

Oracle 1z0-071 認定試験の出題範囲：

トピック	出題範囲
トピック 1	sing Single-Row Functions to Customize Output: This particular domain of the exam covers topics such as manipulating strings with character functions in SQL SELECT and WHERE clauses, performing arithmetic with date data, and manipulating numbers with the ROUND, TRUNC, and MOD functions. It also covers manipulating dates with the date function.
トピック 2	Managing Indexes Synonyms and Sequences: In this domain, exam-takers are tested for their skill in managing Indexes, Synonyms, and Sequences.
トピック 3	Managing Views: This domain covers how to manage or oversee modules.
トピック 4	Managing Objects with Data Dictionary Views: This domain covers utilizing data dictionary views.
トピック 5	Displaying Data from Multiple Tables: In this domain, topics include how to use Self-joins, use different types of Joins, how to utilize Nonequijoins, and OUTER joins.
トピック 6	Using Conversion Functions and Conditional Expressions: In this domain, the topics covered are how to apply the NVL, NULLIF, and COALESCE functions to data, how to understand the implicit and explicit data type conversion, utilize the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions, and nesting multiple functions.
トピック 7	Reporting Aggregated Data Using Group Functions: In this section of the exam, it is covered how to restrict Group Results and create Groups of Data.
トピック 8	Relational Database concepts: In this domain, the topics covered include explaining the theoretical and physical aspects of a relational database and relating clauses in SQL Select Statement to Components of an ERD.
トピック 9	Using SET Operators: In this domain, the topics covered match the SELECT statements, utilizing the ORDER BY clause in set operations, the INTERSECT operator, and the MINUS operator.
トピック 10	Managing Tables using DML statements: In this exam domain, topics covered include managing Database transactions, controlling them, perform Insert, Update, and Delete operations. It also covers how to perform multi-table Inserts and Merge statements.
トピック 11	Managing Indexes Synonyms and Sequences: In this domain, topics include managing Indexes, managing Synonyms, and managing Sequences.
トピック 12	Granting privileges on tables and distinguishing between giving privileges and roles.
トピック 13	Using Subqueries to Solve Queries: In this module, topics include using Single Row Subqueries, Multiple Row Subqueries, and how to delete and update rows.
トピック 14	Retrieving Data using the SQL SELECT Statement: This domain comprises topics such as using Column aliases, the SQL SELECT statement, utilizing the concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword.

質問 # 104
Which two actions can you perform with object privileges?

A. Set default and temporary tablespaces for a user.
B. Create roles.
C. Create FOREIGN KEY constraints that reference tables in other schemas.
D. Execute a procedure or function in another schema.
E. Delete rows from tables in any schema except sys.

正解：D、E

解説：
Regarding object privileges in an Oracle database:
* B. Delete rows from tables in any schema except sys: Object privileges include DELETE on tables, which can be granted by the owner of the table or a user with adequate privileges, excluding system schemas like SYS due to their critical role.
* E. Execute a procedure or function in another schema: EXECUTE is a specific object privilege that can be granted on procedures and functions, allowing users to run these objects in schemas other than their own.
Incorrect options:
* A: Creation of roles is related to system privileges, not object privileges.
* C: Setting default and temporary tablespaces for a user involves system-level operations, not object-level privileges.
* D: Creation of foreign key constraints involves referencing rights, which, while related, are not directly granted through object privileges but need appropriate REFERENCES permission.

質問 # 105
Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp
( emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
ename VARCHAR2(15),
salary NUMBER (8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp_no));
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_PK?

A. It would remain disabled and has to be enabled manually using the ALTER TABLE command.
B. It would be automatically enabled and deferred.
C. It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.
D. It would be automatically enabled and immediate.

正解：A

質問 # 106
Which three statements are correct regarding indexes? (Choose three.)

A. A non-deferrable PRIMARY KEYor UNIQUE KEYconstraint in a table automatically attempts to create a unique index.
B. When a table is dropped, corresponding indexes are automatically dropped.
C. Indexes should be created on columns that are frequently referenced as part of any expression.
D. For each DML operation performed on a table, the corresponding indexes are automatically updated if required.

正解：A、B、D

解説：
Explanation/Reference:
References:
http://viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/

質問 # 107
Which three statements are true reading subqueries? (Choose three.)

A. Only one column or expression can be compared between the subquery and main query.
B. The subquery and main query must retrieve date from the same table.
C. Multiple columns or expressions can be compared between the subquery and main query.
D. A Main query can have many subqueries.
E. The subquery and main query can retrieve data from different tables.
F. A subquery can have more than one main query.

正解：C、D、E

解説：
Explanation/Reference:

質問 # 108
In the EMPLOYEES table there are 1000 rows and employees are working in the company for more than 10 years.
Evaluate the following SQL statement:

What would be the result?

A. It executes successfully and updates the records of those employees who have been working in the company for more than 600 days.
B. It gives an error because NVL function cannot be used with UPDATE.
C. It gives an error because multiple NVL functions are used in an expression.
D. It executes successfully but no rows updated.

正解：A

質問 # 109
Which two will execute successfully?

A. SELECT NVL('DATE',SYSDATE) FROM DUAL;
B. SELECT COALESCE(O,SYSDATE) TRCH DUAL;
C. SELECT NVL('DATE',200) FROM (SELECT NULL AS "DATE" FROM DUAL);
D. SELECT COALESCR('DATE', SYSDATE) FROM DUAL;
E. SELECT COALESCE('DATE',SYSDATE) FROM (SELECT NULL AS "DATE" FROM DUAL) ;

正解：A、C

質問 # 110
Examine the description of the PRODUCTStable:

Which three queries use valid expressions? (Choose three.)
SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;

A. SELECT product_id, unit_price | | 5 "Discount", unit_price + surcharge -
B. SELECT product_id, expiry_date * 2 FROM products;
C. FROM products;
SELECT product_id, unit_price, unit_price + surcharge FROM products;
D. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
E. discount FROM products;
SELECT product_id, unit_price, 5 "Discount", unit_price + surcharge - discount

正解：B、C、D

解説：
Explanation

質問 # 111
Examine the structure of the SALES table.

Examine this statement:

Which two statements are true about the SALES1 table? (Choose two.)

A. It is created with no rows.
B. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
C. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
D. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
E. It will not be created because of the invalid WHERE clause.

正解：A、C

質問 # 112
Examine the structure proposed for the TRANSACTIONStable:

Which two statements are true regarding the storage of data in the above table structure? (Choose two.)

A. The TRANS_DATEcolumn would allow storage of dates only in the dd-mon-yyyy format.
B. The CUST_STATUScolumn would allow storage of data up to the maximum VARCHAR2size of 4,000 characters.
C. The CUST_CREDIT_VALUEcolumn would allow storage of positive and negative integers.
D. The TRANS_VALIDITYcolumn would allow storage of a time interval in days, hours, minutes, and seconds.

正解：C、D

質問 # 113
You want to write a query that prompts for two column names and the WHERE condition each time It is executed in a session but only prompts for the table name the first time it is executed. The variables used in your query are never undefined in your session . Which query can be used?

A. SELECT&&col1, &&col2
FROM &table
WHERE &&condition;
B. SELECT &&col1,&&col2
FROM &table
WHERE &&condition= &&cond;
C. SELECT'&co11','&&co12'
FROM &table
WHERE'&&condition' ='&cond';
D. SELECT &col1, &col2
FROM &&table
WHERE &condition;
E. SELECT &col1, &col2
FROM "&table"
WHERE &condition;

正解：D

解説：
The scenario requires prompting for column names and WHERE condition each time the query is executed, but only prompting for the table name once. This behavior is achievable through the use of substitution variables in SQL*Plus or SQL Developer:
* A.
SELECT &col1, &col2 FROM &&table WHERE &condition;
This query uses & for columns and condition, which will prompt every time the query is run. &&table will prompt for the table name the first time and reuse the same value in the same session without re-prompting.
Options B, C, D, and E use incorrect syntax or variable types, leading to various errors or undesired behaviors, such as not maintaining the value of the table name across executions or incorrect usage of single quotes and comparison in SQL.

質問 # 114
Evaluate the following CREATE SEQUENCEstatement:
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
The sequence SEQ1has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECTstatement?

A. 0
B. 1
C. 2
D. an error

正解：B

質問 # 115
You issue the following command to drop the PRODUCTS table:
SQL>DROP TABLE products;
What is the implication of this command? (Choose all that apply.)

A. All views and synonyms will remain but they are invalidated
B. All data along with the table structure is deleted
C. All indexes on the table will remain but they are invalidated
D. The pending transaction in the session is committed
E. All data in the table are deleted but the table structure will remain

正解：A、B、D

質問 # 116
Examine the description of the ORDERStable:

Examine the description of the INVOICEStable:

Examine this query:

Which three rows will it return? (Choose three.)
5 01-MAR-2019

A.
B. 3 01-JAN-2019
C. 5 <null>
D. 1 <null>
E. 3 <null>
F. 2 <null>
G. 4 01-FEB-2019

正解：A、C、E

質問 # 117
An Oracle Database session has an uncommitted transaction in progress which updated 5000 rows in a table.
In which three situations does the transaction complete thereby committing the updates? (Choose three.)

A. when a COMMIT statement is issued by the same user from another session in the same database instance
B. when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT
C. when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT
D. when the session logs out successfully
E. when a CREATE TABLE AS SELECT statement is issued in the same session but fails with a syntax error
F. when a CREATE INDEX statement is executed successfully in the same session

正解：D、E、F

質問 # 118
Examine the description of the BOOKS table:

The table has 100 rows.
Examine this sequence of statements issued in a new session;
INSERT INTO BOOKS VALUES ('ADV112' , 'Adventures of Tom Sawyer', NULL, NULL); SAVEPOINT a; DELETE from books; ROLLBACK TO SAVEPOINT a; ROLLBACK; Which two statements are true?

A. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.
B. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.
C. The second ROLLBACK command replays the delete.
D. The second ROLLBACK command undoes the insert.
E. The second ROLLBACK command does nothing.

正解：B、D

質問 # 119
The following are the steps for a correlated subquery, listed in random order:
* The WHERE clause of the outer query is evaluated.
* The candidate row is fetched from the table specified in the outer query.
* This is repeated for the subsequent rows of the table, till all the rows are processed.
* Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.
Which is the correct sequence in which the Oracle server evaluates a correlated subquery?

A. 2, 1, 4, 3
B. 4, 2, 1, 3
C. 4, 1, 2, 3
D. 2, 4, 1, 3

正解：D

解説：
Explanation
References:
http://rajanimohanty.blogspot.co.uk/2014/01/correlated-subquery.html

質問 # 120
View the exhibit and examine the structure of ORDERSand CUSTOMERStables.
ORDERS
Name
Null? Type
ORDER_ID NOT NULL NUMBER(4)
ORDER_DATE NOT NULL DATE
ORDER_MODE VARCHAR2(8)
CUSTOMER_ID NOT NULL NUMBER(6)
ORDER_TOTAL NUMBER(8, 2)
CUSTOMERS
Name Null? Type
CUSTOMER_ID NOT NULL NUMBER(6)
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(20)
CREDIT_LIMIT NUMBER(9,2)
CUST_ADDRESS VARCHAR2(40)
Which INSERT statement should be used to add a row into the ORDERStable for the customer whose CUST_LAST_NAMEis Robertsand CREDIT_LIMITis 600? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.

A. INSERT INTO orders
VALUES (1,'10-mar-2007', 'direct',
( SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
B. INSERT INTO orders (order_id, order_date, order_mode,
( SELECT customer id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), order_total);
VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);
C. INSERT INTO orders (order_id, order_date, order_mode,
( SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), order_total);
VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);
D. INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_total
FROM orders o, customers c
WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND
c.credit_limit=600)
VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);

正解：A

質問 # 121

Which two queries will result in an error?

A. SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES;
B. SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE 12 * SALARY > 100000
ORDER BY 12 * SALARY;
C. SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE ANNUAL_SALARY > 100000
ORDER BY ANNUAL_SALARY;
D. SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE 12 * SALARY > 100000
ORDER BY ANNUAL_SALARY;
E. SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE ANNUAL_SALARY > 100000
ORDER BY 12 * SALARY ;
F. SELECT FIRST_NAME LAST_NAME FROM EMPLOYEES;

正解：E、F

解説：
In Oracle SQL, the following syntactical rules apply:
A). This query will result in an error because there is no comma separating the column names FIRST_NAME and LAST_NAME. The correct syntax should include a comma to separate the column names in the SELECT list.
B). This query is correctly formatted with a comma separating the column names, so it will not result in an error.
C). This query will result in an error because an alias defined in the SELECT list (ANNUAL_SALARY) cannot be used in the WHERE clause of the same query level. It must be repeated in the WHERE clause as 12
* SALARY.
D). This query will execute successfully because 12 * SALARY is directly used in the WHERE clause, and ANNUAL_SALARY is used in the ORDER BY clause, which is allowed.
E). This query is correct and will not result in an error. It uses 12 * SALARY in both the WHERE and ORDER BY clauses.
F). Similar to option C, this query will execute successfully because ANNUAL_SALARY is correctly used in the ORDER BY clause, and the WHERE clause does not attempt to reference the alias.
References:
* Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Database Object Names and Qualifiers"

質問 # 122
Examine the structure of the SALES table. (Choose two.)

Examine this statement:

Which two statements are true about the SALES1 table? (Choose two.)

A. It is created with no rows.
B. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
C. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
D. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
E. It will not be created because of the invalid WHERE clause.

正解：A、C

質問 # 123
Which four statements are true about constraints on Oracle tables?

A. A UNIQUE constraint permits NULLS.
B. A PRIMARY KEY Constraint can be added after a table has been created and populated.
C. A Column can have only one CHECK Constraint.
D. A UNIQUE Constraint can use a pre-existing index on the constrained column or columns.
E. A NOT NULL Constraint can be defined at the table level.
F. A CHECK Constraint can refer to values in other rows.
G. A FOREIGN KEY Column can contain NULLS.

正解：A、B、D、G

解説：
C: True. A UNIQUE constraint in Oracle SQL allows for the inclusion of NULL values; specifically, it permits multiple NULLs in a column or set of columns but ensures that all non-NULL values are unique.
D: True. A PRIMARY KEY constraint can indeed be added to a table after it has been created and even after it has been populated, as long as the existing data does not violate the primary key constraint rules (i.e., all values must be unique and not NULL).
F: True. A UNIQUE constraint can utilize a pre-existing index on the columns it covers. If a suitable index already exists, Oracle can use this index to enforce the constraint, optimizing performance and resource utilization.
G: True. Columns that are part of a FOREIGN KEY constraint can contain NULL values. This is permissible under SQL standards and Oracle implementation, as a NULL foreign key value is considered to not refer to any row in the referenced table and thus does not violate referential integrity.

質問 # 124
Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?

A. Select deptno. LISTAGG(ename, ',') WITHIN GROUP AS employee-list
FROM emp
ORDER BY ename;
B. Select deptno. LISTAGG(ename, ',') WITHIN GROUP AS employee-list
FROM emp
GROUP BY emp
C. Select deptno. no LISTAGG(ename, ',') WITHIN GROUP (GROUP BY deptno) AS employee-list FROM emp ORDER BY emp
D. Select deptno. no LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employee-list FROM emp ORDER BY emp

正解：D

質問 # 125
View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)

Examine this SQL statement:
SELECT department_id "DEPT_ID", department_name, 'b' FROM
departments
WHERE departments_id=90
UNION
SELECT department_id, department_name DEPT_NAME, 'a' FROM
departments
WHERE department_id=10
Which two ORDER BY clauses can be used to sort output?