Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL All new customer details have to be inserted into the CUST table. Which technique should be used to load the data most efficiently?
A. external table
B. the MERGE command
C. the multi table INSERT command
D. INSERT using WITHCHECK OPTION
Answer: C
Q2. 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 statementwouldprovide the desired results.
B. Thestatement wouldnotexecute because the ON clause iswritten twice.
C. Thestatement wouldnot execute because the WHERE clause is wrongly placed.
D. The statement wouldnot execute because the self join usestheON clauseinsteadof the USING clause.
Answer: C
Q3. 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
Q4. 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
Q5. 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
Q6. View the Exhibit and examine the data in the LOCATIONS table.
Evaluate the following SOL statement: SELECT street_address FROM locations WHERE REGEXP_INSTR(street_address,'[^[: alpha:]]’) = 1;
Which statement is true regarding the output of this SOL statement?
A. It would display all the street addresses that do not have a substring 'alpha1.
B. It would display all the street addresses where the first character is a special character.
C. It would display all the street addresses where the first character is a letter of the alphabet.
D. It would display all the street addresses where the first character is not a letter of the alphabet.
Answer: D
Q7. View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECTfirst_name, employee_id, NEXr_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM employees;
The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees. The review date is the first Monday after the completion of six months of the hiring. The
NLS_TERRITORY parameter is set to AMERICA in the session. Which statement is true regarding this query?
A. The query would execute to give the desired output.
B. The query would not execute because date functions cannot be nested.
C. The query would execute but the output would give review dates that are Sundays.
D. The query would not execute because the NEXT_DAY function accepts a string as argument.
Answer: C
Q8. 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
Q9. View the Exhibit and examine the description of the ORDERS table.
Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)
A. WHERE order_date> TO_DATE('JUL 10 2006','MON DD YYYY)
B. WHERE TO_CHAR(order_date,'MONDDYYYY) = 'JAN 20 2003'
C. WHEREorder_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MONDDYYYY")
D. WHERE order_dateIN(TO_DATE('Oct 21 2003','Mon DD YYYY"),TO_CHAR('NOV21 2003','Mon DD YYYY"))
Answer: AB
Q10. View the Exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.
To retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:
SELECT employee_id, first_name, department_name
FROM employees NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?
A. The NATURAL JOIN clause is missing the USING clause.
B. The table prefix is missing for the column names in the SELECT clause.
C. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
D. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.
Answer: D
Q11. View the Exhibit and examine the structure of EMPLOYEES and JOB_HISTORY tables.
The EMPLOYEES table maintains the most recent information regarding salary, department, and job for all the employees. The JOB_HISTORY table maintains the record for all the job changes for the employees. You want to delete all the records from the JOB_HISTORY table that are repeated in the EMPLOYEES table.
Which two SQL statements can you execute to accomplish the task? (Choose two.)
A. DELETE FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e WHERE j.employee_id = e.employee_id) AND job_id = (SELECT job_id FROM employees e WHERE j.job_id = e.job_id);
B. DELETE FROM job_history j WHERE (employee_id, job_id) = ALL (SELECT employee_id, job_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id)
C. DELETE FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id)
D. DELETE FROM job_history j WHERE (employee_id, job_id) = (SELECT employee_id, job_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id)
Answer: CD
Q12. 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 jtem_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 indexes ORD_ITM_IDXand ORD_ITM_ID_PKwould becreated.
D. Itwould give an error becausetheUSING INDEX clause isnotpermitted in the CREATETABLEcommand.
Answer: A
Q13. View the Exhibit 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 value1in GRP_LOC means that the LOCATION_ID column is taken into account to generate the subtotal.
B. The value 1 in GRPJOBandGRP_LOC meansthatJOB_IDandLOCATION_ID columns are nottakeninto accountto generatethesubtotal.
C. Thevalue1inGRPJOB and GRP_LOC meansthatthe NULL value in JOBJD and LOCATIONJD columns aretakeninto account togeneratethe subtotal.
D. The value 0 inGRP_DEPT,GRPJOB, and GRP_LOC means that DEPARTMENT_ID, JOB_ID, and LOCATION_ID columnsaretaken into account to generate the subtotal
Answer: BD
Q14. 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
Q15. Which mandatory clause has to be added to the following statement to successfully create an external table called EMPDET?
CREATE TABLE empdet
(empno CHAR(2), ename CHAR(5), deptno NUMBER(4))
ORGANIZATION EXTERNAL
(LOCATION ('emp.daf));
A. TYPE
B. REJECTLIMIT
C. DEFAULT DIRECTORY
D. ACCESS PARAMETERS
Answer: C