Oracle PL/SQL OCP/OCA IZ0-001 Certification Sample Question and answers Bank

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
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
QUESTION NO: 51
Your company wants to get each employee a $100 salary increment. You need to
evaluate the results, from the EMP table prior to actual modification. If you do not
want to store the result in the database which statement is valid?
A. You need to add a column to the EMP table.
B. You need to give the arithmetic expression that involves the salary increment in
the set clause of the update statement.
C. You need to give the arithmetic expression that involves the salary increment in
the select clause of the select statement.
D. You need to give the arithmetic expression that involves the salary increment in
the update clause of the select statement.
E. You need to give the arithmetic expression that involves the salary increment in
the display clause of the select statement.
Answer: C
Explanation:
Answer C is correct because using of the SELECT clause of the SELECT statement WILL
NOT change data and will not store the result of operation in the database.
Incorrect Answers:
43
A: To evaluate results you DON’T NEED to add a column to the EMP table, this can be
done with SELECT statement only, without changing structure of table.
B: Usage of the SET clause of the UPDATE statement will cause data changes in the
database. So this answer is eliminated.
D: The UPDATE clause does not exist in the SELECT statement.
E: The DISPLAY clause does not exist in the SELECT statement.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 6-10
Chapter 1: Selecting Data from Oracle
QUESTION NO: 52
The employee table contains these columns
First_Name VARCHAR2(25)
Last_Name VARCHAR2(25)
Evaluate these two SQL statements.
1. SELECT
Concat(first_name, last)
Length(concat(first_name, last_name)
FROM employee
WHERE UPPER (last_name) Like ‘%J’
OR UPPER (last_name) Like ‘%K’ ;
2. SELECT INITCAP (first_name) || initcap (last_name),
Length (last_name) + Length (first_name)
FROM employee
WHERE INITCAP (SUBSTR (last_name, 1, 1)) IN
(‘J’ , ‘K’ , ‘L’)
How will the results differ?
A. The statement will retrieve different data from the database.
B. The statement will retrieve the same data from the database, but will display it
differently.
C. Statement1 will execute but statement2 will not.
D. Statement2 will execute but statement1 will not.
Answer: A
Explanation:
Answer A is correct because the statements will retrieve different data from the database.
First statement will show concatenated first and last names of EMPLOYEE TABLE and
also length of concatenated string for all rows in the table which have last_name with ‘j’,
‘J’, ‘k’ or ‘K’ on the end. Second statement will show concatenated first and last names
with the initial letter for each first and last name converted to a capital letter and also
44
summary length of first and last name for all rows which started from ‘j’, ‘J’, ‘k’, ‘K’, ‘l’ or
‘L’.
Incorrect Answers:
B: Each statement will show different data from the database.
C: Both statements will execute successfully.
D: Both statements will execute successfully.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 6-10
Chapter 1: Selecting Data from Oracle
QUESTION NO: 53
In which order does the Oracle Server evaluate clauses?
A. HAVING, WHERE, GROUP BY
B. WHERE, GROUP BY, HAVING
C. GROUP BY, HAVING, WHERE
D. WHERE, HAVING, GROUP BY
Answer: B
Explanation:
Answer B is correct because the Oracle server first evaluate WHERE clause to reduce
number of rows that need to be processed, than GROUP BY clause and after that HAVING
clause.
Incorrect Answers:
A: Order of clauses evaluation is incorrect because WHERE clause will be processed
first.
C: Order of clauses evaluation is incorrect because WHERE clause will be processed
first.
D: Order of clauses evaluation is incorrect because HAVING clause cannot be processed
before GROUP BY clause.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 60-64
Chapter 2: Advanced Data Selection in Oracle
QUESTION NO: 54
In which situation should you use outer join query?
A. The employee table has two columns that correspond.
45
B. The employee and region tables have corresponding columns.
C. The employees and region tables have no correspondence.
D. The employee table column correspond to the region table column contains null
values for rows that need to be displayed.
Answer: D
Explanation:
Answer D is correct because to show all data including null values for rows that need to be
displayed you need to use OUTER join between two tables. Notice the special (+) character
string called the outer join operator that forms the join.
Incorrect Answers:
A: You don’t need to use outer join if the employee table has two columns that
correspond.
B: You don’t need to use outer join if the employee table and region table have columns
that correspond.
C: You don’t need to use outer join if the employee table and region table have no
columns that correspond.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 53-55
Chapter 2: Advanced Data Selection in Oracle
QUESTION NO: 55
The employee table contains these columns:
ID_NUMBER(9) PK
LAST_NAME VARCHAR2(25) NN
DEPT_ID NUMBER(9)
Evaluate this SQL script
DEFINE id_2=93004
SELECT
FROM employee
WHERE id = & id_2
Which change should u make to script so that it will execute?
A. Remove the ampersand.
B. Use the ACCEPT account.
C. Add single quotation marks.
D. No change is needed.
46
Answer: D
Explanation:
Answer D is correct because script is absolutely correct by syntax. It uses substitution
variable id_2 initiated by DEFINE command to set WHERE condition for SELECT
statement.
Incorrect Answers:
A: By removing ampersand syntax will be made incorrect and SQL script will fail.
B: Do not use ACCEPT account because DEFINE command is set value for the variable
id_2.
C: Single quotation marks are needed only if ID column would have VARCHAR2 type,
not NUMBER as we see in the EMPLOYEE table definition.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 75-78
Chapter 2: Advanced Data Selection in Oracle
QUESTION NO: 56
In the declarative section of a PL/SQL block, you created but did not initialize a
number variable. When the block executes what will be the initial value of the
variable?
A. 0.
B. Null.
C. It depends on the scale and precision of the variable.
D. The block will not execute because the variable was not initialized.
Answer: B
Explanation:
Answer B is correct because if a number variable is not initialized it has NULL value by
default.
Incorrect Answers:
A: If a number variable is not initialized it has NULL value by default, not zero.
C: If a number variable is not initialized it has NULL value by default, it does not matter
what scale and precision of the variable.
D: will be successfully executed even with not initialized variable.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 211-215
Chapter 5: Introducing PL/SQL
47
QUESTION NO: 57
Evaluate the SQL statement.
SELECT e.id, (.15* e.salary) + (.25* e.bonus),
(s.sale_amount * (.15* e.commision_pct))
FROM employee e , sales
WHERE e.id = s.emp_id;
What would happen if you removed all the parenthesis from calculation?
A. Results will be lower.
B. Results will be higher.
C. Statement will not execute.
D. Statement will achieve some results.
Answer: C
Explanation:
Answer C is correct because after removing all the parenthesis the statement will have
incorrect syntax.
Incorrect Answers:
A: The statement will be incorrect, not results will be lower.
B: The statement will be incorrect, not results will be higher.
D: The statement will be incorrect, not results will be calculated.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 6-8
Chapter 1: Selecting Data from Oracle
QUESTION NO: 58
Which is not an SQL Plus command?
A. List.
B. Accept.
C. Change.
D. Update.
E. Describe.
Answer: D
Explanation:
Answer D is correct because UPDATE is SQL Data Manipulation Language (DML)
command, all others answers represent SQL * Plus commands.
48
Incorrect Answers:
A: LIST is an SQL * Plus command.
B: ACCEPT is an SQL * Plus command. Used to set value for a variable.
C: CHANGE is an SQL * Plus command. Used to correct typing errors.
E: DESCRIBE is an SQL * Plus command. Used to show the table definition.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 6-10
Chapter 1: Selecting Data from Oracle
QUESTION NO: 59
When selecting data which statement is valid about projection?
A. Projection allows due to choose rows.
B. Projection allows due to choose columns.
C. Projection allows due to joined tables together.
D. Projection allows due to add columns to a table.
Answer: B
Explanation:
Answer B is correct because projection is used to choose columns for data selecting.
Incorrect Answers:
A: Not projection, but the WHERE clause is used to choose rows.
C: Projection does not allow to join tables together.
D: Projection does not allow to add columns to a table.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 4-8
Chapter 1: Selecting Data from Oracle
QUESTION NO: 60
The employee table contains these columns:
ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
COMMISSION NUMBER(7,2)
You need to display the current commission for all employees.
Desired results are:
1. Display the commission multiplied by 1.5
49
2. Exclude employees with zero commission.
3. Display a zero for employees with null commission value.
Evaluate this SQL statement.
SELECT id, last_name, first_name, commission*1. 5
FROM employee
WHERE commission <>0;
Which of the desired results does the statement provide?
A. All the desired results.
B. Two of the desired results.
C. One of the desired results.
D. A syntax error.
Answer: B
Explanation:
Answer B is correct because the statement will provide results only for conditions 1 and 2.
Condition 3 will require to use NVL function in SELECT clause of the statement.
Incorrect Answers:
A: Only two conditions will be satisfied with this statement, not all.
C: Two conditions will be satisfied with this statement, not one.
D: There is no syntax error in the statement.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 10-11
Chapter 1: Selecting Data from Oracle
QUESTION NO: 61
View the EXHIBIT.
Examine the trace instance chart for employee table. You want to display each
employee hire date from earliest to latest. Which SQL statement will you use?
A. SELECT hire_date
FROM employee;
B. SELECT hire_date
FROM employee
ORDER BY hire_date;
C. SELECT employee
FROM employee
ORDER by hire_date;
D. SELECT hire_date
50
FROM employee
ORDER BY hire_date DESC;
Answer: B
Explanation:
Answer B is correct because ORDER BY clause of the SELECT statement will sort hire
date from earliest to latest by default.
Incorrect Answers:
A: There is no sorting for hire date in this statement at all.
C: There is an error in this statement, hire date is need to be shown, not employee.
D: DESC clause will sort hire date from latest to earliest, not vice versa.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 18-19
Chapter 1: Selecting Data from Oracle
QUESTION NO: 62
Structure of DEPT table is as follows:
Name Null Type
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
Examine the declaration section.
DECLARE
TYPE dept_table_type IS TABLE OF dept%ROWTYPE
INDEX BY BINARY INTEGER
dept_table dept_table_type;
You need to assign LOC file in record 15 the value of ‘Atlanta’. Which PL/SQL
statement makes this assignment?
A. dept_table.loc.15 := ‘Atlanta’;
B. dept_table[15].loc := ‘Atlanta’;
C. dept_table(15).loc := ‘Atlanta’;
D. dept_table_type(15).loc := ‘Atlanta’;
Answer: C
Explanation:
Answer C is correct because this syntax is correct to assign LOC file in record 15.
51
Incorrect Answers:
A: Syntax is wrong, first need to be identified object, than number of record.
B: Syntax is wrong, number of record need to be inside (), not [].
D: Data type SEPT_TABLE_TYPE cannot be used to assign a value for LOC.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 212-213
Chapter 5: Introducing PL/SQL
QUESTION NO: 63
You need to change the job title Clerk to Administrative Clerk for all Clerks.
Which statement does this?
A. UPDATE emp
SET job = ‘Administrative Clerk’;
B. UPDATE emp
Job := ‘Administrative Clerk’
WHERE UPPER (job) = ‘Clerk’;
C. UPDATE emp
SET job = ‘Administrative Clerk’
WHERE UPPER (job) = ‘CLERK’;
D. UPDATE emp
SET values job = ‘Administrative Clerk’
WHERE UPPER (job) = ‘Clerk’;
Answer: C
Explanation:
Answer C is correct because function UPPER will convert all letters in JOB column to
uppercase and compare it to ‘CLERK’, identifying all Clerks. Command UPDATE … SET
will set new value to ‘Administrative Clerk’ for these records.
Incorrect Answers:
A: Statement will change job to ‘Administrative Clerk’ for ALL records in the table.
B: Statement will not change job to ‘Administrative Clerk’ for any record in the table
because function UPPER will convert all letters in JOB column to uppercase and
comparison of its result with string ‘Clerk’ will return no records to change.
D: The statement contains syntax error : SET values.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 22-23
Chapter 1: Selecting Data from Oracle
52
QUESTION NO: 64
Given the executable section of a PL/SQL block
FROM employee_record IN Salary_Cursor Loop
employee_id_table (employee_id):=
employee_record.last_name;
END Loop
Close Salary_Cursor;
END;
Why does this section cause an error?
A. The cursor needs to be opened.
B. Terminating conditions are missing.
C. No FETCH statements were issued.
D. The cursor does not need to be explicitly closed.
Answer: D
Explanation:
Answer D is correct because cursor does not need to be closed if it’s cursor for loop. The
cursor for loop opens, parses and executes the cursor automatically.
Incorrect Answers:
A: By definition cursor for loop does not need to be open.
B: Loop will be terminated automatically when cursor will not contain any more records.
C: By definition cursor for loop does not need to issue FETCH statement to extract data
from cursor for processing.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 233-234
Chapter 5: Introducing PL/SQL
QUESTION NO: 65
To remove all the data form employee table while leaving the table definition intact.
You want to be able to undo this operation. How would you accomplish this task?
A. DROP TABLE employee.
B. DELETE FROM employee.
C. TRUNCATE TABLE employee.
D. This task can’t be accomplished.
Answer: B
Explanation:
53
Answer B is correct because using DELETE FROM statement you just delete all rows in
the table while leaving the table definition intact. Also DELETE FROM statement is DML
operation, so it can be rolled back if you want to undo this operation.
Incorrect Answers:
A: DROP TABLE command will delete not only all rows from the table but table itself
also without possibility to undo changes.
C: Command TRUNCATE TABLE will leave the table definition, deleting all rows in
the table. But it is impossible to undo this operation which eliminate this answer.
D: This task can be successfully completed with DELETE FROM command.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 123
Chapter 3: Creating database objects
QUESTION NO: 66
In which section of a PL/SQL block is a user defined exception raised?
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 raised. See also question # 42.
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: 67
Which statement is true about nesting blocks?
54
A. Variable name must be unique between blocks.
B. A variable defined in the outer block is visible in the inner blocks.
C. A variable defined in the inner block is visible in the outer blocks.
D. A variable is in an inner block may have the same name as a variable in an
outer block only if the data types are different.
Answer: B
Explanation:
Answer B is correct because a variable defined in the outer block is visible and can be used
in the inner block.
Incorrect Answers:
A: Variables can have the same names in the outer and inner blocks. Question where and
what variable will be used will be defined by the block scopes.
C: A variable from the inner block IS NOT visible from the outer block.
D: Both variables in the inner and outer blocks can have the same name and the same
data type.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 213
Chapter 5: Introducing PL/SQL
QUESTION NO: 68
Examine Code:
1. DECLARE
2. i NUMBER := 0;
3. v_date DATE ;
4. BEGIN
5. i := i + 1;
6. LOOP
7. i := v_date + 5;
8. i := i + 1;
9. EXIT WHEN i = 5;
10. END LOOP;
11. END
You have encountered the unexpected results when above block of code is executed.
How can u trace the values of counter variable i and date variable v_date in SQL*
PLUS environment?
A. By setting SQL* PLUS session variable DEBUGGER=TRUE
B. By inserting the statement
DBMS_OUTPUT.PUT_LINE (i , v_date);
55
Between lines 8-9
C. By inserting the statement
DBMS_OUTPUT.DEBUG_VAR (i , v_date);
Between lines 8-9
D. By inserting the statement
DBMS_OUTPUT.PUT_LINE (i | | ``| | TO_CHAR( v_date));
Between lines 8-9
Answer: D
Explanation:
Answer D is correct because parameter of function PUT_LINE of DBMS_OUTPUT has
type VARCHAR2. Usage of function TO_CHAR and concatenation of strings with ‘||’ will
allow this function to show trace data successfully.
Incorrect Answers:
A: There is no variable DEBUGGER in SQL * PLUS, only DEBUG. And usage of this
variable will not help to determine what causes error inside the statement.
B: Function PUT_LINE does not work with two parameters, only with one.
C: There is no DEBUG_VAR function inside the DBMS_OUTPUT package.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 210
Chapter 5: Introducing PL/SQL
QUESTION NO: 69
Examine code:
SET SERVEROUTPUT ON
DECLARE
v_name emp.ename%TYPE;
v_num NUMBER;
v_sal NUMBER(8,2);
BEGIN
--- This code displays salaries if larger than 10,000.
SELECT ename, sal
INTO v_name, v_sal
FROM emp
WHERE empno=101;
IF(v_sal.GT.10000) THEN
DBMS_OUTPUT.PUT_LINE(‘Salary is ‘||’ v_sal
|| ‘for employee’ || v_name);
END IF;
END
SET SERVER OUTPUT OF
56
This statement produces a compilation error when above PL/SQL block is executed?
A. v_num NUMBER;
B. v_name emp.ename%TYPE;
C. IF (v_sal.GT.10000) THEN
D. ---- This code displays salaries if larger than 10000.
E. SELECT ename, sal
INTO v_name, v_sal
FROM emp
WHERE empno=101;
Answer: C
Explanation:
Answer C is correct because condition inside IF-THEN construction need to use > , < ,
= to compare actual value of v_sal variable with value 10000.
Incorrect Answers:
A: Definition is correct, v_num will use default settings for NUMBER type.
B: Definition of v_name variable is correct, it has the same type as column ENAME in
EMP table.
D: Usage of comment inside of PL/SQL block is correct.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 215-217
Chapter 5: Introducing PL/SQL
QUESTION NO: 70
You are a user of PROD database which contains over 1000 tables and you need to
determine the number of tables you can access. Which data dictionary view could you
query to display this information?
A. USER_OBJECTS.
B. ALL_OBJECTS.
C. DBA_SEGEMENTS.
D. DBA_TABLES.
Answer: B
Explanation:
Answer B is correct because view ALL_OBJECTS shows all objects user can access, not
only user’s objects. Using clause WHERE OBJECT_NAME = ‘TABLE’ user will be able
to determine the number of tables he or she can access.
57
Incorrect Answers:
A: View USER_OBJECTS shows only user owned objects.
C: View DBA_SEGMENTS shows all objects, not only those objects which user can
access. DBA_SEGMENTS contains information not only about tables, but about
other objects, like INDEX segments, too.
D: View DBA_TABLES shows all tables, not only those tables which user can access.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 116-117
Chapter 3: Creating Oracle Database Objects
QUESTION NO: 71
You query the database with this command.
SELECT last_name, first_name
FROM employee
WHERE SALARY IN
(SELECT salary)
FROM employee
WHERE dept_no=3 OR dept_no=5);
Which values are displayed?
A. Last name and the first name of only the employees in the department
number 3 and 5.
B. Last name and first name of all the employees except those working in the
department 3and 5.
C. Last name and first name of all the employees with the same salary as
employee in the department 3 and 5.
D. Last name and first name of only the employees whose salary falls in the
range of salary from department 3 or 5.
Answer: C
Explanation:
Answer C is correct because sub query returns all possible salaries for employees in the
department 3 and 5 and main query will show all employees who have the same salaries.
Incorrect Answers:
A: Command will return information not only about employees in the departments 3 and
5, but other departments also.
B: Command will return information about employees in the departments 3 and 5 also.
D: Command will return information about employees who have exactly the same salary
as employees in the department 3 and 5, not range of salaries.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 64-66
58
Chapter 2: Advanced Data Selection in Oracle
QUESTION NO: 72
Which operator is not appropriate in the joined condition of none equijoin select
statement?
A. In operator.
B. Like operators.
C. Equal operators.
D. Between x and y operator.
E. Greater than and equal to operator.
Answer: C
Explanation:
Answer C is correct because none equijoin select statement can not contain operator = by
definition.
Incorrect Answers:
A: Operator IN can be used in none equijoin select statement.
B: Operator LIKE can be used in none equijoin select statement.
D: BETWEEN operator can be used in none equijoin select statement.
E: Operator >= can be used in none equijoin select statement.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 50-53
Chapter 4: Advanced Data Selection in Oracle
QUESTION NO: 73
What should you do after each fetch statement in the PL/SQL block?
A. Open the cursor.
B. Close the cursor.
C. Initialize the loop.
D. Test for the rows using the cursor attribute.
Answer: D
Explanation:
Answer D is correct because after each FETCH statement you should use
SQL%NOTFOUND or SQL%FOUND implicit cursor attributes to check whether fetch
statement processed any row data from cursor.
59
Incorrect Answers:
A: You need to open cursor before first fetch statement usage. You don’t need reopen
cursor after each FETCH statement.
B: You need to close cursor after you finished to use it. You don’t need close and reopen
cursor after each FETCH statement.
C: Loop does not need to be initialised after each FETCH statement,
SQL%NOTFOUND or SQL%FOUND implicit cursor attributes will show when you
need to exit from the loop.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 224
Chapter 5: Introducing PL/SQL
QUESTION NO: 74
You issue this command:
CREATE PUBLIC SYNONYM emp
FOR ed.employee;
Which task has been accomplished?
A. The object can now be accessed by the all users.
B. All users are given object privileges to the table.
C. The need to qualify object name with its schema was eliminated only for
you.
D. The need to qualify the name with its schema has been eliminated for all the
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. See also question 38.
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
60
QUESTION NO: 75
Which data type should you use for interest rates with varying and unpredictable
decimal places such as 1.234, 3.4, 5 and 1.23?
A. LONG.
B. NUMBER.
C. NUMBER(p, s)
Answer: B
Explanation:
Answer B is correct because NUMBER data type gives the best choice to store interest
rates with varying and unpredictable decimal places.
Incorrect Answers:
A: Data type LONG can be used to store interest rates but it will allocate to much data
blocks to keep data. Also processing of LONG data type is more difficult as
NUMBER, tables with LONG data types have some restrictions.
C: Question says about rates with varying and unpredictable decimal places, which
eliminates answer with data type NUMBER(p, s), where p and s are fixed values.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 390
Chapter 8: Managing Database Objects I
QUESTION NO: 76
Which statement is true a drop table command is executed on a table?
A. Only a DBA can execute the drop table command.
B. Any appending transactions on the table are rolled back.
C. The structure of the table remains in the database and the indexes are
deleted.
D. The drop table command can be executed on a table on which there are
pending transactions.
E. The table structure and its deleted data can’t be rolled back and restored
once the drop table command is executed.
Answer: E
Explanation:
Answer E is correct because after the DROP TABLE command you can not roll back
deleted data. It’s possible only using recovery procedures.
61
Incorrect Answers:
A: Not only DBA can execute the DROP TABLE command, users with DROP ANY
TABLE system privilege can do this command for all tables and also users can drop
tables inside their own schemas without restrictions.
B: After executing the DROP TABLE command no transactions related with this table
can be rolled back.
C: After usage of the DROP TABLE command the structure of the table and all related
indexes are deleted also.
D: The DROP TABLE command cannot be executed on a table with pending
transactions. Transactions need to be finished or rolled back before usage the DROP
TABLE command.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 398-399
Chapter 8: Managing Database Objects I
QUESTION NO: 77
Examine the structure of STUDENTS table;
Name Null Type
STU ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(25)
ADDRESS VARCHER2(50)
GRADUATION DATE
What statement adds a new column after NAME Column to hold phone numbers?
A. ALTER TABLE student
ADD COLUMN3(phone varchar2(9))
B. ALTER TABLE student
ADD COLUMN3(phone varchar2(9)) AS COLUMN3;
C. ALTER TABLE student
ADD COLUMN3(phone varchar2(9)) POSITION 3;
D. You can’t specify position when new column is added.
Answer: D
Explanation:
Answer D is correct because it’s impossible to specify position where new column will be
added after table creation.
Incorrect Answers:
A: You cannot specify position of new column in the table.
B: You cannot specify position of new column in the table.
C: You cannot specify position of new column in the table.
62
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 141-142
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 78
Which three SQL arithmetic expression return a date? (Choose three)
A. ‘03-jul-96’ + 7
B. ‘03-jul-96’ – 12
C. ‘03-jul-96’ + (12/24)
D. ‘03-jul-96’ – ‘04-jul-97’
E. (‘03-jul-96’ – ‘04-jul-97’) /7
F. (‘03-jul-96’ – ‘04-jul-97’) /12
Answer: A, B, C
Explanation:
Answer A, B, C is correct because you can use + add and - operations with date and
numeric values, result will be a DATE.
Incorrect Answers:
D: Result of this arithmetic expression will be numeric, not a DATE.
E: Result of this arithmetic expression will be numeric, not a DATE.
F: Result of this arithmetic expression will be numeric, not a DATE.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 24
Chapter I: Selecting Data from Oracle
QUESTION NO: 79
For which three tasks would you use the where clause? (Choose three)
A. Compare two values.
B. Display only unique data.
C. Designate a table location.
D. Restrict the rows displayed.
E. Restrict the output of the group function.
F. Only display data greater than a specified value.
Answer: A, D, F
Explanation:
63
Answer A, D, F is correct because the WHERE clause used to compare two values, restrict
the rows displayed and data grater than a specified value.
Incorrect Answers:
B: Unique data will be displayed with the DISTINCT clause usage.
C: The WHERE clause is used to designate a table location.
E: To restrict the output of the group function the HAVING clause is used.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 20-21
Chapter 1: Selecting Data from Oracle
QUESTION NO: 80
Which SELECT statement would you use in a PL/SQL block to query the employee
table and retrieve the last name and salary of the employee whose ID is 3?
A. SELECT last_name,salary
FROM employee;
B. SELECT last_name,salary
FROM employee;
WHERE id=3;
C. SELECT last_name,salary
INTO v_last_name,v_salary
FROM employee
WHERE id=3;
D. SELECT l last_name,salary
FROM employee;
INTO v_last_name,v_salary
WHERE id=3;
E. SELECT last_name,salary
INTO v_last_name,v_salary
WHERE id=3;
Answer: C
Explanation:
Answer C is correct because this SELECT statement will populate variables v_last_name
and v_salary from the table EMPLOYEE where employee’s ID is 3.
Incorrect Answers:
A: This statement will show ALL employees from EMPLOYEE table and no variables
will be populated with values from the table.
B: This statement will extract correct data from the table, but not populate variables with
these values in PL/SQL block.
64
D: Statement is wrong because the INTO clause must be used before the FROM clause
in the statement.
E: Statement is wrong because the INTO clause must be followed by the FROM clause,
not WHERE.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 20-21
Chapter 1: Selecting Data from Oracle
QUESTION NO: 81
The structure of the DEPT table as:
Name Null? Type
DEPT NO Not NULL Number(25)
DNAME VARCHER2(14)
LOC VARCHER2(13)
Examine the declaration section.
DECLARE
TYPE dept_record_type is RECORD
(dno NUMBER,
name VARCHER(20));
dept_rec dept_record_type;
How do you retrieve an entire row of the DEPT table using the DEPT-REC variable?
A. SELECT*
INTO dept_rec
FROM dept
WHERE dept no=10;
B. SELECT deptno,dname,loc
INTO dept_rec
FROM dept
WHERE dept no=10;
C. You can’t retrieve the entire row using the DEPT_REC variable
declared in the code.
D. SELECT*
INTO dept_rec.dno,dept_rec.name,dept_rec.
FROM dept
WHERE dept no=10;
Answer: C
Explanation:
Answer C is correct because using declared DEPT_REC variable you cannot retrieve the
entire row.
65
Incorrect Answers:
A: This syntax is used to populate variables, not records with data from the table.
B: This syntax cannot be also used to populate DEPT_REC variable.
D: This syntax cannot be also used to populate DEPT_REC variable.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 211-213
Chapter 5: Introducing PL/SQL
QUESTION NO: 82
Examine the code:
DECLARE.
CURSOR emp_cursor IS
SELECT ename,deptno
FROM emp;
emp_rec emp_cursor %ROWTYPE
BEGIN
OPEN emp_cursor
LOOP
FETCH emp_cursor
INTO emp_rec
EXIT WHEN emp_cursor NOT FOUND;
INSERT INTO temp_emp(name’dno)
VALUES(emp_rec.ename,emp_rec deptno);
END LOOP;
CLOSE emp_cursor;
END;
Using a cursor FOR loop,which PL/SQL block equivalent to the above code?
A. DECLARE
CURSOR emp-cursor 1S
SELECT ename,dept no
FROM emp;
BEGIN
FOR emp-rec IN emp-cursor LOOP
INSERT INTO temp-emp(name,dno)
VALUES (emp-rec.ename,
emp-re.deptno);
END LOOP
END;
B. DECLARE
CURSOR emp-cursor 1S
SELECT ename,dept no
66
FROM emp;
BEGIN
FOR emp-rec IN emp-cursor LOOP
OPEN emp-cursor;
INSERT INTO temp-emp(name,dno)
VALUES (emp-rec.ename,
emp_rec.deptno);
END LOOP
END;
C. DECLARE
CURSOR emp-cursor 1S
SELECT ename,dept no
FROM emp;
BEGIN
FOR emp-rec IN emp-cursor LOOP
OPEN emp-cursor;
INSERT INTO temp-emp(name,dno)
VALUES (emp-rec.ename,
emp-re.deptno);
END LOOP
CLOSE emp-cursor;
END;
D. DECLARE
CURSOR emp-cursor 1S
SELECT ename,dept no
FROM emp;
emp-rec emp-cursor%ROWTYPE;
BEGIN
FETCH emp-cursor
INTO emp-rec;
FOR emp-recIN emp-cursor LOOP
INSERT INTO temp-emp(name,dno)
VALUES (emp-rec.ename,
emp-re.deptno);
END LOOP
END;
Answer: A
Explanation:
Answer A is correct because for cursor for loop you don’t need explicitly open cursor,
fetch data and close cursor, it works automatically by definition of a cursor for loop.
Incorrect Answers:
B: You don’t need to open cursor if you use cursor FOR LOOP.
C: You don’t need to open and close cursor if you use cursor FOR LOOP.
67
D: You don’t need to fetch data into the cursor if you use cursor FOR LOOP.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 233-234
Chapter 5: Introducing PL/SQL
QUESTION NO: 83
Under which situation it is necessary to use an explicit cursor?
A. When any DML or select statement is used in a PL/SQL block?
B. When a delete statement in a PL/SQL block deletes more than one row.
C. When a select statement in a PL/SQL block is more than one row.
D. When an update statement in a PL/SQL block has to modify more than one
row.
Answer: C
Explanation:
Answer C is correct because only an explicit cursor will be able to handle situation when
statement returns more than one row. In all other situations every SQL statement executes
in an implicit cursor, including UPDATE, INSERT, DELETE DML statements and
SELECT statement.
Incorrect Answers:
A: Every SQL statement executes in an implicit cursor, including DML statements, like
UPDATE, INSERT, DELETE, and also SELECT statement.
B: DELETE statements in a PL/SQL code use an implicit cursor.
D: UPDATE statements in a PL/SQL code use an implicit cursor.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 226-227
Chapter 5: Introducing PL/SQL
QUESTION NO: 84
Examine the table instance chart for the patient table.
Column name id_number last_name first_name birth_date doctor_id
Key type PK
Nulls/Unique NN, U NN NN
FK table DOCTOR
FK column ID_NUMBER
Data type NUM VARCHAR2 VARCHAR2 DATE NUM
Length 10 25 25 10
68
Which script would you use to delete the patient from the table by prompting the user
for the id_number of the patient?
A. DELETE
FROM patient
WHERE id_number=&id_number
/
B. DELETE
FROM patient
WHERE id_number=:id_number
/
C. DELETE
DEFINE & id_number
FROM patient
WHERE id_number=&id_number
/
D. DEFINE: id_number
DELETE
FROM patient
WHERE id_number=&id_number
/
E. This task can’t be accomplished.
Answer: A
Explanation:
Answer A is correct because this script will prompt the user for the id_number of the
patient and than delete this patient from the table.
Incorrect Answers:
B: Syntax for substituted variable is not correct, ampersand & need to be used in the
WHERE clause : WHERE id_number=&id_number.
C: Usage DEFINE & id_number is not appropriate, because script will prompt user to
enter id_number of the patient, not defining it in the script. Also the usage of
DEFINE command inside DELETE … FROM clause is wrong.
D: Usage DEFINE: id_number is not appropriate, because script will prompt user to
enter id_number of the patient, not defining it in the script. Also this syntax is wrong
by using ‘:’ after keyword DEFINE.
E: This task can be successfully completed, answer A shows correct way to do this.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 75-78
Chapter 2: Advanced Data Selection in Oracle
69
QUESTION NO: 85
70
Answer:
Explanation :
The CHECK constraints allow Oracle to verify the validity of data being entered on a table
against a set of constants that acts as valid values.
The NOT NULL constraint prevents the data value defined by any row for the column from
being NULL.
The UNIQUE constraint prevents the data value by any row for the column from being
duplicated.
The PRIMARY KEY constraint prevents duplications and NULL values for columns that
are part of PRIMARY KEY.
The FOREIGN KEY constraint creates referential integrity between two columns in the
same table or two tables.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 100-105
Chapter 3: Creating Oracle Database Objects
QUESTION NO: 86
Using SQL Plus you created a user with this command:
CREATE USER Jennifer IDENTIFIED BY jbw122
What should you do to allow users database access?
A. Use the alter user command to assign the user a default tablespace.
B. Grant the user the create session privilege.
C. Use the alter user to assign the user a default profile.
D. No action is required to give the user database access.
Answer: B
Explanation:
71
Answer B is correct. To connect to database user must have at least the CREATE
SESSION privilege.
Incorrect Answers:
A: If you did not assign default tablespace for user during user account creation,
SYSTEM is default tablespace by default for this user. Also default tablespace
question does not have any relation to the database access question.
C: Default profile is assigned for user automatically during user account creation, it also
does not have relation with database access.
D: Privilege CONNECT need to be assign for user, this action is required to allow user
to access database.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 177-178
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 87
A DBA has updated Smiths account by adding the privileges. Create any table and
create procedure. Which task can Smith successfully perform?
A. Smith can create tables, drop tables and create procedures in any schema of
the database.
B. Smith can create any table or procedure only in his schema also he can drop
any table only from his schema.
C. Smith can create a table in any schema of the database but can drop tables
from and create a procedure only in his schemas.
D. Smith can create table or a procedure in any schema of the database also he
can drop the table in any schema of the database.
Answer: C
Explanation:
Answer C is correct because privilege CREATE ANY TABLE allows user to create tables
in any schema, user can drop all tables only in his own schema. CREATE PROCEDURE
privilege allows to user just create procedure in his own schema.
Incorrect Answers:
A: This answer would be correct if Smith’s account have been updated with CREATE
ANY TABLE, DROP ANY TABLE, CREATE ANY PROCEDURE privileges.
B: User can create any table in ALL schemas, not only in his schema.
D: User cannot create procedures in ANY schemas and he cannot drop the table in ANY
schema, only in his own.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 178-179
Chapter 4: Creating Other Database Objects in Oracle
72
QUESTION NO: 88
Which data dictionary view contains the definition of a view?
A. MY_VIEWS.
B. USER_VIEWS.
C. SYSTEM_VIEWS.
D. USER_TAB_VIEWS.
Answer: B
Explanation:
Answer B is correct because the USER_VIEWS contains the definition of a view.
Incorrect Answers:
A: MY_VIEWS data dictionary view does not exist.
C: SYSTEM_VIEWS data dictionary view does not exist.
D: USER_TAB_VIEWS contains table names for user views, not the definition of a
view.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 114-116
Chapter 3: Creating Oracle Database Objects
QUESTION NO: 89
Click on the exhibit button and examine the employee table. You create a with this
command:
CREATE VIEW dept_salary_vu AS SELECT dept_no, salary, last_name
FROM employee
WHERE salary>45000.
WITH CHECK OPTION;
For which employee can you update the dept_no column using this view?
A. Brown.
B. Southall.
C. Chiazza.
D. None.
Answer: D
Explanation:
73
Answer D is correct because you cannot update the dept_no column because of WHERE
clause and WITH CHECK OPTION clause.
Incorrect Answers:
A: Data cannot be changed by definition of WITH CHECK OPTION clause
B: Data cannot be changed by definition of WITH CHECK OPTION clause
C: Data cannot be changed by definition of WITH CHECK OPTION clause
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 167
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 90
You need to retrieve the employee names and salaries from emp tables assorted by
the salary in descending order. If two names match for a salary then two names must
be displayed in alphabetical order. Which statement produces the required results?
A. SELECT ename,sal
FROM emp
ORDER BY ename,sal;
B. SELECT ename,sal
FROM emp
ORDER BY sal,ename;
C. SELECT ename,sal
FROM emp
SORT BY sal DESC,ename;
D. SELECT ename,sal
FROM emp
ORDER BY sal DESC,ename;
E. SELECT ename,sal
FROM emp
ORDER BY sal DESC,ename ASCENDING;
Answer: D
Explanation:
Answer D is correct. ORDER BY clause will sort the salary in descending order because of
keyword DESC and will show the employee names in alphabetical order (default option for
ORDER BY clause).
Incorrect Answers:
A: Result will be sorted first by the employee names, not by the salary.
B: Result will show the salary sorted in ascending, not in descending order.
C: SORT BY is wrong clause of the SELECT statement.
74
E: ASCENDING is wrong clause of the SELECT statement.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 18-19
Chapter 1: Selecting Data from Oracle
QUESTION NO: 91
Which statement about using a sub query in the from clause is true?
A. You can’t use a sub query in the from clause.
B. You eliminate the need to create a new view or table by placing a sub query
in the from clause.
C. You eliminate the need to grant select privileges on the table used in the
from clause sub query.
D. You define a data source for future select statement when using a sub query
in the from clause.
Answer: B
Explanation:
Answer B is correct because you don’t need to create a new view or table, join them with a
table or view in main query and extract result from join construction.
Incorrect Answers:
A: You can use a sub query in the FROM clause.
C: By using a sub query you don’t eliminate the need to grant select privileges on the
tables used in the FROM clause sub query.
D: You cannot use sub query for future needs, it exists only during the main query
execution time.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 64-72
Chapter 2: Advanced Data Selection in Oracle
QUESTION NO: 92
Examine the table instance chart for the patient table.
Column name Id_number last_name first_name birth_date doctor_id
Key type PK
Nulls/Unique NN, UU NN NN
FK table DOCTOR
FK column ID_NUMBER
Data type NUM VARCHAR2 VARCHAR2 DATE NUM
Length 10 25 25 10
75
You created the patient_id_seq sequence to be used with the patient tables primary
key column. The sequence begins at 1000 has a maximum value of 999999999 and
increments by 1. You need to write a script to insert a row into the patient table and
use the sequence you created.
Which script would you use to complete this task?
A. This task can’t be accomplished.
B. INSERT INTO patient(id_number)
VALUES(patient_id_seq.NEXTVALUE)
C. INSERT INTO patient(id_number, last_name, first_name,
Birth_date)
VALUES(patient_id_seq, last_name, first_name,birth_date)
/
D. INSERT INTO patient(id_number, last_name, first_name,
Birth_date)
VALUES(patient_id_seq.NEXTVALUE, &last_name,&first_name,
& birth_date)
/
E. INSERT INTO patient(id_number, last_name, first_name,
Birth_date)
VALUES(patient_id_seq.NEXTVAL, &last_name,&first_name,
& birth_date)
/
F. INSERT INTO patient(id_number, last_name, first_name,
Birth_date)
VALUES(patient_id_seq.CURRVAL, &last_name,&first_name,
& birth_date)
/
Answer: E
Explanation:
Answer E is correct because patient_id_seq.NEXTVAL will generate next value of
sequence PATIENT_ID_SEQ for the ID_NUMBER column.
Incorrect Answers:
A: This task can be accomplished using function NEXTVAL.
B: NEXTVALUE is wrong function to generate next value for the sequence.
D: NEXTVALUE is wrong function to generate next value for the sequence.
F: CURRVAL will not generate next value for the sequence, it will show the same value
after each insert.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 153-154
Chapter 4: Creating Other Database Objects in Oracle
76
QUESTION NO: 93
The employee table has ten columns. Since you often query the table with condition
based on four or more columns, you created an index on all the columns in the table.
Which result will occur?
A. Updates on the table will be slower.
B. The speed of inserts will be increased.
C. All queries on the table will be faster.
D. The size of the employee table will be increased.
Answer: A
Explanation:
Answer A is correct because if you use index built on ALL columns each DML operation
(INSERT, UPDATE, DELETE) will be performed slower than without index. Index will be
rebuilt after each DML operation. If table is large it can cause performance issues with this
table.
Incorrect Answers:
B: Inserts will be slower too as all other DML operations.
C: DML operations will work slower than without index.
D: Index does not increase size of the table.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 169-171
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 94
Examine the table instance chart for the employee table.
EMPLOYEE
Column name ID_NO NAME SALARY DEPT_NO HIRE_DATE
Key type PK FK
Nulls/unique NN, UU NN
FK table DEPARMENT
FK column DEPT_NO
Data type NUM VARCHAR2 NUM NUM DATE
Length 9 25 8,2 3
You need to display the hire_date values in this format:
10 of October 1999
Which SELECT statement can you use?
77
A. SELECT hire_date(‘fmDD of MONTH YYYY’) Date Hired
FROM employee;
B. SELECT hire_date(‘DD of MONTH YYYY’) Date Hired
FROM employee;
C. SELECT TO_CHAR (hire_date,‘DDspth of MONTH YYYY’) Date
Hired
FROM employee;
D. SELECT TO_CHAR(hire_date,‘fmDD of MONTH YYYY’)DATE
HIRED
FROM employee;
Answer: D
Explanation:
Answer D is correct because it will display the hire_date in the format : 10 of October
1999, using string mask of TO_CHAR function.
Incorrect Answers:
A: Information will not be displayed correctly, syntax is wrong.
B: Information will not be displayed correctly, syntax is wrong.
C: Information will not be displayed correctly, result will be :
10th of October 1999.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 31-32
Chapter 1: Selecting Data from Oracle
QUESTION NO: 95
Examine the table instance chart for the employee table.
Column name ID_NO NAME SALARY DEPT_NO HIRE_DA
TE
Key type PK FK
Nulls/unique NN, UU NN
FK table DEPARMENT
FK column DEPT_NO
Data type NUM VARCHAR2 NUM NUM DATE
Length 9 25 8,2 3
You want to display employee hire date from the earliest to latest. Which SQL
statement would you use?
A. SELECT hire_date.
FROM employee;
B. SELECT hire_date.
78
FROM employee
ORDER BY hire_date;
C. SELECT hire_date.
FROM employee
GROUP BY hire_date;
D. SELECT hire_date.
FROM employee
ORDER BY hire_date DESC;
Answer: B
Explanation:
Answer B is correct because ORDER BY clause will sort hire date from earliest to latest by
default.
Incorrect Answers:
A: Results will not be sorted at all.
C: Clause GROUP BY will not sort results, it used for different purposes.
D: Keyword DESC of ORDER BY clause will sort the hire date from latest to earliest,
not vice versa.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 19-20
Chapter 1: Selecting Data from Oracle
QUESTION NO: 96
Examine the table instance chart for the patient table.
Column name Id_number Last_name first_name birth_date doctor_id
Key type PK
Nulls/Unique NN, UU NN NN
FK table DOCTOR
FK column ID_NUMBER
Data type NUM VARCHAR2 VARCHAR2 DATE NUM
Length 10 25 25 10
You created the patient_vu view based on the id_number and last_name columns
from the patient table. What is the best way to modify the view to contain only those
patients born in 1997?
A. Replace the view adding a WHERE clause.
B. Use the ALTER command to add WHERE clause to verify the time.
C. Drop the patient_vu then create a new view with a WHERE clause.
D. Drop the patient_vu then create a new view with a HAVING clause.
79
Answer: A
Explanation:
Answer A is correct because the statement with a WHERE clause will extract only data
about patients born in 1997.
Incorrect Answers:
B: View cannot be modified with the ALTER command.
C: View can be replaced with the WHERE clause, there is no reason to drop it.
D: View can be replaced with the WHERE clause, there is no reason to drop it. A clause
HAVING will not modify view correctly, it can be used only with a GROUP BY
clause.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 20-21
Chapter 1: Selecting Data from Oracle
QUESTION NO: 97
Evaluate this PL/SQL block:
BEGIN
FROM i IN 1. . 5 LOOP
IF i=1 THEN NULL;
ELSIF i=3 THEN COMMIT;
ELSIF 1=5 THEN ROLLBACK;
ELSE INSERT INTO test (results);
VALUES(i);
END IF;
END LOOP;
COMMIT;
END;
How many values will be permanently inserted into the TEST table?
A. 0.
B. 1
C. 2
D. 3
E. 5
F. 6
Answer: B
Explanation:
Answer B is correct because only one value will be inserted into the TEST table. First
iteration in loop will do nothing because of NULL operator. Second one will insert value
into table. Third will commit change. Forth will insert second value, but fifth iteration will
rollback last transaction. So it will be just one value inserted into the table.
80
Incorrect Answers:
A: One value will be inserted, not zero.
C: One value will be inserted, not two.
D: One value will be inserted, not three.
E: One value will be inserted, not four.
F: One value will be inserted, not five.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 219-220
Chapter 5: Introducing PL/SQL
QUESTION NO: 98
Which script would you use to query the data dictionary to view only the names of the
primary key constraints using a substitution parameter for the table name?
A. ACCEPT TABLE PROMPT(‘table to view primary key constraint:’)
SELECT constraint_name
FROM user_constraint
WHERE table_name=upper(‘&table’) AND constraint_type= ‘P’;
B. ACCEPT TABLE PROMPT(‘table to view primary key constraint:’)
SELECT constraint_name
FROM user_constraint
WHERE table_name=upper(‘&table’) AND constraint_type=
‘PRIMARY’;
C. ACCEPT TABLE PROMPT(‘table to view primary key constraint:’)
SELECT constraint_name,constraint_type
FROM user_constraint
WHERE table_name=upper(‘&table’);
D. ACCEPT TABLE PROMPT(‘table to view primary key constraint:’)
SELECT constraint_name
FROM user_cons_columns
WHERE table_name=upper(‘&table’) AND constraint_type= ‘P’;
Answer: A
Explanation:
Answer A is correct because this syntax is correct to display only the names of the primary
key constraints, usage of condition constraint_type = ‘P’ in the WHERE clause is correct.
Information can be found in the USER_CONSTRAINT table.
Incorrect Answers:
B: Usage of condition constraint_type = ‘PRIMARY’ is wrong.
C: There is no any condition regarding CONSTRAINT_TYPE, so ALL constraints will
be displayed, not only primary keys.
81
D: There is wrong view USER_CONS_NAME for this kind of query.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 143-144
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 99
Exhibit button and examine the employee table.
ID NO LAST_NAME FIRST_NAME SALARY DEPT_NO
7 Brown Terry 30000 255
6 Wargner Julie 233
4 southall david 25000 102
3 chiazza mike 50000
2 limber john 32000 145
5 goldberg Kelvin 233
1 lomberg susan 55000
8 oliver tracey 145
You attempt to query the database with this command:
SELECT dept_no,last_name,SUM(salary)
FROM employee
WHERE salary<50000
GROUP BY dept_no
ORDER BY last_name;
Which clause causes an error?
A. FROM employee.
B. WHERE salary<50000.
C. GROUP BY dept_no.
D. ORDER BY last_name;
Answer: C
Explanation:
Answer C is correct because GROUP BY works only with dept_no column, but SELECT
clause displays dept_no and last_name for each dept_no. Correction GROUP BY dept_no,
last_name will fix this error.
Incorrect Answers:
A: The FROM clause is correct, problem related only with a GROUP BY clause.
B: The WHERE clause is correct.
D: The ORDER BY clause will not cause an error.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 60-63
Chapter 2: Advanced Data Selection in Oracle
82
QUESTION NO: 100
Which statement would you use to add a primary key constraint to the patient table
using the id_number column immediately enabling the constraint?
A. This task can’t be accomplished.
B. ALTER TABLE patient
ADD CONSTRAINT pat_id_pk PRIMARY KEY(id_number);
C. ALTER TABLE patient
ADD (id_number CONATRAINT pat_id_pk PRIMARY KEY);
D. ALTER TABLE patient
MODIFY(id_number CONSTRAINT pat_id_pk PRIMARY KEY);
Answer: B
Explanation:
Answer B is correct because this statement will add a primary key constraint to the patient
table and immediately enable it.
Incorrect Answers:
A: This task can be successfully accomplished.
C: Syntax to add a primary key is wrong.
D: Syntax to add a primary key is wrong.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 100-105
Chapter 3: Creating Oracle Database Objects
QUESTION NO: 101
You attempt to create the salary table with this command:
1. CREATE TABLE SALARY.
2. (employee_id NUMBER(9)
3. CONSTRAINT salary_pk PRIMARY KEY,
4. 1995_salary NUMBER(8,2),
5. NUMBER_manager_name VARCHAR2(25)
6. CONSTRAINT mgr_name_nn NOT NULL,
7. $_salary_96 NUMBER(8,2));
Which two lines of the statement will return errors? (Choose two)
A. 1.
B. 2.
C. 3.
D. 4.
83
E. 5.
F. 7.
Answer: D, F
Explanation:
Answers D, F is correct because the first letter in column name cannot be numeric, like
1995_salary, or can not start from $.
Incorrect Answers:
A: The CREATE clause is correct , there are no error.
B: A definition of employee_id column is correct also.
C: A definition of PRIMARY KEY in SALARY table is correct.
E: Column name can include keywords, just cannot be the same as keywords.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 105-107
Chapter 3: Creating Oracle Database Objects
QUESTION NO: 102
Which select statement displays the order id product id and quantity of items in the
item table that matches both the product id and quantity of an item order(605). Do
not display the details of the order 605?
A. SELECT ordeid,prodid,qty
FROM item
WHERE (prodid,qty) IN
(SELECT prodid,qty
FROM item
WHERE ordid=605);
B. SELECT ordeid,prodid,qty
FROM item
WHERE (prodid,qty) =
(SELECT prodid,qty
FROM item
WHERE ordid=605);
AND ordid<>605;
C. SELECT ordeid,prodid,qty
FROM item
WHERE (prodid,qty) IN
(SELECT ordid,prodid,qty
FROM item
WHERE ordid=605);
AND ordid<>605;
D. SELECT ordeid,prodid,qty
FROM item
84
WHERE (prodid,qty) IN
(SELECT prodid,qty
FROM item
WHERE ordid=605);
AND ordid<>605;
Answer: D
Explanation:
Answer D is correct because it shows correct result using operation IN and eliminating
ordid = 605 in the main query.
Incorrect Answers:
A: This query works fine except one thing : it does not eliminate ordid = 605.
B: Sub query can return more than one row, so usage of = can cause an error,
operation IN is need to be used instead.
C: This query would work correctly if there was no error : the WHERE clause contains 2
columns, but sub query will return 3 result columns, it will cause an error.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 100-105
Chapter 2: Advanced Selection in Oracle
QUESTION NO: 103
Which select statement displays all the employees who do not have any subordinate?
A. SELECT
e.ename
FROM emp e
WHERE e.mgr IS NOT NULL;
B. SELECT e.ename
FROM emp e
WHERE e.empno IN (select m.mgr
FROM emp m);
C. SELECT e.ename
FROM emp e
WHERE e.empno NOT IN (select m.mgr
FROM emp m);
D. SELECT e.ename
FROM emp e
WHERE e.empno NOT IN (select m.mgr
FROM emp m
WHERE m.mgr IS NOT NULL);
85
Answer: D
Explanation:
Answer D is correct because sub query will return all employees who are manager for
somebody and main query will show all employees who are not in this list.
Incorrect Answers:
A: This query will just show all employees who have manager.
B: This query will just show all employees who are manager himself or herself.
C: It will show almost the same result as query in answer D, but sub query in answer C
does not analyze is MGR column NULL or not, which will cause wrong result of
query.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 10-11
Chapter 1: Selecting Data from Oracle
QUESTION NO: 104
Given the cursor statement:
DECLARE
CURSOR query_cursor(v_salary)IS
SELECT last_name,salary,dept_no
FROM employee
WHERE SALARY>v_salary;
Why does this statement cause an error?
A. The parameter mode is not defined.
B. A where clause is not allowed in a cursor statement.
C. The into clause is missing from the select statement.
D. A scalar data type was not specified for the parameter.
Answer: D
Explanation:
Answer D is correct because a scalar data type have to be specified in v_salary definition
for cursor query_cursor.
Incorrect Answers:
A: The parameter mode is define and can be used to avoid multiple cursors for different
salaries.
B: A where clause can be used in a cursor statement without restrictions.
C: The INTO clause does not need to be used in cursor definition.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 231-233
86
Chapter 5: Introducing PL/SQL
QUESTION NO: 105
Examine the structure:
EMP TABLE
NAME NULL? TYPE
EMP NUMBER NOT NULL NUMBER(4)
VARCHER2 NUMBER(10)
JOB VARCHAR2 NUMBER(2,9)
MGR NUMBER(4)
HARIDATE DATE
SALARY NUMBER(7,2)
COMM NUMBER(7,2)
DEPT NO NOT NULL NUMBER(2)
TAX TABLE
NAME NULL? TYPE
TAX GRADE NUMBER
LOWSAL NUMBER
HIGHSAL NUMBER
You want to create a report that displays the employee details along with the tax
category of each employee. The tax category is determined by comparing the salary of
the employees. The tax category is determined by comparing the salary of the
employee from the emp table to the lower and the upper salary values in the tax table.
Which select statement produces the required result?
A. SELECT e.name,e.salary,e.tax grade
FROM emp e,tax t
WHERE e.salary between t.lowsal and t.highsal;
B. SELECT e.name,e.salary,e.tax grade
FROM emp e,tax t
WHERE e.salary>=t.lowsal and <= t.highsal;
C. SELECT e.name,e.salary,e.tax grade
FROM emp e,tax t
WHERE e.salary<=t.lowsal and >=t.highsal;
D. SELECT e.name,e.salary,e.tax grade
FROM emp e,tax t
WHERE e.salary in t.lowsal and t.highsal;
Answer: A
Explanation:
87
Answer A is correct because this query uses correct syntax and the BETWEEN operation
of the WHERE clause to show desired results.
Incorrect Answers:
B: Construction e.salary>=t.lowsal and <= t.highsal in the WHERE clause is wrong.
Must be : e.salary>=t.lowsal and e.salary <= t.highsal.
C: Construction e.salary<=t.lowsal and >=t.highsal in the WHERE clause of the
SELECT statement is wrong.
D: Construction e.salary in t.lowsal and t.highsal in the WHERE clause is wrong.
Operation BETWEEN need to be used for this purpose, not operation IN.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 20-21
Chapter 1: Selecting Data from Oracle
QUESTION NO: 106
Examine the structure of the product and the part tables.
There points a line from id PK to product_id
PRODUCT
id PK Name
PART
id PK Name Product_id Cost
You issue this SQL statement:
SELECT pr.name
FROM part pt, product pr
WHERE pt.product_id(+)=pr.id;
What is the result?
A. An error is generated.
B. A list of product names is displayed.
C. A list of all products is displayed for product with parts.
D. A list of product is displayed for parts that have product assigned.
Answer: A
Explanation:
Answer A is correct because outer join operator need be used for the PRODUCT_ID
column from the PART table with different order of columns:
WHERE pr.id=pt.product_id(+);
Incorrect Answers:
B: A list of product names will not be displayed because of an error.
C: A list of all products for product with parts will not be displayed because of an error.
88
D: A list of product for parts that have product assigned will not be displayed because of
an error.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 53-55
Chapter 2: Advanced Data Selection in Oracle
QUESTION NO: 107
Which statement describes the use of a group function?
A. A group function produces a group of results from one row.
B. A group function produces one result from each row in the table.
C. A group function produces one result from many rows per group.
D. A group function produces many results from many rows per group.
Answer: C
Explanation:
Answer C is correct because a group function produces one result from all rows, which
have been included in one row.
Incorrect Answers:
A: A group function produces one result, not a group of results.
B: A group function produces one result for all rows in group, but not for each row in the
table.
D: A group function produces one result, not many results.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 58-60
Chapter 2: Advanced Data Selection in Oracle
QUESTION NO: 108
Examine the structure of the department and employee table.
There points a line from id PK to dept_id
DEPARTMENT
id PK Name
EMPLOYEE
id PK Last_ name First_name Dept_id
Evaluate this SQL statement:
CREATE INDEX emp_dept_id_idx
ON employee(dept_id);
89
Which result will this statement provide?
A. Store and index in the employee table.
B. Increase the chance of full table scans.
C. May reduce the amount of disk I/O for select statement.
D. May reduce the amount of disk I/O for insert statement.
E. Override the unique index created when the PK relation was defined.
Answer: C
Explanation:
Answer C is correct because by creating index on DEPT_ID column you will reduce the
I/O reads for select statement. Oracle will use index without full scan of DEPARTMENT
table.
Incorrect Answers:
A: Indexes always are stored in tablespace, not in the table.
B: Chances for full table scan will be decreased because of index usage, not increased.
D: Insert statements may increase the amount of disk I/O because after each insert index
will need to be rebuilt for new row.
E: New index will not override any unique PRIMARY indexes.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 169-177
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 109
Examine the table instance chart for the patient table.
Column name id_number last_name first_name birth_date doctor_id
Key type PK
Nulls/Unique NN, U NN NN
FK table DOCTOR
FK column ID_NUMBER
Data type NUM VARCHAR2 VARCHAR2 DATE NUM
Length 10 25 25 10
You need to create the patient_id_seq sequence to be used with the patient table’s
primary key column. The sequence will begin with 1000, have a maximum value of
999999999 never reuse any number an increment by 1. Which statement would you
use to complete this task?
A. CREATE SEQUENCE patient_id_seq
START WITH 1000
MAXVALUE 999999999
90
NO CYCLE:
B. CREATE SEQUENCE patient_id_seq
START WITH 1000
MAXVALUE 999999999
STEP BY 1;
C. CREATE SEQUENCE patient_id_seq
ON PATIENT(patient_id)
MINVALUE 1000
MAXVALUE 999999999
INCREAMENT BY 1
NO CYCLE;
D. This task can’t be accomplished.
Answer: A
Explanation:
Answer A is correct because keyword NOCYCLE shows that the sequence will not reuse
generated values after it reach maximum value of sequence.
Incorrect Answers:
B: This statement does not use NOCYCLE keyword, so sequences values will be reused
after maximum value of sequence.
C: This statement is wrong, because sequence exist without any relation with tables.
D: This task can be successfully completed with NOCYCLE keyword in the CREATE
SEQUENCE.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 153-160
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 110
You issue this command:
CREATE SYNONYM emp
FOR ed.employee;
Which task has been accomplished?
A. The need to qualify an object name with its schema was eliminated for user Ed.
B. The need to qualify an object name with its schema was eliminated for only
you.
C. The need to qualify an object name with its schema was eliminated for all
users.
D. The need to qualify an object name with its schema was eliminated for users
with access.
91
Answer: B
Explanation:
Answer B is correct because this command will eliminate the need to qualify an object
name with its owner schema for you only.
Incorrect Answers:
A: This command will eliminate the need to qualify an object name with its owner schema
for you only, user Ed don’t need to specify its name to access objects in his schema.
C: This command will eliminate the need to qualify an object name with its owner schema
for you only, not for all users.
D: This command will eliminate the need to qualify an object name with its owner schema
for you only, usage PUBLIC keyword in the CREATE SYNONYM statement will
eliminate this need for all users with access to the EMPLOYEE table.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 183-185
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 111
Examine this code :
SELECT employee.ename
FROM employee
WHERE employee.empno NOT IN
(SELECT manager.mgr
FROM emp manager);
What is not in operator equivalent to the above query?
A. !=
B. ALL.
C. !=ALL.
D. NOT LIKE.
Answer: C
Explanation:
Answer C is correct because operator !=ALL does not exist
Incorrect Answers:
A: Operator != can be used to rewrite this query.
B: Operator ALL can be used to rewrite this query.
D: Operator NOT LIKE can be used to rewrite this query.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 20-21
Chapter 1: Selecting Data from Oracle
92
QUESTION NO: 112
You want to create a report that gives per department the number of employees and
total salary as a percentage of all the departments. Examine the results from the
report:
DEPARTMENT %EMPLOYEES %SALARY
10 21. 4 30. 15
20 35. 71 37. 47
30 42. 86 32. 39
Which select statement produces the report?
A. SELECT deptno department ,
(COUNT(*)/count(empno))* 100 %employees ,
(SUM(sal)/count(*)) * 100 %salary
FROM scott.emp GROUP BY deptno;
B. SELECT deptno department ,
PCT(empno) %employees ,
PCT(sal) %salary
FROM scott.emp
GROUP BY deptno;
C. SELECT a.deptno department ,
(a.num_emp/COUNT(*))*100 %employees ,
(a.sal_sum/COUNT(*))*100 %salary .
FROM
(SELECT deptno,COUNT(*)num_emp,SUM(SAL)sal_sum
FROM scott.emp
GROUP BY deptno)a;
D. SELECT
a.deptno. deparment ,
ROUND(a.num_emp/b.total_count*100,2) %employee
ROUND(a.sal_sum/b.total_sal*100,2) %salary
FROM
(SELECT deptno,COUNT(*)num_emp,SUM(SAL)sal_sum
FROM scott.emp.
GROUP BY (deptno)a,
(SELECT COUNT(*)total_count,SUM(sal)total_sal.
FROM scott.emp) b;
Answer: D
Explanation:
Answer D is correct because displayed result shows of usage ROUND function to display
two digits after decimal point.
93
Incorrect Answers:
A: Result will not be rounded and result itself will be wrong.
B: There is no PCT function in Oracle.
C: This query uses no ROUND function, so it may be displayed more than two digits
after decimal point
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 24-29
Chapter 1: Selecting Data from Oracle
QUESTION NO: 113
Evaluate these two SQL Commands
1 SELECT distinct object_type
FROM user_object;
2 SELECT object_type
FROM all_objects;
How will the results differ?
A. Statement1 will display the distinct object types in the database.
Statement2 will display all the object types in the database.
B. Statement1 will display the distinct types owned by the user.
Statement2 will display all the object types in the database.
C. Statement1 will display the distinct object type owned by the user.
Statement2 will display the object types the user can access.
D. Statement1 will display the distinct object types that user can access.
Statement2 will display all the object types that the user owns.
Answer: C
Explanation:
Answer C is correct because DISTINCT keyword is used to eliminate duplicates in results,
statement1 will show the distinct object type for objects owned by user (view
USER_OBJECTS), statement2 will show all object types (with duplicates) for all objects
which can be accessed by user (view ALL_OBJECTS).
Incorrect Answers:
A: Both statements will not show object types for the ENTIRE database.
B: Statement2 will not display all the object types for the ENTIRE database.
D: Statement1 will display results for objects owned by user, not for objects which can
be accessed by user.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 113-119
94
Chapter 3: Creating Oracle Database Objects
QUESTION NO: 114
Examine the table instance chart for the sales table.
Column name PURCHASE_N
O
CUSTOMER_ID CAR_ID SALES_I
D
Key type PK FK FK FK
Nulls/Unique NN, U NN NN NN
FK table CUSTOMER CAR EMPLOY
EE
FK column ID ID ID
Data type NUM NUM NUM NUM
Length 9 9 9 9
You attempt to change the data base with this command:
INSERT INTO sales(purchase_no, customer_id,cars_id)
VALUES(1234,345,6);
If this statement fails which condition would explain the failure?
A. The sales table has too many foreign keys.
B. A mandatory column value is missing.
C. The statement has invalid data types.
D. The statement does not fail at all.
Answer: B
Explanation:
Answer B is correct because in this insert columns CAR_ID and SALES_ID are absent, but
they can not be NULL according the table definition.
Incorrect Answers:
A: You can create much more than 3 foreign keys for the table.
C: The statement has correct data types.
D: The statement will fail because of NOT NULL condition violation.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 10-11
Chapter 1: Selecting Data from Oracle
QUESTION NO: 115
You attempt to query the data base with this command:
95
SELECT name,salary
FROM employee
WHERE salary=
(SELECT salary
FROM employee
WHERE last_name= ‘Wagner’ OR dept_no=233)
Why could this statement cause an error?
A. Sub queries are not allowed in the where clause.
B. Logical apparatus are not allowed in where clause.
C. A multiple row sub query used with a single row comparison operator.
D. A single row query is used with a multiple row comparison operator.
Answer: C
Explanation:
Answer C is correct because sub query can return many rows, not only one, so usage of a
single row comparison operator will cause an error.
Incorrect Answers:
A: Sub queries can be used in the WHERE clause.
B: The WHERE clause uses logical apparatus to apply conditions for the rows.
D: It’s a multiple row sub query used in this statement, not a single row one.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 64-72
Chapter 2: Advanced Data Selection in Oracle
QUESTION NO: 116
Which statement shows the view definition of the view emp_view that is created based
on the emp table?
A. DESCRIBE emp
B. DESCRIBE view emp_view
C. SELECT TEXT
FROM user_views
WHERE view_name= ‘EMP_VIEW’;
D. SELECT view_text
FROM my_views
WHERE view_name= ‘EMP_VIEW’;
E. SELECT view_text
FROM table emp
WHERE view_name= ‘EMP_VIEW’;
96
Answer: C
Explanation:
Answer C is correct because this statement uses USER_VIEWS with correct condition in
the WHERE clause.
Incorrect Answers:
A: This command will show description of EMP table, not a view.
B: You cannot see description of view with DESCRIBE command.
D: There is no MY_VIEWS in the Oracle.
E: Statement is incorrect because of usage FROM TABLE. Also you can not see
description of view using table EMP.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 114-119
Chapter 3: Creating Oracle Database Objects
QUESTION NO: 117
Examine the structure of the BOOK_TITLE, COPY and CHECK_OUT tables.
BOOK TITLE
Id.PK title Author
COPY
Id PK Title_id PK available
CHECK_OUT
Id
PK
Copy_id Title_id Check_out_date Expected_return_date Customer-id
You need to create the BOOKS_AVAILABLE view. These are the desired results?
1. Include the title of each book.
2. Include the availability of each book.
3. Order the results by the author.
Evaluate this SQL statement:
CREATE VIEW books_available
AS
SELECT b.title,c.available
FROM book_title b,copy c
WHERE b.id=c.title_id
ORDER BY b.author;
What does this statement provide?
A. All the desired results.
B. Two of the desired results.
C. One of the desired results.
D. A syntax error.
97
Answer: A
Explanation:
Answer A is correct because this view will show results, including the title and availability,
ordered by the author.
Incorrect Answers:
B: All three results will be shown, not two.
C: All three results will be shown, not one.
D: There is no syntax error in this statement.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 160-169
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 118
The employee table contains three columns:
BONUS NUMBER(7,2)
DEPT_ID NUMBER(9)
There are three departments and each department has at least one employee bonus
values at least one employee. Bonus values are greater than 500;not all employee
receive a bonus.
Evaluate this PL/SQL block:
DECLARE
v_bonus employee.bonus%TYPE:=300;
BEGIN
UPDATE employee
SET bonus=bonus+v_bonus
WHERE dept_id IN (10,20,30);
COMMIT;
END;
What will be the result?
A. All the employees will be given a 300 bonus.
B. A subset of 300 employees will be given a 300 bonus.
C. All employees will be given a 300 increase in bonus.
D. A subset of employees will be given a 300 increase in bonus.
Answer: D
Explanation:
98
Answer D is correct because only employees from departments 10, 20, 30 will receive a
300 increase in bonus.
Incorrect Answers:
A: Not all employees have bonus by task description.
B: Not 300 employees, but only whose who works in departments 10, 20, 30 will be
given a 300 bonus.
C: Not all employees have bonus increase.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 211-215
Chapter 5: Introducing PL/SQL
QUESTION NO: 119
You have been granted update privileges on the last_name column of the employee
table. Which data dictionary view would you query to display the column. The
privileges was granted on the schema that owns the employee table?
A. ALL_TABLES.
B. ALL_SOURCES.
C. ALL_OBJECTS.
D. TABLE_PRIVILEGES.
E. ALL_COL_PRIVS_RECD.
F. This information can’t be retrieved from a single data dictionary view.
Answer: E
Explanation:
Answer E is correct because ALL_COL_PRIVS_RECD contains information about
column for which you have been granted update privileges.
Incorrect Answers:
A: ALL_TABLES data dictionary view does not contain information about column for
which you have been granted update privileges.
B: ALL_SOURCES data dictionary view does not exist.
C: ALL_OBJECTS data dictionary view does not contain information about column for
which you have been granted update privileges.
D: TABLE_PRIVILEGES data dictionary view does not contain information about
column for which you have been granted update privileges, it contains only info about
privileges on the table level.
F: This information can be retrieved from ALL_COL_PRIVS_RECD data dictionary
view.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 113-119
99
Chapter 3: Creating Oracle Database Objects
QUESTION NO: 120
Which alter command would you use to reinstate a disabled primary constraint?
A. ALTER TABLE
ENABLE PRIMARY KEY(ID)
B. ALTER TABLE CARS
ENABLE CONSTRAINT cars_id_pk.
C. ALTER TABLE CARS
ENABLE PRIMARY KEY(id)CASCADE;
D. ALTER TABLE CARS
ADD CONSTRAINT cards_id_pk PRIMARY KEY(id);
Answer: B
Explanation:
Answers B is correct because this statement will enable the PRIMARY KEY.
Incorrect Answers:
A: It’s wrong syntax to enable the PRIMARY KEY.
C: It’s wrong syntax to enable the PRIMARY KEY.
D: You don’t need to add or recreate PRIMARY KEY, just enable it with ALTER
TABLE ENABLE CONSTRAINT statement.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 145-148
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 121
You need to perform a major update on the employee table. You have decided to
disable the primary key constraint on the empid column and the check constraint on
the job column. What happens when you try to enable the constraint after the update
is completed?
A. You need to recreate the constraints once they are disabled.
B. Any existing rows that do not conform with the constraints are automatically
deleted.
C. Only the future values are verified to conform with the constraints having
the existing values unchecked.
D. The indexes on both the columns with the primary key constraint and the
check constraints are automatically recreated.
100
E. All the existing columns values are verified to conform with the constraints
and an error message is narrated if any existing values is not conformed.
Answer: E
Explanation:
Answers E is correct because it will be error if any existing values (old and updated) will
violate the PRIMARY KEY or the CHECK constraints.
Incorrect Answers:
A: You don’t need to recreate constraints, just enable them with ALTER TABLE
ENABLE CONSTRAINT statement.
B: Constraint will not be enabled if any existing rows violate it, but these rows will not
be deleted automatically, user must do this manually and than again enable
constraints.
C: ALL existing values will be verified, not only updated or inserted, for the constraints
violation.
D: The indexes will not be automatically recreated.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 145-148
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 122
Which table name is valid?
A. #_667.
B. Number.
C. Catch_#22.
D. 1996_invoices.
E. Invoices-1996.
Answer: C
Explanation:
Answers C is correct because there is no errors in table name Catch_#22.
Incorrect Answers:
A: Table name cannot start with symbol # .
B: Table name cannot be an Oracle keyword, like keyword NUMBER in this answer.
D: Table name cannot start with numeric symbol.
E: Table name cannot use symbol - .
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 105-107
Chapter 3: Creating Oracle Database Objects
101
QUESTION NO: 123
Examine the structure of the student table:
NAME NULL? TYPE
STUD_ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(25)
PHONE NOT NULL VARCHAR2(9)
ADDRESS VARCHAR2(50)
GRADUATION DATE
There are hundred records in the student table. You want to change the name of the
graduation column to the grad_date. Which statement is true?
A. You can’t rename a column.
B. You use the alter table command with the modify clause to rename the column.
C. You use the alter table command with the rename column clause to rename the
column.
D. You use the alter table command with the modify column clause to modify the
column.
Answer: A
Explanation:
Answers A is correct because you can not change the name of the column without
recreating this table.
Incorrect Answers:
B: ALTER TABLE command will not rename the column.
C: ALTER TABLE command will not rename the column.
D: ALTER TABLE command will not rename the column.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 6-8
Chapter 1: Selecting Data from Oracle
QUESTION NO: 124
Examine the table instance chart for the cars table.
Column name ID MODEL STYLE Colour LOT_NO
Key type PK FK
Nulls/Unique NN, UU NN NN NN NN
102
FK table LOT
FK column LOT_NO
Data type NUM CHAR CHAR CHAR NUM
Length 9 25 25 25 3
You query the database with this command:
SELECT lot_number lot number,count(*) number of cars available
FROM cars
WHERE modal= ‘fire’
GROUP BY lot_no
HAVING COUNT (*)>10
ORDER BY COUNT (*);
Which clause restricts which group’s are displayed?
A. SELECT lot_number lot number,count(*) number of cars available
B. WHERE modal= ‘fire’.
C. HAVING COUNT (*)>10.
D. GROUP BY lot_no.
E. ORDER BY COUNT (*);
Answer: C
Explanation:
Answers C is correct because the HAVING clause is used in conjunction with the GROUP
BY clause to restrict which group will be displayed.
Incorrect Answers:
A: The HAVING clause is correct, not the SELECT clause.
B: The HAVING clause is correct, not the WHERE clause.
D: The HAVING clause is correct, not the GROUP BY clause.
E: The HAVING clause is correct, not the ORDER BY clause.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 63-64
Chapter 2: Advanced Data Selection in Oracle
QUESTION NO: 125
Examine the table instances chart for the cars table.
Column name ID MODEL STYLE Colour LOT_NO
Key type PK FK
Nulls/Unique NN, UU NN NN NN NN
FK table LOT
103
FK column LOT_NO
Data type NUM CHAR CHAR CHAR NUM
Length 9 25 25 25 3
Which select table will display the style, colour and lot number for all car based on
the modal enter at the prompt regardless of the case?
B. SELECT style, colour,lot_no
FROM cars
WHERE modal=UPPER(‘&modal’);
B. SELECT style, colour,lot_no
FROM cars
WHERE modal=‘&modal’;
C. SELECT style, colour,lot_no
FROM cars
WHERE UPPER(modal)=UPPER(‘&modal’);
D. SELECT style, colour,lot_no
FROM car
WHERE UPPER(modal)= (‘&modal’);
Answer: C
Explanation:
Answers C is correct because function UPPER will convert values of column and the
modal enter to the uppercase, which allows to avoid problems with the case of the entered
value.
Incorrect Answers:
A: Column values will not be converted to the uppercase.
B: No column values not the entered value will not be converted to the uppercase.
D: The entered value will not be converted to the uppercase.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 22-24
Chapter 1: Selecting Data from Oracle
QUESTION NO: 126
Examine the declaration section:
DECLARE
CURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2)
IS
SELECT EMPNO, ENAME
FROM EMP
WHERE DEPTNO=p_deptno
104
AND JOB=p_job;
BEGIN
. . .
Which statement opens the cursor successfully?
A. OPEN emp_coursor.
B. OPEN emp_cursor(‘clerk’,10);
C. OPEN emp_cursor(10, ‘analyst’);
D. OPEN emp_cursor (p_deptno,p_job);
Answer: C
Explanation:
Answers C is correct because the statement uses correct data types (NUMBER and
VARCHAR2) for the parameters of cursor.
Incorrect Answers:
A: The statement does not use at all parameters of cursor.
B: The statement does not use correct data types for the parameters of cursor.
D: The statement needs to use bind variables to pass correct data types (NUMBER and
VARCHAR2) for the parameters of cursor, not variables used for cursor definition.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 231-233
Chapter 5: Introducing PL/SQL
QUESTION NO: 127
You want to display the average salary for the departments 20 and 50 but only if
those departments have an average salary of at least 2000. Which statement will
produce the required results?
A. SELECT deptno, AVG(sal)
FROM emp
WHERE depno IN(20,50)
GROUP BY deptno
HAVING AVG (sal)>=2000;
B. SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG (sal)>=2000;
Deptno IN (20,50);
C. SELECT deptno, AVG(sal)
FROM emp
WHERE deptno IN (20,50)
AND AVG (sal)>=2000
GROUP BY deptno;
105
D. SELECT deptno, AVG(sal)
FROM emp
WHERE deptno IN (20,50)
GROUP BY AVG(sal)
HAVING AVG(sal)>=2000
Answer: A
Explanation:
Answers A is correct because this statement will display correct results using the WHERE,
the GROUP BY, the HAVING clauses and function AVG.
Incorrect Answers:
B: The WHERE clause need to be used to restrict rows only with employees from the
departments 20 and 50.
C: Function AVG cannot be used in the WHERE clause.
D: We don’t need to group data by average salary, but by the department.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 57-64
Chapter 2: Advanced Data Selection in Oracle
QUESTION NO: 128
As a DBA you have just created a user account for employee Smith by using the
create user command. Smith should be able to create tables and packages in his
schema. Which command will the DBA need to execute next so that Smith can
perform his task successfully?
A. GRANT CREATE TABLE, CREATE PACKAGE
TO smith;
B. GRANT CREATE TABLE, CREATE PROCEDURE
TO smith;
C. GRANT CREATE SESSION,CREATE TABLE,CREATE PROCEDURE
TO smith;
D. GRANT CREATE CONNECT,CREATE TABLE,CREATE PROCEDURE
TO smith;
Answer: C
Explanation :
Answers C is correct because to be able to connect user needs to have CREATE SESSION
privilege, to create table, packages and procedures he needs CREATE TABLE and
CREATE PROCEDURE privileges.
Incorrect Answers:
106
A: User will not be able to connect without CREATE SESSION privilege.
B: User will not be able to connect without CREATE SESSION privilege.
D: CREATE CONNECT privilege does not exist in Oracle
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 177-179
Chapter 4: Creating Other Database Objects in Oracle
QUESTION NO: 129
The EMP table contains columns to hold the birthdate and the hire date of the
employees. Both of these columns are defined with date as their data type. You want
to insert a row with the details of the employee Smith who was born in 1944 and hired
in 2004.
Which statement will ensure that values are inserted into the table in the correct
century?
A. INSERT INTO EMP(empno,ename,birthdate,hiredate)
VALUES(EMPNO_SEQ.NEXTVAL, ‘Smith’, ‘12-dec-44’,
‘10-jun-04’ )
B. INSERT INTO EMP(empno,ename,birthdate,hiredate)
VALUES(EMPNO_SEQ.NEXTVAL, ‘Smith’,
TO_DATE(‘12-dec-44’, ‘DD-MON-RR’),
TO_DATE(‘10-jun-04’, ‘DD-MON-RR’));
C. INSERT INTO EMP(empno,ename,birthdate,hiredate)
VALUES(EMPNO_SEQ.NEXTVAL, ‘Smith’,
TO_DATE(‘12-dec-44’, ‘DD-MON-YY’),
TO_DATE(‘10-jun-04’, ‘DD-MON-YY’));
D. INSERT INTO EMP(empno,ename,birthdate,hiredate)
VALUES(EMPNO_SEQ.NEXTVAL, ‘Smith’,
TO_DATE(‘12-dec-44’, ‘DD-MON-YY’),
TO_DATE(‘10-jun-04’, ‘DD-MON-YY’));
Answer: D
Explanation:
Answers D is correct because it uses correct mask inside TO_DATE functions to convert
character data into date data.
Incorrect Answers:
A: This INSERT statement does not use TO_DATE functions to convert character data
into date data at all.
B: This INSERT statement does not use TO_DATE functions to convert character data
into date data correctly.
107
C: This INSERT statement does not use TO_DATE functions to convert character data
into date data at correctly.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 31-33
Chapter 1: Selecting Data from Oracle
QUESTION NO: 130
You want to retrieve the employee details from the emp table and process them in
PL/SQL block. Which type of variable do you create in the PL/SQL block to retrieve
all the rows and columns using a single select statement from the emp table?
A. PL/SQL record.
B. %ROWTYPE variable.
C. PL/SQL table of scalars.
D. PL/SQL table of records.
Answer: D
Explanation:
Answers D is correct because to retrieve ALL the rows and columns you need to use
PL/SQL table of records.
Incorrect Answers:
A: One PL/SQL record will store only one row, not all rows from the table EMP.
B: %ROWTYPE attribute is used to define PL/SQL variable, not to retrieve rows from
the table EMP.
C: PL/SQL table of scalars will store only scalar types, not columns from the table EMP.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 213-215
Chapter 5: Introducing PL/SQL