[2024年06月] 検証済み Oracle 試験問題集 1z0-071 試験学習ガイド [Q25-Q50]

[2024年06月] 検証済みOracle試験問題集で1z0-071試験学習ガイド

ベスト品質のOracle 1z0-071試験解答リアル練習試験問題集で[2024]

Oracle Database SQL Examとしても知られるOracle 1Z0-071は、SQLプログラミング言語の個人のスキルと知識を評価するように設計されています。この試験は、Database開発者、データベース管理者、およびOracleデータベース管理のSQLスキルと専門知識を検証したい専門家を対象としています。この試験に合格することは、OracleデータベースSQL認定アソシエイト認定を取得するための前提条件です。

Oracle 1Z1-071（Oracle Database SQL）認定試験は、SQLプログラミングとデータベース管理の分野における個人の知識とスキルをテストするために設計されています。この試験は、Oracleデータベースの操作の経験があり、SQLプログラミングの習熟度を実証したい個人を対象としています。この試験では、SQLの基本、データ操作、データ検索、データベース管理など、幅広いトピックをカバーしています。

Oracle 1z0-071試験は、SQLとOracleデータベースに関連する広範なトピックをカバーしており、SQLクエリ、データ操作、データ制御言語（DCL）およびデータ定義言語（DDL）ステートメント、テーブルの作成と管理、およびデータベースのセキュリティなどが含まれます。また、副問い合わせ、集合演算子、およびデータ集約関数などの高度なSQLコンセプトもカバーしています。試験は73の多肢選択問題から構成され、100分以内に回答する必要があり、合格には少なくとも63％のスコアが必要です。

質問 # 25
Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1 table, which are not present in the SALES2 table.
SALES1 table
NameNullType
-----------------------------------------------
SALES_IDNUMBER
STORE_IDNUMBER
ITEMS_IDNUMBER
QUANTITYNUMBER
SALES_DATEDATE
SALES2 table
NameNullType
----------------------------------------------
SALES_IDNUMBER
STORE_IDNUMBER
ITEMS_IDNUMBER
QUANTITYNUMBER
SALES_DATEDATE
Which set operator generates the required output?

A. UNION
B. INTERSECT
C. SUBTRACT
D. PLUS
E. MINUS

正解：E

解説：
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm

質問 # 26
Examine the description of the BOOKS_TRANSACTIONS table:

Examine this partial SQL statement:
SELECT * FROM books_transactions
Which two WHERE conditions give the same result?

A. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'));
B. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102');
C. WHERE (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN ('A101','A102');
D. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102');
E. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102');

正解：C、D

質問 # 27
You want to display 5 percent of the rows from the sales table for products with the lowestAMOUNT_SOLD and also want to include the rows that have the sameAMOUNT_SOLDeven if this causes the output to exceed 5 percent of the rows.
Which query willprovide the required result?

A. SELECT prod_ id, cust_id, amount_sold FROM sales ORDER BY araount_sold FETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;
B. SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS WITH TIES;
C. SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount__sold FETCH FIRST 5 PERCENT ROWS ONLY;
D. SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;

正解：D

質問 # 28
Sales data of a company is stored in two tables, SALES1and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1table, which are not present in the SALES2table.

Which set operator generates the required output?

A. UNION
B. INTERSECT
C. SUBTRACT
D. PLUS
E. MINUS

正解：E

解説：
Explanation/Reference:
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm

質問 # 29

Which three statements are true?

A. The COMMISSION column can contain negative values .
B. The MANAGER column is a foreign key referencing the EMPNO column.
C. The SALARY column must have a value .
D. The DEPTNO column in the EMP table can contain the value 1.
E. An index is created automatically in the MANAGER column.
F. The DNAME column has a unique constraint.
G. The DEPTNO column in the EMP table can contain NULLS .

正解：D、E、G

質問 # 30
Examine these SQL statements that are executed in the given order:

What will be the status of the foreign key EMP_MGR_FK?

A. It will be enabled and immediate.
B. It will be enabled and deferred.
C. It will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.
D. It will remain disabled and can be re-enabled manually.

正解：D

質問 # 31
View the Exhibit and examine the structure of the ORDERS table.

Which UPDATE statement is valid?

A. UPDATE ordersSET order_date = TO_DATE('12-mar-2007','dd-mon-yyyy'),SET order_total = TO_NUMBER (NULL)WHERE order_id = 2455;
B. UPDATE ordersSET order_date = '12-mar-2007',AND order_total
TO_NUMBER(NULL)WHERE order_id = 2455;
C. UPDATE ordersSET order_date = '12-mar-2007',order_total IS NULLWHERE order_id
= 2455;
D. UPDATE ordersSET order_date = '12-mar-2007',order_total = NULLWHERE order_id
2455;

正解：D

質問 # 32
Which two statements are true about a self join?

A. The join key column must have an index.
B. It must be a full outer join.
C. It must be an equijoin.
D. It can be a left outer join.
E. It can be an inner join.

正解：D、E

解説：
A self join is a regular join, but the table is joined with itself. This kind of join can take the form of an inner join, a left outer join, or even a full outer join depending on the requirement.
* A. The join key column must have an index. (Incorrect)
While indexes can improve the performance of joins by reducing the cost of the lookup operations, they are not a requirement for a self join. A self join can be performed with or without an index on the join key columns.
* B. It can be a left outer join. (Correct)
A self join can indeed be a left outer join. This is useful when you want to include all records from the 'left' side of the join (the table itself), even if the join condition does not find any matching record on the 'right' side (the table itself again).

質問 # 33
Examine the data in the CUST NAME column of the CUSTOMERS table:
CUST_NAME
------------------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You want to display the CUST_NAME values where the last name starts with Mc or MC. Which two WHERE clauses give the required result?

A. WHERE UPPER (SUBSTR(cust_name, INSTR(cust_name, '') +1)) LIKE UPPER('MC%')
B. WHERE SUBSTR(cust_name, INSTR(cust_name,'') +1) LIKE'Mc%'
C. WHERE INITCAP (SUBSTR(cust_name, INSTR(cust_name,'') +1)) IN ('MC%','Mc%)
D. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,'') +1)) LIKE'Mc%'
E. WHERE SUBSTR(cust_name,INSTR(cust_name,'') +1) LIKE'Mc%' OR'MC%'

正解：A、D

質問 # 34
Which statements are true? (Choose all that apply.)

A. The data dictionary is created and maintained by the database administrator.
B. The usernames of all the users including the database administrators are stored in the data dictionary.
C. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
D. Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.
E. The data dictionary views consists of joins of dictionary base tables and user-defined tables.
F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary.

正解：B、C、F

解説：
References:
https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm

質問 # 35
Which three statements are true? (Choose three.)

A. The data dictionary is created and maintained by the database administrator.
B. Data dictionary views consist of joins of dictionary base tables and user-defined tables.
C. The usernames of all users including database administrators are stored in the data dictionary.
D. Views with the same name but different prefixes, such as DBA, ALL and USER, reference the same base tables from the data dictionary.
E. The USER_CONS_COLUMNS view should be queried to find the names of columns to which constraints apply.
F. Both USER_OBJECTS and CAT views provide the same information about all objects that are owned by the user.

正解：C、D、E

解説：
References:
https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm

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

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

正解：C、E

質問 # 37
Which three statements are true about the DESCRIBE command? (Choose three.)

A. It displays the NOT NULL constraint for any columns that have that constraint
B. It displays the PRIMARY KEY constraint for any column or columns that have that constraint
C. It can be used only from SQL*Plus
D. It can be used to display the structure of an existing view
E. It displays all constraints that are defined for each column
F. It can be used from SQL Developer

正解：A、C、D

質問 # 38
In your session, the NLS._DAE_FORMAT is DD- MM- YYYY.There are 86400 seconds in a day.Examine this result:
DATE
02-JAN-2020
Which statement returns this?

A. SELECT TO_ CHAR(TO_ DATE('29-10-2019') + INTERVAL '3' MONTH + INTERVAL '7' DAY - INTERVAL '360' SECOND, ' DD-MON-YYYY') AS "date" FROM DUAL;
B. SELECT-TO_CHAR(TO _DATE('29-10-2019'+ INTERVAL '2' MONTH+INTERVAL '4' DAYINTERVAL
'120' SECOND, 'DD-MON-YY') AS "daTe"
FROM DUAL;
C. SELECT-TO_CHAR(TO _DATE('29-10-2019'+ INTERVAL '2' MONTH+INTERVAL '6' DAYINTERVAL
'120' SECOND, 'DD-MON-YY') AS "daTe"
FROM DUAL;
D. SELECT TO_ CHAR(TO_ DATE('29-10-2019') +INTERVAL '2'; MONTH + INTERVAL '5'; DAY
-
INTERVAL '86410' SECOND, ' DD-MON-YYYY') AS "date"
FROM DUAL;
E. SELECT To CHAR(TO _DATE('29-10-2019') + INTERVAL '2' NONTH + INTERVAL '5' DAY INEERVAL '120' SECOND, ' DD-MON-YYY) AS "date" FROM DUAL;

正解：A

解説：
To calculate the date from a given base date with intervals, Oracle allows you to add or subtract intervals from dates. Since the NLS_DATE_FORMAT is set to DD-MM-YYYY, the output is expected to be in that format.
Option B seems to calculate a date that is 3 months and 7 days ahead of October 29, 2019, and then subtracts
360 seconds (which is 6 minutes), resulting in a time that is still within the same day.
Here's how the calculation in option B would work out:
* Start date: 29-10-2019
* Add 3 months: 29-01-2020
* Add 7 days: 05-02-2020
* Subtract 360 seconds: Since it's only a few minutes, the date remains 05-02-2020.
However, this does not match the provided result of 02-JAN-2020. We would need to consider the exact amount of time being subtracted or added to find the correct answer.
But upon reviewing the options, they all have various syntax errors such as a missing TO_CHAR function, incorrect quotes, and date formats not matching the session's NLS_DATE_FORMAT. Therefore, we would need to correct these issues to find the right answer.

質問 # 39
Which two statements are true about the rules of precedence for operators?

A. The + binary operator has the highest precedence in an expression in a SQL statements.
B. The concatenation operator || is always evaluated before addition and subtraction in an expression.
C. Arithmetic operators with equal precedence are evaluated from left to right within an expression.
D. NULLS influence the precedence of operators in an expression.
E. Multiple parentheses can be used to override the default precedence of operators in an expression.

正解：C、E

質問 # 40
You must create a table EMPLOYEES in which the values in the columns EMPLOYEES_ID and LOGIN_ID must be unique and not null.
Which two SQL statements would create the required table? (Choose two.) CREATE TABLE employees

A. (employee_id NUMBER,
login_id NUMBER,
employee_name VARCHAR2(100),
hire_date DATE,
CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id));
CREATE TABLE employees
B. (employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY,
login_id NUMBER UNIQUE,
employee_name VARCHAR2(25),
hire_date DATE);
CREATE TABLE employees
C. (employee_id NUMBER,
login_id NUMBER,
employee_name VARCHAR2(100),
hire_date DATE,
CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id);
CONSTRAINT emp_id_nn NOT NULL (employee_id, login_id));
CREATE TABLE employees
D. (employee_id NUMBER,
login_id NUMBER,
employee_name VARCHAR2(25),
hire_date DATE,
CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, login_id));
CREATE TABLE employees
E. (employee_id NUMBER CONSTRAINT emp_id_nn NOT NULL,
login_id NUMBER CONSTRAINT login_id_nn NOT NULL,
employee_name VARCHAR2(100),
hire_date DATE,
CONSTRAINT emp_num_id_uk UNIQUE (employee_id, login_id));

正解：D、E

質問 # 41
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 the output?

A. ORDER BY 3;
B. ORDER BY DEPT_NAME;
C. ORDER BY DEPT_ID;
D. ORDER BY 'b';

正解：A、C

質問 # 42
Examine the description of the CUSTOMERStable:

You need to display last names and credit limits of all customers whose last name starts with A or B in lower or upper case, and whose credit limit is below 1000.
Examine this partial query:

Which two WHEREconditions give the required result? (Choose two.)

正解：C、E

質問 # 43
Examine the description of the EMPLOYEEStable:

Which query is valid?

A. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;
B. SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;
C. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id,
join_date;
D. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;

正解：D

質問 # 44
Examine the data in the CUSTOMERStable:

You want to list all cities that have more than one customer along with the customer details.
Evaluate the following query:

Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)

A. JOIN
B. FULL OUTER JOIN
C. RIGHT OUTER JOIN
D. LEFT OUTER JOIN
E. NATURAL JOIN

正解：A、C

質問 # 45
Examine this SELECT statement and view the Exhibit to see its output:

SELECT constraints_name, constraints_type, search_condition, r_constraints_name, delete_rule, status, FROM user_constraints WHERE table_name = 'ORDERS'; Which two statements are true about the output? (Choose two.)

A. In the second column, 'c' indicates a check constraint.
B. The STATUS column indicates whether the table is currently in use.
C. The DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.
D. The R_CONSTRAINT_NAME column contains an alternative name for the constraint.

正解：A、C

質問 # 46
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 order_date> TO_DATE(ADD_MONTHS(SYSDATE,6),'MON DD YYYY');
B. WHERE TO_CHAR(order_date,'MON DD YYYY') ='JAN 20 2019';
C. WHERE order_date IN (TO_DATE ('Oct 21 2018','MON DD YYYY'), TO_CHAR('Nov 21 2018','MON DD YYYY'));
D. WHERE ordet_date> TO_CHAR(ADD_MONTHS(SYSDATE, 6),'MON DD YYYY')
E. WHERE order_date> TO_DATE('JUL 10 2018','MON DD YYYY');

正解：B、E

質問 # 47
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 > IN(SELECT
order_date FROM orders WHERE customer_id = 101);
B. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
C. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT order_date FROM orders WHERE customer_id = 101);
D. SELECT order_id, order_date FROM ordersWHERE order_date > ANY(SELECT order_date FROM orders WHERE customer_id = 101);

正解：C

質問 # 48
Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1 table, which are not present in the SALES2 table.

Which set operator generates the required output?

A. UNION
B. INTERSECT
C. SUBTRACT
D. PLUS
E. MINUS

正解：E

解説：
Explanation
https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm

質問 # 49
You must write a query that prompts users for column names and conditions every time it is executed. (Choose the best answer.) The user must be prompted only once for the table name.
Which statement achieves those objectives?