Oracle Database SQL Expert
Question No: 111
Which two statements are true regarding roles? (Choose two.)
A role can be granted to itself.
A role can be granted to PUBLIC.
A user can be granted only one role at any point of time.
The REVOKE command can be used to remove privileges but not roles from other users.
Roles are named groups of related privileges that can be granted to users or other roles.
Question No: 112
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.
Question No: 113
View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables.
You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or 1 in the ORDERS table.
Which DELETE statements are valid? (Choose all that apply.)
WHERE order_id IN (SELECT order_id FROM orders
WHERE order_status in (0,1));
DELETE * FROM order_items
WHERE order_id IN (SELECT order_id FROM orders
WHERE order_status IN (0,1));
DELETE FROM order_items i
WHERE order_id = (SELECT order_id FROM orders o WHERE i. order_id = o. order_id AND
order_status IN (0,1));
FROM (SELECT* FROM order_items i.orders o
WHERE i.order_id = o.order_id AND order_status IN (0,1));
Question No: 114
You need to create a table for a banking application with the following considerations:
You want a column in the table to store the duration of the credit period.
The data in the column should be stored in a format such that it can be easily added and subtracted with 3) date type data without using the conversion functions.
The maximum period of the credit provision in the application is 30 days.
The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
Question No: 115
View the Exhibit and examine the structure of the ORDERS table:
The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT NULL constraint.
Evaluate the following statement:
INSERT INTO (SELECT order_id.order_date.customer_id FROM ORDERS WHERE order_total = 1000 WITH CHECK OPTION) VALUES (13, SYSDATE, 101);
What would be the outcome of the above INSERT statement?
It would execute successfully and the new row would be inserted into a new temporary table created by the subquery.
It would execute successfully and the ORDER_TOTAL column would have the value 1000 inserted automatically in the new row.
It would not execute successfully because the ORDER_TOTAL column is not specified in the SELECT list and no value is provided for it.
It would not execute successfully because all the columns from the ORDERS table should have been included in the SELECT list and values should have been provided for all the columns.
Question No: 116
Which statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose all that apply.)
It can be used only with the SELECT clause.
The WITH clause can hold more than one query.
If the query block name and the table name were the same, then the table name would take precedence.
The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block.
Question No: 117
View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables. Evaluate the following SQL statement:
SELECT oi.order_id, product_id, order_date FROM order_items oi JOIN orders o USING(order_id);
Which statement is true regarding the execution of this SQL statement?
The statement would not execute because table aliases are not allowed in the JOIN clause.
The statement would not execute because the table alias prefix is not used in the USING clause.
The statement would not execute because all the columns in the SELECT clause are not prefixed with table aliases.
The statement would not execute because the column part of the USING clause cannot have a qualifier in the SELECT list.
Question No: 118
Given below are the SQL statements executed in a user session:
CREATE TABLE product (pcode NUMBER(2), pnameVARCHAR2(10));
INSERT INTO product VALUES(1, ‘pen’); INSERT INTO product VALUES (2,#39;penci’);
UPDATE product SET pcode = 10 WHERE pcode = 1; SAVEPOINT b;
DELETE FROM product WHERE pcode = 2; COMMIT;
DELETE FROM product WHERE pcode=10; ROLLBACK TO SAVEPOINT a;
Which statement describes the consequences?
No SQL statement would be rolled back.
Both the DELETE statements would be rolled back.
Only the second DELETE statement would be rolled back.
Both the DELETE statements and the UPDATE statement would be rolled back.
Question No: 119
View the Exhibit and examine the description of the ORDER_ITEMS table.
The following SQL statement was written to retrieve the rows for the PRODUCT_ID that has a UNIT_PRICE of more than 1,000 and has been ordered more than five times:
SELECT product_id, COUNT(order_id) total, unit_price FROM order_items
WHERE unit_pricegt;1000 AND COUNT(order_id)gt;5 GROUP BY product_id, unit_price;
Which statement is true regarding this SQL statement?
The statement would execute and give you the desired result.
The statement would not execute because the aggregate function is used in the WHERE clause.
The statement would not execute because the WHERE clause should have the OR logical operator instead of AND.
The statement would not execute because in the SELECT clause, the UNIT_PRICE column is placed after the column having the aggregate function.
Question No: 120
View the Exhibit and examine the details of the EMPLOYEES table.
You want to generate a hierarchical report for all the employees who report to the employee whose EMPLOYEE_ID is 100.
Which SQL clauses would you require to accomplish the task? (Choose all that apply.)
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|