QUESTION NO: 1
Examine the data in the EMPLOYEES and DEPARTMENTS tables.
EMPLOYEES
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
Kochhar 5000
DEPARTMENTS
DEPARTMENT_ID DEPARTMENT_NAME
10 Sales
20 Marketing
30 Accounts
40 Administration
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:
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
…
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 be created 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 BYempno.
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