21certify.com
Oracle:
Introduction to Oracle9i: SQL
1Z0-007
Version 8.0
1Z0-007 2
21certify.com
Study Tips
This product will provide you questions and answers along with detailed explanations carefully
compiled and written by our experts. Try to understand the concepts behind the questions instead of
cramming the questions. Go through the entire document at least twice so that you make sure that
you are not missing anything.
Latest Version
We are constantly reviewing our products. New material is added and old material is revised. Free
updates are available for 365 days after the purchase. You should check the products page on the
www.21certify.com web site for an update 3-4 days before the scheduled exam date.
Important Note:
Please Read Carefully
This 21certify Exam has been carefully written and compiled by 21certify Exams experts. It is
designed to help you learn the concepts behind the questions rather than be a strict memorization tool.
Repeated readings will increase your comprehension.
We continually add to and update our 21certify Exams with new questions, so check that you have the
latest version of this 21certify Exam right before you take your exam.
For security purposes, each PDF file is encrypted with a unique serial number associated with your
21certify Exams account information. In accordance with International Copyright Law, 21certify
Exams reserves the right to take legal action against you should we find copies of this PDF file has
been distributed to other parties.
Please tell us what you think of this 21certify Exam. We appreciate both positive and critical
comments as your feedback helps us improve future versions.
We thank you for buying our 21certify Exams and look forward to supplying you with all your
Certification training needs.
Good studying!
21certify Exams Technical and Support Team
1Z0-007 3
21certify.com
Q.1 Examine the data in the EMPLOYEES and DEPARTMENTS tables.
EMPLOYEES
LAST_NAM
E
DEPARTMENT_I
D
SALAR
Y
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
Kochhar 5000
DEPARTMENTS
DEPARTMENT_I
D
DEPARTMENT_NA
ME
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
1Z0-007 4
21certify.com
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
Q.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)
1Z0-007 5
21certify.com
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
Q.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
评论0