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

Get Full version on:http://www.getfreedumps.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