Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. 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. WHEREREGEXPJJKE(category_description, 'hard+.s’);
B. WHERE REGEXPJJKE(category_description,‘^H|hard+.s’);
C. WHERE REGEXPJJKE (category_description, '^H|hard+.s$');
D. WHEREREGEXPJJKE (category_description, '[^Hlhard+.s]');
Answer: AB
Q2. Evaluate the following SQL statements in the given order:
DROP TABLE dept;
CREATE TABLE dept
(deptno NUMBER(3) PRIMARY KEY,
deptname VARCHAR2(10));
DROP TABLE dept;
FLASHBACK TABLE dept TO BEFORE DROP; Which statement is true regarding the above FLASHBACK operation?
A. It recovers only the firstDEPTtable.
B. Itrecovers onlythesecondDEPTtable.
C. It does not recoveranyof the tables becauseFLASHBACKis not possible in this case.
D. Itrecovers both the tables but the names would be changed to the ones assigned intheRECYCLEBIN.
Answer: B
Q3. 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_jtems 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?
A. Replace = with theINoperator.
B. Replace = withthe >ANYoperator.
C. Replace = with the>ALLoperator.
D. Remove the GROUP BY clause from the subquery and placeitin the main query.
Answer: A
Q4. View the Exhibit and examine the description of the PRODUCT_INFORMATION table.
Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?
A. SELECT COUNT(list_price)
FROM product_information
WHERE list_price IS NULL;
B. SELECT COUNT(list_price)
FROM product_information
WHERE list_price = NULL;
C. SELECT COUNT(NVL(list_price, 0))
FROM product_information
WHERE list_price IS NULL;
D. SELECT COUNT(DISTINCT list_price)
FROM product_information
WHERE list_price IS NULL;
Answer: C
Q5. Which two statements are true regarding multiple-row subqueries? (Choose two.)
A. They can containgroupfunctions.
B. They always contain a subquery within a subquery.
C. They use the < ALL operator to imply less than the maximum.
D. They can be used to retrieve multiple rows from a single table only.
E. Theyshouldnot be used withthe NOTIN operator inthemainquery if NULLislikelytobea part ofthe result of thesubquery.
Answer: AE
Q6. Which statements are true? (Choose all that apply.)
A. The data dictionary is created and maintained by the database administrator.
B. The data dictionary views can consist of joins of dictionary base tables and user-defined tables.
C. The usernames of all the users including the database administrators are stored in the data dictionary.
D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
E. Both USER_ODBJECTS and CAT views provide the same information about all the objects that are owned by the user.
F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary
Answer: CDF
Q7. 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
Q8. 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. Inthe secondcolumn, indicates a check constraint.
B. TheSTATUS columnindicateswhether the tableiscurrently in use.
C. The R_CONSTRAINT_NAME column givesthealternative name for the constraint.
D. The column DELETE_RULE decides the state oftherelated rows inthechild tablewhenthe corresponding row is deleted from the parent table.
Answer: AD
Q9. Which three statements are true? (Choose three.)
A. Only one LONG column can be used per table.
B. ATIMESTAMP data type column stores only time values with fractional seconds.
C. The BLOB data type column is used to store binary data in an operating system file.
D. The minimum column width that can be specified for a varchar2 data type column is one.
E. The value for a CHAR data type column is blank-padded to the maximum defined column width.
Answer: ADE
Q10. View the Exhibitl and examine the descriptions of the EMPLOYEES and DEPARTMENTS tables.
The following SQL statement was executed:
SELECT e.department_id, e.job_id, d.location_id, sum(e.salary) total,
GROUPING(e.department_id)GRP_DEPT,
GROUPING(e.job_id) GRPJOB,
GROUPING(d. location_id) GRP_LOC
FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY ROLLUP (e.department_id, e.job_id, d.location_id);
View the Exhibit2 and examine the output of the command.
Which two statements are true regarding the output? (Choose two.)
A. The value 1 inGRP_LOC means that the LOCATION_ID column is taken into account to generate the subtotal.
B. The value 1 in GRP_JOB and GRP_LOC means that JOB_ID and LOCATION_ID columns are not taken into account to generate the subtotal.
C. The value 1 in GRP_JOB and GRP_LOC means that the NULL value in JOB_ID and LOCATIONJD columns are taken into account to generate the subtotal.
D. The value 0 in GRP_DEPT, GRPJOB, and GRP_LOC means that DEPARTMENT_ID, JOB_ID, and LOCATION_ID columns are taken into account to generate the subtotal
Answer: BD
Q11. You executed the following SQL statements in the given order:
CREATE TABLE orders
(order_id NUMBER(3) PRIMARY KEY,
order_date DATE,
customer_id number(3));
INSERT INTO orders VALUES (100,'10-mar-2007,,222);
ALTER TABLE orders MODIFY order_date NOT NULL;
UPDATE orders SET customer_id=333;
DELETE FROM order;
The DELETE statement results in the following error:
ERROR at line 1:
ORA-00942: table or view does not exist
What would be the outcome?
A. All the statements before the DELETEstatementwouldberolled back.
B. Allthestatements before theDELETEstatement wouldbeimplicitly committedwithin thesession.
C. Allthe statements up to theALTER TABLEstatement would be committedandthe outcomeof UPDATEstatementwouldberolled back.
D. All the statements up to theALTERTABLEstatementwould be committed and the outcome oftheUPDATE statement is retained uncommittedwithin thesession.
Answer: D
Q12. 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
Q13. 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.)
A. WHERE
B. HAVING
C. GROUP BY
D. START WITH
E. CONNECT BY
Answer: ADE
Q14. 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
Q15. 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