オンライン問題で最適な1z0-071試験練習問題（最新の325問題) [Q165-Q187]

オンライン問題で最適な1z0-071試験練習問題（最新の325問題)

練習問題1z0-071素晴らしい練習用のOracle Database SQLテスト問題

質問 # 165
Examine the description of the CUSTOMERS table:

You need to display last names and credit limits of all customers whose last name starts with A or B In lower or upper case, and whose credit limit is below 1000.
Examine this partial query:
SELECT cust_last_nare, cust_credit_limit FROM customers
Which two WHERE conditions give the required result?

A. WHERE (UPPER(cust_last_name) LIKE 'A%' OR UPPER(cust_last_name) LIKE 'B%') AND ROUND(cust_credit_limit) < 1000;
B. WHERE (UPPER(cust_last_name) like INITCAP ('A') OR UPPER(cust_last_name) like INITCAP('B')) AND ROUND(cust_credit_limit) < ROUND(1000) ;
C. WHERE UPPER(cust_last_name) BETWEEN UPPER('A%' AND 'B%') AND ROUND(cust_credit_limit) < 1000;
D. WHERE UPPER(cust_last_name) IN ('A%', 'B%') AND cust_credit_limit < 1000:
E. WHERE (INITCAP(cust_last_name) LIKE 'A%' OR ITITCAP(cust_last_name) LIKE 'B%') AND cust_credit_limit < 1000

正解：A、E

質問 # 166
You execute this query:

What is the result?

A. It returns the date for the first Monday of the next month.
B. It returns the date for the last Monday of the current month.
C. It executes successfully but does not return any result.
D. It generates an error.

正解：A

質問 # 167
What is the primary difference between the relational database (RDB) and object-oriented database (OODB) models?

A. RDB supports only E.F. Codd's rules, whereas OODB does not support them.
B. OODB incorporates methods with data structure definition, whereas RDB does not allow this.
C. OODB supports multiple objects in the same database, whereas RDB supports only tables.
D. RDB allows the definition of relationships between different tables, whereas OODB does not allow this.

正解：B

解説：
Explanation/Reference:

質問 # 168
Which two queries execute successfully?

A. SELECT NULLIF(100, 100) FROM DUAL;
B. SELECT COALESCE(100, 'A' ) FROM DUAL;
C. SELECT COALESCE(100, NULL, 200) FROM DUAL;
D. SELECT NULLIF(100, 'A') FROM DUAL;
E. SELECT NULLIF(NULL, 100) FROM DUAL;

正解：A、C

質問 # 169
Examine the types and examples of relationships that follow:

Which option indicates correctly matched relationships? (Choose the best answer.)

A. 1-a, 2-b, 3-c, and 4-d
B. 1-c, 2-a, 3-b, and 4-d
C. 1-d, 2-b, 3-a, and 4-c
D. 1-c, 2-d, 3-a, and 4-b

正解：A

質問 # 170
In which three situations does a new transaction always start?

A. When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session
B. When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session
C. When issuing a TRUNCATE statement after a SELECT statement was issued in the same session
D. When issuing a CREATE INDEX statement after a CREATE TABLE statement completed unsuccessfully in the same session
E. When issuing a DML statement after a DML statement filed in the same session.
F. When issuing the first Data Manipulation Language (OML) statement after a COMMIT or ROLLBACK statement was issued in the same session

正解：A、C、F

解説：
Substitution variables in Oracle are used to replace a value dynamically during the execution of SQL statements. The behavior of these variables is well-documented:
C . A substitution variable prefixed with & always prompts only once for a value in a session: This is true. In a session, when you use a single ampersand (&), SQL*Plus or SQL Developer will prompt for the value the first time the variable is encountered. The value for this variable will then be reused for the remainder of the session unless it is redefined.
D . A substitution variable can be used with any clause in a SELECT statement: Substitution variables can be placed in any part of a SQL statement, including the SELECT, WHERE, GROUP BY, ORDER BY, etc. They are not limited to any specific clause.
Reference:
Oracle SQL*Plus User's Guide and Reference, which discusses substitution variables.

質問 # 171
Which three statements are true regarding indexes?

A. A SELECT statement can access one or more indices without accessing any tables.
B. A UNIQUE index can be altered to be non-unique.
C. When a table is dropped and is moved to the RECYCLE BIN, all Indexes built on that table are permanently dropped.
D. An update to a table can result in no updates to any of the table's indexes.
E. A table belonging to one user can have an index that belongs to a different user,
F. An update to a table can result in updates to any or all of the table's indexes.

正解：A、B、F

解説：
Indexes are structures that can improve the retrieval time of data from a database table and have certain characteristics:
* A. A SELECT statement can access one or more indices without accessing any tables: If the index contains all the columns needed for the query, Oracle can retrieve the data from the index alone without accessing the table. This is known as an index-only scan.
* D. A UNIQUE index can be altered to be non-unique: It is possible to alter a unique index to become non-unique by using the ALTER INDEX command.
* F. An update to a table can result in updates to any or all of the table's indexes: If the updated column is part of one or more indexes, those indexes will need to be updated to reflect the change. If the updated column is not part of an index, then no index update is required.
References:
* Oracle Database SQL Language Reference 12c, especially sections on index management and the behavior of DML operations with respect to indexes.

質問 # 172
Examine the data in the PRODUCTS table:

Examine these queries:
1. SELECT prod name, prod list
FROM products
WHERE prod 1ist NOT IN(1020) AND category _id=1;
2. SELECT prod name, | prod _ list
FROM products
WHERE prod list < > ANY (1020) AND category _id= 1;
SELECT prod name, prod _ list
FROM products
WHERE prod_ list <> ALL (10 20) AND category _ id= 1;
Which queries generate the same output?

A. 1, 2 and 3
B. 2 and 3
C. 1 and 3
D. 1 and 2

正解：C

質問 # 173
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 the Oracle (q) operator and delimiter to allow the use of a single quotation mark within the literal character string in the SELECT clause
B. Use the escape character to negate the single quotation mark within the literal character string in the SELECT clause
C. Enclose the character literal string in the SELECT clause within double quotation marks
D. Remove the single quotation marks enclosing the character literal string in the SELECT clause

正解：A

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

質問 # 174
View the Exhibit and examine PRODUCTSand ORDER_ITEMStables.

You executed the following query to display PRODUCT_NAMEand the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;
What would happen when the above statement is executed?

A. The statement would not execute because inline views and outer joins cannot be used together.
B. The statement would not execute because the GROUP BYclause cannot be used in the inline.
C. The statement would not execute because the ITEM_CNTalias cannot be displayed in the outer query.
D. The statement would execute successfully to produce the required output.

正解：D

質問 # 175
Examine the description of the PRODCTS table which contains data:

Which two are true?

A. The PROD_ ID column data type can be changed to VARCHAR2 (2).
B. The PROD NAME column cannot have a DEFAULT clause added to it.
C. The EXPIRY DATE column cannot be dropped.
D. The PROD ID column can be renamed.
E. The EXPIRY DATE column data type can be changed to TIME STAMP.

正解：D、E

質問 # 176
Which three privileges can be restricted to a subset of columns in a table?

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

正解：A、F、G

質問 # 177
Which two join conditions in a from clause are non-equijoins?

A. table1 natural JOIN table2
B. tablet join table2 ON (table1.column = table2.column) where table2.column LIKE 'A'
C. table1 join table2 on (table1.column between table2.column] and table2.column2)
D. table1 join table2 using (column1, column2)
E. table1 join table2 ON (table1.column >= table2.column)

正解：C、E

解説：
Equijoins are joins that use the equality operator (=) to match rows. Non-equijoins are joins that use operators other than the equality operator.
A . False. This condition is an equijoin as it uses the equality operator (=).
B . True. This join condition uses the BETWEEN operator, which is not based on equality.
C . False. A natural join is a type of equijoin where the join is made on all columns with the same names in both tables.
D . False. USING clause is used to specify an equijoin on one or more columns.
E . True. This join condition uses the greater than or equal to (>=) operator, making it a non-equijoin.
Reference:
Oracle Documentation on Joins: https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52358

質問 # 178
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. An EMP_IMAGE column cannot be included in the ORDER BY clause.
C. You cannot add a new column to the table with LONG as the data type.
D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.

正解：B、C

質問 # 179
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 NOCACHE;
B. CREATE SEQUENCE emp_seq START WITH 1 INCRENENT BY 1 NOCACHE;
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 START WITH 1 CACHE;
F. CREATE SEQUENCE emp_seq;

正解：A、B

質問 # 180
These are the steps for a correlated subquery, listed in random order:
* The WHERE clause of the outer query is evaluated.
* A candidate row is fetched from the table specified in the outer query.
* This is repeated for the subsequent rows of the table, until all the rows are processed.
* Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.
Which is the correct sequence in which the Oracle server evaluates a correlated subquery?

A. 2, 4, 1, 3
B. 2, 1, 4, 3
C. 4, 1, 2, 3
D. 4, 2, 1, 3

正解：A

解説：
Explanation
http://rajanimohanty.blogspot.co.uk/2014/01/correlated-subquery.html

質問 # 181
Which two statements cause changes to the data dictionary?

A. AITER SESSION set NLs. _DATE FORMAT = 'DD/MM/YYYY';
B. TRUNCATE TABLE emp:
C. DELETE FROM scott. emp;
D. SELECT * FROM user_ tab._ privs;
E. GRANT UPDATE ON scott. emp TO fin manager;

正解：B、E

解説：
The data dictionary is a read-only set of tables that provides information about the database. Certain operations that modify the database structure will cause changes to the data dictionary:
* Option B: GRANT UPDATE ON scott.emp TO fin_manager;
* Granting privileges on a table will change the data dictionary as it records the new privilege.
* Option D: TRUNCATE TABLE emp;
* Truncating a table affects the data dictionary because it removes all rows from a table and may also reset storage parameters.
Options A, C, and E do not cause changes to the data dictionary:
* Option A is incorrect because the DELETE command modifies data, not the data dictionary structure.
* Option C is incorrect because altering a session parameter does not change the data dictionary; it is a temporary change for the session.
* Option E is incorrect because a SELECT query does not change the database, it just retrieves information.

質問 # 182
View the Exhibit and examine the description of the ORDERS table.

Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)

A. WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')
B. WHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003'
C. WHERE order_date_IN ( TO_DATE('OCT 21 2003','MON DD YYYY'), TO_CHAR('NOV 21
2003','MON DD YYYY') )
D. WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY')

正解：B、D

質問 # 183
Which three statements are true about views in an Oracle database?

A. Tables in the defining query of a view must always exist in order to create the view.
B. The WITH CHECK clause prevents certain rows from being displayed when querying the view.
C. Date Manipulation Language (DML) can always be used on views.
D. The WITH CHECK clause prevents certain rows from being updated or inserted.
E. Deleting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error.
F. Views can be updated without the need to re-grant privileges on the view.
G. Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error.

正解：D、E、F

質問 # 184
Which three statements are true about performing DML operations on a view with no INSTEAD OF triggers defined?

A. Views cannot be used to add or modify rows in an underlying table If the defining query of the view contains the DISTINCT keyword.
B. Insert statements can always be done on a table through a view.
C. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the primary key columns are not referenced in the defining query of the view.
D. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
E. Views cannot be used to add rows to an underlying table If the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
F. Delete statements can always be done on a table through a view.

正解：A、E

解説：
* A: Insert statements can be done through a view only if all NOT NULL constraints without default values of the base table are included in the view. Therefore, statement A is incorrect.
* B: The WITH CHECK OPTION ensures that all DML operations performed through the view result in data that conforms to the view's defining query. It affects DELETE as well as other DML operations, making statement B incorrect.
* C: Similar to inserts, DELETE operations can be done through a view unless the view contains constructs that inherently do not support it, such as certain joins or set operations. Statement C is generally incorrect.
* D: If a view does not include all NOT NULL columns without defaults of the underlying table, it cannot be used to add rows because the missing columns will lack values. This makes statement D correct.
* E: This statement is incorrect as primary keys do not affect querying through views; they affect insert and update operations where uniqueness and non-nullability are enforced.
* F: If the defining query of a view includes DISTINCT, the view generally cannot be used to perform update or insert operations as it may not be able to uniquely determine rows. This makes statement F correct.

質問 # 185
You own table DEPARTMENTS, referenced by views, indexes, and synonyms.
Examine this command which executes successfully:
DROP TABLE departments PURGE;
Which three statements are true?

A. It will remove all synonyms for the DEPARTMENTS table.
B. It will remove all views that are based on the DEPARTMENTS table.
C. Neither can it be rolled back nor can the DEPARTMENTS table be recovered.
D. It will delete all rows from the DEPARTMENTS table, but retain the empty table.
E. It will remove the DE PARTMENTS table from the database.
F. It will drop all indexes on the DEPARTMENTS table.

正解：C、E、F

質問 # 186
Which two statements are true regarding subqueries? (Choose two.)

A. A subquery can be used only in SQL query statements.
B. Only two subqueries can be placed at one level.
C. A subquery can appear on either side of a comparison operator.
D. A subquery can retrieve zero or more rows.
E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

正解：C、D

質問 # 187
......

リアルな1z0-071試験別格な練習試験問題：https://jp.fast2test.com/1z0-071-premium-file.html

100%合格率でリアルな1z0-071試験成功ゲット：https://drive.google.com/open?id=1MD7qtgGXb3TEby_dB_UNZXjH90DQG6NP