[2023年更新]1z0-071試験問題集でテストエンジン練習テスト問題
合格できる1z0-071試験[2023年11月06日]最新305問題
質問 # 82
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 SELECT clause.
- B. Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.
- C. Do not enclose the character literal string in the SELECT clause within the single quotation marks.
- D. Enclose the literal character string in the SELECT clause within the double quotation marks.
正解：B
解説：
Explanation
References:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm
質問 # 83
You need to produce a report where each customer's credit limit has been incremented by $1000. In the output, the customer's last name should have the heading Nameand the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase.
Which statement would accomplish this requirement?
SELECT cust_last_name AS "Name", cust_credit_limit + 1000
- A. "New Credit Limit"
FROM customers;
SELECT INITCAP (cust_last_name) "Name", cust_credit_limit + 1000 - B. AS New Credit Limit
FROM customers;
SELECT cust_last_name AS Name, cust_credit_limit + 1000 - C. INITCAP ("NEW CREDIT LIMIT")
FROM customers; - D. AS "New Credit Limit"
FROM customers;
SELECT cust_last_name AS Name, cust_credit_limit + 1000
正解：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 NVL2(salary)+commission,salary+commission,
- B. SELECT * FROM employees WHERE NVL(salary+commission,0)>==20000;
- C. SELECT * FROM employees WHERE NVL(salary+commission,0)>=20000;
- D. SELECT * FROM employees WHERE salary+NVL(commission,0)>=20000;
- E. SELECT * FROM employees WHERE salary+NULLF(commission,0)>=20000;
- F. SELECT * FROM employees WHERE salary+NVL2(commission,commission,0)>=20000;
正解：A、D、F
質問 # 85
Evaluate the following SQL statement:
SQL> select cust_id, cust_last_name "Last name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30
Identify three ORDER BY clauses either one of which can complete the query. (Choose three.)
- A. ORDER BY CUST_NO
- B. ORDER BY "Last name"
- C. ORDER BY 2, cust_id
- D. ORDER BY "CUST_NO"
- E. ORDER BY 2,1
正解：B、C、E
解説：
Explanation
Using the ORDER BY Clause in Set Operations
-The ORDER BY clause can appear only once at the end of the compound query.
-Component queries cannot have individual ORDER BY clauses.
-The ORDER BY clause recognizes only the columns of the first SELECT query.
-By default, the first column of the first SELECT query is used to sort the output in an ascending order.
質問 # 86
Which task can be performed by using a single Data Manipulation Language (DML) statement?
- A. removing all data only from a single column on which a primary key constraint is defined
- B. adding a column with a default value while inserting a row into a table
- C. adding a column constraint while inserting a row into a table
- D. removing all data only from a single column on which a unique constraint is defined
正解：D
質問 # 87
See the Exhibit and examine the structure of the PROMOTIONS table:
Using the PROMOTIONS table,
you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in
category A.
You issue the following SQL statements:
What would be the outcome?
- A. It generates an error because CASE cannot be used with group functions.
- B. It generates an error because multiple conditions cannot be specified for the WHEN clause.
- C. It executes successfully and gives the required result.
- D. It generates an error because NULL cannot be specified as a return value.
CASE Expression
Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
正解：C
質問 # 88
In which three situations does a transaction complete? (Choose three.)
- A. when a data definition language (DDL) statement is executed
- B. when a PL/SQL anonymous block is executed
- C. when a TRUNCATE statement is executed after the pending transaction
- D. when a ROLLBACK command is executed
- E. when a DELETE statement is executed
正解：A、C、D
解説：
Explanation
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm
質問 # 89
Which three statements are true about sequences in a single instance Oracle database? (Choose three.)
- A. A sequence's unallocated cached value are lost if the instance shuts down
- B. Two or more tables cannot have keys generated from the same sequence
- C. Sequences can always have gaps
- D. A sequence can only be dropped by a DBA
- E. A sequence can issue duplicate values
- F. A sequence number that was allocated can be rolled back if a transaction fails
正解：A、D、F
解説：
Explanation/Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6015.htm
質問 # 90
Examine the description of the EMP_DETAILS table given below:
Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL TABLE?
- A. An EMP_IMAGE column can be included in the GROUP BY clause.
- B. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.
- C. An EMP_IMAGE column cannot be included in the ORDER BY clause.
- D. You cannot add a new column to the table with LONG as the data type.
正解：C、D
質問 # 91
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
質問 # 92
Examine the commands used to create DEPARTMENT_DETAILSand COURSE_DETAILS tables:
You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?
- A. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c. department_id);
- B. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c. department_id);
- C. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d. department_id);
- D. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d. department_id);
正解：A
質問 # 93
Examine these statements which execute successfully:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYY HH24 :MT:SS;'
ALTER SESSION SET TIME-ZONE = '-5:00;:
Examine the result:
If LOCALTIMESTAMP was selected at the same time, what would it return?
- A. 11-JUL-2019 6.00.00.00000000 AM
- B. 11-JUL-2019 11.00.00.00000000 AM
- C. 11-JUL_2019 6.00.00.00000000 AM -05:00
- D. 11-JUL_2019 11.00.00.00000000 AM -05:00
正解：A
質問 # 94
View the Exhibit and examine the description of the EMPLOYEES table.
You want to calculate the total remuneration for each employee. Total remuneration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.
Which SQL statement would you execute to get the desired output?
- A. SELECT first_name, salary, salary*12 + NVL(salary,0)*commission_pct, "Total"FROM EMPLOYEES;
- B. SELECT first_name, salary, salary*12+salary*commission_pct "Total"FROM EMPLOYEES;
- C. SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct,
salary,salary+commission_pct))"Total"FROM EMPLOYEES; - D. SELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12 "Total"FROM EMPLOYEES;
正解：C
質問 # 95
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
(Choose the best answer.)
You executed this UPDATE statement:
UPDATE
( SELECT order_date, order_total, customer_id FROM orders)
Set order_date = '22-mar-2007'
WHERE customer_id IN
(SELECT customer_id FROM customers
WHERE cust_last_name = 'Roberts' AND credit_limit = 600);
Which statement is true regarding the execution?
- A. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
- B. It would not execute because two tables cannot be referenced in a single UPDATE statement.
- C. It would not execute because a SELECT statement cannot be used in place of a table name.
- D. It would execute and restrict modifications to the columns specified in the SELECT statement.
正解：D
質問 # 96
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')<>0 AND
SUBSTR(cust_first_name, -2, 1)='a';
SELECT cust_first_name - B. FROM customers
WHERE INSTR(cust_first_name, 'e')<>'' AND
SUBSTR(cust_first_name, -2, 1)='a';
SELECT cust_first_name - C. FROM customers
WHERE INSTR(cust_first_name, 'e')IS NOT NULL AND
SUBSTR(cust_first_name, 1, -2)='a';
SELECT cust_first_name - D. FROM customers
WHERE INSTR(cust_first_name, 'e')<>0 AND
SUBSTR(cust_first_name, LENGTH(cust_first_name), -2)='a';
正解：A
解説：
Explanation
質問 # 97
Which two statements are true regarding the DELETE and TRUNCATE commands?
- A. DELETE can be used to remove rows only for tables that are parents for a child table that has a referential integrity constraint referring to the parent.
- B. DELETE can be used to remove rows from only one table in one statement.
- C. DELETE can be used to remove data from specific columns as well as complete rows.
- D. DELETE and TRUNCATE can be used for tables that are parents for a child table that has a referential integrity constraint having an ON DELETE rule.
- E. DELETE can be used to remove rows from multiple tables in one statement.
正解：B、D
質問 # 98
Which two statements are true about the rules of precedence for operators?
- A. NULLS influence the precedence of operators in an expression.
- B. Multiple parentheses can be used to override the default precedence of operators in an expression.
- C. The + binary operator has the highest precedence in an expression in a SQL statements.
- D. Arithmetic operators with equal precedence are evaluated from left to right within an expression.
- E. The concatenation operator || is always evaluated before addition and subtraction in an expression.
正解：B、D
質問 # 99
......
Oracle 1z0-071リアルな2023年最新の知能問題集模擬試験問題集：https://www.jpntest.com/shiken/1z0-071-mondaishu
Oracle 1z0-071リアルな問題と100%カバーリアルな試験問題：https://drive.google.com/open?id=1QRiLm8y47KU9HgflNteOT_6Io9Ltv_0F