[2023年更新]1z0-071まとめセット試験ガイド練習からあなたの1z0-071試験を合格させます！ [Q82-Q99]

[2023年更新]1z0-071まとめセット試験ガイド練習からあなたの1z0-071試験を合格させます！

準備1z0-071試験2023年最新のOracle PL/SQL Developer Certified Associate無制限305問題

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

トピック	出題範囲
トピック 1	Describe the different types of joins and their features Use joins to retrieve data from multiple tables
トピック 2	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
トピック 3	Identify the connection between an ERD and a database using SQL SELECT statements Use the ORDER BY clause to sort SQL query results
トピック 4	Using DDL Statements to Create and Manage Tables Describe data types that are available for columns Describe how schema objects work
トピック 5	Build a SELECT statement to retrieve data from an Oracle Database table Use the WHERE clause to the SELECT statement to filter query results
トピック 6	Managing Objects with Data Dictionary Views Use the data dictionary views to research data on objects Query various data dictionary views
トピック 7	Using Structured Query Language (SQL) Explain the relationship between a database and SQL
トピック 8	Using Conversion Functions and Conditional Expressions Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

質問 82
Examine the structure of the BOOKS_TRANSACTIONS table:
Examine the SQL statement:
Which statement is true about the outcome?

A. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.
B. It displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE.
C. It displays details only for members who have borrowed before today with RM as 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.

正解: A

質問 83
View the Exhibit and examine the structure of CUSTOMERStable.
Using the CUSTOMERStable, you need to generate a report that shows an increase in the credit limit by
15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?

A. SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT"
FROM customers;
B. SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;
C. SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;
D. SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"
FROM customers;

正解: B

質問 84
You are designing the structure of a table in which two columns have the specifications:
COMPONENT_ID - must be able to contain a maximum of 12 alphanumeric characters and uniquely identify the row
EXECUTION_DATETIME - contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons between components.
Which two options define the data types that satisfy these requirements most efficiently?

A. The EXECUTION_DATATIME must be of DATE data type.
B. The COMPONENT_ID must be of VARCHAR2 data type.
C. The COMPONENT_ID column must be of CHAR data type.
D. The EXECUTION _DATETIME must be of TIMESTAMP data type.
E. The COMPONENT_ID must be of ROWID data type.
F. The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.

正解: A,B

質問 85
View the Exhibit and examine the details of PRODUCT_INFORMATIONtable.

You have the requirement to display PRODUCT_NAMEfrom the table where the CATEGORY_IDcolumn has values 12or 13, and the SUPPLIER_IDcolumn has the value 102088. You executed the following SQL statement:
SELECT product_name
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?

A. It would not execute because the same column has been used in both sides of the ANDlogical operator to form the condition.
B. It would execute but the output would return no rows.
C. It would execute and the output would display the desired result.
D. It would not execute because the entire WHEREclause condition is not enclosed within the parentheses.

正解: B

質問 86
Examine this list of requirements for a sequence:
1. Name:EMP_SEQ
2. First value returned:1
3. Duplicates are never permitted.
4. Provide values to be inserted into the EMPLOYEES.EMPLOYEE_ID COLUMN.
5. Reduce the chances of gaps in the values.
Which two statements will satisfy these requirements?

A. CREATE SEQUENCE emp_seq START WITH 1 CACHE;
B. CREATE SEQUENCE emp_seq;
C. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;
D. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;
E. CREATE SEQUENCE emp_seq NOCACHE;
F. CREATE SEQUENCE emp_seq START WITH 1 INCRENENT BY 1 NOCACHE;

正解: E,F

質問 87
Which two are true about granting privileges on objects?

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

正解: A,C

質問 88
Examine the structure of the two tables.

Which two queries execute successfully? (Choose two.)

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

正解: A,D

質問 89
Examine the structure of the BOOKS_TRANSACTIONS table:

Examine the SQL statement:

Which statement is true about the outcome?

A. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.
B. It displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE.
C. It displays details only for members who have borrowed before today with RM as 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.

正解: A

質問 90
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 SALARY column would be returned in descending 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 ascending order for all employees having the same value in the DEPARTMENT_ID column.
C. 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.
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 and FIRST_NAME column.

正解: B,E

質問 91
Which three actions can you perform only with system privileges?

A. Query any table in a database.
B. Execute a procedure in another schema.
C. Use the WITH GRANT OPTION clause.
D. Truncate a table in another schema.
E. Log in to a database.
F. Access flat files via a database, which are stored in an operating system directory.

正解: A,C,E

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

正解: D

質問 93
Which two statements are true about views?

A. Views can be updated without the need to re-grant privileges on the view.
B. The with check clause prevents certain rows from being updated or inserted in the underlying table through the view.
C. Tables in the defining query of a view must always exist in order to create the view.
D. The with check clause prevents certain rows from being displayed when querying the view.
E. Views can be indexed.

正解: A,B

質問 94
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?
SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN

A. department_details d ON (c.department_id=d. department_id);
SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN
B. course_details c ON (c.department_id=d. department_id);
C. course_details c ON (d.department_id=c. department_id);
SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN
D. course_details c ON (d.department_id=c. department_id);
SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN

正解: D

質問 95
Examine the data in the CUST_NAMEcolumn of the CUSTOMERStable.

You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows:

Which two queries give the required output? (Choose two.)

A. SELECT LPAD(SUBSTR(cust_name, INSTR (cust_name ' ')),LENGTH(cust_name) - INSTR(cust_name, '
'), '*') "CUST NAME"
FROM customers
WHERE INSTR(cust_name, ' ',1,2)<>0;
B. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',-1,2)<>0;
C. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',1,2)<>0;
D. SELECT LPAD(SUBSTR(cust_name, INSTR (cust_name ' ')),LENGTH(cust_name) - INSTR(cust_name, '
'), '*') "CUST NAME"
FROM customers
WHERE INSTR(cust_name, ' ',1,-2)<>0;

正解: B,C

質問 96
Examine the command to create the BOOKS table.
SQL> create table books(book id CHAR(6) PRIMARY KEY,
title VARCHAR2(100) NOT NULL,
publisher_id VARCHAR2(4),
author_id VARCHAR2 (50));
The BOOK ID value 101 does not exist in the table.
Examine the SQL statement.
insert into books (book id title, author_id values
('101','LEARNING SQL','Tim Jones')

A. It executes successfully and the row is inserted with a null PLBLISHER_ID.
B. It executes successfully only NULL PUBLISHER_ID column name is added to the columns list in the INSERT statement.
C. It executes successfully onlyif NULL PUBLISHER ID column name is added to the columns list and NULL is explicitly specified In the INSERT statement.
D. It executes successfully only if NULL is explicitly specified in the INSERT statement.

正解: A

質問 97
Examine the structure of the EMPLOYEES table. (Choose the best answer.)

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.

A. 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. 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 ALL SELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)INETRSECTSELECT last_name, hire_date, salaryFROM employees WHERE hire_date > SYSDATE- 180;
D. (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;

正解: B

質問 98
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?