1z0-071 Dumps (2023) Prepare Your Exam With 305 Questions [Q164-Q179] : Actual Test Materials : http://blog.actualtests4sure.com

Q164. Which two are SQL features?

providing graphical capabilities

providing variable definition capabilities.

providing database transaction control

processing sets of data

providing update capabilities for data in external files

Q165. Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

The outer query stops evaluating the result set of the inner query when the first value is found.

It is used to test whether the values retrieved by the inner query exist in the result of the outer query.

It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.

The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Q166. 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?

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1

SELECT promo_category, DISTINCT promo_cost FROM promotions

SELECT DISTINCT promo_cost, promo_category FROM promotions

SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;

Q167. The CUSTOMERStable has a CUST_CREDIT_LIMITcolumn of data type NUMBER.
Which two queries execute successfully? (Choose two.)

SELECT NVL(cust_credit_limit * .15, ‘Not Available’) FROM customers;

SELECT TO_CHAR(NVL(cust_credit_limit * .15, ‘Not Available’)) FROM customers;

SLECT NVL(TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;

SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), ‘Not

Available’) FROM customers;
SELECT NVL2(cust_credit_limit * .15, ‘Not Available’) FROM customers;

Q168. You executed the following CREATE TABLE statement that resulted in an error:
SQL> CREATE TABLE employees(emp_id NUMBER(10) PRIMARY KEY, ename VARCHAR2(20), email NUMBER(3) UNIQUE, address VARCHAR2(500), phone VARCHAR2(20), resume LONG, hire_date DATE, remarks LONG, dept_id NUMBER(3) CONSTRAINT emp_dept_id_fk REFERENCES departments (dept_id), CONSTRAINT ename_nn NOY NULL(ename)); Identify two reasons for the error.

The NOT NULLconstraint on the ENAMEcolumn must be defined as the column level

FOREIGN KEYdefined on the DEPT_IDcolumn must be at the table level only

Only one LONGcolumn can be used per table

The FOREIGN KEYkeyword is missing in the constraint definition

The PRIMARY KEYconstraint in the EMP_IDcolumn must have a name and must be defined at the table level only

Q169. Which two are true about savepoints?

After issuing a savepoint, you cannot roll back the complete transaction.

You can commit updates done between two savepoints without committing other updates In the current transaction.

After issuing a savepoint, you can roll back to the savepoint name within the current transaction.

They make uncommitted updates visible to other sessions owned by the? same user.

A rollback to savepoint command issued before the start of a transaction results In an error.

They make uncommitted updates visible to sessions owned by other users.

Q170. View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

You executed the following query to display PRODUCT_NAME and 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?

The statement would execute successfully to produce the required output.

The statement would not execute because inline views and outer joins cannot be used together.

The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

The statement would not execute because the GROUP BY clause cannot be used in the inline.

Q171. Which two statements are true regarding constraints? (Choose two.)

A table can have only one primary key and one foreign key.

A table can have only one primary key but multiple foreign keys.

Only the primary key can be defined at the column and table levels.

The foreign key and parent table primary key must have the same name.

Both primary key and foreign key constraints can be defined at both column and table levels.

Q172. View the Exhibit and examine the data in the employees table.
You want to generate a report showing the total compensation paid to each employee to date.
You issue the following query:
What is the outcome?

It executes successfully but does not give the correct output.

It generates an error because the concatenation operator can be used to combine only two items.

It generates an error because the usage of the round function in the expression is not valid

It generates an error because the alias is not valid.

It executes successfully and gives the correct output.

Q173. Examine the description of the CUSTONERS table:

CUSTNO is the PRIMARY KEY.
You must determine if any customers’ details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?

LEFT OUTER JOIN with self join

PULL OUTER JOIN with self join

subquery

RIGHT OUTER JOIN with self join

self Join

Q174. Examine the command:

What does ON DELETE CASCADE imply?

When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.

When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted but the table structure is retained.

When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted.

When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column.

Q175. Which two statements are true about selecting related rows from two tables based on entity relationship diagram (ERD)?

Relating data from a table with data from the same table is implemented with a self join.

An inner join relates rows within the same table.

Rows from unrelated tables cannot be joined.

Implementing a relationship between two tables might require joining additional tables.

Every relationship between the two tables must be implemented in a Join condition.

Q176. Which three are true about multitable INSERT statements?

They can be performed on external tables using SQL* Loader.

They can be performed on relational tables.

They can be performed only by using a subquery.

They can insert each computed row into more than one table.

They can be performed on views.

They can be performed on remote tables.

Q177. 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?

The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.

The EXECUTION_DATETIME must be of TIMESTAMP data type.

The EXECUTION_DATETIME must be of DATE data type.

The COMPONENT_IDmust be of ROWID data type.

The COMPONENT_IDmust be of VARCHAR2 data type.

The COMPONENT_IDcolumn must be of CHAR data type.

Q178. Examine this statement:

On which two columns of the table will an index be created automatically?

STATUS

ORDER_ID

PRODUCT_ID

SERIAL_NO

ORDER_TOTAL

ORDER_DATE

Q179. Examine this statement:

What is returned upon execution?

1 row

an error

0 rows

2 rows