Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. Given below is a list of functions and their purpose in random order.
Function Purpose 1)NVL a) Used for evaluating NOT NULL and NULL values 2)NULLIF b) Used to return the first non- null values in a list of expressions 3)COALESCE c) Used
to compare two expressions. If both are same, it returns NULL; otherwise, it returns only the first expression.
4)NVL2 d) Used to convert NULL values to actual values
Identify the correct combination of functions and their usage.
A. 1-a,2-c,3-b,4-d
B. 1-d,2-c,3-b,4-a
C. 1-b,2-c,3-d,4-a
D. 1-d,2-b,3-c,4-a
Answer: B
Q2. 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
Q3. View the Exhibit and examine the description of the EMPLOYEES and DEPARTMENTS tables.
You want to display the LAST_NAME for the employees, LAST_NAME for the manager of the employees, and the DEPARTMENT_NAME for the employees having 100 as MANAGER_ID. The following SQL statement was written:
SELECT m.last_name "Manager", e.last_name "Employee", department_name "Department"
FROM employees m JOIN employees e
ON (m.employee_id = e.manager_id)
WHERE e.manager_id=100
JOIN departments d
ON (e.department_id = d.department_id);
Which statement is true regarding the output of this SQL statement?
A. The statement would provide the desired results.
B. The statement would not execute because the ON clause is written twice.
C. The statement would not execute because the WHERE clause is wrongly placed.
D. The statement would not execute because the self join uses the ON clause instead of the USING clause.
Answer: C
Q4. View the Exhibit and examine the structure of the EMP table.
You executed the following command to add a primary key to the EMP table:
ALTER TABLE emp
ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
USING INDEX emp_id_idx;
Which statement is true regarding the effect of the command?
A. The PRIMARY KEY is created alongwitha new index.
B. The PRIMARY KEY is created anditwould use an existinguniqueindex.
C. The PRIMARY KEYwould becreated in a disabled state because it is usinganexisting index.
D. The statementproduces an error because the USING clause is permitted only intheCREATE TABLE command.
Answer: B
Q5. View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.
You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;
What would happen when the above statement is executed?
A. Thestatement would execute successfullytoproducetherequired output.
B. Thestatement wouldnotexecute because inlineviewsandouterjoins cannot be usedtogether.
C. The statementwouldnot execute because the ITEM_CNT alias cannotbedisplayedintheouter query.
D. The statement wouldnot execute because the GROUP BYclausecannot be used intheinline view.
Answer: A
Q6. View the Exhibit and examine the description of the EMPLOYEES table.
You want to know the EMPLOYEE_ID and FIRST_NAME of all the records in the EMPLOYEES table wherein the JOB_ID column has ST_CLERK or ST_MAN values, the DEPARTMENT_ID column has value 30, and the SALARY column has a value greater than 3,000.
Which SOL statement would get you the desired result?
A. SELECT employee_id, first_name
FROM employees
WHERE job_id like‘MAN%' OR job_id like 'CLERK%'
AND department_id = 30 AND salary > 3000;
B. SELECT employee_ d, first_name
FROM employees
WHERE job_id like‘%MAN' OR job_id like '%CLERK'
AND (department_id = 30 OR salary > 3000);
C. SELECT employee_id, first_name
FROM employees
WHERE (job_id like‘%MAN' AND job_id like '%CLERK’)
AND department_id = 30 OR salary > 3000;
D. SELECT employee_id, first_name
FROM employees
WHERE (job_id like '%MAN' OR job_id like '%CLERK')
AND department_id= 30 AND salary > 3000;
Answer: D
Q7. View the Exhibit and examine the structure of the ORDER_ITEMS and ORDERS tables.
You are asked to retrieve the ORDER_ID, PRODUCT_ID, and total price (UNIT_PRICE multiplied
by QUANTITY), where the total price is greater than 50,000.
You executed the following SQL statement:
SELECT order_id, product_id, unit_price*quantity "Total Price" FROM order_items WHERE
unit_price*quantity > 50000 NATURAL JOIN orders; Which statement is true regarding the execution of the statement?
A. The statement would execute and provide the desired result.
B. The statement would not execute because the ON keyword is missing in the NATURAL JOIN clause.
C. The statement would not execute because the WHERE clause is before the NATURAL JOIN clause.
D. The statement would not execute because the USING keyword is missing in the NATURAL JOIN clause.
Answer: C
Q8. 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
Q9. Which statement is true regarding the ROLLUP operator specified in the GROUP BY clause of a SQL statement?
A. It produces only the subtotals for the groups specified in the GROUP BY clause.
B. It produces only the grand totals for the groups specified in the GROUP BY clause.
C. It produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause.
D. It produces higher-level subtotals, moving in all the directions through the list of grouping columns specified in the GROUP BY clause.
Answer: C
Q10. 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
Q11. View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite PRIMARY KEY is (ORD_NO, ITEM_NO).
Which two CREATE INDEX statements are valid? (Choose two.)
A. CREATE INDEX ord_idx ON ord(ord_no);
B. CREATE INDEX ord_idx ON ord_items(ord_no);
C. CREATE INDEX ord_idx ON ord_items(item_no);
D. CREATE INDEX ord_idx ON ord,ord_items(ord_no, ord_date,qty);
Answer: BC
Q12. 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. Itwouldreturn a hierarchical outputstarting withthe employeewhose EMPLOYEE_IDis101, followed by employeesbelowhim orherin thehierarchy.
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
Q13. Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.)
A. it can be used to concatenate two strings.
B. it can be used to find out the total length of the string.
C. it can be used for string manipulation and searching operations.
D. it can be used to format the output for a column or expression having string data.
E. it can be used to find and replace operations for a column or expression having string data.
Answer: CDE
Q14. View the Exhibit and examine the structure of the ORD table.
Evaluate the following SQL statements that are executed in a user session in the specified order: CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL, 25-jan-2007',101);
UPDATE ord
SET ord_no= ord_seq.NEXTVAL
WHERE cust_id =101;
What would be the outcome of the above statements?
A. All the statements would execute successfullyandthe ORD_NO column would containthevalue 2 for the CUSTJD101.
B. TheCREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.
C. The CREATE SEQUENCE command wouldnotexecute because the starting value of the sequence and the incrementvaluehave not been specified.
D. Allthe statementswould execute successfully and the ORD_NO column wouldhave the value 20 for the CUST_ID101becausethedefault CACHE value is 20.
Answer: A
Q15. View the Exhibit and examine the structure of the ORDERS table.
The ORDERS table belongs to the user OE. HR is another user in the database.
Evaluate the commands issued by users OE and HR in the following order:
Statement 1 by user OE: GRANT SELECT,
UPDATE(customer_id, order_total)
ON orders
TOhr;
Statement 1 by user HR: SELECT * FROM oe.orders;
Statement 2 by user HR: UPDATE oe.orders
SET order_totah 10000;
Which statement is true regarding the above commands?
A. Statement 1 by user OE would not work because the statement has to be issued by the DBA.
B. Statement 2 by user HR would not work because the grant is only for SELECT in a subquery of update.
C. There are no errors in the statements issued by OE and HR; all the statements would execute successfully.
D. Statement 1 by user HR would not work because SELECT and UPDATE privileges have been granted only on CUSTOMER_ID and ORDER_TOTAL columns.
Answer: C