[2022年更新]1z0-071リアルな試験問題集で1z0-071練習テスト [Q110-Q134]

[2022年更新]1z0-071リアルな試験問題集で1z0-071練習テスト

1z0-071問題集でOracle PL/SQL Developer Certified Associate高確率練習問題集

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

トピック	出題範囲
トピック 1	Managing Objects with Data Dictionary Views Use the data dictionary views to research data on objects Query various data dictionary views
トピック 2	Apply general functions and conditional expressions in a SELECT statement Displaying Data from Multiple Tables
トピック 3	Using Conversion Functions and Conditional Expressions Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
トピック 4	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
トピック 5	Create and maintain indexes including invisible indexes and multiple indexes on the same columns Drop co
トピック 6	Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) functions in SELECT statements Reporting Aggregated Data Using the Group Functions
トピック 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	Using DDL Statements to Create and Manage Tables Describe data types that are available for columns Describe how schema objects work
トピック 9	Build a SELECT statement to retrieve data from an Oracle Database table Use the WHERE clause to the SELECT statement to filter query results
トピック 10	lumns and set column UNUSED Explain the theoretical and physical aspects of a relational database Use DDL to manage tables and their relationships
トピック 11	Include or exclude grouped rows by using the HAVING clause Describe the types of problems subqueries can solve Update and delete rows using correlated subqueries
トピック 12	Identify the connection between an ERD and a database using SQL SELECT statements Use the ORDER BY clause to sort SQL query results
トピック 13	Describe the different types of joins and their features Use joins to retrieve data from multiple tables

質問 110
Examine the description of the EMPLOYERS table:

Examine these requirements:
Disable the manager ID and salary of the lowest paid employees for that manager.
Exclude anyone whose manager is not known.
Exclude any managers where the minimum salary is 6000 or less.
Sort the output by minimum salary with the highest salary shown first.
Which statement will do this?

A. SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
AND MIN(salary) > 6000
GROUP BY manager_id
ORDER BY MlN (salary) DESC;
B. SELECT manager_id, HTN(salary)
FROM employees
WHERE manager_id IS NOT NULL
HAVING MIN (salary) > 6000
GROUP BY manager_id
ORDEU BY MIN (salary) DESC;
C. SELECT managar_id, MIN (salary)
FROM employees
HAVING MIN(salary) > 6000
WHERE manager_id IS NOT NULL
GROUP BY manager_id
ORDER BY MTN(salary) DESC;
D. SELECT manager_id, MTN(salary)
FROM employees
WHERE manager_ id IS NOT NULL
GROUP BY manager_id
HAVING MlN (salary) > 6000
ORDER BV MlN (salary);

正解: D

質問 111
You want to display the date for the first Monday of the next month and issue the following command:

What is the outcome?

A. In generates an error because TO_CHARshould be replaced with TO_DATE.
B. It executes successfully but does not return the correct result.
C. In generates an error because fmand double quotation marks should not be used in the format string.
D. In generates an error because rrrrshould be replaced by rrin the format string.
E. It executes successfully and returns the correct result.

正解: E

質問 112
View the exhibit and examine the structures of the EMPLOYEESand DEPARTMENTStables.

You want to update EMPLOYEEStable as follows:
Update only those employees who work in Boston or Seattle (locations 2900 and 2700).

Set department_idfor these employees to the department_idcorresponding to London

(location_id 2100).
Set the employees' salary in location_id2100 to 1.1 times the average salary of their department.

Set the employees' commission in location_id2100 to 1.5 times the average commission of their

department.
You issue the following command:

What is outcome?

A. It executes successfully but does not give the desired update
B. It executes successfully and gives the desired update
C. It generates an error because multiple columns (SALARY, COMMISSION)cannot be specified together in an UPDATEstatement.
D. It generates an error because a subquery cannot have a join condition in a UPDATEstatement.

正解: A

質問 113
Examine this SQL statement:

Which two are true? (Choose two.)

A. The DELETE statement executes successfully even if the subquery selects multiple rows.
B. The subquery is executed before the DELETEstatement is executed.
C. The subquery is executed for every row in the EMPLOYEEStable.
D. The subquery is not a correlated subquery.
E. All existing rows in the EMPLOYEEStable are deleted.

正解: B,C

質問 114
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:

You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses.
Which SQL statement must you use?

A. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=d. department_id)
B. SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id)
C. SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN .department_details d ON (c.depatrment_id=d.department_id)
D. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id<>d. department_id)

正解: A

質問 115
You want to display the date for the first Monday of the next month and issue the following command:
SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'),
'dd "is the first Monday for" fmmonth rrrr')
FROM DUAL;
What is the outcome?

A. In generates an error because TO_CHARshould be replaced with TO_DATE.
B. It executes successfully but does not return the correct result.
C. In generates an error because fmand double quotation marks should not be used in the format string.
D. In generates an error because rrrrshould be replaced by rrin the format string.
E. It executes successfully and returns the correct result.

正解: E

質問 116
Which two statements best describe the benefits of using the WITH clause? (Choose two.)

A. It enables sessions to store the results of a query permanently.
B. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.
C. It enables sessions to store a query block permanently in memory and use it to create complex queries.
D. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.

正解: B,D

質問 117
Examine the structure of the PROMOTIONS table: (Choose the best answer.)

Management requires a report of unique promotion costs in each promotion category.
Which query would satisfy this requirement?

A. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
B. SELECT promo_category, DISTINCT promo_cost FROM promotions
C. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1
D. SELECT DISTINCT promo_cost, promo_category FROM promotions

正解: C

質問 118
Which three privileges can be restricted to a subset of columns in a table? (Choose three.)

A. SELECT
B. DELETE
C. INDEX
D. INSERT
E. REFERENCES
F. UPDATE
G. ALTER

正解: A,B,F

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

正解: B

質問 120
Examine the description of the EMPLOYEES table:

Which query is valid?

A. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;
B. SELECT dept_id, AVG(MAX(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, MAX(AVG(salary)) FROM employees GROUP BY dept_id;

正解: A

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

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

正解: A,E

質問 122
Examine the structure of the members table:

What is the outcome?

A. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.
B. It fails because the alias name specified after the column names is invalid.
C. It executes successfully and displays the column details in a single column with only the alias column heading.
D. It fails because the space specified in single quotation marks after the first two column names is invalid.

正解: A

質問 123
Which two statements are true about single row functions?

A. FLOOR: returns the smallest integer greater than or equal to a specified number
B. CONCAT: can be used to combine any number of values
C. TRUNC: can be used with NUMBER and DATE values
D. MOD: returns the quotient of a division operation
E. CEIL: can be used for positive and negative numbers

正解: C,E

質問 124
Examine the structure of the MEMBERS table.
NameNull?Type
------------------------------------------------------------
MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
CITYVARCHAR2 (25)
STATENOT NULL VARCHAR2 (3)
Which query can be used to display the last names and city names only for members from the states MO and MI?

A. SELECT last_name, city FROM members WHERE state LIKE 'M%';
B. SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';
C. SELECT last_name, city FROM members WHERE state IN ('MO', 'MI');
D. SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';

正解: C

質問 125
Examine the structure of the PROGRAMS table:

Which two SQL statements would execute successfully?

A. SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;
B. SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;
C. SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), 'Ongoing') FROM programs
D. SELECT NVL (MONTHS_BETWEEN (start_date, end_date), 'Ongoing') FROM programs;

正解: B,C

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

正解: C,D

質問 127
Which three queries execute successfully?

A. SELECT (SYSDATE-DATE '2019-01-01') / 1 FROM DUAL;
B. SELECT SYSDATE - DATE '2019-01-01' - 1 FROM DUAL;
C. SELECT SYSDATE - 1 - DATE'2019-01-01' EROM DUAL;
D. SELECT 1 / SYSDATE - DATE '2019-01-01' FROM DUAL;
E. SELECT 1 - SYSDATE- DATE '2019-01-01' FROM DUAL;
F. SELECT SYSDATE / DATE '2019-01-01' - 1 FROM DUAL

正解: A,B,C

質問 128
Examine the structure of the PROMOTIONS table: (Choose the best answer.)

Management requires a report of unique promotion costs in each promotion category.
Which query would satisfy this requirement?

A. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
B. SELECT promo_category, DISTINCT promo_cost FROM promotions
C. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1
D. SELECT DISTINCT promo_cost, promo_category FROM promotions

正解: C

質問 129
Which two statements are true about the SET VERIFY ON command? (Choose two.)

A. It can be used only in SQL*Plus
B. It displays values for variables prefixed with &&
C. It can be used in SQL Developer and SQL*Plus
D. It displays values for variables used only in the WHERE clause of a query
E. It displays values for variables created by the DEFINE command

正解: A,E

質問 130
View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.
ORDERS_MASTER
ORDER_ID
ORDER_TOTAL
1
1000
2
2000
3
3000
4
MONTHLY_ORDERS
ORDER_ID
ORDER_TOTAL
2
2500
3
Evaluate the following MERGE statement:
MERGE_INTO orders_master o
USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total)
What would be the outcome of the above statement?

A. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.
B. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.
C. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.
D. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.

正解: D

解説:
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

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

You execute this SQL statement:

Which three statements are true?

A. The statement will execute successfully and a new row will be inserted into the SALES table.
B. A customer can exist in many countries.
C. The statement will fail because a subquery may not be contained in a VALUES clause.
D. The SALES table has five foreign keys.
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,D

質問 132
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 degrade in query 2.
B. Performance would improve 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
References:
http://oraclexpert.com/restricting-and-sorting-data/

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

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

正解: D

質問 134
......

1z0-071リアルな問題と知能問題集：https://www.goshiken.com/Oracle/1z0-071-mondaishu.html

合格できる1z0-071試験と最新1z0-071試験問題集PDF2022：https://drive.google.com/open?id=1gOYiAhTSzKyc6u24FGno4S4rGnLMHMX9

関するブログ

もっと

1z0-071無料問題集