Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. 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.)
A. SELECT product_id, quantity_on_hand , supplier_id FROM product_information NATURAL JOIN inventories AND quantity_on_hand<5;
B. SELECT i.product_id, i.quantity_on_hand , pi.supplier_id FROM product_information pi JOIN inventoriesi USING (product_id) AND quantity_on_hand < 5;
C. SELECT i.product_id, i.quantity_on_hand , pi.supplier_id FROM product_information pi JOIN inventories i ON (pi. product_id=i. product_id) WHERE quantity_on_hand < 5;
D. SELECT i.product_id, i.quantity_on_hand , pi.supplier_id FROM product_information pi JOIN inventories i ON (pi. product_id=i. product_id) AND quantity_on_hand < 5;
Answer: CD
Q2. Evaluate the following DELETE statement:
DELETE FROM orders; There are no other uncommitted transactions on the ORDERS table. Which statement is true about the DELETE statement?
A. Itremovesallthe rows in thetable andallows ROLLBACK
B. It would not removetherows if thetablehasaprimary key.
C. It removesallthe rows as well as the structure of the table.
D. Itremoves all the rows inthetable and doesnotallow ROLLBACK
Answer: A
Q3. View the Exhibit and examine the data in EMP and DEPT tables.
In the DEPT table, DEPTNO is the PRIMARY KEY.
In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing the DEPTNO column in the DEPT table.
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,COTT 10); INSERT INTO emp VALUES (3,ING 55);
A. Both the INSERT statements would fail because all constraints are automatically retrieved when the table is flashed back.
B. Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back.
C. Only the first INSERT statement would succeed because all the constraints except the primary key constraint are automatically retrieved after a table is flashed back.
D. Only the second INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.
Answer: D
Q4. View the Exhibit and examine the structure of the EMPLOYEES table.
You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree?
A. WHERE
B. HAVING
C. GROUP BY
D. STARTWITH
E. CONNECT BY PRIOR
Answer: E
Q5. Which statement best describes the GROUPING function?
A. It is used to set the order for the groups to be used for calculating the grand totals and subtotals.
B. It is used to form various groups to calculate total and subtotals created using ROLLUP and CUBE operators.
C. It is used to identify if the NULL value in an expression is a stored NULL value or created by ROLLUP or CUBE.
D. It is used to specify the concatenated group expressions to be used for calculating the grand totals and subtotals.
Answer: C
Q6. Evaluate the following statement:
INSERT ALL
WHEN order_total < 10000 THEN
INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN
INTO medium_orders
WHEN order_total > 2000000 THEN
INTO large_orders
SELECT order_id, order_total, customer_id
FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?
A. They areevaluatedby allthe three WHENclauses regardlessofthe resultsof the evaluation ofany other WHEN clause.
B. They are evaluated by thefirst WHENclause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
C. They are evaluated by the first WHEN clause. If the condition isfalse,thenthe row wouldbeevaluated by the subsequentWHENclauses.
D. TheINSERT statement would give an error becausetheELSE clause is notpresent forsupport in case none of theWHENclauses are true.
Answer: A
Q7. Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)
A. The HAVING clause conditions can have aggregate functions.
B. The HAVING clause conditions can use aliases for the columns.
C. WHERE and HAVING clauses cannot be used together in a SQL statement.
D. The WHERE clause is used to exclude rows before the grouping of data.
E. The HAVING clause is used to exclude one or more aggregated results after grouping data.
Answer: ADE
Q8. View the Exhibit and examine the description of the ORDERS table.
Evaluate the following SQL statement:
SELECT order_id, customer_id
FROM orders
WHERE order_date > 'June 30 2001'
Which statement is true regarding the execution of this SQL statement?
A. It would not execute because 'June 30 2001' in the WHERE condition is not enclosed within double quotation marks.
B. It would execute and would return ORDER_ID and CUSTOMER_ID for all records having ORDER_DATE greater than 'June 30 2001'.
C. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_DATE conversion function for proper execution.
D. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_CHAR conversion function for proper execution.
Answer: C
Q9. View the Exhibit and examine the structure for the ORDERS and ORDER_ITEMS tables.
You want to display ORDER_ID, PRODUCT_ID, and TOTAL (UNIT_PRICE multiplied by QUANTITY) for all the orders placed in the last seven days.
Which query would you execute?
A. SELECT orde_id, product_id, unit_price*quantity "TOTAL"
FROM order_items oi JOIN orders o
ON (o.order_id=oi. order_id)
WHERE o.order_date>=SYSDATE-7;
B. SELECT o.order_id,oi.product_id, oi.unit_price*oi.quantity "TOTAL"
FROM order_items oi JOIN orders o
USING (order_id)
WHERE o.order_date>=SYSDATE-7;
C. SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL"
FROM order_items oi JOIN orders o
WHERE o.order_date>=SYSDATE-7 ON (o.order_id=oi. order_id);
D. SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL"
FROM orde_items oi JOIN orders o
ON (o.order_id=oi. order_id)
WHERE o. order date>=SYSDATE-7;
Answer: D
Q10. View the Exhibit and examine the structure of the EMPLOYEES table.
You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?
A. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE m.manager_id=100;
B. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE e.manager_id=100;
C. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON e.employee_id = m.manager_id WHERE m.manager_id=100;
D. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
WHERE m.employee_id = e.manager_id AND e.manager_id=100;
Answer: B
Q11. Evaluate the following CREATE TABLE command:
CREATE TABLE order_item
(order_id NUMBER(3),
item_id NUMBER(2),
qty NUMBER(4),
CONSTRAINT ord_itm_id_pk
PRIMARY KEY (order_id item_id)
USING INDEX
(CREATE INDEX ord_itm_idx
ON order_item(order_id,item_id)));
Which statement is true regarding the above SOL statement?
A. Itwould execute successfullyandonly ORD_ITM_IDX index would be created.
B. It would give an error because the USING INDEX clause cannot be used onacomposite primarykey.
C. It wouldexecutesuccessfully and two indexesORD_ITM_IDXand ORD_ITM_ID_PKwould becreated.
D. Itwould give an error becausetheUSING INDEX clause isnotpermitted in the CREATETABLEcommand.
Answer: A
Q12. View the Exhibit and examine the structure of the ORDERS table.
Which UPDATE statement is valid?
A. UPDATE orders
SETorder_date = 12-mar-2007\ order_total IS NULL WHERE order_id = 2455;
B. UPDATE orders
SET order_date = 12-mar-2007', order_total = NULL WHERE order_id = 2455;
C. UPDATE orders
SET order_date = '12-mar-2007'
AND order_total = TO_NUMBER(NULL)
WHERE order_id = 2455;
D. UPDATE orders
SET order_date = TO_DATE('12-mar-2007,l,dd-mon-yyyy')lSET order_total =
TO_NUMBER(NULL) WHERE order id = 2455;
Answer: B
Q13. View the Exhibit and examine the structure of the ORDERS table.
You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101
Which query would give you the desired output?
A. SELECT order id, order_date FROM orders
WHERE order_date > ALL (SELECT MAX(order_date)
FROM orders)AND
Customer_id = 101;
B. SELECT order id, order_date FROM orders
WHERE order_date > ANY (SELECT order_date
FROM orders
WHERE customer_id = 101);
C. SELECT order _id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101);
D. SELECT order id, order_date FROM orders WHERE order_date IN (SELECT order_date FROM orders WHERE customer id = 101);
Answer: C
Q14. Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)
A. You can use column alias in the GROUP BY clause.
B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.
Answer: DE
Q15. View the Exhibit and examine the structure of the CUSTOMERS table.
CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table. CUSTOMERS needs to be updated to reflect the latest information about the customers.
What is the error in the following MERGE statement?
MERGE INTO customers c
USING customer_vu cv
ON (c.customer_id = cv.customer_id)
WHEN MATCHED THEN
UPDATE SET
c.customer_id = cv.customer_id,
c.cust_name = cv.cust_name,
c.cust_email = cv.cust_email,
c.income_level = cv. Income_level
WHEN NOT MATCHED THEN
INSERT VALUESfcv.customer_id.cv.cus_name.cv.cus_email.cv.income_level)
WHERE cv. Income_level >100000;
A. The CUSTOMERJD column cannot be updated.
B. The INTO clause is misplaced in the command.
C. The WHERE clause cannot be used with INSERT.
D. CUSTOMER VU cannot be used as a data source
Answer: A