Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. View the Exhibit and examine the data in the DEPARTMENTS tables.
Evaluate the following SQL statement:
SELECT department_id "DEPT_ID", department_name , 'b' FROM departments WHERE department__id=90 UNION SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10
Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.)
A. ORDERBY 3;
B. ORDER BY'b'
C. ORDER BYDEPT_ID;
D. ORDER BY DEPTNAME;
Answer: AC
Q2. Which statement is true regarding Flashback Version Query?
A. It returns versions of rows only within a transaction.
B. It can be used in subqueries contained only in a SELECT statement.
C. It will return an error if the undo retention time is less than the lower bound time or SCN specified.
D. It retrieves all versions including the deleted as well as subsequently reinserted versions of the rows.
Answer: D
Q3. Evaluate the following SQL statement:
CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
Which query would use the UPPER_NAME_IDX index?
A. SELECT UPPER(product_name)
FROM product_information
WHERE product_jd = 2254;
B. SELECT UPPER(product_name)
FROM product_jnformation;
C. SELECT product_id
FROM product_jnformation
WHERE UPPER(product_name) IN ('LASERPRO', 'Cable);
D. SELECT product_jd, UPPER(product_name)
FROM product_information
WHERE UPPER(product_name)='LASERPRO' OR list_price > 1000;
Answer: C
Q4. View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
Evaluate the following UPDATE statement:
UPDATE
(SELECT order_date, order_total, customer_id
FROM orders)
SET order_date = 22-mar-2007'
WHERE customer_id = (SELECT customer_id FROM customers WHERE cust_last_name = 'Roberts'AND Credit_limit = 600);
Which statement is true regarding the execution of the above UPDATE statement?
A. Itwouldnot execute becausetwotables cannot be usedin a single UPDATEstatement.
B. It wouldexecuteandrestrict modifications toonly thecolumns specifiedin theSELECT statement.
C. It wouldnotexecute becauseasubquery cannot be used in the WHERE clause ofanUPDATE statement.
D. Itwould not execute becausetheSELECTstatementcannot be used in place of the table name.
Answer: B
Q5. View the Exhibit and examine the structure of the ORDER_ITEMS table.
You need to display the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.
Which query would produce the desired output?
A. SELECT order_id FROM order_items
WHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id;
B. SELECT order_id FROM order_items
WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) GROUP
BY order_id;
C. SELECT order_id FROM order_items
WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP
BY order_id);
D. SELECT order_id FROM order_items GROUP BY order_id
HAVING SUM(unit_price*quantity) =(SELECT MAX(SUM(unit_price*quantity)jFROM order_items
GROUP BY order_id);
Answer: D
Q6. Which two statements are true about sequences created in a single instance database? (Choose two.)
A. The numbers generated by a sequence can be used only for one table.
B. DELETE <sequencename> would remove a sequence from the database.
C. CURRVAL is used to refer to the last sequence number that has been generated.
D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.
E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.
Answer: CD
Q7. The following are the steps for a correlated subquery, listed in random order:
1) The WHERE clause of the outer query is evaluated.
2) The candidate row is fetched from the table specified in the outer query.
3) The procedure is repeated for the subsequent rows of the table, till all the rows are processed.
4) Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query. Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery.
A. 4,2,1,3
B. 4,1,2,3
C. 2,4,1,3
D. 2,1,4,3
Answer: C
Q8. The following are the steps for a correlated subquery, listed in random order: 1) The WHERE clause of the outer query is evaluated.
2) The candidate row is fetched from the table specified in the outer query.
3) The procedure is repeated for the subsequent rows of the table, till all the rows are processed.
4) Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query. Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery.
A. 4,2,1,3
B. 4,1,2,3
C. 2,4,1,3
D. 2,1,4,3
Answer: C
Q9. Evaluate the following statement:
CREATE TABLE bonuses(employee_id NUMBER, bonus NUMBER DEFAULT 100);
The details of all employees who have made sales need to be inserted into the BONUSES table. You can obtain the list of employees who have made sales based on the SALES_REP_ID column of the ORDERS table. The human resources manager now decides that employees with a salary of $8,000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who have made sales get a bonus of 1 % of their salary and also a salary increase of 1 %. The salary of each employee can be obtained from the EMPLOYEES table.
Which option should be used to perform this task most efficiently?
A. MERGE
B. Unconditional INSERT
C. ConditionalALLINSERT
D. Conditional FIRST INSERT
Answer: A
Q10. View the Exhibit and examine the data in the CUST_DET table.
You executed the following multitable INSERT statement:
INSERT FIRST
WHEN credit_limit >= 5000 THEN
INTO cust_1 VALUES(cust_id, credit_limit, grade, gender)
WHEN grade = THE
INTO cust_2 VALUES(cust_id, credit_limit, grade, gender)
WHEN gender = THE
INTO cust_3 VALUES(cust_id, credit_limit, grade, gender)
INTO cust_4 VALUES(cust_id, credit_limit, grade, gender)
ELSE
INTO cust_5 VALUES(cust_id, credit_limit, grade, gender)
SELECT * FROM cust_det;
The row will be inserted in________.
A. CUST_1 table only because CREDIT_LIMIT condition is satisfied
B. CUST_1and CUST_2 tables because CREDIT_LIMIT and GRADE conditions are satisfied
C. CUST_1,CUST_2 and CUST_5 tables because CREDIT_LIMIT and GRADE conditions are satisfied but GENDER condition is not satisfied
D. CUST 1, CUST 2 and CUST 4 tables because CREDIT LIMIT and GRADE conditions are satisfied for CUST 1 and CUST 2, and CUST 4 has no condition on it
Answer: A
Q11. View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table.
Evaluate the following SQL statement:
ALTER TABLE emp DROP COLUMN first_name;
Which two statements are true regarding the above command? (Choose two.)
A. The FIRST_NAME column wouldbedropped provided it does notcontainany data.
B. The FIRST_NAME column would bedropped providedat least one or more columnsremain inthe table.
C. The FIRST_NAME column can be rolledback providedthe SET UNUSED option is added to the above SQL statement.
D. The FIRST_NAME column can be dropped evenif itis part ofacomposite PRIMARY KEY provided the CASCADE option is used.
Answer: BD
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. User OE, the owner of the ORDERS table, issues the following command:
GRANT SELECT .INSERT ON orders TO hr WITH GRANT OPTION;
The user HR issues the following command:
GRANT SELECT ON oe. orders TO scott;
Then, OE issues the following command:
REVOKE ALL ON orders FROM hr;
Which statement is correct?
A. The user SCOTT losestheprivilege to select rowsfromOE.ORDERS.
B. Theuser SCOTT retainstheprivilege to selectrowsfrom OE.ORDERS.
C. The REVOKE statement generates an error because OE has to first revoke the SELECT privilege from SCOTT.
D. TheREVOKE statement generates an error because the ALL keyword cannot be usedforprivilegesthathave been granted using WITH GRANT OPTION.
Answer: A
Q14. View the Exhibit and examine the structure of the ORDERS table. The columns ORDER_MODE and ORDER_TOTAL have the default values 'direct' and 0 respectively.
Which two INSERT statements are valid? (Choose two.)
A. INSERT INTO orders
VALUES (1, O9-mar-2007', 'online',",1000);
B. INSERT INTO orders
(order_id ,order_date ,order_mode,
customer_id ,order_total)
VALUES(1 ,TO_DATE(NULL), 'online', 101, NULL);
C. INSERT INTO
(SELECT order_id ,order_date .customer_id
FROM orders)
VALUES (1,O9-mar-2007', 101);
D. INSERT INTO orders
VALUES (1,09-mar-2007', DEFAULT, 101, DEFAULT);
E. INSERT INTO orders
(order_id,order_date ,order_mode .order_total)
VALUES (1 ,'10-mar-2007','online',1000);
Answer: CD
Q15. View the Exhibit and examine the description of the EMPLOYEES table.
You executed the following SQL statement:
SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the output of the above query? (Choose two.)
A. The valuesinall the columns wouldbe sortedin the descending order.
B. Thevalues in theSALARYcolumn wouldbesorted indescendingorder for all the employees havingthesame valueinthe DEPARTMENT_ID column.
C. The values intheFIRST_NAME column would be sortedinascending order for alltheemployees having the same valueinthe DEPARTMENT_ID column.
D. Thevalues in the FIRST_NAME column would be sorted in the descendingorder forall the employees having the same valueinthe DEPARTMENT_ID column.
E. Thevalues in the SALARY column wouldbe sortedin descending order foralltheemployeeshaving thesame value intheDEPARTMENT_IDandFIRST_NAME column.
Answer: CE