OCP IZ0-001 Sample Questions and answers Oracle Certification

QUESTION NO: 1
You need to create a report to display the ship date and order totals of your ordid
table. If the order has not been shipped your report must display not shipped. If the
total is not available your report must say not available. In the ordid table the ship
date column has a data type of date the total column has a data type of number.
Which statement do you use to create this report?
A. Select ordid, shipdate Not shipped ,
total Not available
FROM order;
B. Select ordid, NVL (shipdate ‘Not shipped’),
NVL (total, Not available )
FROM order;
C. Select ordid, NVL (TO_CHAR (shipdate), ‘Not shipped’),
NVL (TO_CHAR (total), ‘Not available’)
FROM order;
D. Select ordid, TO_CHAR (shipdate, ‘Not shipped’)
TO_CHAR (total, ‘Not available’)
FROM order;
Answer: C
Explanation:
Answer C shows correct syntax of command NVL
Incorrect Answers:
A: This command will show ALL data with name substitution of columns shipdate and
total.
B: Incorrect usage for NVL command, because shipdate and total are needed to be
converted into VARCHAR2 type with TO_CHAR function. Both parameters of NVL
command have to have the same data type.
D: Incorrect syntax. TO_CHAR command is used just to convert data type into
VARCHAR2 data type, it have nothing to do with NULL values in columns.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 10-11
Chapter 1: Selecting Data from Oracle
QUESTION NO: 2
You want of display the details or all employees whose last names is Smith. But you
are not sure in which case last names are stored.Which statement will list all the
employees whose last name is Smith?
A. Select last name, first name.
FROM emp
www.chinatag.com
4
WHERE last name= ‘smith’;
B. Select last name, first name.
FROM emp
WHERE UPPER (last name)= ‘smith’;
C. Select last name, first name.
FROM emp
WHERE last name=UPPER (‘smith’);
D. Select last name, first name.
FROM emp
WHERE LOWER (last name)= ‘smith’;
Answer: D
Explanation:
Select last name, first name.
FROM emp
WHERE LOWER (last name)= ‘smith’
Answer D shows all records with last name Smith because function LOWER returns the
column value passed as x into all lowercase
Incorrect Answers:
A: This command will show only records with last name ‘smith’.
B: Command UPPER converts all data in last_name column into uppercase.
C: This command will show only records with last name ‘SMITH’.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 22
Chapter 1: Selecting Data from Oracle
QUESTION NO: 3
You need to analyze how long your orders to be shipped from the date that the order
is placed. To do this you must create a report that displays the customer number, date
order, date shipped and the number of months in whole numbers from the time the
order is placed to the time the order is shipped. Which statement produces the
required results?
A. SELECT custid, orderate, shipdate,
ROUND(MONTHS_BETWEEN(shipdate,orderate)) Time Taken
FROM ord;
B. SELECT custid, orderate, shipdate,
ROUND(DAYS_BETWEEN(shipdate,orderate))/30.
FROM ord;
C. SELECT custid, orderate, shipdate,
www.chinatag.com
5
ROUND OFF (shipdate-orderate) Time Taken
FROM ord;
D. SELECT custid, orderate, shipdate,
MONTHS_BETWEEN (shipdate,orderate) Time Taken .
FROM ord;
Answer: A
Explanation:
Answer A shows the number of months (rounded to integer) between the date of order
and the date of shipment.
Incorrect Answers:
B: Function, function DAYS_BETWEEN shows number of days between shipping date
and order date.
C: Incorrect function ROUND OFF.
D: This command will show not rounded to integer value, like 8.6451613.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 30
Chapter 1: Selecting Data from Oracle
QUESTION NO: 4
The employee table contains these columns:
Last_name Varchar2 (25)
First_name Varchar2 (25)
Salary Number7, 2
You need to display the names of employees on more than an average salary of all
employees. Evaluate the SQL statement.
SELECT, LAST_NAME, FIRST_NAME from employee where salary< avg(salary);
Which change should you make to achieve the desired results?
A. Change the function in the Where clause.
B. Move the function to the select clause and add a group clause.
C. Use a sub query in the where clause to compare the average salary value.
D. Move the function to the select clause and add a group by clause and a having
clause.
Answer: C
Explanation:
Answer C shows the correct way to change query, because function AVG can not be used
in WHERE clause.
www.chinatag.com
6
Incorrect Answers:
A: Usage of function AVG is correct
B: This query does not require grouping to extract correct information from the table.
D: This query does not require to use GROUP BY and HAVING clauses to extract
correct information from table
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 57
Chapter 2: Advanced Data Selection in Oracle
QUESTION NO: 5
The employee table contains these columns:
FIRST-NAME VARCHER2(25)
COMISSION NUMBER(3,2)
Evaluate this SQL statement
SELECT first-name,commission
FROM employee
WHERE commission=
(SELECTcomission
FROM employee
WHERE UPPER(first-name)= ‘scott’)
Which statement will cause this statement to fail?
A. Scott has a null commission resolution.
B. Scott has a zero commission resolution.
C. There is no employee with the first name Scott.
D. The first name values in the data base are in the lower case.
Answer: A
Explanation:
Answer A is correct because if Scott has a null commission expression in WHERE clause
will cause error.
Incorrect Answers:
B: Query will work correctly.
C: Query will work even without employee with the first name Scott.
D: Name values will be converted to upper case by function UPPER, query will work,
but for correct result you need to change UPPER to LOWER function.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 64
Chapter 2: Advanced Data Selection in Oracle
www.chinatag.com
7
QUESTION NO: 6
You create the sales table with this command
CREATE TABLE sale.
(purchase-no NUMBER(9)
CONSTRAINT sale-purchase-no-pk PRIMARY KEY,
costumer-id NUMBER(9)
CONSTRAINT sale-customer-id-nk NOT NULL);
Which index or indexes are created for this table?
A. No indexes are created for this table.
B. An index is created for purchase_no column.
C. An index is created for the customer_no column.
D. An index is created for each column.
Answer: B
Explanation:
Answer B is correct because index will be created for PRIMARY KEY column
automatically during table creation. Also index is created for UNIQUE constraint, but
this table creation statement does not include any UNIQUE constraint.
Incorrect Answers:
A: A system index will be created for PRIMARY KEY column.
C: An index will not be created for customer_no column.
D: Indexes will not be created for EACH column.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 110
Chapter 3: Creating Oracle Database Objects
QUESTION NO: 7
How would you add a foreign key constraint on the dept_no column in the EMP
table. Referring to the ID column in the DEPT table?
A. Use the ALTER TABLE command with the ADD clause in the DEPT table.
B. Use the ALTER TABLE command with the ADD clause on the EMP table.
C. Use the ALTER TABLE command with the MODIFY clause on the DEPT
table.
D. Use the ALTER TABLE command with the MODIFY clause on the EMP table.
E. This task cannot be accomplished.
Answer: B
www.chinatag.com
8
Explanation:
Answer B is correct because constraint will be created for EMP table using ALTER
TABLE command.
Incorrect Answers:
A: Foreign key constraint will not be created for DEPT table, only for EMP table. DEPT
table needs to have PRIMARY KEY for successful creation foreign key for EMP
table.
C: Foreign key constraint will not be created for DEPT table and MODIFY clause is
used for different purposes.
D: MODIFY clause of ALTER TABLE command is used for different purposes.
E: It’s possible to add foreignconstraint after table creation.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 143
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 8
Examine the structure of student table:
Name Null Type
STU ID NOT NULL NUMBER(3)
NAME VARCHER2(25)
ADDRESS VARCHER2(50)
GRADUATION DATE
Currently the table is empty. You have decided that null values should not be allowed
for the NAME column. Which statement restricts NULL values from being entered
into column?
A. ALTER TABLE student ADD CONSTRAINT name(NOT NULL);
B. ALTER TABLE student ADD CONSTRAINT NOT NULL (name);
C. ALTER TABLE student MODIFY CONSTRAINT name(NOT NULL);
D. ALTER TABLE student MODIFY(name varcher2(25) NOT NULL);
Answer: D
Explanation:
Answer D is correct because this query add NOT NULL constraint to Student table
Incorrect Answers:
A: Incorrect ADD CONSTRAINT clause in ALTER TABLE command.
B: Incorrect ADD CONSTRAINT clause in ALTER TABLE command.
C: Incorrect syntax using MODIFY clause of ALTER TABLE command. There is no
MODIFY CONSTRAINT clause in ALTER TABLE command.
www.chinatag.com
9
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 143
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 9
You have decided to permanently remove all the data from the STUDENT table and
you need the table structure in the future.Which single command performs this?
A. DROP TABLE student;
B. TRUNCATE TABLE student;
C. DELETE* FROM student;
D. TRUNCATE TABLE student KEEP STRUCTURE;
E. DELETE* FROM student KEEP STRUCTURE.
Answer: B
Explanation:
Answer B is correct because after truncating table you delete all data and keep table and
its structure for future use. Also command TRUNCATE reset highwatermark level to
zero for table.
Incorrect Answers:
A: This DDL operation will drop table and its structure without possibility to rollback
this operation.
C: This operation can be used to perform question task, but it works slowly and usually
used when you need to delete NOT ALL table rows. It does not reset high water
mark for table.
D: Incorrect clause KEEP STRUCTURE inside TRUNCATE TABLE command.
E: Incorrect clause KEEP STRUCTURE inside DELETE command.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 151
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 10
Examine this block of code:
SET OUTPUT ON
Declare
X NUMBER;
V_SAL NUMBER;
V_found VARCHAR2(10):=’TRUE’;
Begin
X:=1;
V_sal := 1000;
www.chinatag.com
10
Declare
V_found VARCHAR2(10);
Y NUMBER
Begin
IF (V_Sal>500) THEN
V_found := ’YES’;
END IF;
DBMS_OUTPUT.PUT_LINE(‘Value of V_found is ‘|| V_Sal);
DBMS_OUTPUT.PUT_LINE(‘Value ofV_Sal is ‘|| TO_CHAR (V_Sal));
Y:=20;
END;
DBMS_OUTPUT.PUT_LINE(‘Value of V_found is’ || V_found);
DBMS_OUTPUT.PUT_LINE(‘Value of Y is’ || TO_CHAR(Y));
END;
SET server OUTPUT if
What is the result of executing this block of code?
A. PLS-00201: identifier ‘Y’ must be declared.
B. Value of V_found is YES
Value of V_sal is 1000
Value of V_found is TRUE
C. Value of V_found is YES
Value of V_found is 1000
Value of V_found is TRUE
Value of Y is 20
D. PLS-00201: identifier ‘V_sal’ must be declared
PLS-00201: identifier ‘Y’ must be declared
E. Value of V_found is YES
Value of V_sal is 1000
Value of V_found is TRUE
Value of Y is 20
Answer: A
Explanation:
Answer A is correct because PL/SQL variable ‘Y’ is not declared in DECLARE section
of PL/SQL block, but only inside BEGIN ¡END
block and executing of this block will
return error.
Incorrect Answers:
B: This code does work because of V_found and V_sal are identified.
C: This code does work because of V_found and V_sal are identified.
D: Identifier V_sal is declared, so code will fail because of identifier ‘Y’, not V_sal,
must be declared.
E: This code does work because of all variables are declared and populated with values.