究極のガイド準備1z0-071認証試験Oracle PL/SQL Developer Certified Associateは2025年更新 [Q81-Q104]

究極のガイド準備1z0-071認証試験Oracle PL/SQL Developer Certified Associateは2025年更新

リアル1z0-071問題集でOracle正確なアンサーは最新問題は2025年更新

Oracle 1z0-071（Oracle Database SQL）認定試験は、SQLプログラミング言語とOracleデータベース管理における専門知識を検証したいプロフェッショナルにとって人気のある認定試験です。この試験は、基本的なSQLコンセプト、データベース設計、データ操作、クエリ最適化技術を含むOracle Database 12cでの作業に必要な知識とスキルをテストするように設計されています。この試験に合格することは、キャリアの見通しを改善し、Oracleデータベース管理の専門家として認められるための素晴らしい方法です。

Oracle 1z1-071（Oracle Database SQL）認定試験は、候補者のSQLデータベース管理の知識とスキルを検証するプロフェッショナル認定試験です。この認定は、データベース開発者、データベース管理者、およびデータベース管理の経験があるITプロフェッショナルが対象となっており、Oracle SQLのスキルと知識を向上させたい人に適しています。試験は、SQLの基礎、データの取得、データの操作、およびデータベースオブジェクトなど、幅広いトピックをカバーしています。

 

質問 # 81
Which two queries return the string Hello! we're ready?

• A. SELECT q'[Hello! We're ready]'FROM DUAL;
• B. SELECT "Hello! We're ready "FROM |DUAL;
• C. SELECT q'! Hello! We're ready! 'FROM DUAL;
• D. SELECT 'Hello! we\ re ready' ESCAPE'N'FROMDUAL:
• E. SELECT 'Hello! We're ready' FROM DUAL;

正解：A、C

質問 # 82
Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)?

• A. None of the set operators can be used when selecting CLOB columns.
• B. Each SELECT statement in the query can have an ORDER BY clause.
• C. The FOR UPDATE clause cannot be specified.
• D. The name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list.
• E. There must be an equal number of columns in each SELECT list.

正解：C、E

解説：
A). True. When using set operators, the number and order of columns must be the same in all SELECT statements involved in the query. The data types of those columns must also be compatible.
E). True. The FOR UPDATE clause cannot be specified in a subquery or a SELECT statement that is combined with another SELECT statement by using a set operator.
B is incorrect because the column names do not need to match; only the number and data types of the columns must be compatible. C is incorrect because only the final SELECT statement in the sequence of UNION/INTERSECT/MINUS operations can have an ORDER BY clause. D is incorrect because, as of Oracle 12c, UNION ALL can be used with CLOB columns, but UNION, INTERSECT, and MINUS cannot.

質問 # 83
On your Oracle 12c database, you invoked SQL *Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command:
$> sqlldr hr/hr@pdb table=employees
Which two statements are true regarding the command?

• A. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.
• B. It fails because no SQL *Loader control file location is specified.
• C. It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database.
• D. It fails because no SQL *Loader data file location is specified.

正解：A、C

質問 # 84
Examine the structure of the MARKS table:
Which two statements would execute successfully? (Choose two.)

• A. SELECT SUM(subject1+subject2+subject3)FROM marksWHERE student_name IS NULL;
• B. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)FROM marksWHERE
  subject1 > subject2;
• C. SELECT student_name,SUM(subject1)FROM marksWHERE student_name LIKE 'R%';
• D. SELECT student_name subject1FROM marksWHERE subject1 > AVG(subject1);

正解：A、B

質問 # 85
Which three statements are true about Oracle synonyms?

• A. A synonym created by one user can refer to an object belonging to another user.
• B. A SEQUENCE can have a synonym.
• C. Any user can drop a PUBLIC synonym.
• D. A synonym cannot be created for a PL /SQL package.
• E. A synonym can be available to all users .

正解：A、D、E

質問 # 86
View the Exhibit and examine the description for the PRODUCTS and SALES table.

PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table with ON DELETE CASCADE option. The SALES table contains data for the last three years. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years.
Which is the valid DELETE statement?

• A. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE time_id >= SYSDATE - 3*365 );
• B. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE SYSDATE -
  3*365 >= time_id);
• C. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE time_id - 3*365 = SYSDATE );
• D. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE SYSDATE >= time_id - 3*365 );

正解：B

質問 # 87
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables:

You want to generate a list of all department IDs that do not exist in the COURSE_DETAILS table.
You execute the SQL statement:

What is the outcome?

• A. It fails because the ON clause condition is not valid.
• B. It executes successfully and displays the required list.
• C. It executes successfully but displays an incorrect list.
• D. It fails because the join type used is incorrect.

正解：B

質問 # 88
You must create a table for a banking application. (Choose the best answer.) One of the columns in the table has these requirements:
1: A column to store the duration of a short team loan
2: The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions.
3: The maximum loan period is 30 days.
4: Interest must be calculated based on the number of days for which the loan remains unpaid.
Which data type would you use?

• A. Interval day to second
• B. Number
• C. Timestamp
• D. Date
• E. Interval year to month

正解：A

質問 # 89
The ORDERS table has a column ORDER_DATE of date type DATE The default display format for a date is DD-MON-RR Which two WHERE conditions demonstrate the correct usage of conversion functions?

• A. WHERE TO_CHAR(order_date,'MON DD YYYY') ='JAN 20 2019';
• B. WHERE order_date IN (TO_DATE ('Oct 21 2018','MON DD YYYY'), TO_CHAR('Nov 21
  2018','MON DD YYYY'));
• C. WHERE ordet_date> TO_CHAR(ADD_MONTHS(SYSDATE, 6),'MON DD YYYY')
• D. WHERE order_date> TO_DATE('JUL 10 2018','MON DD YYYY');
• E. WHERE order_date> TO_DATE(ADD_MONTHS(SYSDATE,6),'MON DD YYYY');

正解：A、D

質問 # 90
Examine the structure of the BOOKS_TRANSACTIONS table:

You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?

• A. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
• B. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS
• C. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS
• D. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS

正解：D

質問 # 91
View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)

In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator:
SQL > SELECT p.prod_id, count(s.prod_id)
FROM products p ______________ sales s
ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN operations can be used to obtain the required output?

• A. FULL OUTER JOIN
• B. LEFT OUETR JOIN
• C. JOIN
• D. RIGHT OUTER JOIN

正解：A、B

質問 # 92
Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION?

• A. Only column names from the first SELECT statement in the compound query are recognized.
• B. The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause.
• C. Each SELECT statement in the compound query must have its own ORDER BY clause.
• D. Column positions must be used in the ORDER BY clause.
• E. Each SELECT statement in the compound query can have its own ORDER BY clause.

正解：A、E

質問 # 93
Examine the structure of the PROMOTIONS table:

Management requires a report of unique promotion costs in each promotion category.
Which query would satisfy this requirement?

• A. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
• B. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
• C. SELECT DISTINCT promo_cost, promo_category FROM promotions;
• D. SELECT promo_category, DISTINCT promo_cost FROM promotions;

正解：A

質問 # 94
Which three actions can you perform on an existing table containing data? (Choose three.)

• A. Add a new NOT NULL column with a DEFAULT value
• B. Define a default value that is automatically inserted into a column containing nulls
• C. Add a new column as the table's first column
• D. Increase the width of a numeric column
• E. Change a DATE column containing data to a NUMBER data type
• F. Change the default value of a column

正解：C、F

質問 # 95
View the Exhibit and examine the structure of CUSTOMERStable.
Using the CUSTOMERStable, you need to generate a report that shows an increase in the credit limit by
15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?

• A. SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;
• B. SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT"
  FROM customers;
• C. SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;
• D. SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"
  FROM customers;

正解：A

質問 # 96
View the Exhibit and examine the structure of the ORDER_ITEMS table. (Choose the best answer.)

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_idFROM order_itemsWHERE(unit_price*quantity)
  MAX(unit_price*quantity)GROUP BY order_id);
• B. SELECT order_idFROM order_itemsWHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)FROM order_itemsGROUP BY order_id)
• C. SELECT order_idFROM order_itemsWHERE(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity)FROM order_items) GROUP BY order_id);
• D. SELECT order_idFROM order_itemsGROUP BY order_idHAVING SUM(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity))FROM order_items GROUP BY order_id);

正解：D

質問 # 97
View the Exhibit and examine the structure of the ORDERS table. (Choose the best answer.) You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.
Which query would give you the desired result?

• A. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
• B. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT order_date FROM orders WHERE customer_id = 101);
• C. SELECT order_id, order_date FROM ordersWHERE order_date > ANY(SELECT order_date FROM orders WHERE customer_id = 101);
• D. SELECT order_id, order_date FROM ordersWHERE order_date > IN(SELECT
  order_date FROM orders WHERE customer_id = 101);

正解：B

質問 # 98
View the Exhibit and examine the description of the EMPLOYEES table.
You want to calculate the total renumeration for each employee. Total renumeration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.
Which SQL statement would you execute to get the desired output?

• A. SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct,
  salary,salary+commission_pct))"Total"FROM EMPLOYEES;
• B. SELECT first_name, salary, salary*12+salary*commission_pct "Total"FROM EMPLOYEES;
• C. SELECT first_name, salary*12 + NVL(salary,0)*commission_pct, "Total"FROM EMPLOYEES;
• D. SELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12 "Total"FROM EMPLOYEES;

正解：A

質問 # 99
Examine the data in the ENPLOYEES table:

Which statement will compute the total annual compensation tor each employee

• A. SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * NVL (monthly_commission_pct, 0)) AS annual_comp FROM employees
• B. SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * monthly_commission_pct) AS annual_comp FROM employees
• C. SELCECT last_namo, (monthly_salary * 12) + (monthly_commission_pct * 12) AS annual_comp FROM employees
• D. SECECT last_namo, (menthy_salary + monthly_commission_pct) * 12 AS annual_comp FROM employees;

正解：A

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

Examine this query:

Which line produces an error?

• A. Line 3
• B. Line 5
• C. Line 7
• D. Line 8

正解：A

質問 # 101
Evaluate the following SQL statement:
SQL> select cust_id, cust_last_name "Last name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30
Identify three ORDER BY clauses either one of which can complete the query. (Choose three.)

• A. ORDER BY "Last name"
• B. ORDER BY 2,1
• C. ORDER BY 2, cust_id
• D. ORDER BY CUST_NO
• E. ORDER BY "CUST_NO"

正解：A、B、C

解説：
Explanation
Using the ORDER BY Clause in Set Operations
-The ORDER BY clause can appear only once at the end of the compound query.
-Component queries cannot have individual ORDER BY clauses.
-The ORDER BY clause recognizes only the columns of the first SELECT query.
-By default, the first column of the first SELECT query is used to sort the output in an ascending order.

質問 # 102
Examine this statement:

Which two statements are true?

• A. The names of employees remaining the maximum salary will appear first in an ascending order
• B. All remaining employee names will appear in an ascending order
• C. The names of employees maximum salary will appear fist to descending order
• D. The names of employees maximum salary will appear fist to ascending order
• E. All remaining employee names will appear in descending order
• F. All remaining employee names will appear in ascending order

正解：C、F

解説：
D . All remaining employee names will appear in descending order F. The names of employees with maximum salary will appear first in descending order Comprehensive and Detailed Explanation WITH all Reference:
Analyzing the given SQL query:
ORDER BY CASE WHEN salary = (SELECT max(salary) FROM employees) THEN 'A' ELSE last_name END, last_name DESC; Here's what happens in the query:
The CASE statement checks if the employee's salary is equal to the maximum salary in the table.
If an employee has the maximum salary, they are assigned a constant value 'A', else their last_name is used for sorting.
Since 'A' comes before any possible last name value in ascending order, all employees with the maximum salary will be listed first.
After the CASE expression, the query specifies last_name DESC. This means that within each group sorted by the CASE expression, the names will be sorted in descending order.
Therefore, the employees with the maximum salary will appear first, and within that group, if there is more than one employee with the same maximum salary, they will be sorted in descending order by last name (F). Then, all remaining employee names will appear in descending order (D).
Reference:
Oracle Documentation on CASE Expressions: CASE Expressions
Oracle Documentation on Sorting Query Results: Sorting Query Results

質問 # 103
Which two queries execute successfully?

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

正解：B、E

質問 # 104
......

Oracle PL/SQL Developer Certified Associate 1z0-071試験練習問題集：https://jp.fast2test.com/1z0-071-premium-file.html

1z0-071プレミアム資料テストPDFで無料問題集お試しセット：https://drive.google.com/open?id=1k09O34PMzsnA0H-sRSRAYQeNEYZIYT_8