Oracle 1Z0-007 Introduction to oracle9l:sql Part 1

Oracle 1Z0-007 Introduction to oracle9l:sql Part 1

Get Full version on: http://www.updateddumps.com/


QUESTION 1


Examine the data in the EMPLOYEES and DEPARTMENTS tables.
EMPLOYEES
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
Bill 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 ON (e. department_ id = d. departments_id);
FROM employees(+) e JOIN departments d 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);
Correct 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 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',");
Correct Answer: CEF

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 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;
Correct 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 4

Examine the data in the EMPLOYEES table:
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
Bill 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 ANG (SALARY));
Correct Answer: CDE

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 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.
Correct 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

Get Full version on: http://www.updateddumps.com/

QUESTION 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.
Correct Answer: AB

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 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

QUESTION 7

Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
JOB_CAT VARCHARD2(30)
SALARY NUMBER(8,2)
Which statement shows the maximum salary paid in each job category of each department?
http://www.gratisexam.com/
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;
Correct 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 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:
LAST NAME VARCNAR2(35) NOT NULL
SALARY NUMBER(9,2) NOT NULL
COMMISION_PCT NUMBER(4,2)
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;
Correct 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 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;
Correct Answer: C
Section: (none)
Explanation
Explanation/Reference:
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 10

Examine the description of the STUDENTS table:
STD_ID NUMBER(4)
COURSE_ID VARCHARD2(10)
START_DATE DATE
END_DATE DATE
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)
Correct Answer: CE
Section: (none)
Explanation
Explanation/Reference:
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

Get Full version on: http://www.updateddumps.com/

Comments

Popular posts from this blog

1z0-020 Oracle8i: New Features for Administrators Part 2

1Z0-023 Oracle8i: Architecture and Administration Part 2

1Z0-024 Oracle8i: Performance and Tuning Part 3