Oracle 1z0-071テストエンジン問題集トレーニングには305問あります [Q65-Q82]

Oracle 1z0-071テストエンジン問題集トレーニングには305問あります

1z0-071問題一発合格させる問題集はOracle PL/SQL Developer Certified Associate認定

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

正解：A

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

Examine the SQL statement:

Which statement is true about the outcome?

• A. It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
• B. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.
• C. It displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE.
• D. It displays details for members who have borrowed before today's date with either RM as TRANSACTION_TYPE or MEMBER_ID as A101 and A102.

正解：B

質問 # 67
Which statements are correct regarding indexes? (Choose all that apply.)

• A. For each DML operation performed, the corresponding indexes are automatically updated.
• B. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically attempts to creates a unique index.
• C. Indexes should be created on columns that are frequently referenced as part of any expression.
• D. When a table is dropped, the corresponding indexes are automatically dropped.

正解：A、B、D

解説：
References:
http://viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/

質問 # 68
View the exhibit and examine the structure of ORDERS and CUSTOMERS tables.

Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.

• A. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total)VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);
• B. INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND c.credit_limit=600)VALUES (1,'10-mar-2007', 'direct', (SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
• C. INSERT INTO ordersVALUES (1,'10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
• D. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total)VALUES (1,'10-mar-2007', 'direct', &&customer_id, 1000);

正解：C

質問 # 69
Examine the description of the ORDERStable:

Examine the description of the INVOICEStable:

Examine this query:

Which three rows will it return? (Choose three.)
5 01-MAR-2019

• A. 3 01-JAN-2019
• B. 2 <null>
• C. 4 01-FEB-2019
• D. 1 <null>
• E. 5 <null>
• F.
• G. 3 <null>

正解：E、F、G

質問 # 70
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

質問 # 71
You need to display the first names of all customers from the CUSTOMERStable that contain the character 'e' and have the character 'a' in the second last position.
Which query would give the required output?
SELECT cust_first_name

• A. FROM customers
  WHERE INSTR(cust_first_name, 'e')IS NOT NULL AND
  SUBSTR(cust_first_name, 1, -2)='a';
  SELECT cust_first_name
• B. FROM customers
  WHERE INSTR(cust_first_name, 'e')<>0 AND
  SUBSTR(cust_first_name, -2, 1)='a';
  SELECT cust_first_name
• C. FROM customers
  WHERE INSTR(cust_first_name, 'e')<>0 AND
  SUBSTR(cust_first_name, LENGTH(cust_first_name), -2)='a';
• D. FROM customers
  WHERE INSTR(cust_first_name, 'e')<>'' AND
  SUBSTR(cust_first_name, -2, 1)='a';
  SELECT cust_first_name

正解：B

解説：
Explanation

質問 # 72
Table HR. employees contains a row where the employee_id is 109.
User ALICE has no privileges to access HR.employees.
User ALICE starts a session.
User HR starts a session and successfully executes these statements:
GRANT DELETE ON employees to ALICE;
UPDATE employees SET salary = 24000 WHERE employee_id 109;
In her existing session ALICE then executes:
DELETE FROM HR.employees WHERE employee_ID =109;
What Is the result?

• A. The delete command will immediately return an error.
• B. The delete command will immediately delete the row.
• C. The delete command will wait for HR's transaction to end then return an error.
• D. The delete command will wait for HR's transaction to end them delete the row.

正解：D

質問 # 73
Evaluate the following SQL statement:
SELECT product_name || 'it's not available for order'
FROM product_information
WHERE product_status = 'obsolete';
You received the following error while executing the above query:
ERROR
ORA-01756: quoted string not properly terminated
What would you do to execute the query successfully?

• A. Use escape character to negate the single quotation mark inside the literal character string in the SELECTclause.
• B. Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.
• C. Enclose the literal character string in the SELECTclause within the double quotation marks.
• D. Do not enclose the character literal string in the SELECTclause within the single quotation marks.

正解：B

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

質問 # 74
Which statement is true regarding the INTERSECT operator?

• A. It ignores NULLs.
• B. The number of columns and data types must be identical for all SELECT statements in the query.
• C. The names of columns in all SELECT statements must be identical.
• D. Reversing the order of the intersected tables alters the result.

正解：B

解説：
INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.
The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.

質問 # 75
Examine these statements executed in a single Oracle session:
CREATE TABLE product (pcode NUMBER(2),pname VARCHAR2(20));
INSERT INTO product VALUES(1,'pen');
INSERT INTO product VALUES (2,'pencil');
INSERT INTO product VALUES(3,'fountain pen');
SAVEPOINT a;
UPDATE product SET pcode=10 WHERE pcode =1;
COMMIT;
DELETE FROM product WHERE pcode =2;
SAVEPOINT b;
UPDATE product SET pcode=30 WHERE pcode =3;
SAVEPOINT c;
DELETE FROM product WHERE pcode =10;
ROLLBACK TO SAVEPOINT b;
COMMIT;
Which three statements are true?

• A. There is no row containing pencil.
• B. The code for pen is 1.
• C. There is no row containing fountain pen.
• D. The code for pen is 10.
• E. There is no row containing pen
• F. The code for fountain pen is 3

正解：A、D、F

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

Examine the SQL statement:

Which statement is true about the outcome?

• A. It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
• B. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.
• C. It displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE.
• D. It displays details for members who have borrowed before today's date with either RM as TRANSACTION_TYPE or MEMBER_ID as A101 and A102.

正解：B

質問 # 77
What is true about non-equijoin statement performance?

• A. Table aliases can improve performance.
• B. The BETWEEN condition always performs less well than using the >= and <= conditions.
• C. The BETWEEN condition always performs better than using the >= and <= conditions.
• D. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
• E. The join syntax used makes no difference to performance.

正解：A、E

質問 # 78
Examine the command:

What does ON DELETE CASCADE imply?

• A. When the BOOKStable is dropped, all the rows in the BOOK_TRANSACTIONStable are deleted but the table structure is retained.
• B. When a row in the BOOKStable is deleted, the rows in the BOOK_TRANSACTIONStable whose BOOK_IDmatches that of the deleted row in the BOOKStable are also deleted.
• C. When a value in the BOOKS.BOOK_IDcolumn is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_IDcolumn.
• D. When the BOOKStable is dropped, the BOOK_TRANSACTIONStable is dropped.

正解：B

質問 # 79
Examine this SELECT statement and view the Exhibit to see its output: (Choose two.)

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?

• A. 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.
• B. In the second column, 'c' indicates a check constraint.
• C. The R_CONSTRAINT_NAME column contains an alternative name for the constraint.
• D. The STATUS column indicates whether the table is currently in use.

正解：A、B

質問 # 80
SCOTTis a user in the database.
Evaluate the commands issued by the DBA:

Which statement is true regarding the execution of the above commands?

• A. Statement 1 would not execute because the IDENTIFIED BY <password>clause is missing.
• B. Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANTstatement.
• C. Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANTcommand.
• D. Statement 1 would not execute because the WITH GRANToption is missing.

正解：C

質問 # 81
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.)

• A. CREATE TABLE employees(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));
• B. CREATE TABLE employees(employee_id NUMBER,login_id NUMBER,employee_name VARCHAR2(25),hire_date DATE,CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, login_id));
• C. CREATE TABLE employees(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));
• D. CREATE TABLE employees(employee_id NUMBER,login_id NUMBER,employee_name VARCHAR2(100),hire_date DATE,CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id));
• E. CREATE TABLE employees(employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY,login_id NUMBER UNIQUE,employee_name VARCHAR2(25),hire_date DATE);

正解：A、B

質問 # 82
......

1z0-071練習テストPDF試験材料：https://www.jpntest.com/shiken/1z0-071-mondaishu

1z0-071解答の無料サンプルには全てリアル試験に基づいています：https://drive.google.com/open?id=1WmOuUB-OsAjLZtejaS-phIU4D1EB-eSj