[2025年03月]更新の1z1-071試験事前練習テスト試験問題と解答Oracle PL/SQL Developer Certified Associate学習ガイド
Oracle Database SQL認証サンプル解答
Oracle 1z0-071 認定試験は、OracleデータベースのSQLプログラミングにおける専門知識を証明することを目的としています。この試験は、データベース管理者、開発者、プログラマーなど、Oracleデータベースを定期的に使用する個人に最適です。また、データベース管理の分野でキャリアを進めたいと考えている個人にも適しています。
質問 # 35
View the Exhibit and examine the structure in the EMPLOYEES tables.
Evaluate the following SQL statement:
SELECT employee_id, department_id
FROM employees
WHERE department_id= 50 ORDER BY department_id
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 90
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 10;
What would be the outcome of the above SQL statement?
- A. The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
- B. The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
- C. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.
- D. The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
正解：C
質問 # 36
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_VALIDITYcolumn would allow storage of a time interval in days, hours, minutes, and seconds.
- B. The TRANS_DATEcolumn would allow storage of dates only in the dd-mon-yyyy format.
- C. The CUST_CREDIT_VALUEcolumn would allow storage of positive and negative integers.
- D. The CUST_STATUScolumn would allow storage of data up to the maximum VARCHAR2size of 4,000 characters.
正解：A、C
質問 # 37
Examine the structure of the PROMOTIONS table: (Choose the best answer.)
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, promo_category FROM promotions
- C. SELECT promo_category, DISTINCT promo_cost FROM promotions
- D. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
正解：A
質問 # 38
Examine this partial command:
Which two clauses are required for this command to execute successfully?
- A. the REJECT LIMIT clause
- B. the DEFAULT DIRECTORY clause
- C. the LOCATION clause
- D. the access driver TYPE clause
- E. the ACCESS PARAMETERS clause
正解：C、D
質問 # 39
Examine the description of the EMPLOYEEStable:
Which query is valid?
- A. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;
- B. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id,
join_date; - C. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;
- D. SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;
正解：A
質問 # 40
Which two are true about the WITH GRANT OPTION clause?
- A. It cannot be used to pass on privileges to PUBLIC by the grantee.
- B. It can be used for system and object privileges.
- C. It can be used to pass on privileges to other users by the grantee.
- D. It can be used when granting privileges to roles.
- E. The grantee must have the GRANT ANY OBJECT PRIVILEGE system prvilege to use this option.
- F. The grantee can grant the object privilege to any user in the database, with of without including this option.
正解：A、C
解説：
The WITH GRANT OPTION clause in Oracle SQL allows the grantee to grant the privilege they have received to another user or role.
* E. It cannot be used to pass on privileges to PUBLIC by the grantee: The WITH GRANT OPTION does not allow the grantee to pass on privileges to PUBLIC. Only the object's owner or a user with the GRANT ANY OBJECT PRIVILEGE system privilege can grant privileges to PUBLIC.
* F. It can be used to pass on privileges to other users by the grantee: This is true. When a user receives privileges with the WITH GRANT OPTION, they can grant that privilege to another user or role.
References:
* Oracle Database SQL Language Reference 12c, specifically sections on user privileges and the WITH GRANT OPTION.
質問 # 41
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. LEFT OUTER JOIN
- B.
- C. RIGHT OUTER JOIN
- D. JOIN
正解：C、D
質問 # 42
Examine the description of the PROMTIONS table:
You want to display the unique promotion costs in each promotion category.
Which two queries can be used?
- A. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
- B. SELECT DISTINCT promo_cost ||' in' II DISTINCT promo_category FROM promotions ORDER BY 1;
- C. SELECT promo_cost, | pxomo_category FROM promotions ORDER BY 1;
- D. SELECT promo_category, DISTINCT promo_cost PROM promotions ORDER BY 2:
- E. SELECT DISTINCT promo_category ||'has'|| promo_cost AS COSTS FROM promotions ORDER BY 1;
正解：A、E
質問 # 43
Examine this SQL statement
DELETE FROM employees e
WHERE EXISTS
(SELECT' dummy'
FROM emp history
WHERE employee_ id= e. employee id);
Which two are true?
- A. The subquery is executed for every row in the EMPLOYEES table.
- B. The subquery is not a correlated subquery.
- C. The DELETE statement executes successfully even if the subquery selects multiple rows.
- D. All existing rows in the EMPLOYEES table are deleted,
- E. The subquery is executed before the DELETE statement is executed.
正解：A、C
質問 # 44
Which statement is true about the INTERSECT operator used in compound queries?
- A. It processes NULLS in the selected columns.
- B. Multiple INTERSECT operators are not possible in the same SQL statement.
- C. INTERSECT is of lower precedence than UNION or UNION ALL.
- D. It ignores NULLS.
正解：A
解説：
For the question about the INTERSECT operator in SQL:
* A. It processes NULLS in the selected columns: The INTERSECT operator compares two SELECT statements and returns rows that exist in both queries. It considers NULLs during this process, and NULLs in corresponding columns must match for rows to be considered equal. This means if both selected columns in the intersecting queries have NULLs, those rows will be included in the output.
Incorrect options:
* B: INTERSECT has higher precedence than UNION and UNION ALL, not lower.
* C: It does not ignore NULLs; rather, it processes them, as explained.
* D: Multiple INTERSECT operators are indeed possible in the same SQL statement, allowing for complex compound queries.
質問 # 45
Examine the structure of the PROMOTIONS table: (Choose the best answer.)
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, promo_category FROM promotions
- C. SELECT promo_category, DISTINCT promo_cost FROM promotions
- D. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
正解：A
質問 # 46
View the Exhibit and examine the structure of the EMPLOYEES table.
You want to display all employees and their managers having 100 as the MANAGER_ID.
You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?
- A. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON e.employee_id = m.manager_id
WHERE m.manager_id=100; - B. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE e.managerjd=100; - C. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
WHERE m.employee_id = e.manager_id AND e.managerjd=100; - D. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE m.manager_id=100;
正解：B
質問 # 47
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 TO_CHARshould be replaced with TO_DATE.
- B. It executes successfully but does not return the correct result.
- C. In generates an error because rrrrshould be replaced by rrin the format string.
- D. It executes successfully and returns the correct result.
- E. In generates an error because fmand double quotation marks should not be used in the format string.
正解：D
質問 # 48
Examine the structure of the CUSTOMERS table: (Choose two.)
CUSTNO is the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?
- A. Left outer-join with self-join
- B. Right outer-join with self-join
- C. Full outer-join with self-join
- D. Subquery
- E. Self-join
正解：D、E
質問 # 49
Examine the structure of the EMPLOYEES table.
You must display the maximum and minimum salaries of employees hired 1 year ago.
Which two statements would provide the correct output? (Choose two.)
- A. SELECT minsal, maxsal
FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employees
WHERE hire_date < SYSDATE-365)
GROUP BY maxsal, minsal; - B. SELECT minsal, maxsal
FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employees
WHERE hire_date < SYSDATE-365
GROUP BY MIN(salary), MAX(salary); - C. SELECT MIN(Salary), MAX(salary)
FROM (SELECT salary FROM
employees
WHERE hire_date < SYSDATE-365); - D. SELECT MIN(Salary) minsal, MAX(salary) maxsal
FROM employees
WHERE hire_date < SYSDATE-365
GROUP BY MIN(salary), MAX(salary);
正解：A、C
質問 # 50
View the exhibits and examine the structures of the COSTSand PROMOTIONStables.
Evaluate the following SQL statement:
What would be the outcome of the above SQL statement?
- A. It displays prod IDs in the promos with the highest cost in the same time interval.
- B. It displays prod IDs in the promos which cost less than the highest cost in the same time interval.
- C. It displays prod IDs in the promos with the lowest cost in the same time interval.
- D. It displays prod IDs in the promo with the lowest cost.
正解：B
質問 # 51
Which three statements are true about dropping and unused columns in an Oracle database?
- A. An unused column's space is reclaimed automatically when the row containing that column is next queried.
- B. An unused column's space is reclaimed automatically when the block containing that column is next queried.
- C. Partition key columns cannot be dropped.
- D. A drop column command can be rolled back.
- E. A column that is set to unused still counts towards the limit of 1000 columns per table.
- F. A primary key column referenced by another column as a foreign key can be dropped if using the cascade option.
正解：B、C、E
質問 # 52
View the exhibit for the structure of the STUDENTand FACULTYtables.
You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements:
Statement 1
SQL>SELECT faculty_name, COUNT(student_id)
FROM student JOIN faculty
USING (faculty_id, location_id)
GROUP BY faculty_name;
Statement 2
SQL>SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty
GROUP BY faculty_name;
Which statement is true regarding the outcome?
- A. Both statements 1 and 2 execute successfully and give different results.
- B. Only statement 2 executes successfully and gives the required result.
- C. Only statement 1 executes successfully and gives the required result.
- D. Both statements 1 and 2 execute successfully and give the same required result.
正解：C
質問 # 53
Examine the structure of the SALES table.
Examine this statement:
Which two statements are true about the SALES1 table? (Choose two.)
- A. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
- B. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
- C. It will not be created because of the invalid WHERE clause.
- D. It is created with no rows.
- E. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
正解：A、D
質問 # 54
......
Oracle試験練習テスト最高得点を獲得しよう：https://jp.fast2test.com/1z1-071-premium-file.html