Q1. - (Topic 2) 

View the Exhibit; e xamine the structure of the PROMOTIONS table. 

Each promotion has a duration of at least seven days. 

Your manager has asked you to generate a report, which provides the weekly cost for each promotion done to l date. 

Which query would achieve the required result? 

A. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions; 

B. SELECT promo_name,(promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions; 

C. SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions; 

D. SELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7) FROM promotions; 

View Answer

Q2. - (Topic 1) 

When does a transaction complete? (Choose all that apply.) 

A. When a PL/SQL anonymous block is executed B. When a DELETE statement is executed 

C. When a data definition language statement is executed 

D. When a TRUNCATE statement is executed after the pending transaction 

E. When a ROLLBACK command is executed 

View Answer

Q3. - (Topic 2) 

You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department. 

Which statement accomplishes this task? 

A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id; 

B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal; 

C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id); 

D. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal; 

View Answer

Q4. - (Topic 1) 

Evaluate the following SQL statements: Exhibit: 

Which is the correct output of the above query? 

A. +00-300, +54-02,+00 11:12:10.123457 

B. +00-300,+00-650,+00 11:12:10.123457 

C. +25-00, +54-02, +00 11:12:10.123457 

D. +25-00,+00-650,+00 11:12:10.123457 

View Answer

Q5. - (Topic 1) 

View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables. 

You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007. 

You issue the following query: 

Which statement is true regarding the above query? 

A. It executes successfully and gives the required result. 

B. It executes successfully but does not give the required result. 

C. It produces an error because the join order of the tables is incorrect. 

D. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statement. 

View Answer

Q6. - (Topic 2) 

The user Sue issues this SQL statement: 

GRANT SELECT ON sue.EMP TO alice WITH GRANT OPTION; 

The user Alice issues this SQL statement: 

GRANT SELECT ON sue.EMP TO reena WITH GRANT OPTION; 

The user Reena issues this SQL statement: 

GRANT SELECT ON sue.EMP TO timber; 

The user Sue issues this SQL statement: 

REVOKE select on sue.EMP FROM alice; 

For which users does the revoke command revoke SELECT privileges on the SUE.EMP table? 

A. Alice only 

B. Alice and Reena 

C. Alice, Reena, and Timber 

D. Sue, Alice, Reena, and Timber 

View Answer

Q7. - (Topic 2) 

View the Exhibit and examine the data in the EMPLOYEES table: 

You want to display all the employee names and their corresponding manager names. 

Evaluate the following query: 

SQL> SELECT e.employee_name "EMP NAME", m.employee_name "MGR NAME" 

FROM employees e ______________ employees m 

ON e.manager_id = m.employee_id; 

Which JOIN option can be used in the blank in the above query to get the required output? 

Exhibit: 

A. only inner JOIN 

B. only FULL OUTER JOIN 

C. only LEFT OUTER JOIN 

D. only RIGHT OUTER JOIN 

View Answer

Q8. - (Topic 1) 

Which statement is true regarding synonyms? 

A. Synonyms can be created only for a table 

B. Synonyms are used to reference only those tables that are owned by another user 

C. The DROP SYNONYM statement removes the synonym and the table on which the synonym has been created becomes invalid 

D. A public synonym and a private synonym can exist with the same name for the same table 

View Answer

Q9. - (Topic 2) 

You want to display the date for the first Monday of the next month and issue the following command: 

SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'), 'dd "is the first Monday for"fmmonth rrrr') FROM DUAL; 

What is the outcome? 

A. It executes successfully and returns the correct result. 

B. It executes successfully but does not return the correct result. 

C. It generates an error because TO_CHAR should be replaced with TO_DATE. 

D. It generates an error because rrrr should be replaced by rr in the format string. 

E. It generates an error because fm and double quotation marks should not be used in the format string. 

View Answer

Q10. - (Topic 1) 

Evaluate the following SQL statements: Exhibit: 

Exhibit: 

The above command fails when executed. What could be the reason? 

A. The BETWEEN clause cannot be used for the CHECK constraint 

B. SYSDATE cannot be used with the CHECK constraint 

C. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY 

D. The CHECK constraint cannot be placed on columns having the DATE data type 

View Answer