Monday
Oracle certification Questions and Answers
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;
Guaranteed stuff for all IT certification exams: mail at certking@certificationking.net
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
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,
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.
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
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
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 foreign constraint 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.
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;
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 of V_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.
11
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 209
Chapter 5: Introducing PL/SQL
QUESTION NO: 11
You need to store currency data and you know that data will always have two digits to
the right of the decimal points. However the number of digits to the left of the
decimal place will vary greatly. Which data type would be most appropriate to store
the data?
A. NUMBER
B. NUMBER(T)
C. LANG
D. LANGRA
Answer: A
Explanation:
Answer A is correct because by default NUMBER is NUMBER(L, P) type, which is
always stored as variable-length data, where 1 byte is used to store the exponent, 1 byte is
used to store for every two significant digits of the number’s mantissa, and 1 byte is used
for negative numbers if the number of significant digits is less than 38 bytes.
Incorrect Answers:
B: NUMBER(T) type will not allow to keep more than T numbers of digits to the left of
the decimal place, but question says that it will vary greatly.
C: There is no type LANG in Oracle 8i.
D: Type LANGRA does not exists too.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 390
Chapter 8: Managing Database objects I
QUESTION NO: 12
Examine the structure of STUDENT table.
NAME NULL TYPE
STUDENT ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(25)
PHONE NOT NULL VARCHAR2(9)
ADDRESS VARCHAR2(50)
GRADUATION DATE
12
There are hundred records in the student table. You need to modify the Phone
column to hold only numeric value. Which statement will modify the data type of the
Phone column?
A. ALTER TABLE student MODIFY phone NUMBER(9)
B. ALTER STUDENT table MODIFY COLUMN phone NUMBER(9);
C. You can not modify a VARCHAR2 data type to a NUMBER data type for a
D. Column.
E. You cannot modify the data type of a column if there is data in the column.
Answer: E
Explanation:
Answer E is correct because Phone column in STUDENT table has NOT NULL constraint,
which does not allows to modify data type of a column if there is data in the column.
Incorrect Answers:
A: The statement will fail because it is incorrect way to change data type for column
with NOT NULL constraint on it.
B: There is a wrong syntax ‘ALTER STUDENT table’ and ‘MODIFY COLUMN’ in the
statement.
C: It is possible to modify VARCHAR2 data type to NUMBER data type (with some
restrictions).
D: Does not exists in question
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 141-142
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 13
You need to update employee salaries if the salary of an employee is less than 1000.
The salary needs to be incremented by 10%. Use SQL*Plus substitution variable to
accept the employee number. Which PL/SQL block successfully updates the salaries?
A. Declare
V_sal emp.sal % TYPE;
Begin
SELECT Sal
INTO V_sal
FROM emp
WHERE empno = and P_empno;
IF (V_Sal<1000) THEN
UPDATE emp
INTO Sal := Sal*1.1
WHERE empno = and p_empno;
13
END IF;
END;
B. Declare
V_sal emp.sal % TYPE;
Begin
SELECT Sal
INTO V_sal
FROM emp
WHERE empno = and P_empno;
IF (V_Sal<1000) THEN
SAL := SAL * 1.1;
END IF;
END;
C. Declare
V_sal emp.sal % TYPE;
Begin
SELECT Sal
INTO V_sal
FROM emp
WHERE empno = and P_empno;
IF (V_Sal<1000) THEN
UPDATE emp
Sal := Sal*1.1
WHERE empno = and p_empno;
END IF;
END;
D. Declare
V_sal emp.sal % TYPE;
Begin
SELECT Sal
INTO V_sal
FROM emp
WHERE empno = and P_empno;
IF (V_Sal<1000) THEN
UPDATE emp
Set Sal := Sal*1.1
WHERE empno = and p_empno;
END IF;
END;
Answer: D
Explanation:
Answer D is correct because it’s uses cursor and IF-THEN structure correctly to increase
salary for all employees with current salary less than 1000.
14
Incorrect Answers:
A: UPDATE INTO is wrong construction for UPDATE command
B: There is no SAL variable defined in PL/SQL block, so SAL:=SAL*1.1 will fail and
it’s wrong way to change value of column Sal in table EMP.
C: Sal:=Sal*1.1 can not be inside UPDATE … WHERE command.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 215-217
Chapter 8: Introducing PL/SQL
QUESTION NO: 14
The employee table contains these columns.
LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) DEPT_ID
NUMBER(9)
You need to display the names of the employees that are not assigned to the
department. Evaluate this SQL statement.
SELECT last_name, first_name
FROM employee
WHERE dept_id is NULL
Which change should you make to achieve the desired result?
A. Create an outer join.
B. Change the column in the where condition.
C. Change the operator in the where condition
D. Add a second condition to the where condition
Answer: C
Explanation:
Answer C is correct because the NULL operator need to be changed in the WHERE
condition to display correct result.
Incorrect Answers:
A: One table is used for query, so there is no reason to use outer join operation.
B: The column in the WHERE condition is correct and don’t need to be changed.
D: Query does not require additional condition to extract correct data.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 10-11
Chapter 1: Selecting data from Oracle
QUESTION NO: 15
Which statement about SQL is true?
15
A. Null values are displayed last in the ascending sequences.
B. Data values are displayed in descending order by default.
C. You cannot specify a column alias in an ORDER BY clause.
D. You cannot sort query results by a column that is not included in the SELECT
list.
E. The results are sorted by the first column in the SELECT list, if the ORDER BY
clause is not provided.
Answer: A
Explanation:
Answer A is correct because of null values are displayed last in the ascending sequences.
Incorrect Answers:
B: Data values are displayed in ASCENDING order by default.
C: It is possible to specify a column alias in an ORDER BY clause (but not in a GROUP
BY clause).
D: You can sort query results by a column that is not included in the SELECT list.
E: The results will not be sorted at all, if the ORDER BY clause was not used in query.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 18-19
Chapter 8: Selecting Data from Oracle
QUESTION NO: 16
Written a PL/SQL loop, you need to test if the current FETCH was successful. Which
SQL cursor attribute would you use to accomplish this task?
A. SQL % ISOPEN
B. SQL % ROWCOUNT
C. SQL % FOUND
D. This task cannot be accomplished with a SQL cursor attribute.
E. A SQL cursor attribute cannot be used within a PL/SQL loop.
Answer: C
Explanation:
Answer C is correct because of SQL%FOUND attribute returns TRUE if current FETCH
have been successful.
Incorrect Answers:
A: SQL%ISOPEN attribute returns TRUE if cursor is OPEN and ready for use.
B: SQL%ROWCOUNT returns the number of rows that were processed by the
statement.
D: This task can be successfully accomplished with a SQL%FOUND cursor attribute.
16
E: PL/SQL loop can contain a SQL cursor attribute.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 222-224
Chapter 5: Introducing PL/SQL
QUESTION NO: 17
The structure of the DEPT table is as follows:
NAME NULL TYPE
Deptno Not Null NUMBER(2)
Dname VARCHAR2(14)
Loc VARCHAR2(13)
Examine the code
Declare
Type dept_record_type is record
(dno NUMBER, name VARCHAR2(20));
dept_rec dept_record;
Begin
Select deptno, dname
INTO dept_rec
FROM dept
WHERE deptno=10;
END
Which statement displays the name of selected department?
A. DBMS_OUTPUT.PUT_LINE (name);
B. DBMS_OUTPUT.PUT_LINE (dname);
C. DBMS_OUTPUT.PUT_LINE (dept_rec.name);
D. DBMS_OUTPUT.PUT_LINE (dept_rec.dname);
E. DBMS_OUTPUT.PUT_LINE (dept_rec (name));
Answer: C
Explanation:
Answer C is correct because it shows field NAME for record DEPT_REC of
DEPT_RECORD type.
Incorrect Answers:
A: Statement does not specify that NAME is field of DEPT_REC record.
B: DNAME is name of column in DEPT table and cannot be used this way to show data.
D: There is not field DNAME in record DEPT_REC.
E: This statement just has wrong syntax of function PUT_LINE in DBMS_OUTPUT
package to represent data.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 211-213
17
Chapter 5: Introducing PL/SQL
QUESTION NO: 18
Which privilege concerns with system level security?
A. Drop any table.
B. DELETE
C. ALTER
D. INDEX
E. UPDATE
Answer: A
Explanation:
Answer A is correct because of only DROP ANY TABLE privilege from all choices
belongs to SYSTEM level security, all others – not.
Incorrect Answers:
B: DELETE is object privilege. Permits the grantee of this object privilege to delete data
from a table or view.
C: ALTER is object privilege. Permits the grantee of this object privilege to alter the
definition of a table or sequence only. The ALTER privileges on all other database
objects are considered system privileges.
D: INDEX is object privilege. Permits the grantee of this object privilege to create an
index on a table already defined.
E: UPDATE is object privilege. Permits the grantee of this object privilege to update
data into a table or view.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 485
Chapter 10: Managing Database Use
QUESTION NO: 19
Evaluate the SQL statement.
CREATE ROLE manager;
CREATE ROLE clerk;
CREATE ROLE inventory;
CREATE USER scott IDENTIFIED BY tiger;
GRANT inventory TO clerk;
GRANT clerk TO manager;
GRANT inventory TO scott;
/
18
How many roles will user scott have access to?
A. 0
B. 1
C. 2
D. 3
Answer: B
Explanation:
Answer B is correct because of only INVENTORY role will be granted to user SCOTT.
Incorrect Answers:
A: One role (INVENTORY) is granted to user SCOTT, not zero.
C: This choice would be correct if role CLERK would be granted to SCOTT because
this role includes INVENTORY role.
D: This choice would be correct if role MANAGER would be granted to SCOTT
because this role includes INVENTORY and CLERK roles.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 490-494
Chapter 10: Managing Database Use
QUESTION NO: 20
Scott forgot his password while on location. Which command must be executed to set
a password for scott?
A. Scott must execute the command. ALTER USER scott PASSOWRD BY lion
B. The DBA must execute the command. ALTER USER scott IDENTIFIED BY
lion
C. Scott must execute the command ALTER USER scott IDENTIFIED BY lion
D. The scott must execute the command CHANGE password to lion WHERE
“user=scott”;
E. The DBA must execute the command CHANGE password to lion WHERE
“user=scott”;
Answer: B
Explanation:
Answer B is correct because of only DBA (not user himself) can change password for
account if user forgot his password.
Incorrect Answers:
A: Clause ‘PASSWORD BY’ is wrong for ‘ALTER USER’ command.
19
C: User needs to be connected to change his password, but he cannot connect if he forgot
password.
D: Command CHANGE is incorrect to change password and user cannot connect
himself to change password because he forgot it.
E: Command CHANGE is incorrect to change password.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 182
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 21
You are updating the employee table. Jane has been granted the same privileges as
you on the employee table. You ask Jane to logon to the database to check your world
before you issue the commit command. What can Jane do to the employee table?
A. Jane can access the table and verify your changes.
B. Jane cannot access the table.
C. Jane can access the table but she cannot see your changes, she can make the
changes for you.
D. Jane can access the table but she cannot see your changes and cannot make the
changes to the roles that you are changing.
Answer: D
Explanation:
Answer D is correct because before you committed changes in the employee table nobody
can see changed data.
Incorrect Answers:
A: Jane can access table but she cannot verify changes because she does not see them
before you commit data changes.
B: Jane can access table because she has been granted the same privileges as you on the
employee table.
C: Jane cannot commit herself changes you did to the data.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 484-485
Chapter 10: Managing Database Use
QUESTION NO: 22
Examine the structure of STUDENT table.
Name Null? Type.
20
STUD-ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHER2(25)
ADDRESS VARCHER2(50)
GRADUATION DATE.
Which statement inserts a new row into the STUDENT table?
A. INSERT INTO student.
VALUES(101, ‘Smith’);
B. INSERT INTO student.
VALUES(101, ‘100 Main Street’, ‘17-JUN-99’, ‘Smith’);
C. INSERT INTO test.
VALUES(101, ‘Smith’, ‘100 Main Street’, ‘17-JUN-99’);
D. INSERT INTO student.(stud-id,address,gradulation)
VALUES(101, ‘Smith’, ‘100 Main Street’, ‘17-JUN-99’);
E. INSERT INTO student.(stud-id,address,name,gradulation)
VALUES(101, ‘100 Main Street’,‘Smith’, ‘17-JUN-99’);
Answer: E
Explanation:
Answer E is correct because you need to show ALL columns and in correct order for
values which you try to insert into table STUDENT.
Incorrect Answers:
A: You need to show ALL columns for values, which you try to insert into table
STUDENT.
B: You need to show columns in correct order for values, which you try to insert into
table STUDENT. In this statement Name is located in wrong place according table
definition.
C: This statement contains wrong table name TEST, not STUDENT.
D: This statement shows list of only 3 columns when there are 4 values need to be
inserted into table row.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 120-122
Chapter 4: Creating Oracle Database Objects
QUESTION NO: 23
Examine the structure of the STUDENT table.
NAME NULL TYPE
STUDENT_ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(25)
21
ADDRESS VARCHAR2(50)
GRADUATION DATE
Graduation column is a foreign key column to the graduate table. Examine the data
in the GRADE DATE table.
Graduation 20-jan-1999
12-may-1999
19-jan-2000
25-may-2000
13-jan-2001
29-may-2001
Which update statement produces the following error: ORA-02291 integrity
constraint(sys_c23) violated parent key not found?
A. UPDATE student
SET stud-id=999,
graduation= ’29-MAY-2001’
WHERE stud-id=101;’
B. UPDATE student
SET name= ‘Smith’,
graduation= ’29-MAY-2001’
WHERE stud-id=101;
C. UPDATE student
SET name= ‘Smith’,
graduation= ‘15-AUG-2000’
WHERE stud-id=101
D. UPDATE student
SET stud-id=NULL,
address= ‘100 Main Street’
WHERE graduation= ‘20-JAN-1999’
Answer: C
Explanation:
Answer C is correct because there is no ’15-AUG-2000’ value in the graduate table.
Foreign constraint for this statement will be violated because parent key not found.
Incorrect Answers:
A: There is ’29-MAY-2001’ in the graduate table and foreign constraint will not be
violated.
B: There is ’29-MAY-2001’ in the graduate table and foreign constraint will not be
violated.
D: There is ’20-JUN-1999’ in the graduate table and foreign constraint will not be
violated.
22
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 428-433
Chapter 9: Managing Database Objects II
QUESTION NO: 24
In SQL Plus You issued this command:
Delete from dept where dept_id=901
You received an integrated constraint error because the child record was found.
What could you do to make the statement execute?
A. Delete the child record first.
B. You cannot make the command execute.
C. Add a fourth keyword to the command.
D. Add the constraints cascade option to the command.
Answer: A
Explanation:
Answer A is correct because you need first delete the child record in dependable table for
avoid foreign constraint violation.
Incorrect Answers:
B: You can execute the command if you don’t have child records in other tables for
record you need to delete in the parent table DEPT.
C: Adding an additional keyword will not help because foreign constraint will be
violated.
D: CASCADE CONSTRAINTS option works only for DROP TABLE command, not
for DELETE.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 398-399
Chapter 8: Managing Database Objects I
QUESTION NO: 25
The view EMP-VIEW is created based on the EMP table as follows.
CREATE OF REPLACE VIEW emp-view
AS
SELECT deptno,SUM(sal)TOT_SAL,COUNT(*)NOT-EMP
FROM emp
GROUP BY deptno;
What happens when the command is used?
23
UPDATE emp-view
SET tot-sal=20000
WHERE deptno=10;
A. The base table cannot be updated through this view.
B. The TOT_SAL column in the EMP table is updated to 20,000 for department
10.
C. The TOT_SAL column in the EMP view is updated to 20,000 for department10.
D. The SAL column in the EMP table is updated to 20,000 for employees in
department 10.
Answer: A
Explanation:
Answer A is correct because the user may not INSERT, DELETE, or UPDATE data on the
table underlying the sample view if the SELECT statement creating the view contains
GROUP BY, or a single-row operation.
Incorrect Answers:
B: TOT_SAL column in the EMP table will not be updated.
C: TOT_SAL column in the EMP table will not be updated for any rows.
D: UPDATE command does not contain SAL column for update.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 164
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 26
You have a view card ANN_SAL that is based on the employee table. The structure
of the ANN_SAL view is:
NAME NULL TYPE
EMPNO NOT NULL NUMBER(4)
YEARLY_SAL NUMBER(9,2)
MONTHLY_SAL NUMBER(9,2)
Which statement retrieves the data from the ANN_SAL view?
A. SELECT * FROM ANN_SAL
B. SELECT * FROM EMPLOYEE
C. SELECT * FROM VIEW ANN_SAL
D. SELECT * FROM VIEW ANN_SAL IS DON EMPLOYEE
Answer: A
24
Explanation:
Answer A is correct because correct syntax for SELECT command for view is SELECT *
FROM
Incorrect Answers:
B: This statement will show data from table EMPLOYEE, not view ANN_SAL.
C: It’s an incorrect statement because of usage word ‘VIEW’.
D: It’s an incorrect statement because of usage words ‘VIEW’ and ‘IS DON
EMPLOYEE’.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 160-169
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 27
Evaluate this IF statement.
IF v_value>100 THEN
v_new-value:=2*v-value;
ELSIF v-value>200 THEN
v-new-value:=3*v-value;
ELSIF v-value>300 THEN
v-new-value:=4*v-value;
ELSE
v-new-value:=5*v-value;
END IF
What would be assigned to v_new_value if v_value=250?
A. 250
B. 500
C. 750
D. 1000
Answer: B
Explanation:
Answer B is correct because first IF condition v_value > 100 will be TRUE if v_value have
been assigned with new value equal 250. Result of 2*v_value is 500.
Incorrect Answers:
A: All IF conditions multiple 250 on 2, 3, 4 or 5, so result cannot be 250.
C: First IF condition will work not second one, so result will be 500, not 750.
25
D: First IF condition will work not second or third one, so result will be 500, not 750 or
1000.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 215-217
Chapter 5: Introducing PL/SQL
QUESTION NO: 28
The PLAYER table contains these columns
id number(9)
name varchar(2)
manager_id number(9)
In this instance, managers are players with you need to display a list of players.
Evaluate these TWO SQL statements:
SELECT p.name,m.name
FROM player p,player m
WHERE m.id= m.manager_id;
SELECT p.name,m.name
FROM player p,player m
WHERE m.manager_id=p.id;
How would the results differ?
A. Statement1 will not execute, statement2 will.
B. Statement1 will execute, statement2 will not.
C. Statement1 is self join, statement2 is not.
D. The results will be same but the display will be different.
Answer: D
Explanation:
Answer D is correct because the results of these queries will be same, just will look
different. In first statement driving column is ID, in second – MANAGER_ID.
Incorrect Answers:
A: Both statements will be executed successfully.
B: Both statements will be executed successfully.
C: Both statements are self join, not only Statement1.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 55-56
Chapter 4: Advanced Data Selection in Oracle
26
QUESTION NO: 29
How would you declare a PL/SQL table of records to hold the rows selected from the
EMP table?
A. DECLARE
emp-table is TABLE of emp%ROWTYPE.
B. BEGIN
TYPE emp-table is TABLE of emp%ROWTYPE
emp-table emp-table-type;
C. DECLARE
TYPE emp-table is TABLE of emp%ROWTYPE
INDEX BY WHOLE NUMBER:
emp-table emp-table-type;
D. DECLARE
TYPE emp-table is TABLE of emp%ROWTYPE
INDEX BY BINARY INTEGRATDE.
emp-table emp-table-type;
Answer: D
Explanation:
Answer D is correct because INDEX BY BINARY INTEGRATED clause need to be set
for TABLE type.
Incorrect Answers:
A: INDEX BY clause is not used in definition statement of PL/SQL block.
B: INDEX BY clause is not used in definition statement of PL/SQL block.
C: INDEX BY clause uses WHOLE NUMBER option, which is incorrect for TABLE
type definition.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 392
Chapter 8: Managing Database Objects I
QUESTION NO: 30
You want to create a cursor that can be used several times in a block. Selecting a
different active set each time that it is opened. Which type of cursor do you create?
A. A cursor for loop.
B. A multiple selection cursor.
C. A cursor for each active set.
27
D. A cursor that uses parameters.
Answer: D
Explanation:
Answer D is correct because a cursor with parameters can be used several times in a block,
selecting active set each time that it was opened depending on parameters’ values.
Incorrect Answers:
A: A cursor for loop used for different purpose.
B: A multiple selection cursor does not exist.
C: A cursor for each active set does not allow to use the same cursor several times in a
block.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 231-233
Chapter 5: Introducing PL/SQL
QUESTION NO: 31
Which statement is true when writing a cursor for loop?
A. You must explicitly fetch the rows within a cursor for loop.
B. You must explicitly open the cursor prior to the cursor for loop.
C. You must explicitly close the cursor prior to the end of program.
D. You do not explicitly open, fetch or close a cursor within a cursor for loop.
E. You must explicitly declare the record variable that holds the row returned from
the cursor.
Answer: D
Explanation:
Answer B is correct because when using a cursor for loop you don’t need explicitly open,
fetch or close a cursor (by definition of a cursor for loop).
Incorrect Answers:
A: You don’t need explicitly fetch the cursor within a cursor for loop.
B: You don’t need explicitly open the cursor within a cursor for loop.
C: You don’t need explicitly close the cursor within a cursor for loop.
E: You don’t need to declare the record variable to hold the row from the cursor if you
use a cursor for loop.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 233-234
Chapter 5: Introducing PL/SQL
28
QUESTION NO: 32
The structure of the DEPT table as:
Name Null? Type
DEPT NO Not NULL Number(25)
DNAME VARCHER2(14)
LOC VARCHER2(13)
Examine the code:
DECLARE
Dept_rec dept%ROWTYPE:
BEGIN
SELECT*
INTO dept_rec
FROM dept.
WHERE deptno=10;
END;
Which PL/SQL statement displays the location of selected department?
A. DBMS_OUTPUT.PUT-LINE(dept_rec);
B. DBMS_OUTPUT.PUT-LINE(dept_rec.loc);
C. DBMS_OUTPUT.PUT-LINE(dept_rec(1).loc);
D. You can’t display a single field in the record because they are not specially
identified in declarative section.
Answer: B
Explanation:
Answer B is correct because PUT_LINE function from DBMS_OUTPUT package will
show LOC field from DEPT_REC record.
Incorrect Answers:
A: You cannot show ALL record fields using DEPT_REC.
C: Syntax ‘dept_rec(1).loc’ is wrong to display the location of selected department.
D: You can display a single field of record.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 211-213
Chapter 5: Introducing PL/SQL
29
QUESTION NO: 33
Which statement about implicit cursors is true?
A. Implicit cursors are declared implicitly only for DML statements.
B. Implicit cursors are declared implicitly for all the DML and SELECT
statements.
C. Programmers need to close all the implicit cursors before the end of the PL/SQL
program.
D. Programmers can declare implicit cursors by using the cursor type in the
declaration section.
Answer: B
Explanation:
Answer B is correct because implicit cursors are declared implicitly for all DML and
SELECT statements.
Incorrect Answers:
A: Implicit cursors are declared implicitly not only for all DML, but for SELECT
statements too.
C: Programmers don’t need to close the implicit cursor, because it will be closed
automatically at the end of the PL/SQL block.
D: Programmers cannot declare implicit cursors, only explicit cursors.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 222-224
Chapter 5: Introducing PL/SQL
QUESTION NO: 34
Evaluate this PL/SQL block:
DECLARE
v-result NUMBER(2);
BEGIN
DELETE
FROM employee
WHERE dep_id IN (10,20,30);
v-result:= SQL%ROWCOUNT;
COMMIT;
END;
What will be the value of v_result if no rows are deleted?
A. 0
B. 1
30
C. True
D. Null
Answer: A
Explanation:
Answer A is correct because if no rows are deleted SQL%ROWCOUNT attribute of cursor
will return 0 – number of deleted rows.
Incorrect Answers:
B: No rows are deleted so SQL%ROWCOUNT will return 0, not 1.
C: SQL%ROWCOUNT attribute of cursor returns always a numeric value, not
BOOLEAN.
D: SQL%ROWCOUNT attribute of cursor returns always a numeric value, which cannot
be a NULL.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 224
Chapter 5: Introducing PL/SQL
QUESTION NO: 35
Which two conditions in a PL/SQL block cause an exception error to occur?
(Choose two)
A. Select statement does not return a row.
B. Select statement returns more than one row.
C. Select statement contains a group by clause.
D. Select statement does not have where clause.
E. The data type in the select list are inconsistent with the data types in the into
clause.
Answer: A, B
Explanation:
Answers A and B is correct because there are two common exceptions can cause an error :
NO_DATA_FOUND, if no rows were selected or changed by the SQL operation or
TOO_MANY_ROWS, if more than one row was obtained by a single-row subquery, or in
another SQL statement operation where Oracle was expecting one row.
Incorrect Answers:
C: It will not be an exception error if SELECT statement contains a GROUP BY clause.
D: It will not be an exception error if SELECT statement contains a WHERE clause.
E: It will ROWTYPE_MISMATCH an exception error if the datatypes of the record to
which data from the cursor is assigned are INCOMPATIBLE, but not
INCONSISTENT as answer E says.
31
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 239
Chapter 5: Introducing PL/SQL
QUESTION NO: 36
You need to create a PL/SQL program to insert records into employee table.
Which block of code successfully uses the insert command?
A. DECLARE
v_hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO emp(empnp, ename, hiredate, deptno)
VALUES(empno_sequence.nextval, ‘and name’,v_hirerdate and deptno)
B. DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO emp(empnp,ename,hiredate,deptno)
C. DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO emp(empnp,ename,hiredate)
VALUES(empno_sequence.nextval, name, v_hirerdate)
END:
D. DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO emp(empnp,ename,heridate,deptno)
VALUES(empno_sequence.nextval, ‘and name’,v_herdate and deptno)
Job=Clerk
END:
Answer: C
Explanation:
Answer C is correct because expression EMPNO_SEQUENCE.NEXTVAL will calculate
next value for EMPNP column automatically after each insert.
Incorrect Answers:
A: Syntax ‘v_iredate and deptno’ is incorrect in INSERT VALUES statement.
B: Statement does not display VALUES which need to be inserted into table.
32
D: Using ‘Job=Clerk’ is incorrect syntax in the INSERT statement and in the PL/SQL
block.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 153-160
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 37
Evaluate this PL/SQL block.
BEGIN
FOR i IN 1..10 LOOP
IF I=4 OR I=6 THEN null;
ELSE
INSERT INTO test(result)
VALUES (I) ;
END IF;
COMMIT;
END LOOP;
ROLL BACK;
END.
How many values will be inserted into the TEST table?
A. 0
B. 4
C. 6
D. 8
E. 10
Answer: D
Explanation:
Answer D is correct because loop will be executed 10 times, but 2 times IF-THEN
condition will not allow to insert 2 values into TEST table, so result is 8.
Incorrect Answers:
A: Loop will be executed exactly 10 times, because I will change value from 1 to 10 by
LOOP-FOR definition.
B: Loop will be executed exactly 10 times, not 4.
C: Loop will be executed exactly 10 times, not 6.
E: This answer would be correct if the condition IF-THEN have been absent inside this
PL/SQL block.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 219-220
33
Chapter 5: Introducing PL/SQL
QUESTION NO: 38
You issue this command:
CREATE public synonym EMP for ed.employee;
Which task has been accomplished?
A. The object can now be accessed by all the users.
B. All users were given object privileges to the table.
C. The need to qualify the object name with its schema is eliminated only for
you.
D. The need to qualify the object name with its schema is eliminated for all users.
Answer: D
Explanation:
Answer D is correct because the public synonym will be created for table EMPLOYEE of
ED owner. After that other users will not need to use object owner prefix to access data
inside this table.
Incorrect Answers:
A: Creation public synonym will not open access for ALL users, but will make more
easy access for user, which have access to EMPLOYEE table of user ED.
B: Creation public synonym for table does not provide objects privileges for ALL users
to the table.
C: Because of creation PUBLIC synonym for ED.EMPLOYEE need to qualify the
object name with its owner name is eliminated for ALL users who have already
access to this table, not only for you.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 183-185
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 39
Which statement about multiple sub-queries is True?
A. A pair wise comparison produces a cross product.
B. A non-pair wise comparison produces a cross product.
C. In a pair wise subquery, the values returned from the subquery are compared
individually to the values in the outer query.
D. In a non-pair wise subquery, the values returned from the subquery are
compared as a group to the values in the outer query.
34
Answer: B
Explanation:
Answer B is correct because a non-pair wise comparison really produces a cross product.
Incorrect Answers:
A: A pair comparison does not produces a cross product because of a non-pair wise
comparison does.
C: In a pair wise subquery, the values returned from the subquery are compared as a
GROUP, not INDIVIDUALLY to the values in the outer query.
D: In a non-pair wise subquery, the values returned from the subquery are compared
INDIVIDUALLY to the values in the outer query, not as a GROUP.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 64-72
Chapter 2: Advanced Data Selection
QUESTION NO: 40
You attempt to query to the database with this command:
SELECT dept_no,AVG(MONTHS_BETWEEN(SYSDATE,hire-data))
FROM employee WHERE AVG(MONTHS_BETWEEN(SYSDATE,hire_date))>60
GROUP BY by dept_no
ORDER BY AVG(MONTHS_BETWEEN(SYSDATE,hire_date));
Why does this statement cause an error?
A. A select clause cannot contain a group function.
B. A where clause cannot be used to restrict groups.
C. An order by clause cannot contain a group function.
D. A group function cannot contain a single row function.
Answer: B
Explanation:
Answer B is correct because function AVG cannot be used in a WHERE clause of
SELECT statement.
Incorrect Answers:
A: SELECT clause can contain a group function.
C: ORDER BY clause can contain a group function.
D: A GROUP function can contain a single row function.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 57
35
Chapter 5: Advanced Data Selection in Oracle
QUESTION NO: 41
The path table contains these columns:
ID NUMBER(7) PK
COST NUMBER(7,2)
PRODUCT_ID NUMBER(7)
Evaluate these SQL statements:
SELECT ROUND(max(cost),2),
ROUND(min(cost),2), round(sum(cost),2),
ROUND(AVG(cost),2)
FROM part;
SELECT product_id, ROUND(max(cost),2),
ROUND(min(cost),2), ROUND(sum(cost),2),
ROUND(AVG(cost),2)
FROM part
GROUP BY product_id;
How will the results differ?
A. The results will be same but the display will differ.
B. The statement1 will only display one row of results, statement2 can display
more than one.
C. Statement1 will display a result for each part, statement2 will display a result
for each product.
D. One of the statements will generate an error.
Answer: B
Explanation:
Answer B is correct because Statement2 uses GROUP BY function to calculate aggregated
functions for each PRODUCT_ID while Statement1 returns one row result.
Incorrect Answers:
A: The results will not be the same.
C: Statement1 will not display a result for each part, but only one row.
D: Both statements will be successfully completed without errors.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 58-64
Chapter 5: Advanced Data Selection in Oracle
36
QUESTION NO: 42
In which section of a PL/SQL block is a user defined exception waste?
A. Heading
B. Executable
C. Declarative
D. Exception handling
Answer: B
Explanation:
Answer B is correct because in the executable section of PL/SQL block a user-defined
exception waste.
Incorrect Answers:
A: Heading section of PL/SQL block is used for function, procedure, package naming,
not for exceptions.
C: Declarative section of PL/SQL block, which is optional, identifies all variables
constructs that will be used in the code block.
D: Exception handling section of PL/SQL block defines all errors that may occur in the
block and specifies how they should be handled.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 209
Chapter 5: Introducing PL/SQL
QUESTION NO: 43
Examine the code:
SET SERVER OUTPUT ON
DECLARE
v_char_val varchar2(100);
BEGIN
v_char_val:= ‘Hello World’,
DBMS_OUTPUT.PUT_LINE(v_char_val);
END
SET SERVER OUTPUT OFF
This code is stored in a script file name “myproc,sql”. Which statement executes the
code in the script file?
A. Myproc.sql
B. RUN myproc,sql
37
C. START myproc.sql
D. EXECUTE myproc.sql
E. BEGIN myproc.sql END;
Answer: C
Explanation:
Answer C is correct because command START is used to execute the code in the script file.
Incorrect Answers:
A: This statement will not start execution of the script.
C: Command RUN is not used to start the stored script.
D: Command EXECUTE is used to execute function or procedure, but not a stored script
E: Structure BEGIN … END represent executable section of PL/SQL block, it will not
start to execute a stored script.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 16
Chapter 1: Selecting data from Oracle
QUESTION NO: 44
Examine this block F code
Set server output ON
Declare
X NUMBER;
V_SAL NUMBER;
V_found VARCHAR2(10) := ‘TRUE’
Begin
X:=1;
V_SAL :=1000;
Declare
V_found VARCHAR2(10);
Y NUMBER;
Begin
IF (V_sal>500) THEN
V_found := ‘YES’;
END IF;
DBMS_OUTPUT.PUT_LINE(‘value f V_found is’ || V_found);
DBMS_OUTPUT.PUT_LINE (‘value f V_found is’ || V_found);
Y:20;
END
DBMS_OUTPUT.PUT_LINE (‘value f V_found is’ || V_found);
DBMS_OUTPUT.PUT_LINE (‘value f Y is’ || TO_CHAR (Y);
END
Why does this code produce an error when executed?
38
A. The value f V_found cannot be YES.
B. Variable V_found is declared at more than one location.
C. Variable Y is declared in the inner block and referenced in the outer block.
D. Variable V_sal is declared in the outer block and referenced in the inner block.
Answer: C
Explanation:
Answer C is correct because the usage of variable Y, which have been defined in the inner
block, in the outer block (second line of code from the bottom) will cause the error.
Incorrect Answers:
A: V_found variable is VARCHAR2 type, so it can have value ‘YES’.
B: Variable V_found is declared at more that one location, in the inner and the outer
blocks, but it is correct usage of the variables in PL/SQL block. Each of it has its
own scope. There is no error in this situation.
D: All variables defined in the outer block can be referenced in the inner block, but not
vice versa.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 213
Chapter 5: Introducing PL/SQL
QUESTION NO: 45
Which statement is valid within the executable section of Pl/SQL block?
A. BEGIN
emp_rec emp%ROWTYPE
END;
B. WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT.LINE(‘No records found’);
C. Select ename,sal
into v_ename,v_sal
from emp
where
empno=101;
D. Procedure cal_max(n1 NUBER n2 NUMBER, p_max OUT NUMBER)
IS
BEGIN
If n1>n2 then
p_max:=n1;
Else
p_max=n2;
END.
39
Answer: C
Explanation:
Answer C is correct because this statement populates variables v_ename and v_sal with
data from EMP table. Syntax is correct.
Incorrect Answers:
A: Record cannot be defined inside the executable section of PL/SQL block, only inside
the declarative section.
B: Exception NO_DATA_FOUND can be used only inside the exception handler block,
not inside the executable block.
D: It represents header section of the PL/SQL block, not the executable block.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 209
Chapter 5: Introducing PL/SQL
QUESTION NO: 46
How do you send the output of your SQL* Plus session to a text operating system file
called MYOUTPUT.LST?
A. SAVE MYOUTPUT.LST
B. SPOOL MYOUTPUT.LST
C. PRINT MYOUTPUT.LST
D. SEND MYOUTPUT.LST
Answer: B
Explanation:
Answer B is correct because command SPOOL is used in SQL * Plus to send output of the
session to a text operation system file.
Incorrect Answers:
A: Command SAVE is not used for output generation in SQL * Plus.
C: Command PRINT is used for printing from SQL * Plus.
D: Command SEND does not exist.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 7
Chapter 1: Selecting data from Oracle
QUESTION NO: 47
The product table contains these columns.
40
ID NUMBER(9) PK
COST NUMBER(7,2)
SALE_PRICE NUMBER(7,2)
Management has asked you to calculate the net revenue per unit for each product, if
the cost of each product is increased by 10% and the sale price of each product is
increased by 25%. You issue this SQL statement.
SELECT id, sale_price * 1.25 – cost * 1.10
FROM product;
Which conclusion can you draw from the results?
A. Only the required results are displayed.
B. The results provide more information than management requested.
C. A function needs to be included in the SELECT statement to achieve the
desired result.
D. The order on the operations in the calculation needs to be changed to achieve
the required results.
Answer: A
Explanation:
Answer A is correct because only the requested results will be displayed.
Incorrect Answers:
B: This query returns only information requested by management.
C: There is no need to use additional function to display desired results.
D: The order of operations is correct and does not need to be changed.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 6-8
Chapter 1: Selecting data from Oracle
QUESTION NO: 48
You want to create report to show different jobs in each department. You do not
want to display any duplicate roles in the report. Which SELECT statement do you
use to create the report?
A. SELECT deptno, job
FROM emp;
B. SELECT no duplicate deptno, job
FROM emp;
C. SELECT distinct deptno, job
FROM emp;
41
D. CREATE report
DISPLAY deptno, job
FROM emp;
E. SELECT distinct deptno, distinct job
FROM emp;
Answer: C
Explanation:
Answer C is correct because this query uses keyword DISTINCT which allows to avoid
duplications in displayed results.
Incorrect Answers:
A: This statement returns results with duplications.
B: NO DUPLICATE is wrong syntax in SELECT statement.
D: CREATE REPORT is wrong syntax in SELECT statement.
E: Keyword DISTINCT does not need to be used for each column in the list of SELECT
statement.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 70-71
Chapter 2: Advanced Data Selection in Oracle
QUESTION NO: 49
Which SELECT statement displays employee names, salary, department numbers
and average salaries for all employees who earn more than the average salary in their
department?
A. SELECT ename, sal, deptno, AVG(sal)
FROM emp
GROUP BY ename, sal, deptno
Answer: A
QUESTION NO: 50
Mr. King is the president of a company. Five managers report to him. All other
employees report to these managers. Examine the code.
SELECT employee.ename
FROM emp employee
WHERE employee. empno not in
SELECT manager.mgr
FROM emp manager;
42
The above statement returns no rows selected as the result why?
A. All employees have a manager.
B. None of the employees have a manager.
C. A null value is returned from the sub query.
D. Operator is not allowed in sub queries.
Answer: C
Explanation:
Answer C is correct because a null value is returned from the sub query (president of a
company does not have manager for himself).
Incorrect Answers:
A: Not ALL employees have a manager.
B: Some of the employees have a manager.
D: Statement is correct, all operators is used correctly.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 64-72
Chapter 5: Advanced Data Selection in Oracle
