Q1. 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. The statement would execute successfully to produce the required output. 

B. The statement would not execute because inline views and outer joins cannot be used together. 

C. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query. 

D. The statement would not execute because the GROUP BY clause cannot be used in the inline view. 

View Answer

Q2. 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 

View Answer

Q3. View the Exhibit and examine the description of the EMPLOYEES table. 

Evaluate the following SQL statement: 

SELECT first_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 querywouldexecutetogive the desired output. 

B. Thequerywouldnot execute becausedatefunctions cannot be nested. 

C. Thequerywould executebuttheoutputwouldgive reviewdates that are Sundays. 

D. Thequery would not execute becausetheNEXT_DAY function acceptsastring as argument. 

View Answer

Q4. 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. 

View Answer

Q5. 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. 

View Answer

Q6. 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. 

View Answer

Q7. 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; 

View Answer

Q8. View the Exhibit and examine the structure of the EMPLOYEES table. 

You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree? 

A. WHERE 

B. HAVING 

C. GROUP BY 

D. START WITH 

E. CONNECT BY PRIOR 

View Answer

Q9. 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 

View Answer

Q10. View the Exhibit and examine the description of the EMPLOYEES table. 

Your company decided to give a monthly bonus of $50 to all the employees who have completed five years in the company. The following statement is written to display the LAST_NAME, DEPARTMENT_ID, and the total annual salary: 

SELECT last_name, departmentjd, salary450*12 "Annual Compensation" FROM employees WHERE MONTHS_BETWEEN(SYSDATE, hire_date)/12 >= 5; 

When you execute the statement, the "Annual Compensation" is not computed correctly. What changes would you make to the query to calculate the annual compensation correctly? 

A. Change the SELECT clause to SELECT last_name, department_id, salary*12+50 "Annual Compensation". 

B. Change the SELECT clause to SELECT last_name, department_id, salary+(50*12) "Annual Compensation". 

C. Change the SELECT clause to SELECT last_name, department_id, (salary +50)*12 "Annual Compensation". 

D. Change the SELECT clause to SELECT last_name, department_id, (salary*12)+50 "Annual Compensation". 

View Answer