Oracle 1Z0-007 Introduction to oracle9l:sql Part 3
Introduction to oracle9l:sql Part 3
Get Full version on:http://www.getfreedumps.com/
QUESTION 21
The EMP table contains these columns:
LAST NAME VARCHAR2(25)
SALARY NUMBER(6,2)
DEPARTMENT_ID NUMBER(6)
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 DEPARMENT_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.
Correct Answer: C
Section: (none)
Explanation
Explanation/Reference:
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 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
Correct Answer: A
Section: (none)
Explanation
Explanation/Reference:
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 23
Examine the description of the MARKS
table:
STD_ID NUMBER(4)
STUDENT_NAME VARCHAR2(30)
SUBJ1 NUMBER(3)
SUBJ2 NUMBER(3)
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.
Correct Answer: C
Section: (none)
Explanation
Explanation/Reference:
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 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.
Correct Answer: A
Section: (none)
Explanation
Explanation/Reference:
Explanation:
Lexical substitution variables can be
used to replace values in the WHERE clause.
Incorrect Answers
B: There is 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 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;
Correct Answer: D
Section: (none)
Explanation
Explanation/Reference:
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
Get Full version on:http://www.getfreedumps.com/
QUESTION 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);
Correct Answer: C
Section: (none)
Explanation
Explanation/Reference:
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 27
Examine the SQL statements that
creates ORDERS table:
CREATE TABLE orders
(SER_NO NUMBER UNIQUE,
ORDER_ID NUMBER,
ORDER_DATE DATE NOT NULL
STATUS VARCHARD2(10)
CHECK (status IN ('CREDIT','CASH')),
PROD_ID_NUMBER
REFERENCES PRODUCTS(PRODUCT_ID),
ORD_TOTAL NUMBER,
PRIMARY KEY (order id, order date));
For which columns would an index be
automatically created when you execute the aboveSQL 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
Correct Answer: AF
Section: (none)
Explanation
Explanation/Reference:
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 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
manger_id NUMBER);
B. MODIFY VIEW EMP_dept_vu (ADD
manger_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.
Correct Answer: E
Section: (none)
Explanation
Explanation/Reference:
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 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
Correct Answer: BE
Section: (none)
Explanation
Explanation/Reference:
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 30
Which three SELECT statements displays
2000 in the format "$2,000.00"? (Choose three)
A. SELECT TO CNAR(2000, '$#,###.##')
FROM dual;
B. SELECT TO CNAR(2000, '$0,000.00')
FROM dual;
C. SELECT TO CNAR(2000, '$9,999.00')
FROM dual;
D. SELECT TO CNAR(2000, '$9,999.99')
FROM dual;
E. SELECT TO CNAR(2000, '$2,000.00')
FROM dual;
F. SELECT TO CNAR(2000, '$N,NNN.NN')
FROM dual;
Correct Answer: BCD
Section: (none)
Explanation
Explanation/Reference:
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
Comments
Post a Comment