Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. 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
Q2. View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables.
Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them?
SELECT location_id, city
FROM locations
SELECT location_id, city
FROM locations JOIN departments
USING(location_id);
A. UNION
B. MINUS
C. INTERSECT
D. UNION ALL
Answer: C
Q3. Evaluate the following SELECT statement and view the Exhibit to examine its output:
SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS
Which two statements are true about the output? (Choose two.)
A. In the second column, indicates a check constraint.
B. The STATUS column indicates whether the table is currently in use.
C. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.
D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.
Answer: AD
Q4. Evaluate the following statements:
CREATE TABLE digits
(id NUMBER(2),
description VARCHAR2(15));
INSERT INTO digits VALUES (1,'ONE’);
UPDATE digits SET description =TWO'WHERE id=1;
INSERT INTO digits VALUES (2.’TWO’);
COMMIT;
DELETE FROM digits;
SELECT description FROM digits
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;
What would be the outcome of the above query?
A. Itwouldnot display any values.
B. It would displaythevalue TWO once.
C. Itwould display the valueTWOtwice.
D. Itwould display the values ONE, TWO, andTWO.
Answer: C
Q5. Which statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose all that apply.)
A. It can be used only with the SELECT clause.
B. The WITH clause can hold more than one query.
C. If the query block name and the table name were the same, then the table name would take precedence.
D. 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.
Answer: ABD
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 two statements are true? (Choose two.)
A. The USER_SYNONYMSviewcan provide information about private synonyms.
B. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
C. All the dynamic performance views prefixed with V$ are accessible to all the database users.
D. The USER_OBJECTS view can provide information about the tables and views created by the user only.
E. DICTIONARY is a view thatcontains thenamesof allthe datadictionary views that theuser can access.
Answer: AE
Q8. 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
Q9. EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table?
A. UPDATE empdet SET ename ='Amit' WHERE empno = 1234;
B. DELETE FROM empdet WHERE ename LIKE 'J%' C. CREATE VIEWempvu AS SELECT* FROMempdept;
D. CREATEINDEX empdet_idx ON empdet(empno);
Answer: C
Q10. Which statements are true regarding the hierarchical query in Oracle Database 10g? (Choose all that apply.)
A. It is possible to retrieve data only in top-down hierarchy.
B. Itis possible to retrieve data in top-down or bottom-up hierarchy.
C. It is possible to remove an entire branch from the output of the hierarchical query.
D. You cannot specify conditions when you retrieve data by using a hierarchical query.
Answer: BC
Q11. View the Exhibit and examine the structure of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR employee_id=manager_id;
Which statement is true regarding the output for this command?
A. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by his or her peers.
B. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by the employee to whom he or she reports.
C. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by employees below him or her in the hierarchy.
D. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is101, followed by employees up to one level below him or her in the hierarchy.
Answer: C
Q12. Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)
A. The nested query executes after the outer query returns the row.
B. The nested query executes first and then the outer query executes.
C. The outer query executes only once for the result returned by the inner query.
D. Each row returned by the outer query is evaluated for the results returned by the inner query.
Answer: AD
Q13. View the Exhibit and examine the details for the CATEGORIES_TAB table.
Evaluate the following incomplete SQL statement:
SELECT category_name ,category_description
FROM categories_tab
You want to display only the rows that have 'harddisks' as part of the string in the CATEGORY_DESCRIPTION column.
Which two WHERE clause options can give you the desired result? (Choose two.)
A. WHEREREGEXP_LIKE(category_description, 'hard+.s’);
B. WHERE REGEXP_LIKE(category_description,‘^H|hard+.s’);
C. WHERE REGEXP_LIKE (category_description, '^H|hard+.s$');
D. WHEREREGEXP_LIKE (category_description, '[^Hlhard+.s]');
Answer: AB
Q14. Evaluate the following ALTER TABLE statement:
ALTER TABLE orders
SET UNUSED order_date;
Which statement is true?
A. The DESCRIBE command would still display the ORDER_DATE column.
B. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
C. The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully.
D. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.
Answer: D
Q15. Which SQL statement would display the view names and definitions of all the views owned by you?
A. SELECTviewjiame, text FROM user_view;
B. SELECTviewjiame, text FROM user_object;
C. SELECTviewjiame, text FROM user_objects;
D. SELECTview_name, text FROM user views;
Answer: D