QUESTION NO: 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.
Answer: C
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 NO: 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.
Answer: A
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 NO: 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;
Answer: D
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
QUESTION NO: 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);
Answer: C
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 NO: 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 above
SQL 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
Answer: A, F
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 NO: 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 manager_id NUMBER);
B. MODIFY VIEW emp_dept_vu (ADD manager_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.
Answer: E
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 NO: 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
Answer: B, E
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