[2025年02月06日] 合格Oracle PL/SQL Developer Certified Associate 1z0-071試験問題集には325問があります
究極ガイドの無料準備Oracle 1z0-071試験問題と解答
質問 # 75
View the Exhibit and examine the structure of the CUSTOMERS table.
Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.
Customers with no credit limit should have "Not Available" displayed.
Which SQL statement would produce the required result?
- A. SELECT TO_CHAR(NVL(cust_credit_limit*.15), 'Not Available')) "NEW CREDIT" FROM customers
- B. SELECT NVL (cust_credit_limit, 'Not Available')*.15 "NEW CREDIT" FROM customers
- C. SELECT NVL (TO_CHAR(cust_credit_limit*.15), 'Not Available') "NEW CREDIT" FROM customers
- D. SELECT NVL (cust_credit_limit*.15, 'Not Available') "NEW CREDIT" FROM customers
正解：D
質問 # 76
You need to list the employees in DEPARTMENT_ID 20 days in a single row, ordered by HIRE_DATE.
Examine the sample output:
Which query will provide the required output?
- A. SELECT LISTAGG(last_name, '; ') "Emp_list", MIN(hire_date) "Earliest" FROM employees WHERE department_id = 30; ORDER BY (hire_date);
- B. SELECT LISTAGG(last_name, '; ') "Emp_list", MIN(hire_date) "Earliest" FROM employees WHERE department_id = 30; WITHIN GROUP ORDER BY (hire_date);
- C. SELECT LISTAGG(last_name, '; ')
WITHIN GROUP ORDER BY (hire_date) "Emp_list", MIN(hire_date) "Earliest" FROM employees WHERE department_id = 30; - D. SELECT LISTAGG(last_name)
WITHIN GROUP ORDER BY (hire_date) "Emp_list", MIN(hire_date) "Earliest" FROM employees WHERE department_id = 30;
正解：C
質問 # 77
Examine the structure and data in the PRICE_LIST table:
You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.
Which SQL statement would give the required result?
- A. SELECT TO_CHAR (TO_NUMBER(prod_price) * .25, '$99,999.00')
FROM PRICE_LIST - B. SELECT TO_NUMBER (TO_NUMBER(prod_price, '$99,999.99') * . 25, '$99,999.00') FROM PRICE_LIST
- C. SELECT TO_CHAR (prod_price* .25, '$99,999.99')
FROM PRICE_LIST - D. SELECT TO_CHAR (TO_NUMBER(prod_price, '$99,999.99') * . 25, '$99,999.00') FROM PRICE_LIST
正解：D
質問 # 78
Which three statements are true regarding group functions? (Choose three.)
- A. They can be used together with the single-row functions in the SELECT clause.
- B. They can be used with a SQL statement that has a GROUP BY clause.
- C. They can be used only on one column in the SELECT clause.
- D. They can be passed as an argument to another group function.
- E. They can be used on columns or expressions.
正解：A、D、E
解説：
https://www.safaribooksonline.com/library/view/mastering-oracle-sql/0596006322/ch04.html
質問 # 79
Which two tasks require subqueries?
- A. Display the total number of products supplied by supplier 102 which have a product status of obsolete.
- B. Display suppliers whose PROD_LIST_PRICE is less than 1000.
- C. Display the minimum PROD_LIST_PRICE for each product status.
- D. Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.
- E. Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable.
正解：D、E
解説：
C: True. To display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE, you would need to use a subquery to first calculate the average PROD_LIST_PRICE and then use that result to compare each product's list price to the average.
E: True. Displaying products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products and whose status is orderable would require a subquery. The subquery would be used to determine the average PROD_LIST_PRICE, and then this average would be used in the outer query to filter the products accordingly.
Subqueries are necessary when the computation of a value relies on an aggregate or a result that must be obtained separately from the main query, and cannot be derived in a single level of query execution.
References:Oracle's SQL documentation provides guidelines for using subqueries in scenarios where an inner query's result is needed to complete the processing of an outer query.
質問 # 80
Examine the description of the ORDER_ITEMS table:
Examine this incomplete query:
SELECT DISTINCT quantity * unit_price total_paid FROM order_items ORDER BY <clause>; Which two can replace <clause> so the query completes successfully?
- A. quantity * unit_price
- B. quantity
- C. total_paid
- D. quantity, unit_price
- E. product_id
正解：A、D
解説：
In a SELECT statement with DISTINCT, the ORDER BY clause can only order by expressions that are part of the SELECT list.
A . quantity alone is not sufficient to replace <clause> as it is not included in the SELECT list after DISTINCT.
B . This option can successfully replace <clause> because both quantity and unit_price are used in the SELECT expression, and thus their individual values are valid for the ORDER BY clause.
C . total_paid is an alias for the expression quantity * unit_price, but it cannot be used in the ORDER BY clause because Oracle does not allow aliases of expressions in DISTINCT queries to be used in ORDER BY.
D . product_id is not included in the SELECT list after DISTINCT and thus cannot be used in ORDER BY.
E . The expression quantity * unit_price is exactly what is selected, so it can replace <clause> and the query will complete successfully.
Reference:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ORDER BY Clause"
質問 # 81
Examine this statement:
Which two things must be changed for it to execute successfully?
- A. The NOT NULL constraint on ENAME must be defined at the column level instead of the table level.
- B. The primary key constraint on BMP_ID must have a name.
- C. One of the LONG columns must be changed to a VARCHAR2 or CLOB.
- D. The word CONSTRAINT in the foreign key constraint on DEPT_ID must be changed to FOREIGN KEY.
- E. The foreign key constraint on DEPT_ID must be defined at the table level instead of the column level.
正解：A、C
質問 # 82
Which statement is true about transactions?
- A. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
- B. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.
- C. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.
- D. Each Data Definition Language (DDL) statement executed forms a single transaction.
正解：D
解説：
References:
https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT038
質問 # 83
Which three statements are true about the ALTER TABLE ----DROP COLUMN .....command?
- A. A column can be dropped only if it does not contain any data.
- B. A column can be dropped only if another column exists in the table.
- C. A parent key column in the table cannot be dropped.
- D. A dropped column can be rolled back.
- E. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.
正解：D
質問 # 84
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 NVL(salary+commission,0)>=20000;
- B. SELECT * FROM employees WHERE salary+NVL(commission,0)>=20000;
- C. SELECT * FROM employees WHERE NVL(salary+commission,0)>==20000;
- D. SELECT * FROM employees WHERE NVL2(salary)+commission,salary+commission,
- E. SELECT * FROM employees WHERE salary+NULLF(commission,0)>=20000;
- F. SELECT * FROM employees WHERE salary+NVL2(commission,commission,0)>=20000;
正解：B、E、F
解説：
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.
質問 # 85
Examine the structure of the BOOKS_TRANSACTIONStable.
You want to update this table such that BOOK_IDis set to 'INVALID'for all rows where no MEMBER_ID has been entered.
Examine this partial SQL statement:
Which condition must be used in the WHEREclause to perform the required update?
- A. MEMBER_ID = NULL;
- B. MEMBER_ID = '';
- C. MEMBER_ID IS NULL;
- D. MEMBER_ID = "";
正解：C
質問 # 86
Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?
- A. first normal form
- B. third normal form
- C. second normal form
- D. fourth normal form
正解：C
解説：
Explanation/Reference:
References:
https://blog.udemy.com/database-normal-forms/
質問 # 87
Examine the structure of the MEMBERS table: (Choose the best answer.)
Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC; What would be the result execution?
- A. It fails because a column number and a column alias cannot be used together in the ORDER BY clause.
- B. It fails because a column alias cannot be used in the ORDER BY clause.
- C. It displays all cities in descending order, within which the last names are further sorted in descending order.
- D. It displays all cities in ascending order, within which the last names are further sorted in descending order.
正解：D
質問 # 88
View the Exhibit and examine the structure of the CUSTOMERStable.
You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10,000.
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_credit_limit FROM customers
WHERE (UPPER(cust_last_name) LIKE 'A%' OR
UPPER (cust_last_name) LIKE 'B%' OR UPPER (cust_last_name) LIKE 'C%')
AND cust_credit_limit < 10000;
SQL>SELECT cust_last_name, cust_credit_limit FROM customers
WHERE UPPER (cust_last_name) BETWEEN 'A' AND 'C'
AND cust_credit_limit < 10000;
Which statement is true regarding the execution of the above queries?
- A. Both execute successfully but do not give the required result
- B. Only the first query gives the correct result
- C. Only the second query gives the correct result
- D. Both execute successfully and give the same result
正解：B
質問 # 89
Examine this partial statement:
SELECT ename, sal,comm FROM emp
Now examine this output:
WHICH ORDER BY clause will generate the displayed output?
- A. ORDER BY comm DESC NULLS LAST, ename
- B. ORDER BY NVL(enam,0) DESC, ename
- C. ORDER BY NVL(comm,0) ASC NULLS FIRST, ename
- D. ORDER BY NVL(comm,0) ASC NULLS LAST, ename
正解：A、B
質問 # 90
Which two are true about the USING clause when joining tables?
- A. It is used to specify an explicit join condition involving operators.
- B. All column names in a USING clause must be qualified with a table name or table alias.
- C. It can never be used with onatural join.
- D. It is used to specify an equijoin of columns that have the same name in both tables.
- E. It can never be used with a full outer join.
正解：D、E
解説：
When joining tables in Oracle Database 12c, the USING clause has specific behaviors:
* Option C: It is used to specify an equijoin of columns that have the same name in both tables.
* The USING clause is indeed used to specify an equijoin between two tables based on columns with identical names in the tables being joined. It simplifies the syntax of the JOIN operation by eliminating the need to qualify the joined columns with table names.
* Option D: It can never be used with a full outer join.
* The USING clause cannot be used with a full outer join because the full outer join requires a specification of how to treat each side of the join, including rows that don't match the join condition, which is not compatible with the semantics of the USING clause.
Options A, B, and E are incorrect:
* Option A is incorrect because when using the USING clause, you do not need to qualify the columns with table names or aliases in the select list; Oracle assumes that they are the same in both tables.
* Option B is incorrect because the USING clause can be used with natural joins.
* Option E is incorrect as the USING clause is not meant to specify explicit join conditions with operators; it's specifically for equijoins on columns of the same name.
質問 # 91
View the Exhibit and examine the structure of the SALESand PRODUCTStables. (Choose two.)
In the SALEStable, 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 JOINoperator:
Which two JOINoperations can be used to obtain the required output? (Choose two.) FULL OUTER JOIN
- A. RIGHT OUTER JOIN
- B. LEFT OUTER JOIN
- C. JOIN
- D.
正解：A、C
質問 # 92
Which two statements are true? (Choose two.)
- A. The USER SYNONYMS view can provide information about private synonyms.
- B. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.
- C. All the dynamic performance views prefixed with V$ are accessible to all the database users.
- D. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
- E. The USER OBJECTS view can provide information about the tables and views created by the user only.
正解：A、B
解説：
* A. The USER SYNONYMS view can provide information about private synonyms: This view contains information about private synonyms that have been created by the user. It lists all synonyms that are accessible to the user in their schema.
* E. DICTIONARY is a view that contains the names of all the data dictionary views that the user
* can access: This view lists the names of all data dictionary views available to the user, providing a directory of useful database metadata.
Incorrect options:
* B: The SYSTEM user does not own all base tables of the data dictionary; some are owned by SYS.
* C: Not all users have access to dynamic performance views prefixed with V$; access is restricted based on privileges.
* D: The USER_OBJECTS view lists all objects owned by the user, not just tables and views but also other types of schema objects like procedures and functions.
質問 # 93
You execute this query:
SELECT TO CHAR (NEXT_DAY(LAST_DAY(SYSDATE),'MON' ),' dd"Monday for" fmMonth rrr') FROM DUAL;
What is the result?
- A. It generates an error.
- B. It returns the date for the last Monday of the current month.
- C. It executes successfully but does not return any result.
- D. It returns the date for the first Monday of the next month.
正解：D
質問 # 94
View the Exhibit and examine the structure of the SALES table.
The following query is written to retrieve all those product IDs from the SALES table that have more than
55000 sold and have been ordered more than 10 items.
Which statement is true regarding this SQL statement?
- A. It produces an error because COUNT (*) should be specified in the SELECT clause also.
- B. It executes successfully and generates the required result.
- C. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).
- D. It produces an error because COUNT (*) should be only in the HAVING clause and not in the WHERE clause.
正解：D
質問 # 95
View the Exhibit and examine the structure of the stores table.
You want to display the name of the store along with the address, START_DATE, PROPERTV_PRICE, and the projected property price, which is 115% of the property price. The stores displayed must have START_DATE in the range of 36 months starting from 01Jan-2000 and above.
Which SQL statement would get the desired output?
A)
B)
C)
D)
- A. Option C
- B. Option D
- C. Option B
- D. Option A
正解：A
質問 # 96
You execute the SQL statement:
What is the outcome?
- A. It succeeds and an index is created for CITIZEN_ID.
- B. It fails because the NOT NULL and DEFAULToptions cannot be combined for the same column.
- C. It It succeeds and CITYcan contain only 'SEATTLE'or null for all rows.
- D. It fails because the condition for the CNAMESconstraint is not valid.
正解：A
質問 # 97
......
Oracle Database SQL試験としても知られるOracle 1Z0-071試験は、SQL開発とデータベース管理のスキルを実証したい個人向けに設計された専門的な認定試験です。この試験は、Database管理者、開発者、およびOracle Databaseを扱い、SQLの専門知識を検証したいIT専門家向けです。
Oracle Database SQL練習テスト2025年最新のストレスなしで1z0-071合格！：https://drive.google.com/open?id=1Dd-M9rUbAu3S_-d6NwD02pJWAzDxx9C8
合格させる1z0-071テストエンジンとPDFで完全版無料問題集：https://www.jpntest.com/shiken/1z0-071-mondaishu