[Jan-2023] Exam 1z0-071 New Brain Dump Professional – PracticeMaterial [Q138-Q155]

QUESTION 138
View the exhibit and examine the description of SALESand PROMOTIONStables.

You want to delete rows from the SALES table, where the PROMO_NAMEcolumn in the PROMOTIONStable has either blowout saleor everyday low priceas values.
Which three DELETEstatements are valid? (Choose three.)
DELETE

QUESTION 139
You execute this query:

What is the result?

QUESTION 140
View the Exhibit and examine the data in EMPand DEPTtables.

In the DEPTtable, DEPTNOis the PRIMARY KEY.
In the EMPtable, EMPNOis the PRIMARY KEYand DEPTNOis the FOREIGN KEYreferencing the DEPTNO column in the DEPTtable.
What would be the outcome of the following statements executed in the given sequence?
DROP TABLE emp;
FLASHBACK TABLE emp TO BEFORE DROP;
INSERT INTO emp VALUES (2, ‘SCOTT’, 10);
INSERT INTO emp VALUES (3, ‘KING’, 55);

QUESTION 141
View the Exhibit and examine the structure in the EMPLOYEEStables.

Evaluate the following SQL statement:
SELECT employee_id, department_id
FROM employees
WHERE department_id= 50 ORDER BY department_id
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 90
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 10;
What would be the outcome of the above SQL statement?

QUESTION 142
View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name, list_price
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?

QUESTION 143
Which two statements are true regarding multiple-row subqueries? (Choose two.)

QUESTION 144
View the exhibit and examine the data in the PROJ_TASK_DETAILS table. (Choose the best answer.)

The PROJ_TASK_DETAILS table stores information about project tasks and the relation between them.
The BASED_ON column indicates dependencies between tasks.
Some tasks do not depend on the completion of other tasks.
You must generate a report listing all task IDs, the task ID of any task upon which it depends and the name of the employee in charge of the task upon which it depends.
Which query would give the required result?

QUESTION 145
View the Exhibit and examine the structure of the CUSTOMERS table.

You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10,000.
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_credit_limit FROM customers
WHERE (UPPER(cust_last_name) LIKE ‘A%’ OR
UPPER (cust_last_name) LIKE ‘B%’ OR UPPER (cust_last_name) LIKE ‘C%’)
AND cust_credit_limit < 10000;
SQL>SELECT cust_last_name, cust_credit_limit FROM customers
WHERE UPPER (cust_last_name) BETWEEN ‘A’ AND ‘C’
AND cust_credit_limit < 10000;
Which statement is true regarding the execution of the above queries?

QUESTION 146
Which two statements are true about a full outer join?

QUESTION 147
You must create a table for a banking application.
One of the columns in the table has these requirements:
1: A column to store the duration of a short team loan
2: The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions.
3: The maximum loan period is 30 days.
4: Interest must be calculated based on the number of days for which the loan remains unpaid.
Which data type would you use?

QUESTION 148
View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.

Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
AS
SELECT order_id.order_date,customer_id
FROM orders;
Which statement is true regarding the above command?

QUESTION 149
You execute this command:

Which two are true? (Choose two.)

QUESTION 150
Examine this SELECT statement and view the Exhibit to see its output:

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? (Choose two.)

QUESTION 151
You execute the SQL statement:

What is the outcome?

QUESTION 152
See the Exhibit and examine the structure of the PROMOTIONS table:

Using the PROMOTIONS table,
you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:

What would be the outcome?

QUESTION 153
View the exhibit and examine the structure of the PROMOTIONS table.
You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the ‘INTERNET’ category.
Which query would give you the required output?

QUESTION 154
Examine the description of the CUSTOMERStable:

Which two SELECTstatements will return these results (Choose two.):

SELECT customer_name FROM customers WHERE customer_name = ‘*Ma*’;

QUESTION 155
Examine the structure of the BOOKS_ TRANSACTIONS table:

Examine the SQL statement:

Which statement is true about the outcome?