Oracle 1z1-071 Certification Exam Dumps with 325 Practice Test Questions [Q87-Q102]

Rate this post

Oracle 1z1-071 Certification Exam Dumps with 325 Practice Test Questions

New 1z1-071 Exam Dumps with High Passing Rate

Q87. Which three statements are true regarding subqueries?

Multiple columns or expressions can be compared between the main query and subquery.

Subqueries can contain ORDER BY but not the GROUP BY clause.

Main query and subquery can get data from different tables.

Subqueries can contain GROUP BY and ORDER BY clauses.

Main query and subquery must get data from the same tables.

Only one column or expression can be compared between the main query and subquery.

Q88. View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.

You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task? (Choose two.)

SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id)WHERE quantity_on_hand < 5;

SELECT product_id, quantity_on_hand, supplier_idFROM product_informationNATURAL JOIN inventories AND quantity_on_hand < 5;

SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id) AND quantity_on_hand < 5;

SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iUSING (product_id) AND quantity_on_hand < 5;

Q89. Which three statements about roles are true? (Choose three.)

Roles are assigned to roles using the ALTER ROLEstatement.

A single role can be assigned to multiple users.

A single user can be assigned multiple roles.

Privileges are assigned to a role using the ALTER ROLEstatement.

A role is named group of related privileges that can only be assigned to a user.

Privileges are assigned to a role using the GRANTstatement.

Roles are assigned to users using the ALTER USERstatement.

Q90. View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER__ID is the primary key in the ORDERS table. It is also the foreign key in the
ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.

Which DELETE statement would execute successfully?

DELETE order_id FROM orders WHERE order_total < 1000;

DELETE orders WHERE order_total < 1000;

DELETE FROM orders WHERE (SELECT order_id FROM order_items);

DELETE orders o, order_items i WHERE o.order id = i.order id;

Q91. You execute this command:

Which two are true?

It drops any triggers defined on the table.

It always retains the space used by the removed rows.

A ROLLBACK statement can be used to retrieve the deleted data.

It retains the integrity constraints defined on the table.

It retains the indexes defined on the table.

A FLASHBACK TABLE statement can be used to retrieve the deleted data.

Q92. You execute the SQL statement:

What is the outcome?

It fails because the NOT NULL and DEFAULToptions cannot be combined for the same column.

It It succeeds and CITYcan contain only ‘SEATTLE’or null for all rows.

It fails because the condition for the CNAMESconstraint is not valid.

It succeeds and an index is created for CITIZEN_ID.

Q93. You must find the number of employees whose salary is lower than employee 110.
Which statement fails to do this?

SELECT COUNT (*)
FROM employees
JOIN employees a
ON e. salary< a. salary
WHERE a. employee_ id= 110;

SELECT COUNT (* )
FROM employees
WHERE salary < (SELECT salary FROM employees WHERE employee.id =
110) ;

SELECT COUNT (*)
FROM employees e
JOIN (SELECT salary FROM employees WHERE employee_ id= 110) a
ON e. salary< a. salary;

SELECT COUNT (* )
FROM employees e
WHERE e. salary < (SELECT a. salary FROM employees a WHERE e. employee_ id = 110);

Q94. Examine the structure of the EMPLOYEES table.

You must display the maximum and minimum salaries of employees hired 1 year ago.
Which two statements would provide the correct output? (Choose two.)

SELECT MIN(Salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);

SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365)GROUP BY maxsal, minsal;

SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary));

SELECT MIN(Salary), MAX(salary)FROM (SELECT salary FROMemployeesWHERE hire_date < SYSDATE-365);

Q95. View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. (Choose two.)

Evaluate this SQL statement:
ALTER TABLE emp
DROP COLUMN first_name;
Which two statements are true?

The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.

The FIRST_NAME column would be dropped provided at least one column remains in the table.

The FIRST_NAME column would be dropped provided it does not contain any data.

The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.

Q96. View the Exhibit and examine the structure of the ORDER_ITEMS table.

Examine the following SQL statement:
SELECT order_id, product_id, unit_price
FROM order_items
WHERE unit_price =
(SELECT MAX(unit_price)
FROM order_items
GROUP BY order_id);
You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID.
What correction should be made in the above SQL statement to achieve this?

Remove the GROUP BY clause from the subquery and place it in the main query

Replace = with the >ANY operator

Replace = with the >ALL operator

Replace = with the IN operator

Q97. Which two statements are true regarding the EXISTSoperator 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.

Q98. Which statement is true regarding the INTERSECToperator?

The names of columns in all SELECTstatements must be identical.

It ignores NULLvalues.

Reversing the order of the intersected tables alters the result.

The number of columns and data types must be identical for all SELECTstatements in the query.

Q99. View the exhibit and examine the structure of the CUSTOMERStable.

Which two tasks would require subqueries or joins to be executed in a single statement?

finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers

finding the average credit limit of male customers residing in ‘Tokyo’or ‘Sydney’

listing of customers who do not have a credit limit and were born before 1980

finding the number of customers, in each city, who’s marital status is ‘married’.

listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city ‘Tokyo’.

Q100. View the Exhibit and examine the structure of the ORDERS table. (Choose the best answer.)

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?

SELECT order_id, order_date FROM ordersWHERE order_date > ANY(SELECT order_date FROM orders WHERE customer_id = 101);

SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;

SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT order_date FROM orders WHERE customer_id = 101);

SELECT order_id, order_date FROM ordersWHERE order_date > IN(SELECT
order_date FROM orders WHERE customer_id = 101);

Q101. View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SQL statement would you execute to accomplish the task?

UPDATE dept dSET city = ALL (SELECT cityFROM locations lWHERE d.location_id = l.location_id);

UPDATE dept dSET city = (SELECT cityFROM locations l)WHERE d.location_id = l.location_id;

UPDATE dept dSET city = ANY (SELECT cityFROM locations l)

UPDATE dept dSET city = (SELECT cityFROM locations lWHERE d.location_id = l.location_id);

Q102. Examine the command:

What does ON DELETE CASCADE imply?

When the BOOKStable is dropped, the BOOK_TRANSACTIONStable is dropped.

When the BOOKStable is dropped, all the rows in the BOOK_TRANSACTIONStable are deleted but the table structure is retained.

When a row in the BOOKStable is deleted, the rows in the BOOK_TRANSACTIONStable whose BOOK_ID matches that of the deleted row in the BOOKStable are also deleted.

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

Get 1z1-071 Braindumps & 1z1-071 Real Exam Questions: https://www.real4dumps.com/1z1-071_examcollection.html

Oracle 1z1-071 Certification Exam Dumps with 325 Practice Test Questions [Q87-Q102]

Leave a Reply Cancel reply

Related Posts

Get Special Discount Offer of 1z0-1111-23 Certification Exam Sample Questions and Answers [Q27-Q49]

Latest 1Z0-1195-25 exam dumps with real Oracle questions and answers [Q15-Q35]

Regular Free Updates 1z0-1057-22 Dumps Real Exam Questions Test Engine Feb 24, 2024 [Q66-Q86]