QUESTION NO: 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?
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;
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 NO: 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;
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 NO: 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;
1z0 -007
F. ALTER TABLE table_name
TURN ON CONSTRAINT constraint_name;
Answer: C
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 NO: 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)
Answer: C, E
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.
1z0 -007
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
QUESTION NO: 11
The EMPLOYEE tables has these columns:
LAST_NAME VARCHAR2(35)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(5,2)
You want to display the name and annual salary multiplied by the commission_pct for
all employees. For records that have a NULL commission_pct, a zero must be displayed
against the calculated column.
Which SQL statement displays the desired results?
A. SELECT last_name, (salary * 12) * commission_pct
FROM EMPLOYEES;
B. SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0)
FROM EMPLOYEES;
C. SELECT last_name, (salary * 12) * NVL2(commission_pct, 0)
FROM EMPLOYEES;
D. SELECT last_name, (salary * 12) * NVL(commission_pct, 0)
FROM EMPLOYEES;
Answer: D
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 isreturned. 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: There is no IFNULL() function in Oracle.
C: The NVL2() function requires 3 parameters, not 2. Function NVL2(expr1, expr2, expr3)
returns expr2 if expr1 is not NULL. If expr1 is NULL, it returns expr3.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 31-32
Chapter 1: Overview of Oracle Databases
QUESTION NO: 12
Examine the data from the ORDERS and CUSTOMERS table.
ORDERS
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
100 12-JAN-2000 15 10000
101 09-MAR-2000 40 8000
102 09-MAR-2000 35 12500
103 15-MAR-2000 15 12000
104 25-JUN-2000 15 6000
105 18-JUL-2000 20 5000
106 18-JUL-2000 35 7000
107 21-JUL-2000 20 6500
108 04-AUG-2000 10 8000
CUSTOMERS
CUST_ID CUST_NAME CITY
10 Smith Los Angeles
15 Bob San Francisco
20 Martin Chicago
25 Mary New York
30 Rina Chicago
35 Smith New York
40 Linda New York
Which SQL statement retrieves the order ID, customer ID, and order total for the
orders that are placed on the same day that Martin places his orders?
A. SELECT ord_id, cust_id, ord_total
FROM orders, customers
WHERE cust_name=’Mating’
AND ord_date IN (’18-JUL-2000’,’21-JUL-2000’);
B. SELECT ord_id, cust_id, ord_total
FROM orders
Where ord_date IN (SELECT ord_date
FROM orders
WHERE cust_id = (SELECT cust_id
FROM customers
WHERE cust_name =
‘Martin’));
C. SELECT ord_id, cust_id, ord_total
FROM orders
Where ord_date IN (SELECT ord_date
FROM orders, customers
Where cust_name = ‘Martin’);
D. SELECT ord_id, cust_id, ord_total
FROM orders
WHERE cust_id IN (SELECT cust_id
FROM customers
WHERE cust name = ‘Martin’);
Answer: B
Explanation:
This query will return the order ID, customer ID, and order total for the orders that are placed
on the same day that Martin places his orders.
Incorrect Answers
A: This query returns only Martin’s orders for July 18, 2000 and July 21, 2002, not orders of
others that were placed on the same day that Martin placed his orders.
C: This query uses incorrect sub-query to extract dates when Martin placed his orders.
D: This query will return only Martin’s orders.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 145-156
Chapter 4: Subqueries
QUESTION NO: 13
You need to modify the STUDENTS table to add a primary key on the STUDENT_ID
column. The table is currently empty.
Which statement accomplishes this task?
A. ALTER TABLE students
ADD PRIMARY KEY student_id;
B. ALTER TABLE students
ADD CONSTRAINT PRIMARY KEY (student_id);
C. ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
D. ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
E. ALTER TABLE students
MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
Answer: D
Explanation:
This statement provides correct syntax to add a primary key on the STUDENT_ID column of
the STUDENT table.
1z0 -007
Incorrect Answers
A: This ALTER TABLE statement is missing CONSTRAINT keyword and the name of the
constraint.
B: This ALTER TABLE statement is missing the name of the constraint.
C: It’s incorrect syntax in the ALTER TABLE command: STUDENT_ID must be used with
brackets.
E: We need to add constraint, not to modify existing one. Usage of the MODIFY keyword is
incorrect in this case.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 239-240
Chapter 5: Creating Oracle Database Objects