更新済みの2025年02月 1z1-071試験練習テスト問題
検証済み1z1-071問題集と解答100%一発合格保証で更新された問題集
試験の準備には、OracleはSQLの基礎を理解し、Oracle Databaseのコンセプトを理解することを推奨しています。候補者は、トレーニングコース、学習ガイド、模擬試験を利用して試験の準備をすることができます。Oracleはまた、試験のカバーするトピックを概説し、各トピックのサンプル問題を提供する試験準備ガイドを提供しています。
質問 # 16
Examine the description of EMPLOYEES table:
Which three queries return all rows for which SALARY+COMMISSION is greate than 20000?
- A. SELECT * FROM employees WHERE salary+NULLF(commission,0)>=20000;
- B. SELECT * FROM employees WHERE NVL(salary+commission,0)>==20000;
- C. SELECT * FROM employees WHERE salary+NVL2(commission,commission,0)>=20000;
- D. SELECT * FROM employees WHERE NVL2(salary)+commission,salary+commission,
- E. SELECT * FROM employees WHERE salary+NVL(commission,0)>=20000;
- F. SELECT * FROM employees WHERE NVL(salary+commission,0)>=20000;
正解：A、C、E
解説：
In Oracle Database 12c, when dealing with NULL values in arithmetic expressions, the functions NVL, NVL2, and equivalent techniques using NULLIF or arithmetic operations with NULL can be crucial.
Option A: SELECT * FROM employees WHERE salary + NULLIF(commission, 0) >= 20000; NULLIF(value1, value2) returns NULL if value1 is equal to value2, otherwise it returns value1. In this case, if commission is NULL, it treats it as 0.
This query correctly adds salary and commission (assuming 0 when commission is NULL) and checks if the total is at least 20000.
Option B: SELECT * FROM employees WHERE salary + NVL2(commission, commission, 0) >= 20000; NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not NULL; otherwise, it returns expr3. Here, if commission is not NULL, it uses commission, otherwise 0.
This condition correctly calculates salary + commission (assuming commission as 0 if it is NULL) and checks if the total is at least 20000.
Option D: SELECT * FROM employees WHERE salary + NVL(commission, 0) >= 20000; NVL(expr1, expr2) returns expr2 if expr1 is NULL; otherwise, it returns expr1. This query treats commission as 0 if it is NULL.
It correctly sums salary and commission and compares the result against 20000.
Options C, E, and F contain either syntax errors or logical errors in handling NULLs and comparisons.
質問 # 17
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 gives an error because NVL function cannot be used with UPDATE.
- B. It executes successfully and updates the records of those employees who have been working in the company for more than 600 days.
- C. It gives an error because multiple NVL functions are used in an expression.
- D. It executes successfully but no rows updated.
正解：B
質問 # 18
View the Exhibit and examine the data in the PROMOTIONStable.
PROMO_BEGIN_DATEis stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the POSTcategory that were launched before January 1, 2000.
Which SQL statement would you use?
SELECT promo_name, promo_cost, promo_begin_date
- A. FROM promotions
WHERE promo_category = 'post' AND promo_begin_date < '01-01-00';
SELECT promo_name, promo_cost, promo_begin_date - B. FROM promotions
WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000';
SELECT promo_name, promo_cost, promo_begin_date - C. FROM promotions
WHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00';
SELECT promo_name, promo_cost, promo_begin_date - D. FROM promotions
WHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00';
正解：D
質問 # 19
Examine the description of the PRODUCTStable:
Which three queries use valid expressions? (Choose three.)
- A. discount FROM products;
SELECT product_id, unit_price, 5 "Discount", unit_price + surcharge - discount - B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
- C. SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;
- D. SELECT product_id, unit_price | | 5 "Discount", unit_price + surcharge -
- E. FROM products;
SELECT product_id, unit_price, unit_price + surcharge FROM products; - F. SELECT product_id, expiry_date * 2 FROM products;
正解：A、E、F
質問 # 20
Which two will execute successfully?
- A. SELECT NVL('DATE',SYSDATE) FROM DUAL;
- B. SELECT COALESCR('DATE', SYSDATE) FROM DUAL;
- C. SELECT COALESCE(O,SYSDATE) TRCH DUAL;
- D. SELECT COALESCE('DATE',SYSDATE) FROM (SELECT NULL AS "DATE" FROM DUAL) ;
- E. SELECT NVL('DATE',200) FROM (SELECT NULL AS "DATE" FROM DUAL);
正解：E
解説：
D). True. The NVL function can replace a NULL value with a specified value, and it does not require the data types to match exactly, allowing implicit conversion where possible. Here, 'DATE' is a string literal, and 200 is a number, and since the selected column is NULL, NVL will return 200.
The other options are incorrect because the COALESCE function requires all arguments to be of the same data type or at least compatible types that Oracle can implicitly convert. In A and E, the use of COALESCE with a string literal 'DATE' and SYSDATE (which is a date type) is not compatible without explicit conversion. Option C has a typo (TRCH instead of FROM) and is mixing data types incorrectly.
質問 # 21
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 PUBLIC SYNONIM emp FOR hcm.employee_records;
- B. CREATE SYNONIM PUBLIC.emp FOR hcm.employee_records;
- C. CREATE SYNONIM emp FOR hcm.employee_records;
- D. CREATE SYNONIM SYS.emp FOR hcm.employee_records;
- E. CREATE GLOBAL SYNONIM emp FOR hcm.employee_records;
正解：A
解説：
CREATE PUBLIC SYNONYM emp_table
Reference: https://docs.oracle.com/database/121/SQLRF/statements_7001.htm#SQLRF01401
質問 # 22
Examine the description of the PRODUCTS table:
Which three queries use valid expressions?
- A. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
- B. SPLECT product_id, expiry_date * 2 FROM products;
- C. SELECT product_id,unit_price || "Discount", unit_price + surcharge-discount FROM products;
- D. SELEGT product_id, unit_price, unit_price + surcharge FROM products;
- E. SELECT produet_id, unit_pricer, 5 "Discount",unit_price+surcharge-discount FROM products;
- F. SELECT ptoduct_id, (expiry_date-delivery_date) * 2 FROM products;
正解：A、D、F
質問 # 23
Examine the structure of the MEMBERStable.
Which query can be used to display the last names and city names only for members from the states MO and MI?
SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';
- A. SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';
- B. SELECT last_name, city FROM members WHERE state LIKE 'M%';
- C. SELECT last_name, city FROM members WHERE state IN ('MO', 'MI');
- D.
正解：A
質問 # 24
Which two statements are true regarding constraints? (Choose two.)
- A. The column with a UNIQUE constraint can store NULLS.
- B. A constraint is enforced only for an INSERT operation on a table.
- C. You can have more than one column in a table as part of a primary key.
- D. A foreign key cannot contain NULL values.
正解：A、C
質問 # 25
In which three situations does a transaction complete?
- A. when a DELETE statement is executed
- B. when a TRUNCATE statement is executed after the pending transaction
- C. when a data definition language (DDL) statement is executed
- D. when a PL/SQL anonymous block is executed
- E. when a ROLLBACK command is executed
正解：B、C、E
解説：
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm
質問 # 26
Which three are true about privileges and roles?
- A. A role can contain a combination of several privileges and roles.
- B. System privileges always set privileges for an entire database.
- C. A user has all object privileges for every object in their schema by default.
- D. PUBLIC acts as a default role granted to every user in a database
- E. A role is owned by the user who created it.
- F. All roles are owned by the SYS schema.
- G. PUBLIC can be revoked from a user.
正解：A、C、D
解説：
Roles and privileges in Oracle manage access and capabilities within the database:
* Option A: False. Roles are not "owned" in the traditional sense by the user who created them. They exist independently within the Oracle database and are assigned to users.
* Option B: False. System privileges can be very granular, affecting specific types of operations or database objects, not always the entire database.
* Option C: False. Roles are not owned by the SYS schema but are managed by database security and can be created by any user with sufficient privileges.
* Option D: True. A role can indeed contain a combination of several privileges, including other roles, allowing for flexible and layered security configurations.
* Option E: True. By default, a user has all object privileges for objects they own (i.e., objects in their schema).
* Option F: False. PUBLIC is a special designation that applies to all users; individual privileges granted to PUBLIC cannot be revoked from a single user without revoking them from all users.
* Option G: True. PUBLIC is a role granted by default to every user in an Oracle database, providing
* basic privileges necessary for general usability of the database.
質問 # 27
View the Exhibit and examine the structure of the BOOKS table.
The BOOKS table contains details of 100 books.
Examine the commands executed and their outcome:
Which statement is true?
- A. Both ROLLBACK commands restore the 100 rows that were deleted.
- B. 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.
- C. The first rollback restores the 100 rows that were deleted and the second rollback commits only the changes.
- D. Both ROLLBACK commands restore the 101 rows that were deleted.
正解：B
質問 # 28
View the Exhibit and examine the structure of the PORDUCT_INFORMATION table.
(Choose the best answer.)
PRODUCT_ID column is the primary key.
You create an index using this command:
SQL > CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
No other indexes exist on the PRODUCT_INFORMATION table.
Which query would use the UPPER_NAME_IDX index?
- A. SELECT UPPER(product_name)FROM product_informationWHERE product_id = 2254;
- B. SELECT product_id, UPPER(product_name)FROM product_informationWHERE
UPPER(product_name) = 'LASERPRO' OR list_price > 1000; - C. SELECT product_idFROM product_informationWHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');
- D. SELECT UPPER(product_name)FROM product_information;
正解：C
質問 # 29
View the Exhibit and examine the structure of the ORDER_ITEMS table.
Examine the following SQL statement:
SELECT order_id, product_id, unit_price
FROM order_items
WHERE unit_price =
(SELECT MAX(unit_price)
FROM order_items
GROUP BY order_id);
You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID.
What correction should be made in the above SQL statement to achieve this?
- A. Remove the GROUP BY clause from the subquery and place it in the main query
- B. Replace = with the >ALL operator
- C. Replace = with the IN operator
- D. Replace = with the >ANY operator
正解：C
質問 # 30
Examine this query:
SELECT TRUNC(ROUND(156.00, -2), -1) FROM DUAL;
What is the result?
- A. 0
- B. 1
- C. 2
- D. 3
- E. 4
正解：B
解説：
Explanation
Reference https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions200.htm
質問 # 31
Examine the data in the PRODUCTStable:
Examine these queries:
Which queries generate the same output?
- A. 2 and 3
- B. 1, 2, and 3
- C. 1 and 2
- D. 1 and 3
正解：D
解説：
Explanation/Reference: https://www.dofactory.com/sql/where-any-all (statement 2 syntax in wrong)
質問 # 32
Which three statements are true regarding group functions? (Choose three.)
- A. They can be used on columns or expressions.
- B. They can be used on only one column in the SELECT clause of a SQL statement.
- C. They can be used only with a SQL statement that has the GROUP BY clause.
- D. They can be used along with the single-row function in the SELECT clause of a SQL statement.
- E. They can be passed as an argument to another group function.
正解：A、D、E
質問 # 33
A session's NLS_DATE_FORMAT is set to DD Mon YYYY .
Which two queries return the value 1 Jan 2019?
- A. SELECT '2019-01-01' FROM DUAL ; 2019-01-01
- B. SELECT to_date(' 2019-01-01 ', 'YYYY -MM-DD' ) FROM DUAL;
- C. SELECT DATE '2019-01-01' FROM DUAL ;
- D. SELECT TO_ DATE('2019-01-01') FROM DUAL;
- E. SELECT TO_CHAR('2019-01-01') FROM DUAL; 2019-01-01
正解：B、C
質問 # 34
......
Oracle 1z0-071認定試験は、データベース管理者、開発者、またはデータアナリストとして働きたい個人に最適です。この試験は、SQLの基礎、データモデリング、データベース設計、およびデータベースセキュリティなど、さまざまなトピックをカバーしています。SQLコンセプトに関する堅固な理解力が必要な難しい試験であり、候補者はSQLプログラミングにおける実践的なスキルを証明することが求められます。試験に合格することで、個人は就職市場で競争優位性を獲得することができ、また、より高い給与やキャリアアップの機会を得ることができます。
合格できるOracle PL/SQL Developer Certified Associate 1z1-071試験問題集には325問があります：https://jp.fast2test.com/1z1-071-premium-file.html