Introduction to Oracle9i: SQL

QUESTION NO: 1

Examine the data in the EMPLOYEES and DEPARTMENTS tables.

You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use?

A. SELECT last_name, department_name

FROM employees , departments(+);

B. SELECT last_name, department_name

FROM employees JOIN departments (+);

C. SELECT last_name, department_name

FROM employees(+) e JOIN departments d

ON (e.department_id = d.department_id);

D. SELECT last_name, department_name

FROM employees e

RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);

E. SELECT last_name, department_name

FROM employees(+) , departments

ON (e.department_id = d.department_id);

F. SELECT last_name, department_name

FROM employees e LEFT OUTER

JOIN departments d ON (e.department_id = d.department_id);

Answer: F

Explanation:

Answer F is correct. This query shows correct syntax to retrieve all employees, whether or not they have matching departments in the department table. Oracle9i extends its compliance with ANSI/ISO by supporting that standard’s requirements for outer join syntax and semantics.

Incorrect Answers

A: This query uses “+” to create outer join as it was in Oracle8i, but it requires also usage of WHERE clause in SELECT statement.

B: The JOIN clause cannot be used with in conjunction with “+”: syntax is incorrect.

C: The JOIN clause cannot be used with in conjunction with “+”: syntax is incorrect.

D: This statement requires LEFT OUTER JOIN, not RIGHT OUTER JOIN.

E: This query uses incorrect syntax with “+” and ON to create outer join.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 112-114

Chapter 3: Advanced Data Selection in Oracle

QUESTION NO: 2

Examine the structure of the EMPLOYEES table:

EMPLOYEE_ID NUMBER Primary Key

FIRST_NAME VARCHAR2(25)

LAST_NAME VARCHAR2(25)

Which three statements inserts a row into the table? (Choose three)

A. INSERT INTO employees

VALUES ( NULL, ‘John’,‘Smith’);

B. INSERT INTO employees( first_name, last_name)

VALUES(‘John’,‘Smith’);

C. INSERT INTO employees

VALUES (‘1000’,‘John’,NULL);

D. INSERT INTO employees(first_name,last_name, employee_id)

VALUES ( 1000, ‘John’,‘Smith’);

E. INSERT INTO employees (employee_id)

VALUES (1000);

F. INSERT INTO employees (employee_id, first_name, last_name)

VALUES ( 1000, ‘John’,‘’);

Answer: C, E, F

Explanation:

Since EMPLOYEE_ID column is used as primary key, it cannot be NULL, so only INSERT statements in C, E and F are correct. You can insert the row with NULL LAST_NAME as in answer C, or only the row with EMPLOYEE_ID as in answer E, or the row with empty LAST_NAME column.

Incorrect Answers

A: This answer is incorrect because a primary key cannot be NULL.

B: INSERT statement does not contain primary key value at all, so this answer needs to be eliminated as correct one.

D: This statement shows incorrect order of columns of row which needs to be inserted into the table.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 260-267

Chapter 6: Manipulating Oracle Data

QUESTION NO: 3

You need to give the MANAGER role the ability to select from, insert into, and modify existing rows in the STUDENT_GRADES table. Anyone given this MANAGER role should be able to pass those privileges on to others.

Which statement accomplishes this?

A. GRANT select, insert, update

ON student_grades

TO manager;

B. GRANT select, insert, update

ON student_grades

TO ROLE manager;

C. GRANT select, insert, modify

ON student_grades

TO manager

WITH GRANT OPTION;

D. GRANT select, insert, update

ON student_grades

TO manager

WITH GRANT OPTION;

E. GRANT select, insert, update

ON student_grades

TO ROLE manager

WITH GRANT OPTION;

F. F.GRANT select, insert, modify

ON student_grades

TO ROLE manager

WITH GRANT OPTION;

Answer: D

Explanation:

This answer provides correct syntax of GRANT command to give the MANAGER role all asked privileges. Clause WITH GRANT OPTION will allow this role to pass those privileges on to others.

Incorrect Answers

A: This statement would be correct if it included WITH GRANT OPTION clause to allow this role to pass those privileges on to others.

B: This statement uses incorrect clause TO ROLE.

C: There is no option with name MODIFY in the GRANT command.

E: This statement uses incorrect clause TO ROLE.

F: There is no option with name MODIFY in the GRANT command. And this statement also uses incorrect clause TO ROLE.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365

Chapter 8: User Access Control in Oracle

QUESTION NO: 4

Examine the data in the EMPLOYEES table:

Which three subqueries work? (Choose three)

A. SELECT *

FROM employees

where salary > (SELECT MIN(salary)

FROM employees

GROUP BY department.id);

B. SELECT *

FROM employees

WHERE salary = (SELECT AVG(salary)

FROM employees

GROUP BY department_id);

C. SELECT distinct department_id

FROM employees

Where salary > ANY (SELECT AVG(salary)

FROM employees

GROUP BY department_id);

D. SELECT department_id

FROM employees

WHERE SALARY > ALL (SELECT AVG(salary)

FROM employees

GROUP BY department_id);

E. SELECT last_name

FROM employees

Where salary > ANY (SELECT MAX(salary)

FROM employees

GROUP BY department_id);

F. SELECT department_id

FROM employees

WHERE salary > ALL (SELECT AVG(salary)

FROM employees

GROUP BY AVG(SALARY));

Answer: C, D, E

Explanation:

These answers show correct syntax, because they use ANY and ALL keywords for convert multi-row output of sub-query to one-row result.

Incorrect Answers

A: This SELECT statement is incorrect because of multi-row return of sub-query: it will return minimal salary for EACH department.

B: This SELECT statement is incorrect because of multi-row return of sub-query: it will return average salary for EACH department.

F: This SELECT statement is incorrect because GROUP BY clause cannot contain functions, like AVG(), MIN(), MAX() and so on.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 145-156

Chapter 4: Subqueries

QUESTION NO: 5

The database administrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table.

As a user of the database, you created a table called HR in your schema. What happens when you execute this query?

SELECT *

FROM HR;

A. You obtain the results retrieved from the public synonym HR created by the database administrator.

B. You obtain the results retrieved from the HR table that belongs to your schema.

C. You get an error message because you cannot retrieve from a table that has the same name as a public synonym.

D. You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a Cartesian product.

E. You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a FULL JOIN.

Answer: B

Explanation:

By executing this query you will extract data from the HR table in your own schema, it will not work with HR synonym for the HUMAN_RESOURCES table of the GENERAL schema.

Incorrect Answers

A: The results will be retrieved from the table in your own schema, not from the GENERAL schema, using synonym HR.

C: There is no error: data from the table in your own schema will be retrieved by this query.

D: This query will not generate Cartesian product from both tables.

E: This query will not retrieve data from both tables as a FULL JOIN.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 331-335

Chapter 7: Creating Other Database Objects in Oracle

QUESTION NO: 6

Which two statements about views are true? (Choose two.)

A. A view can be created as read only.

B. A view can be created as a join on two or more tables.

C. A view cannot have an ORDER BY clause in the SELECT statement.

D. A view cannot be created with a GROUP BY clause in the SELECT statement.

E. A view must have aliases defined for the column names in the SELECT statement.

Answer: A, B

Explanation:

A view can becreated as read only object. However, it is possible to change data in the underlying table(s) with some restrictions.A view also can be created as a join on two or more tables. This type of view is called complex view. Complex views provide complicated data models where many base tables are drawn together into one virtual table.

Incorrect Answers

C: Query operations containing ORDER BY clause are also permitted, so long as the ORDER BY clause appearsoutside the parentheses. The following is an example of what I mean: CREATE VIEW my_view AS (SELECT*FROM emp) ORDER BY empno .

D: A view can be created with a GROUP BY clause in the SELECT statement.

E: It is not required to have aliases defined for the column names in the SELECT statement.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 292-309

Chapter 7: Creating Other Database Objects in Oracle

QUESTION NO: 7

Examine the description of the EMPLOYEES table:

Which statement shows the maximum salary paid in each job category of each department?

A. SELECT dept_id, job_cat, MAX(salary)

FROM employees

WHERE salary > MAX(salary);

B. SELECT dept_id, job_cat, MAX(salary)

FROM employees

GROUP BY dept_id, job_cat;

C. SELECT dept_id, job_cat, MAX(salary)

FROM employees;

D. SELECT dept_id, job_cat, MAX(salary)

FROM employees

GROUP BY dept_id;

E. SELECT dept_id, job_cat, MAX(salary)

FROM employees

GROUP BY dept_id, job_cat, salary;

Answer: B

Explanation:

This answer provides correct syntax and semantics to show the maximum salary paid in each job category of each department.

Incorrect Answers

A: This query will not return any row because condition SALARY > MAX(SALARY) is FALSE.

C: This query will return error because you cannot show maximum salary with DEPT_ID and JOB_CAT without grouping by these columns.

D: The GROUP BY clause is missing JOB_ID column.

E: You don’t need to group results of query by SALARY in the GROUP BY column.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365

Chapter 8: User Access Control in Oracle

QUESTION NO: 8

Management has asked you to calculate the value 12*salary* commission_pct for all the employees in the EMP table. The EMP table contains these columns:

Which statement ensures that a value is displayed in the calculated columns for all employees?

A. SELECT last_name, 12*salary* commission_pct

FROM emp;

B. SELECT last_name, 12*salary* (commission_pct,0)

FROM emp;

C. SELECT last_name, 12*salary*(nvl(commission_pct,0))

FROM emp;

D. SELECT last_name, 12*salary*(decode(commission_pct,0))

FROM emp;

Answer: C

Explanation:

This SELECT statement provides correct usage of NVL function to calculate columns for all employees. Oracle give you possibility to substitute a value in place of NULL. The basic syntax for NVL() is NVL(column_name, value_if_null). Notice that the column specified in NVL() contains an actual value. That value is what Oracle returns; when the column is NULL, the special string is returned. The value specified to be returned if the column value is NULL must be the same datatype as the column specified.

Incorrect Answers

A: This SELECT statement will return NULL value for rows with NULL COMMISION_PCT column.

B: It is incorrect syntax in this query: NVL function needs to be used for correct result.

D: The DECODE function is used as substitution of IF-THEN-ELSE PL/SQL construction in SQL queries. The SELECT statement provides incorrect syntax of it cannot have only two parameters.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 31-32

Chapter 1: Overview of Oracle Databases

QUESTION NO: 9

Which syntax turns an existing constraint on?

A. ALTER TABLE table_name

ENABLE constraint_name;

B. ALTER TABLE table_name

STATUS = ENABLE CONSTRAINT constraint_name;

C. ALTER TABLE table_name

ENABLE CONSTRAINT constraint_name;

D. ALTER TABLE table_name

STATUS ENABLE CONSTRAINT constraint_name;

E. ALTER TABLE table_name

TURN ON CONSTRAINT constraint_name;

F. ALTER TABLE table_name

TURN ON CONSTRAINT constraint_name;

Answer: C

Explanation:

ALTER TABLE statement with ENABLE CONSTRAINT keywords is correct answer to enable an existing constraint.

Incorrect Answers

A: This statement is missing CONSTRAINT keyword.

B: “STATUS =” is incorrect syntax to enable constraint for the table.

D: There is no STATUS keyword in the command to enable constraint.

E: There is no TURN ON keywords in the command to enable constraint.

F: There is no TURN ON keywords in the command to enable constraint.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 239-240

Chapter 5: Creating Oracle Database Objects

QUESTION NO: 10

Examine the description of the STUDENTS table:

Which two aggregate functions are valid on the START_DATE column? (Choose two)

A. SUM (start_date)

B. AVG (start_date)

C. COUNT (start_date)

D. AVG (start_date, end_date)

E. MIN (start_date)

F. MAXIMUM (start_date)

Answer: C, E

Explanation:

It is possible to apply COUNT() and MIN() functions on the column with DATE data type.

Incorrect Answers

A: Function SUM() cannot be used with DATE data type column.

B: Function AVG() cannot be used with DATE data type column.

D: Function AVG() cannot be used with DATE data type column. And function AVG() just has one parameter X, not two. It averages all X column values returned by the SELECT statement.

F: There is no MAXIMUM() function in Oracle, only MAX() function exists.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 81-85

Chapter 2: Limiting, Sorting, and Manipulating Return Data

QUESTION NO: 11

The EMPLOYEE tables have these columns:

You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column.

Which SQL statement displays the desired results?

A. SELECT last_name, (salary * 12) * commission_pct

FROM EMPLOYEES;

B. SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0)

FROM EMPLOYEES;

C. SELECT last_name, (salary * 12) * NVL2(commission_pct, 0)

FROM EMPLOYEES;

D. SELECT last_name, (salary * 12) * NVL(commission_pct, 0)

FROM EMPLOYEES;

Answer: D

Explanation:

This SELECT statement provides correct usage of NVL function to calculate columns for all employees. Oracle give you possibility to substitute a value in place of NULL. The basic syntax for NVL() is NVL(column_name, value_if_null). Notice that the column specified in NVL() contains an actual value. That value is what Oracle returns; when the column is NULL, the special string isreturned. The value specified to be returned if the column value is NULL must be the same datatype as the column specified.

Incorrect Answers

A: This SELECT statement will return NULL value for rows with NULL COMMISION_PCT column.

B: There is no IFNULL() function in Oracle.

C: The NVL2() function requires 3 parameters, not 2. Function NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not NULL. If expr1 is NULL, it returns expr3.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 31-32

Chapter 1: Overview of Oracle Databases

QUESTION NO: 12

Examine the data from the ORDERS and CUSTOMERS table.

Which SQL statement retrieves the order ID, customer ID, and order total for the orders that are placed on the same day that Martin places his orders?

A. SELECT ord_id, cust_id, ord_total

FROM orders, customers

WHERE cust_name=’Mating’

AND ord_date IN (’18-JUL-2000’,’21-JUL-2000’);

B. SELECT ord_id, cust_id, ord_total

FROM orders

Where ord_date IN (SELECT ord_date

FROM orders

WHERE cust_id = (SELECT cust_id

FROM customers

WHERE cust_name =

‘Martin’));

C. SELECT ord_id, cust_id, ord_total

FROM orders

Where ord_date IN (SELECT ord_date

FROM orders, customers

Where cust_name = ‘Martin’);

D. SELECT ord_id, cust_id, ord_total

FROM orders

WHERE cust_id IN (SELECT cust_id

FROM customers

WHERE cust name = ‘Martin’);

Answer: B

Explanation:

This query will return the order ID, customer ID, and order total for the orders that are placed on the same day that Martin places his orders.

Incorrect Answers

A: This query returns only Martin’s orders for July 18, 2000 and July 21, 2002, not orders of others that were placed on the same day that Martin placed his orders.

C: This query uses incorrect sub-query to extract dates when Martin placed his orders.

D: This query will return only Martin’s orders.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 145-156

Chapter 4: Subqueries

QUESTION NO: 13

You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.

Which statement accomplishes this task?

A. ALTER TABLE students

ADD PRIMARY KEY student_id;

B. ALTER TABLE students

ADD CONSTRAINT PRIMARY KEY (student_id);

C. ALTER TABLE students

ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

D. ALTER TABLE students

ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

E. ALTER TABLE students

MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

Answer: D

Explanation:

This statement provides correct syntax to add a primary key on the STUDENT_ID column of the STUDENT table.

Incorrect Answers

A: This ALTER TABLE statement is missing CONSTRAINT keyword and the name of the constraint.

B: This ALTER TABLE statement is missing the name of the constraint.

C: its incorrect syntax in the ALTER TABLE command: STUDENT_ID must be used with brackets.

E: We need to add constraint, not to modify existing one. Usage of the MODIFY keyword is incorrect in this case.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 239-240

Chapter 5: Creating Oracle Database Objects

QUESTION NO: 14

Evaluate the SQL statement:

1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal

2 FROM employees a,

3 (SELECT dept_id, MAX(sal) maxsal

4. FROM employees

5. GROUP BY dept_id) b

6. WHERE a.dept_id = b.dept_id

7. AND a.sal < b.maxsal;

What is the result of the statement?

A. The statement produces an error at line 1.

B. The statement produces an error at line 3.

C. The statement produces an error at line 6.

D. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all departments that pay less salary then the maximum salary paid in the company.

E. The statement returns the 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.

Answer: E

Explanation:

The statement returns the 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. This query is example of an inline view which is the sub-query in the FROM clause of the main query. The sub-query can be a SELECT statement that utilizes joins, the GROUP BY clause, or the ORDER BY clause.

Incorrect Answers

A: The statement does not produce an error at line 1.

B: The statement does not produce an error at line 3.

C: The statement does not produce an error at line 6.

D: The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all EMPLOYEES, NOT DEPARTMENTS, who earn less than the maximum salary in their department.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 161-165

Chapter 4: Subqueries

QUESTION NO: 15

Examine the data in the EMPLOYEES and DEPARTMENTS tables:

Also examine the SQL statements that create the EMPLOYEES and DEPARTMENTS tables:

CREATE TABLE departments

(department_id NUMBER PRIMARY KEY,

department_name VARCHAR2(30));

CREATE TABLE employees

(EMPLOYEE_ID NUMBER PRIMARY KEY,

EMP_NAME VARCHAR2(20),

DEPT_ID NUMBER REFERENCES

departments(department_id),

MGR_ID NUMBER REFERENCES

employees(employee id),

MGR_ID NUMBER REFERENCES

employees(employee id),

JOB_ID VARCHAR2(15).

SALARY NUMBER);

ON the EMPLOYEES,

On the EMPLOYEES table, EMPLOYEE_ID is the primary key.

MGR_ID is the ID of managers and refers to the EMPLOYEE_ID.

DEPT_ID is foreign key to DEPARTMENT_ID column of the DEPARTMENTS table.

On the DEPARTMENTS table, DEPARTMENT_ID is the primary key.

Examine this DELETE statement:

DELETE

FROM departments

WHERE department id = 40;

What happens when you execute the DELETE statement?

A. Only the row with department ID 40 is deleted in the DEPARTMENTS table.

B. The statement fails because there are child records in the EMPLOYEES table with department ID 40.

C. The row with department ID 40 is deleted in the DEPARTMENTS table. Also the rows with employee IDs 110 and 106 are deleted from the EMPLOYEES table.

D. The row with department ID 40 is deleted in the DEPARTMENTS table. Also the rows with employee IDs 106 and 110 and the employees working under employee 110 are deleted from the EMPLOYEES table.

E. The row with department ID 40 is deleted in the DEPARTMENTS table. Also all the rows in the EMPLOYEES table are deleted.

F. The statement fails because there are no columns specifies in the DELETE clause of the DELETE statement.

Answer: B

Explanation:

It will be error generated because there are 2 child records in the EMPLOYEES table with department number you try to delete from the DEPARTMENTS table.

Incorrect Answers

A: The row with department ID 40 will not be deleted because of the child records in the EMPLOYEES table.

C: Neither the row with department ID 40 will not be deleted not child records in the EMPLOYEES table will be deleted.

D: It will be error when you try to execute the DELETE statement, no rows will be deleted in the EMPLOYEES or the DEPARTMENTS tables.

E: It will be error when you try to execute the DELETE statement, no rows will be deleted in the EMPLOYEES or the DEPARTMENTS tables.

F: The statement fails because of constraint violation not because there are no columns specifies in the DELETE clause of the DELETE statement.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 240-245

Chapter 5: Creating Oracle Database Objects

QUESTION NO: 16

Which three are DATETIME data types that can be used when specifying column definitions? (Choose three.)

A. TIMESTAMP

B. INTERVAL MONTH TO DAY

C. INTERVAL DAY TO SECOND

D. INTERVAL YEAR TO MONTH

E. TIMESTAMP WITH DATABASE TIMEZONE

Answer: A, C, D

Explanation:

TIMESTAMP, INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH can be used to specify column definition.

Incorrect Answers

B: The INTERVAL MONTH TO DAY data type cannot be used when specifying column definitions there are only INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH data types.

E: The TIMESTAMP WITH DATABASE TIMEZONE data type cannot be used when specifying column definitions, because there are only TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 215-217

Chapter 5: Creating Oracle Database Objects

QUESTION NO: 17

Which SQL statement defines the FOREIGN KEY constraint on the DEPTNO column of the EMP table?

A. CREATE TABLE EMP

(empno NUMBER(4),

ename VARCHAR2(35),

deptno NUMBER(7,2) NOT NULL

CONSTRAINT emp_deptno_fk FOREIGN KEY deptno

REFERENCES dept deptno);

B. CREATE TABLE EMP

(empno NUMBER(4),

ename VARCHAR2(35),

deptno NUMBER(7,2)

CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

C. CREATE TABLE EMP

(empno NUMBER(4)

ename VARCHAR2(35),

deptno NUMBER(7,2) NOT NULL,

CONSTRAINT emp_deptno_fk REFERENCES dept (deptno)

FOREIGN KEY (deptno));

D. CREATE TABLE EMP (empno NUMBER(4),

ename VARCHAR2(35),

deptno NUMBER(7,2) FOREIGN KEY

CONSTRAINT emp deptno fk REFERENCES dept (deptno));

Answer: B

Explanation:

This statement provides correct syntax to define the FOREIGN KEY constraint on the DEPTNO column of the EMP table.

Incorrect Answers

A: There is incorrect syntax, because list of columns and column for the constraint need to be surrounded with the brackets.

C: It is incorrect to use FOREIGN KEY keywords to define constraint on the table. It can be used to add integrity constraint to existing table.

D: It is incorrect to use FOREIGN KEY keywords to define constraint on the table. It can be used to add integrity constraint to existing table.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 238-245

Chapter 5: Creating Oracle Database Objects

QUESTION NO: 18

Evaluate the set of SQL statements:

CREATE TABLE dept

(deptno NUMBER(2),

dname VARCHAR2(14),

loc VARCHAR2(13));

ROLLBACK;

DESCRIBE DEPT

What is true about the set?

A. The DESCRIBE DEPT statement displays the structure of the DEPT table.

B. The ROLLBACK statement frees the storage space occupies by the DEPT table.

C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.

D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.

Answer: A

Explanation:

The structure of the DEPT table will be displayed because the CREATE TABLE statement is DDL operation and it cannot be rolled back because implicit commit occurs on the database when a user exits SQL*Plus or issues a data-definition language (DDL) command such as a create table statement, user to create a database object, or an alter table statement, used to alter a database object.

Incorrect Answers

B: The ROLLBACK statement has nothing to do with the storage space of the DEPT table.

C: The DESCRIBE DEPT statement does not produce the error. It displays the structure of the DEPT table.

D: The COMMIT statement does not need to be introduced because implicit commit occurs on the database after creation of the table.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 281-283

Chapter 6: Manipulating Oracle Data

QUESTION NO: 19

Which data dictionary table should you query to view the object privileges granted to the user on specific columns?

A. USER_TAB_PRIVS_MADE

B. USER_TAB_PRIVS

C. USER_COL_PRIVS_MADE

D. USER_COL_PRIVS

Answer: D

Explanation:

The USER_COL_PRIVS data dictionary view will show the object privileges granted to the user on specific columns.

Incorrect Answers

A: There is no USER_TAB_PRIVS_MADE view in Oracle.

B: The USER_TAB_PRIVS data dictionary view is used to show the object privileges granted to the user on the tables, not specific columns.

C: There is no USER_COL_PRIVS_MADE view in Oracle.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365

Chapter 8: User Access Control in Oracle

QUESTION NO: 20

Examine the structure of the EMPLOYEES and DEPARTMENTS tables:

Evaluate this SQL statement:

SELECT employee_id, e.department_id, department_name,

Salary

FROM employees e, departments d

WHERE e.department_id = d.department_id;

Which SQL statement is equivalent to the above SQL statement?

A. SELECT employee_id, department_id, department_name,

Salary

FROM employees

WHERE department_id IN (SELECT department_id

FROM departments);

B. SELECT employee_id, department_id, department_name,

Salary

FROM employees

NATURAL JOIN departments;

C. SELECT employee_id, d.department_id, department_name,

Salary

FROM employees e

JOIN departments d

ON e.department_id = d.department_id;

D. SELECT employee_id, department_id, department_name,

Salary

FROM employees

JOIN departments

USING (e.department_id, d.department_id);

Answer: C

Explanation:

This query shows correct JOIN ON clause syntax and provides equivalent to the above SQL statement.

Incorrect Answers

A: This statement will show data only for the EMPLOYEES table with records that have department ID from DEPARTMENTS table, not join result of two tables.

B: NATURAL join selects rows from the tables that have equal values in all matched columns (same column names). If the columns having the same names have different datatypes, an error is returned.

D: There is incorrect usage of JOIN clause with USING keyword.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 112-114

Chapter 3: Advanced Data Selection in Oracle

QUESTION NO: 21

The EMP table contains these columns:

You need to display the employees who have not been assigned to any department.

You write the SELECT statement:

SELECT LAST_NAME, SALARY, DEPARTMENT_ID

FROM EMP

WHERE DEPARTMENT_ID = NULL;

What is true about this SQL statement?

A. The SQL statement displays the desired results.

B. The column in the WHERE clause should be changed to display the desired results.

C. The operator in the WHERE clause should be changed to display the desired results.

D. The WHERE clause should be changed to use an outer join to display the desired results.

Answer: C

Explanation:

The operator in the WHERE clause should be changed to display the desired results. There are times when you want to substitute a value in place of NULL. Oracle provides this functionality with a special function, called NVL(). You cannot use operation equal with NULL, but you can achieve desired results using NVL() function after the WHERE clause.

Incorrect Answers

A: The SQL statement will generate an error because you cannot use operation equal with NULL.

B: The column in the WHERE clause should not be changed to display the desired results.

D: Since there is only one table used in this query you don’t need to use outer join to display the desired results.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 31-32

Chapter 1: Overview of Oracle Databases

QUESTION NO: 22

Evaluate the SQL statement:

SELECT ROUND (TRUNC(MOD(1600,10),-1),2)

FROM dual;

What will be displayed?

A. 0

B. 1

C. 0.00

D. An error statement

Answer: A

Explanation:

Result will be 0. MOD(x,y) function calculates the modulus of x, defined in long division as the integer remainder when x is divided by y until no further whole number can be produced. TRUNC() function truncates x to the decimal precision of y. ROUND(x,y) rounds x to the decimal precision of y.

Incorrect Answers

B: Result will be 0, not 1.

C: Result will be 0, not 0.00 because MOD(1600,10) return 0 and all other functions (TRUNC and ROUND) return 0 also.

D: There is no error in this statement.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 69-71

Chapter 2: Limiting, Sorting, and Manipulating Return Data

QUESTION NO: 23

Examine the description of the MARKS table:

SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects.

Examine this SELECT statement based on the MARKS table:

SELECT subj1+subj2 total_marks, std_id

FROM marks

WHERE subj1 > AVG (subj1) AND subj2 > AVG(subj2)

ORDER BY total_marks;

What is the result of the SELECT statement?

A. The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject.

B. The statement returns an error at the SELECT clause.

C. The statement returns an error at the WHERE clause.

D. The statement returns an error at the ORDER BY clause.

Answer: C

Explanation:

The statement returns an error at the WHERE clause because group function AVG() cannot be used in the WHERE clause. Group functions can be used in SELECT clause and GROUP BY clause. They allow you to perform data operations on several values in a column of data as though the column were one collective group of data.

Incorrect Answers

A: The statement does not execute successfully because an error will be generated.

B: The statement returns an error at the WHERE, not at the SELECT clause.

D: The statement returns an error at the WHERE, not at the ORDER BY clause.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 122-125

Chapter 3: Advanced Data Selection in Oracle

QUESTION NO: 24

Which /SQL*Plus feature can be used to replace values in the WHERE clause?

A. Substitution variables

B. Replacement variables

C. Prompt variables

D. Instead-of variables

E. This feature cannot be implemented through /SQL*Plus.

Answer: A

Explanation:

Lexical substitution variables can be used to replace values in the WHERE clause.

Incorrect Answers

B: There are no replacement variables SQL*Plus feature in Oracle.

C: There is no prompt variables SQL*Plus feature in Oracle.

D: There is no instead-of variables SQL*Plus feature in Oracle.

E: This feature is implemented in the SQL*Plus with lexical substitution variables.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 165-173

Chapter 4: Subqueries

QUESTION NO: 25

You want to display the titles of books that meet these criteria:

1. Purchased before January 21, 2001

2. Price is less then $500 or greater than $900

You want to sort the results by their data of purchase, starting with the most recently bought book.

Which statement should you use?

A. SELECT book_title

FROM books

WHERE price between 500 and 900

AND purchase_date < ’21-JAN-2001’

ORDER BY purchase_date;

B. SELECT book_title

FROM books

WHERE price IN (500,900)

AND purchase_date < ’21-JAN-2001’

ORDER BY purchase date ASC;

C. SELECT book_title

FROM books

WHERE price < 500 or > 900

AND purchase_date < ’21-JAN-2001’

ORDER BY purchase date DESC;

D. SELECT book_title

FROM books

WHERE (price < 500 OR price > 900)

AND purchase_date < ’21-JAN-2001’

ORDER BY purchase date DESC;

Answer: D

Explanation:

This statement provides required results.

Incorrect Answers

A: This query will show books with price in range $500 and $900, not less then $500 or greater than $900.

B: This query will show books with prices exactly $500 or $900, not less then $500 or greater than $900.

C: This order will not show correct rows because of incorrect syntax in the WHERE clause..

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 56-66

Chapter 2: Limiting, Sorting, and Manipulating Return Data

QUESTION NO: 26

Which statement explicitly names a constraint?

A. ALTER TABLE student_grades

ADD

FOREIGN KEY (student_id) REFERENCES students(student_id);

B. ALTER TABLE student_grades

ADD CONSTRAINT NAME = student_id_fk

FOREIGN KEY (student_id) REFERENCES students(student_id);

C. ALTER TABLE student_grades

ADD CONSTRAINT student_id_fk

FOREIGN KEY (student_id) REFERENCES students(student_id);

D. ALTER TABLE student_grades

ADD NAMED CONSTRAINT student_id_fk

FOREIGN KEY (student_id) REFERENCES students(student_id);

E. ALTER TABLE student_grades

ADD NAME student_id_fk

FOREIGN KEY (student_id) REFERENCES students(student_id);

Answer: C

Explanation:

This statement provides correct syntax to add a foreign key constraint to the existing table.

Incorrect Answers

A: The ADD FOREIGN KEY is wrong construction to add a foreign key constraint to the existing table.

B: The ADD CONSTRAINT NAME is wrong construction to add a foreign key constraint to the existing table.

D: The ADD NAMED CONSTRAINT is wrong construction to add a foreign key constraint to the existing table.

E: The ADD NAME is wrong construction to add a foreign key constraint to the existing table.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 238-239

Chapter 5: Creating Oracle Database Objects

QUESTION NO: 27

Examine the SQL statements that create ORDERS table:

For which columns would an index be automatically created when you execute the above

SQL statement? (Choose two)

A. SER_NO

B. ORDER_ID

C. STATUS

D. PROD_ID

E. ORD_TOTAL

F. Composite index on ORDER_ID and ORDER_DATE

Answer: A, F

Explanation:

Indexes are created automatically by Oracle to support integrity constraints that enforce uniqueness. The two types of integrity constraints that enforce uniqueness are PRIMARY KEY and UNIQUE constraints. When the primary key or UNIQUE constraint is declared, a unique index to support the column’s uniqueness is also created, and all values in all columns that were defined as part of the primary key or UNIQUE constraint are placed into the index.

Incorrect Answers

B: There will not be index for ORDER_ID column.

C: There will not be index for STATUS column.

D: There will not be index for PROD_ID column.

E: There will not be index for ORD_TOTAL column.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 237-238

Chapter 5: Creating Oracle Database Objects

QUESTION NO: 28

You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables:

EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME.

The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table.

You want to modify the view by adding a fourth column, MANAGER_ID of NUMBER data type from the EMPLOYEES tables.

How can you accomplish this task?

A. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);

B. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);

C. ALTER VIEW emp_dept_vu AS

SELECT employee_id, employee_name,

department_name, manager_id

FROM employee e, departments d

WHERE e.department_id = d.department_id;

D. MODIFY VIEW emp_dept_vu AS

SELECT employee_id, employee_name,

department_name, manager_id

FROM employees e, departments d

WHERE e.department_id = d.department_id;

E. CREATE OR REPLACE VIEW emp_dept_vu AS

SELECT employee_id, employee_name,

department_name, manager_id

FROM employees e, departments d

WHERE e.department_id = d.department_id;

F. You must remove the existing view first, and then run the CREATE VIEW command with a new column list to modify a view.

Answer: E

Explanation:

When we want to alter the underlying data used in the definition of a view, we use the CREATE OR REPLACE VIEW statement. When a CREATE OR REPLACE VIEW statement is issued, Oracle will disregard the error that arises when it encounters the view that already exists with that name, and it will overwrite the definition for the old view with the definition for the new one.

Incorrect Answers

A: There is no ALTER VIEW command in Oracle.

B: There is no MODIFY VIEW command in Oracle.

C: There is no ALTER VIEW command in Oracle.

D: There is no MODIFY VIEW command in Oracle.

F: You don’t need to remove the existing view to create modified view. You are able to do that with CREATE OR REPLACE command.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 310-313

Chapter 7: Creating Other Database Objects in Oracle

QUESTION NO: 29

For which two constraints does the Oracle Server implicitly create a unique index? (Choose two.)

A. NOT NULL

B. PRIMARY KEY

C. FOREIGN KEY

D. CHECK

E. UNIQUE

Answer: B, E

Explanation:

Indexes are created automatically by Oracle to support integrity constraints that enforce uniqueness. The two types of integrity constraints that enforce uniqueness are PRIMARY KEY and UNIQUE constraints. When the primary key or UNIQUE constraint is declared, a unique index to support the column’s uniqueness is also created, and all values in all columns that were defined as part of the primary key or UNIQUE constraint are placed into the index.

Incorrect Answers

A: Oracle will not implicitly create an unique index for the NOT NULL constraint.

C: Oracle will not implicitly create an unique index for the FOREIGN KEY constraint.

D: Oracle will not implicitly create an unique index for the FOREIGN KEY constraint.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 237-238

Chapter 5: Creating Oracle Database Objects

QUESTION NO: 30

Which three SELECT statements displays 2000 in the format “$2,000.00”? (Choose three)

A. SELECT TO CHAR (2000,‘$#,###.##’)

FROM dual;

B. SELECT TO CHAR (2000, ‘$0,000.00’)

FROM dual;

C. SELECT TO CHAR (2000, ‘$9,999.00’)

FROM dual;

D. SELECT TO CHAR (2000, ‘$9,999.99’)

FROM dual;

E. SELECT TO CHAR (2000, ‘$2,000.00’)

FROM dual;

F. SELECT TO CHAR (2000, ‘$N,NNN.NN’)

FROM dual;

Answer: B, C, D

Explanation:

Only queries in answers B, C and D will show result as in the format “$2,000.00”.

Incorrect Answers

A: Oracle error “ORA-01481: invalid number format model” will be generated.

E: Oracle error “ORA-01481: invalid number format model” will be generated.

F: Oracle error “ORA-01481: invalid number format model” will be generated.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365

Chapter 8: User Access Control in Oracle

QUESTION NO: 31

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Which UPDATE statement is valid?

A. UPDATE new_employees SET name = (Select last_name||

first_name

FROM employees

Where employee_id

=180)

WHERE employee_id =180;

B. UPDATE new_employees SET name = (SELECT

last_name||first_name

FROM employees)

WHERE employee_id =180;

C. UPDATE new_employees SET name = (SELECT last_name||

first_name

FROM employees

WHERE employee_id

=180)

WHERE employee_id =(SELECT employee_id

FROM new employees);

D. UPDATE new_employees SET name = (SELECT last name||

first_name

FROM employees

WHERE employee_id=

(SELECT employee_id

FROM new_employees))

WHERE employee_id

=180;

Answer: A

Explanation:

Sub-query in this answer will return one row value, concatenated first and last name for the employee with ID 180, so update will be successful. When sub-queries are linked to the parent by equality comparisons, the parent query expects only one row of data from the sub-query.

Incorrect Answers

B: Sub-query will return concatenated first and last name for ALL records from the table EMPLOYEES. It will cause an error for the parent query, because it expects one only one row.

C: Last WHERE statement in this query will generate error because sub-query returns multi-row result.

D: SELECT statement for the NEW_EMPLOYEES table in this query will generate error because sub-query returns multi-row result.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 150-156

Chapter 4: Subqueries

QUESTION NO: 32

Examine the structure of the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables.

Which two SQL statements produce the name, department name, and the city of all the employees who earn more then 10000? (Choose two)

A. SELECT emp_name, department_name, city

FROM employees e

JOIN departments d

USING (department_id)

JOIN locations 1

USING (location_id)

WHERE salary > 10000;

B. SELECT emp_name, department_name, city

FROM employees e, departments d, locations 1

JOIN ON (e.department_id = d.department id)

AND (d.location_id =1.location_id)

AND salary > 10000;

C. SELECT emp_name, department_name, city

FROM employees e, departments d, locations 1

WHERE salary > 10000;

D. SELECT emp_name, department_name, city

FROM employees e, departments d, locations 1

WHERE e.department_id = d.department_id

AND d.location_id = 1.location_id

AND salary > 10000;

E. SELECT emp_name, department_name, city

FROM employees e

NATURAL JOIN departments, locations

WHERE salary > 10000;

Answer: A, D

Explanation:

A is a correct response (JOIN USING).

D: This is the correct syntax for a traditional (Non-ANSI Standard) JOIN. The join is defined in the WHERE Clause.

Incorrect Answers

B: Not the correct syntax for an ANSI Standard JOIN

C: This query will built Cartesian product because there is no join conditions in WHERE clause to join tables.

E: NATURAL JOIN is a join between two where Oracle joins the tables according to the column(s) in the two tables sharing the same name. It is required to add one more NATURAL JOIN clause to join additional table.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 98-118

Chapter 3: Advanced Data Selection in Oracle

QUESTION NO: 33

Examine the description of the EMPLOYEES table:

Which statement shows the department ID, minimum salary, and maximum salary paid in that department, only of the minimum salary is less then 5000 and the maximum salary is more than 15000?

A. SELECT dept_id, MIN(salary(, MAX(salary)

FROM employees

WHERE MIN(salary) < 5000 AND MAX(salary) > 15000;

B. SELECT dept_id, MIN(salary), MAX(salary)

FROM employees

WHERE MIN(salary) < 5000 AND MAX(salary) > 15000

GROUP BY dept_id;

C. SELECT dept_id, MIN(salary), MAX(salary)

FROM employees

HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;

D. SELECT dept_id, MIN(salary), MAX(salary)

FROM employees

GROUP BY dept_id

HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;

E. SELECT dept_id, MIN(salary), MAX(salary)

FROM employees

GROUP BY dept_id, salary

HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;

Answer: D

Explanation:

This SELECT statement shows correct result.

Incorrect Answers

A: To provide correct data statement needs also GROUP BY clause.

B: This statement will not provide correct results.

C: HAVING clause can be used only in conjunction with GROUP BY clause.

E: You need only grouping by department, not by salary.

QUESTION NO: 34

Examine the structure if the EMPLOYEES table:

You need to create a view called EMP_VU that allows the user to insert rows through the view. Which SQL statement, when used to create the EMP_VU view, allows the user to insert rows?

A. CREATE VIEW emp_Vu AS

SELECT employee_id, emp_name,

department_id

FROM employees

WHERE mgr_id IN (102, 120);

B. CREATE VIEW emp_Vu AS

SELECT employee_id, emp_name, job_id

department_id

FROM employees

WHERE mgr_id IN (102, 120);

C. CREATE VIEW emp_Vu AS

SELECT department_id, SUM(sal) TOTALSAL

FROM employees

WHERE mgr_id IN (102, 120)

GROUP BY department_id;

D. CREATE VIEW emp_Vu AS

SELECT employee_id, emp_name, job_id,

DISTINCT department_id

FROM employees;

Answer: B

Explanation:

This statement will create view that can be used to change tables in underlying table through simple views. It includes primary key, NOT NULL column and foreign key to avoid constraint restrictions.

Incorrect Answers

A: This statement does not include JOB_ID column that cannot be NULL. In general, all constraint restrictions defined on the underlying table also apply to modifying data via the view. For example, you can’t add data to an underlying table via a view that violates the table’s primary key constraint.

C: You cannot update a column of an underlying table if the simple view use a single-row function to define the column.

D: You may not insert, update, or delete records data on the table underlying the simple view if the SELECT statement creating the view contains a GROUP BY clause, GROUP function, or DISTINCT clause.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 298-299

Chapter 7: Creating Other Database Objects in Oracle

QUESTION NO: 35

The STUDENT_GRADES table has these columns:

The registrar has asked for a report on the average grade point average (GPA) for students enrolled during semesters that end in the year 2000. Which statement accomplish this?

A. SELECT AVERAGE (gpa)

FROM student_grades

WHERE semester_end > ’01-JAN-2000’ and semester end < 31-DEC-2000’;

B. SELECT COUNT (gpa)

FROM student grades

WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;

C. SELECT MIN (gpa)

FROM student grades

WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;

D. SELECT AVG (gpa)

FROM student_grades

WHERE semester_end BETWEEN ’01-JAN-2000’ and ’31.DEC.2000’;

E. SELECT SUM (gpa)

FROM student grades

WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;

F. SELECT MEDIAN (gpa)

FROM student_grades

WHERE semester end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;

Answer: D

Explanation:

Statement in this answer will show correct result, using function AVG(). This function takes the values for a single column on all rows returned by the query and calculates the average value for that column.

Incorrect Answers

A: There is no AVERAGE() function in Oracle.

B: COUNT() will calculate number of row, not an average grade point.

C: MIN() function will calculate minimum grade for all students enrolled during semesters that end in the year 2000.

E: SUM() will calculate sum of all grade points for all students enrolled during semesters that end in the year 2000.

F: There is no MEDIAN() function in Oracle.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 123-125

Chapter 3: Advanced Data Selection in Oracle

QUESTION NO: 36

Examine the structure of the EMPLOYEES and NEW EMPLOYEES tables:

Which MERGE statement is valid?

A. MERGE INTO new_employees c

USING employees e

ON (c.employee_id = e.employee_id)

WHEN MATCHED THEN

UPDATE SET

c.name = e.first_name ||’,’|| e.last_name

WHEN NOT MATCHED THEN

INSERT VALUES(e.employee_id, e.first_name ||’,

‘||e.last_name);

B. MERGE new_employees c

USING employees e

ON (c.employee_id = e.employee_id)

WHEN EXIST THEN

UPDATE SET

c.name = e.first_name ||’,’|| e.last_name

WHEN NOT MATCHED THEN

INSERT VALUES(e.employee_id, e.first_name ||’,

‘||e.last_name);

C. MERGE INTO new employees c

USING employees e

ON (c.employee_id = e.employee_id)

WHEN EXISTS THEN

UPDATE SET

c.name = e.first_name ||’,’|| e.last_name

WHEN NOT MATCHED THEN

INSERT VALUES(e.employee_id, e.first_name ||’,

‘||e.last_name);

D. MERGE new_employees c

FROM employees e

ON (c.employee_id = e.employee_id)

WHEN MATCHED THEN

UPDATE SET

c.name = e.first_name ||’,’|| e.last_name

WHEN NOT MATCHED THEN

INSERT INTO new_employees VALUES(e.employee_id, e.first_name ||’.’||e.last_name);

Answer: A

Explanation:

Correct syntax for the MERGE command is MERGE INTO table1 USING table2 on (join_condition) WHEN MATCHED UPDATE SET col1 = value WHEN NOT MATCHED INSERT (column_list) values (column_values).

Incorrect Answers

B: WHEN EXIST THEN clause cannot be used in the MERGE statement.

C: WHEN EXIST THEN clause cannot be used in the MERGE statement.

D: FROM clause cannot be used in the MERGE statement.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 273-275

Chapter 6: Manipulating Oracle Data

QUESTION NO: 37

Which two are true about aggregate functions? (Choose two.)

A. You can use aggregate functions in any clause of a SELECT statement.

B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.

C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.

D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.

E. You can use aggregate functions on a table, only by grouping the whole table as one single group.

F. You cannot group the rows of a table by more than one column while using aggregate functions.

Answer: C, D

Explanation:

It is possible to mix single row columns with aggregate functions in the column list of a

SELECT statement by grouping on the single row columns. Also it is acceptable to pass column names, expressions, constraints, or other functions as parameters to an aggregate function.

Incorrect Answers

A: You cannot use aggregate functions in any clause of a SELECT statement. For example, they cannot be used with a WHEN statement.

B: It is not possible to use aggregate functions in the WHERE clause of a SELECT statement. But they can be used with a HAVING clause used after the GROUP BY clause, for example.

E: You don’t need to group the whole table as one single group.

F: It is possible to group more than one column while using aggregate functions.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 122-135

Chapter 3: Advanced Data Selection in Oracle

QUESTION NO: 38

Examine the data of the EMPLOYEES table.

EMPLOYEES (EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID)

Which statement lists the ID, name, and salary of the employee, and the ID and name of the employee's manager, for all the employees who have a manager and earn more than 4000?

A. SELECT employee_id "Emp_id", emp_name "Employee",

salary,

employee_id "Mgr_id", emp_name "Manager"

FROM employees

WHERE salary > 4000;

B. SELECT e.employee_id "Emp_id", e.emp_name "Employee",

e.salary,

m.employee_id "Mgr_id", m.emp_name "Manager"

FROM employees e, employees m

WHERE e.mgr_id = m.mgr_id

AND e.salary > 4000;

C. SELECT e.employee_id "Emp_id", e.emp_name "Employee",

e.salary,

m.employee_id "Mgr_id", m.emp_name "Manager"

FROM employees e, employees m

WHERE e.mgr_id = m.employee_id

AND e.salary > 4000;

D. SELECT e.employee_id "Emp_id", e.emp_name "Employee",

e.salary,

m.mgr_id "Mgr_id", m.emp_name "manager"

FROM employees e, employees m

WHERE e.mgr_id = m.employee_id

AND e.salary > 4000;

E. SELECT e.employee_id "Emp_id", e.emp_name "Employee",

e.salary,

m.mgr_id "Mgr_id", m.emp_name "Manager"

FROM employees e, employees m

WHERE e.employee_id = m.employee_id

AND e.salary > 4000;

Answer: C

Explanation:

This statement lists the ID, name, and salary of the employee, and the ID and name of the employee's manager, for all the employees who have a manager and earn more than 4000

Incorrect Answers

A: This statement does not check does employee have a manager or not, so it will not provide correct result.

B: Usage of “e.mgr_id = m.mgr_id” condition is wrong to achieve required result.

D: This statement uses “m.mgr_id” to show manager’s manager, not employ’s manager.

E: Usage of “WHERE e.employee_id = m.employee_id” condition is wrong to achieve required result.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 118-122

Chapter 3: Advanced Data Selection in Oracle

QUESTION NO: 39

In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the SELECT statement?

A. Immediately after the SELECT clause

B. Before the WHERE clause

C. Before the FROM clause

D. After the ORDER BY clause

E. After the WHERE clause

Answer: E

Explanation:

The GROUP BY clause can be place only after the WHERE clause, or after FROM clause if there is no the WHERE clause in the statement.

Incorrect Answers

A: It is not possible to place the GROUP BY clause immediately after the SELECT clause.

B: It is not possible to place the GROUP BY clause before the WHERE clause, it can be done only after it.

C: It is not possible to place the GROUP BY clause before the FROM clause.

D: It is not possible to place the GROUP BY clause after the ORDER BY clause.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 56-67

Chapter 2: Limiting, Sorting, and Manipulating Return Data

QUESTION NO: 40

The STUDENT_GRADES table has these columns:

STUDENT_ID NUMBER (12)

SEMESTER_END DATE

GPA NUMBER (4,3)

The register has requested a report listing the students' grade point averages (GPA), sorted from highest grade point average to lowest within each semester, starting from the earliest date. Which statement accomplishes this?

A. SELECT student_id, semester_end, gpa

FROM student_grades

ORDER BY semester_end DESC, gpa DESC;

B. SELECT student_id, semester_end, gpa

FROM student_grades

ORDER BY semester_end ASC, gpa ASC;

C. SELECT student_id, semester_end, gpa

FROM student_grades

ORDER BY semester_end, gpa DESC;

D. SELECT student_id, semester_end, gpa

FROM student_grades

ORDER BY gpa DESC, semester_end DESC;

E. SELECT student_id, semester_end, gpa

FROM student_grades

ORDER BY gpa DESC, semester_end ASC;

Answer: C

Explanation:

This answer shows correct syntax and semantics to receive desired result.

Incorrect Answers

A: Semesters will be sorted started from the oldest date, not the earliest.

B: GPA data will be sorted in ascending order, what is opposite to our task.

D: Semesters will be sorted started from the oldest date, not the earliest. Only difference with answer A is order of columns in the ORDER BY clause.

E: This query has wrong order of columns to sort: results need to be sorted first by semester, than by grade point average.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 56-61

Chapter 2: Limiting, Sorting, and Manipulating Return Data

Site Search:

Close

Close
Download Free Demo of VCE
Exam Simulator

Experience Avanset VCE Exam Simulator for yourself.


Simply submit your e-mail address below to get started with our interactive software demo of your free trial.


Enter Your Email Address

Free Demo Limits: In the demo version you will be able to access only first 5 questions from exam.