Oracle 1z0-007
Introduction to Oracle9i: SQL
Version 12.0
1z0 -007
Leading the way in IT testing and certification tools, www.testking.com
- 2 -
Important Note, Please Read Carefully
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.
Further Material
For this test TestKing also provides:
* Interactive Test Engine Examinator. Check out an Examinator Demo at
http://www.testking.com/index.cfm?pageid=724
Latest Version
We are constantly reviewing our products. New material is added and old material is revised.
Free updates are available for 90 days after the purchase. You should check your member
zone at TestKing an update 3-4 days before the scheduled exam date.
Here is the procedure to get the latest version:
1. Go to www.testking.com
2. Click on Member zone/Log in
3. The latest versions of all purchased products are downloadable from here. Just click
the links.
For most updates, it is enough just to print the new questions at the end of the new version,
not the whole document.
Feedback
Feedback on specific questions should be send to feedback@testking.com. You should state:
Exam number and version, question number, and login ID.
Our experts will answer your mail promptly.
Explanations
Currently this product does not include explanations. If you are interested in providing
TestKing with explanations contact feedback@testking.com
. Include the following
information: exam, your background regarding this exam in particular, and what you consider
a reasonable compensation for the work.
Copyright
Each pdf file contains a unique serial number associated with your particular name and
contact information for security purposes. So if we find out that a particular pdf file is being
distributed by you, TestKing reserves the right to take legal action against you according to
the International Copyright Laws.
1z0 -007
Leading the way in IT testing and certification tools, www.testking.com
- 3 -
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.
1z0 -007
Leading the way in IT testing and certification tools, www.testking.com
- 4 -
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.
1z0 -007
Leading the way in IT testing and certification tools, www.testking.com
- 5 -
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.
评论0