1z0-071のPDF問題集で2022年05月03日最近更新された問題 [Q91-Q112]

1z0-071のPDF問題集で2022年05月03日最近更新された問題

1z0-071試験問題有効な1z0-071問題集PDF

質問 91
View the exhibits and examine the structures of the COSTSand PROMOTIONStables.


Evaluate the following SQL statement:
SQL> SELECT prod_id FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions
WHERE promo_cost < ALL
( SELECT MAX(promo_cost) FROM promotions
GROUP BY (promo_end_date-
promo_begin_date)));
What would be the outcome of the above SQL statement?

• A. It displays prod IDs in the promos with the lowest cost in the same time interval.
• B. It displays prod IDs in the promos with the highest cost in the same time interval.
• C. It displays prod IDs in the promos which cost less than the highest cost in the same time interval.
• D. It displays prod IDs in the promo with the lowest cost.

正解: C

 

質問 92
Which two statements are true about * _TABLES views?

• A. ALL TABLES displays all tables owned by the current user.
• B. All users can query DBA_TABLES successfully.
• C. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in ALL_TABLES.
• D. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in USER_TABLES.
• E. USER TABLES displays all tables owned by the current user.
• F. You must have ANY TABLE system privileges, or be granted object privilges on the table, to viewa tabl e in DBA TABLES.

正解: E,F

 

質問 93
View the Exhibit and examine the description of the EMPLOYEEStable.

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?
SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct,

• A. salary,salary+commission_pct))"Total"
  FROM EMPLOYEES;
  SELECT first_name, salary, salary*12+salary*commission_pct "Total"
• B. FROM EMPLOYEES;
  SELECT first_name, salary, salary*12 + NVL(salary,0)*commission_pct, "Total"
• C. FROM EMPLOYEES;
• D. FROM EMPLOYEES;
  SELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12 "Total"

正解: A

 

質問 94
View the Exhibit and examine the description of the tables.

You execute this SQL statement:

Which three statements are true?

• A. The SALES table has five foreign keys.
• B. A customer can exist in many countries.
• C. The statement will execute successfully and a new row will be inserted into the SALES table.
• D. The statement will fail because a subquery may not be contained in a VALUES clause.
• E. The statement will fail if a row already exists in the SALES table for product 23.
• F. A product can have a different unit price at different times.

正解: A,B,C

 

質問 95
Examine the description of the PRODUCTStable:

Which three queries use valid expressions? (Choose three.)
SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;

• A. SELECT product_id, unit_price | | 5 "Discount", unit_price + surcharge -
• B. FROM products;
  SELECT product_id, unit_price, unit_price + surcharge FROM products;
• C. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
• D. SELECT product_id, expiry_date * 2 FROM products;
• E. discount FROM products;
  SELECT product_id, unit_price, 5 "Discount", unit_price + surcharge - discount

正解: B,C,D

解説:
Explanation

 

質問 96
Examine the following query:
SQL> SELECT prod_id, amount_sold
FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY;
What is the output of this query?

• A. It results in an error because the ORDER BYclause should be the last clause.
• B. It displays the first 5 percent of the rows from the SALEStable.
• C. It displays 5 percent of the products with the highest amount sold.
• D. It displays 5 percent of the products with the lowest amount sold.

正解: D

解説:
Explanation/Reference:
References:
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1

 

質問 97
View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
Exhibit

You must display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088.
You executed this SQL statement:

Which statement is true regarding the execution?

• A. It would execute and return the desired result.
• B. It would not execute because the entire WHERE clause is not enclosed within parentheses.
• C. It would not execute because the same column has been used twice with the AND logical operator.
• D. It would execute but would return no rows.

正解: D

 

質問 98
Examine the data in the CUST_NAME column of the CUSTOMERS table:

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 INITCAP (SUBSTR (cust_name, INSTR(cust_name, '')+1)) IN ('MC%', 'Mc%)
• B. WHERE INITCAP (SUBSTR (cust_name, INSTR(cust_name, '')+1)) LIKE 'Mc%'
• C. WHERE SUBSTR (cust_name, INSTR (cust_name, '') +1) LIKE 'Mc%'
• D. WHERE UPPER (SUBSTR (cust_name, INSTR(cust_name, '')+1)) LIKE UPPER ('MC%')
• E. WHERE SUBSTR (cust_name, INSTR (cust_name, '') +1) LIKE 'Mc%' OR 'MC%'

正解: C

 

質問 99
Examine the structure of the EMPLOYEES table.

You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.
Which query would retrieve the required result?

• A. SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180;
• B. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100')UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000;
• C. (SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180;
• D. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)UNION ALL(SELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000);

正解: B

 

質問 100
Which two tasks can be performed by using Oracle SQL statements? (Choose two.)

• A. changing the password for an existing database user
• B. querying data from tables in different databases
• C. starting up a database instance
• D. executing operating system (OS) commands in a session
• E. connecting to a database instance

正解: A,B

解説:
Explanation/Reference:
References:
http://www.techonthenet.com/oracle/password.php
https://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm

 

質問 101
Which two are the minimal requirements for a self-join? (Choose two.)

• A. Outer joins must not be used in the query.
• B. The table used for the self-join must have two different alias names in the query.
• C. Only equijoin conditions may be used in the query.
• D. No other condition except the self-join may be specified.
• E. There must be a condition on which the self-join is performed.

正解: B,E

 

質問 102
View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.

You wrote this SQL statement to retrieve EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, for all employees:
SELECT employee_id, first_name, department_name
FROM employees
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?

• A. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.
  Natural join needs only one column to be the same in each table. The EMPLOYEES and DEPARTMENTS tables have two columns that are the same (Department_ID and Manager_ID)
• B. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
• C. The table prefix is missing for the column names in the SELECT clause.
• D. The NATURAL JOIN clause is missing the USING clause.

正解: A

 

質問 103
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. DELETEFROM ordersWHERE (SELECT order_idFROM order_items);
• B. DELETE order_idFROM ordersWHERE order_total < 1000;
• C. DELETE ordersWHERE order_total < 1000;
• D. DELETE orders o, order_items iWHERE o.order_id = i.order_id;

正解: C

 

質問 104
Which statement is true regarding the INTERSECT operator?

• A. Reversing the order of the intersected tables alters the result.
• 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. It ignores NULLs.

正解: B

解説:
Explanation
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.

 

質問 105
Which three statements are true about defining relations between tables in a relational database?

• A. Primary key columns allow null values.
• B. Every foreign key value must refer to a matching primary or unique key value.
• C. Foreign key columns allow null values.
• D. Unique key columns allow null values.
• E. Every primary or unique key value must refer to a matching foreign key value.

正解: B,C

 

質問 106
Examine the description of the PRODUCT_DETAILS table:

Which two statements are true?

• A. PRODUCT_PRICE contains the value zero by default if no value is assigned to it.
• B. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.
• C. PRODUCT_NAME cannot contain duplicate values.
• D. PRODUCT_ID can be assigned the PRIMARY KEY constraint.
• E. EXPIRY_DATE cannot be used in arithmetic expressions.
• F. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it.

正解: C,E

 

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

正解: B

 

質問 108
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 CUST_CREDIT_VALUEcolumn would allow storage of positive and negative integers.
• B. The CUST_STATUScolumn would allow storage of data up to the maximum VARCHAR2size of 4,000 characters.
• C. The TRANS_DATEcolumn would allow storage of dates only in the dd-mon-yyyy format.
• D. The TRANS_VALIDITYcolumn would allow storage of a time interval in days, hours, minutes, and seconds.

正解: A,D

 

質問 109
Which two are true about granting privilege on objects?

• A. The owner of an object acquires all object privilege on that object by default.
• B. A table owner must grant the references privilege to allow other users to create FOREIGN KEY constraints using that table.
• C. An object privilege can be granted to a role only by the owner of that object.
• D. An object privilege can be granted to other users only by the owner of object.
• E. The WITH GRANT OPTION clause can be used only by DBA users.

正解: A,B

 

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

正解: A

 

質問 111
Which two statements are true about Data Manipulation Language (DML) statements? (Choose two.)

• A. A DELETE FROM..... statement can remove rows based on only a single condition on a table.
• B. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.
• C. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.
• D. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.
• E. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
• F. An UPDATE....SET.... statement can modify multiple rows based on only a single condition on a table.

正解: D,E

解説:
Explanation
References:
http://www.techonthenet.com/sql/and_or.php

 

質問 112
......

Oracle 1z0-071 認定試験の出題範囲：

トピック	出題範囲
トピック 1	Use the EXISTS and NOT EXISTS operators Use single-row and multiple-row subqueries Create simple and complex views with visible invisible columns Create, maintain and use sequences
トピック 2	Describe the different types of joins and their features Use joins to retrieve data from multiple tables
トピック 3	Build a SELECT statement to retrieve data from an Oracle Database table Use the WHERE clause to the SELECT statement to filter query results
トピック 4	lumns and set column UNUSED Explain the theoretical and physical aspects of a relational database Use DDL to manage tables and their relationships
トピック 5	Managing Objects with Data Dictionary Views Use the data dictionary views to research data on objects Query various data dictionary views
トピック 6	Using Data Manipulation Language (DML) and Transaction Control Language (TCL) Describe the purpose of DML
トピック 7	View data that generally does not meet a join condition by using outer joins Use a set operator to combine multiple queries into a single query
トピック 8	Use SELECT statements to access data from more than one table using equijoins and nonequijoins Join a table to itself by using a self-join
トピック 9	Using Conversion Functions and Conditional Expressions Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
トピック 10	Identify the connection between an ERD and a database using SQL SELECT statements Use the ORDER BY clause to sort SQL query results
トピック 11	Using DDL Statements to Create and Manage Tables Describe data types that are available for columns Describe how schema objects work
トピック 12	Using Structured Query Language (SQL) Explain the relationship between a database and SQL
トピック 13	Apply general functions and conditional expressions in a SELECT statement Displaying Data from Multiple Tables
トピック 14	Create and maintain indexes including invisible indexes and multiple indexes on the same columns Drop co
トピック 15	Limit and Sort the rows that are retrieved by a query Use ampersand substitution to restrict and sort output at runtime Use various types of functions available in SQL
トピック 16	Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) functions in SELECT statements Reporting Aggregated Data Using the Group Functions

 

1z0-071問題集合格確定させる練習には305問があります：https://jp.fast2test.com/1z0-071-premium-file.html

1z0-071練習テスト問題解答更新された305問があります：https://drive.google.com/open?id=1MD7qtgGXb3TEby_dB_UNZXjH90DQG6NP