無料Oracle 1z0-071試験問題と解答トレーニングを提供しています [Q104-Q125]

無料Oracle 1z0-071試験問題と解答トレーニングを提供しています

トップクラスOracle 1z0-071オンライン問題集

Oracle 1z1-071（Oracle Database SQL）試験は、SQLのスキルと知識を示したいプロフェッショナル向けに設計されています。この試験は、候補者のOracleデータベースシステムのSQL言語を設計、管理、操作する能力を測定します。この試験に合格することは、多くのOracle認定プログラムの前提条件となります。

 

質問 # 104
You want to display 5 percent of the rows from the SALEStable for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLDeven if this causes the output to exceed 5 percent of the rows.
Which query will provide the required result?

• A. SELECT prod_id, cust_id, amount_sold
  FROM sales
  ORDER BY amount_sold
  FETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;
• 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 WITH TIES ONLY;

正解：B

質問 # 105
Examine these SQL statements which execute successfully:

Which two statements are true after execution? (Choose two.)

• A. The foreign key constraint will be enabled and DEFERRED.
• B. The primary key constraint will be enabled and DEFERRED.
• C. The primary key constraint will be enabled and IMMEDIATE.
• D. The foreign key constraint will be disabled.
• E. The foreign key constraint will be enabled and IMMEDIATE.

正解：C、D

質問 # 106
View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)

You executed this SQL statement:
SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the result? (Choose two.)

• A. The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.
• B. The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
• C. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.
• D. The values in all columns would be returned in descending order.
• E. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.

正解：A、C

質問 # 107
View the Exhibit and examine the data in EMP and DEPT tables.

In the DEPT table, DEPTNO is the PRIMARY KEY.
In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing the DEPTNO column in the DEPT table.
What would be the outcome of the following statements executed in the given sequence?
DROP TABLE emp;
FLASHBACK TABLE emp TO BEFORE DROP;
INSERT INTO emp VALUES (2, 'SCOTT', 10);
INSERT INTO emp VALUES (3, 'KING', 55);

• A. Only the first INSERT statement would succeed because all constraints except the primary key constraint are automatically retrieved after a table is flashed back.
• B. Both the INSERT statements would fail because the constraints are automatically retrieved when the table is flashed back.
• C. Only the SECOND INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.
• D. Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back.

正解：C

質問 # 108
Evaluate the following query

What is the correct output of the above query?

• A. +25-00, +00-650, +00 11:12:10.123457
• B. +25-00, +54-02, +00 11:12:10.123457
• C. +00-300, +00-650, +00 11:12:10.123457
• D. +00-300, +54-02, +00 11:12:10.123457

正解：B

質問 # 109
Which three statements are true regarding subqueries? (Choose three.)

• A. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
• B. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.
• C. A subquery can be used in the FROM clause of a SELECT statement.
• D. The ORDER BY Clause can be used in a subquery.
• E. If a subquery returns NULL, the main query may still return rows.

正解：C、D、E

解説：
Explanation/Reference:

質問 # 110
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. INTERSECT
• B. SUBTRACT
• C. MINUS
• D. PLUS
• E. UNION

正解：C

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

質問 # 111
View the Exhibit and examine the structure of the PRODUCT_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_information;
• B. SELECT product_id
  FROM product_information
  WHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');
• C. SELECT product_id, UPPER(product_name)
  FROM product_information
  WHERE UPPER(product_name) = 'LASERPRO' OR list_price > 1000;
• D. SELECT UPPER(product_name)
  FROM product_information
  WHERE product_id = 2254;

正解：B

質問 # 112
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 multiple NVL functions are used in an expression.
• B. It gives an error because NVL function cannot be used with UPDATE.
• C. It executes successfully but no rows updated.
• D. It executes successfully and updates the records of those employees who have been working in the company for more than 600 days.

正解：D

質問 # 113
Which statement is true about TRUNCATE and DELETE?

• A. For large tables TRUNCATE is faster than DELETE.
• B. You can never DELETE rows from a table if foreign key constraints will be violated.
• C. You can never TRUNCATE a table if foreign key constraints will be violated.
• D. For tables with multiple indexes and triggers DELETE is faster than TRUNCATE.

正解：A

質問 # 114
In which three situations does a new transaction always start?

• A. When issuing a DML statement after a DML statement filed in the same session.
• B. When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session
• C. When issuing a CREATE INDEX statement after a CREATE TABLE statement completed unsuccessfully in the same session
• D. When issuing a TRUNCATE statement after a SELECT statement was issued in the same session
• E. When issuing the first Data Manipulation Language (OML) statement after a COMMIT or ROLLBACK statement was issued in the same session
• F. When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session

正解：B、C、E

質問 # 115
The first DROP operation is performed on PRODUCTS table using this command:
DROP TABLE products PURGE;
Then a FLASHBACK operation is performed using this command:
FLASHBACK TABLE products TO BEFORE DROP;
Which is true about the result of the FLASHBACK command?

• A. It recovers the table structure and data but not the related indexes.
• B. It recovers only the table structure.
• C. It is not possible to recover the table structure, data, or the related indexes.
• D. It recovers the table structure, data, and the indexes.

正解：C

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

質問 # 116
Examine the description of the CUSTOMERS table:

For customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.
Which query should be used?

• A. Option D
• B. Option E
• C. Option B
• D. Option C
• E. Option A

正解：E

質問 # 117
View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER_ID is the primary key in the ORDERS table and the foreign key of the ORDER_ITEMS table, whose constraint is defined with the ON DELETE CASCADE option.
Which DELETE statement would execute successfully?

• A. DELETE orders o, order_items iWHERE o.order_id = i.order_id;
• B. DELETEFROM ordersWHERE (SELECT order_idFROM order_items);
• C. DELETE ordersWHERE order_total < 1000;
• D. DELETE order_idFROM ordersWHERE order_total < 1000;

正解：C

質問 # 118
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_ID;
• C. ORDER BY 'b';
• D. ORDER BY DEPT_NAME;

正解：A、B

質問 # 119
Which three statements are true regarding group functions? (Choose three.)

• A. They can be used on only one column in the SELECTclause of a SQL statement.
• B. They can be used only with a SQL statement that has the GROUPBYclause.
• C. They can be passed as an argument to another group function.
• D. They can be used along with the single-row function in the SELECTclause of a SQL statement.
• E. They can be used on columns or expressions.

正解：C、D、E

解説：
Explanation/Reference:
References:
https://www.safaribooksonline.com/library/view/mastering-oracle-sql/0596006322/ch04.html

質問 # 120
Which two statements are true regarding the USING and ON clauses in table joins?

• A. Both USING and ON clauses can be used for equijoins and nonequijoins.
• B. A maximum of one pair of columns can be joined between two tables using the ON clause.
• C. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.
• D. The ON clause can be used to join tables on columns that have different names but compatible data types.

正解：C、D

質問 # 121
Which two statements will do an implicit conversion?

• A. SELECT * FROM customers WHERE TO_ CHAR (customer_ id) ='0001';
• B. SELECT * FROM customers WHERE insert_ date = DATE '2019-01-01';
• C. SELECT * FROM customers WHERE customer_ id = 0001 ;
• D. SELECT * FROM customers WHERE customer id = '0001';
• E. SELECT * FROM customers WHERE insert date ='01-JAN-19'

正解：D、E

質問 # 122
Which three statements are true about Data Manipulation Language (DML)?

• A. INSERT INTO...SELECT...FROM statements automatically commit.
• B. UPDATE statements can have different subqueries to specify the values for each updated column.
• C. DELETE statements can remove multiple rows based on multiple conditions.
• D. INSERT statements can insert NULLS explicitly into a column.
• E. DML statements require a primary key be defined on a table.

正解：B、C、D

質問 # 123
Which statement is true regarding the default behavior of the ORDER BYclause? (Choose two.)

• A. Column aliases can be used in the ORDER BY clause.
• B. NULLs are not included in the sort operation.
• C. Only columns that are specified in the SELECTlist can be used in the ORDER BYclause.
• D. In a character sort, the values are case-sensitive.
• E. Numeric values are displayed in descending order if they have decimal positions.

正解：A、D

質問 # 124
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit IN (1000, 2000, 3000);
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit = 1000 or cust_credit_limit = 2000 or
cust_credit_limit = 3000
Which statement is true regarding the above two queries?

• A. Performance would improve in query 2.
• B. Performance would degrade in query 2.
• C. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.
• D. There would be no change in performance.

正解：D

解説：
Explanation/Reference:
References:
http://oraclexpert.com/restricting-and-sorting-data/

質問 # 125
......

Oracleの1z1-071（Oracle Database SQL）認定試験は、73問からなる択一式試験です。候補者は100分間の時間を与えられ、少なくとも63％のスコアを取得する必要があります。試験は英語と日本語で利用可能であり、世界中の認定テストセンターで受験することができます。試験料は国や地域によって異なります。

 

最新（2023)Oracle 1z0-071試験問題集：https://www.passtest.jp/Oracle/1z0-071-shiken.html

1z0-071練習問題集で検証済みのPassTest更新された305問題あります：https://drive.google.com/open?id=1KGCx4lWHy2-Vz0Xk5_mu9Lu-fGOKlvfu