Oracle Introduction to PL/SQL

1.

The worker table has 11 columns. You often query the table with conditions based on four or more
columns. You create an index on all columns in the table. What will result?

a.
b.
c.
d.

Updates on the table will be slower.

Updates on the table will be faster.
Queries on the table will be faster.
The size of the worker table will be increased.

2.

Examine the following command:

CREATE TABLE pay
(employee-id Number(9)
CONSTRAINT pay-pk PRIMARY KEY,
2000-pay NUMBER(8,2)
manager-name VARCHAR2(25)
CONSTRAINT mgr-name-nn. NOT NULL,
pay-96 NUMBER(8,2));

Which two lines of this command will return an error?

a.
b.
c.
d.
e.
f.
g.

First
Second
Third
Fourth
Fifth
Sixth
Seventh

3.

You must store currency data. All data will have two digits to the fight of the decimal point (ie,
25.xx). The numbers to the left of the decimal point will vary. Which data type is most appropriate?

a.
b.
c.
d.

NUMBER
NUMBER(T)
LANG
LANGRA

4.

Examine the WORKER table below:

NAME
WORKER ID
NAME
PHONE
ADDRESS
POSITION

NULL
NOT NULL
NOT NULL
NOT NULL

TYPE
NUMBER(3)
VARCHAR2(25)
VARCHAR2(9)
VARCHAR2(50)
UPDATE

There are hundreds of records in the EMPLOYEE TABLE. You need to modify the phone column to hold
only number values. Which statement will modify the data type appropriately?
a.
b.
c.

ALTER TABLE worker

MODIFY phone NUMBER(9)
ALTER WORKER table
MODIFY COLUMN phone NUMBER(9)
A VARCHAR2 data type cannot be changed to a NUMBER data type for a column.

d. The data type of a column cannot be modified if there is data in the column.
5.

What should be done after each fetch statement in a PL/SQL block?

a.
b.
c.
d.

Open the cursor.

Close the cursor.
Initialize the loop.
Test for rows using the cursor attribute.

6.

Examine this executable section of a PL/SQL block:

BEGIN
FOR worker_record IN pay_cursor LOOP
Worker_id_table(worker_id):=
Worker_record.last_name;
END LOOP;
CLOSE salary_cursor;
END;
Why does this section cause an error?
a.
b.
c.
d.

The cursor needs to be open.

No fetch statements were used.
Terminating conditions are missing.
The cursor does not need to be explicitly closed.

7.

Your company will be granting workers a $150 salary increase. You need to evaluate results of the
increase from the worker table prior to the actual modification. You do not want to store in the results
in the current database. Which of the following is untrue?

a.
b.

A column must be added to the worker table.

An arithmetic expression must be added that invokes the salary increment in the SET section of the
upgrade clause.
An arithmetic expression must be added that invokes the salary increment in the SELECT
clause of the SELECT statement.
An arithmetic expression must be added that invokes the salary increment in the UPDATE clause of
the SELECT statement.

c.
d.
8.

What command should be used to execute a script file named QUERYCOLOR.SQL from the SQL
Plus environment?

a.
b.
c.
d.

START QUERYCOLOR
EXECUTE QUERYCOLOR
RUN QUERYCOLOR
GET QUERYCOLOR

9.

You need to test if the current fetch within a PL/SQL loop was successful. Which cursor attribute is
needed to accomplish this task?

a.
b.
c.
d.

SQL%ISOPEN
SQLROWCOUNT
SQL%FOUND
A cursor attribute cannot be used within a PL/SQL loop.

10. You have been assigned the task of updating worker salaries. If a salary is less than 1000, it must be
incremented by 10%. The SQL Plus substitution variable will be used to accept a worker number.
Which PL/SQL block successfully updates salaries?

a.

DECLARE
V_sal worker.sal%type;
BEGIN
SELECT sal
INTO v_sal
FROM worker
WHERE workerno=&&p_workerno;
IF v_sal<1000 then
UPDATE worker
INTO sal:=sal*1.1
WHERE workerno=&p_workerno;
END IF;
END;

b.

DECLARE
V_sal worker.sal%type;
BEGIN
SELECT sal
INTO v_sal
FROM worker
WHERE worker=&&p_wokerno;
IF v_sal<1000 then sal:=sal*1. 1
END IF;
END;

c.

DECLARE
V_sal worker.sal%type;
BEGIN
SELECT sal
INTO v_sal
FROM worker
WHERE workerno=&&p_workerno;
IF v_sal<1000 then
UPDATE emp
sal:=sal*1.1
WHERE workerno=&p_workerno;
END IF;
END;

d. DECLARE
V_sal worker.sal%type;
BEGIN
SELECT sal
INTO v_sal
FROM worker
WHERE workerno=&&p_workerno;
IF v_sal<1000 then
UPDATE worker
SET sal:=sal*1.1
WHERE workerno=&p_workerno;
END IF;
END;
11. Examine the following:
SET SERVER OUTPUT ON
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 OF v_found is||v_found);
DBMS_OUTPUT.PUT_LINE(VALUE OF v_sal is||v_sal);
Y:=20;
END;
DBMS_OUTPUT.PUT_LIN(VALUE OF v_found is||v_found);
DBMS_OUTPUT.PUT_LINE(VALUE OF Y is||to_char(Y));
END;
SET SERVER OUT PUT OFF;
Why will this procedure produce an error when executed?
a.
b.
c.
d.

The value of v_found cannot be YES.

The variable v_found is declared in more than one location.
The variable Y is declared in the inner block and referred to in the outer block.
The variable v_sal is declared in the outer block and referred to in the inner block.

12. The worker table contains the following columns:

First_name
Last_name

VARCHAR2(25)
VARCHAR2(25)

Evaluate these two SQL statements:

SELECT
CONTACT (first_name,last_name),
LENGTH(CONTACT(first_name,last_name))
FROM worker
WHERE UPPER(last_name)like %J
OR UPPER (last_name)like %K
OR UPPER (last_name)like %L;
SELECT
INITCAP(first_name)||
INITCAP(last_name),
LENGTH(last_name)+LENGTH(first_name)
FROM worker
WHERE INTICAP(substr(last_name,1,1))In
( J, K, L);
How will results differ between the two SQL statements?
a.
b.
c.
d.

Different data will be retrieved from the database.

The same data will be retrieved, but it will be displayed differently.
The first statement will be executed; the second will not.
The second statement will be executed; the first will not.

13. In the declarative section of a PL/SQL block, you create-but do not initialize-a number variable.
When the block executes, what will be the initial value of the variable?

a.
b.
c.
d.

0
Null
The value will depend on the scale and precision of the variable.
The block will not execute because the variable was not initialized.

14. The worker table contains the following columns:

LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) DEPT_ID NUMBER(9)
You need to display the names of workers that are not assigned to the department. Evaluate the following:
SELECT last_name, first_name
FROM worker
WHERE dept_id is NULL
Which of the following changes should be made to achieve the desired result?
a.
b.
c.
d.

Create an outer join.

Change the column in the WHERE condition.
Change the operator in the WHERE condition.
Add a second condition to the WHERE condition.

15. Which of the following statements regarding SQL is true?

a.
b.
c.
d.

Null values are displayed last in ascending sequences.

Data values are displayed in descending order by default.
You cannot specify a column alias in an ORDER BY clause.
Query results cannot be sorted by a column that is not included in the SELECT list.

16. You are a user of the PROD database, which has over 1200 tables. What data dictionary view must
you query to determine the number of tables you can access?
a.
b.
c.
d.

ALL_OBJECTS
DBA_TABLES
DBA_SEGMENTS
USER_OBJECTS

17. Evaluate the two following commands:

SELECT
FROM

DISTINCT object-type
user-objects;

SELECT
ROM

object-type
all-objects;

How will the results of the two commands differ?

a.
b.
c.
d.

The first statement will display the distinct object types in the database; the second statement will
display the object types in the database.
The first statement will display the distinct object types owned by the users; the second statement will
display all object types in the database.
The first statement will display distinct object types owned by the user; the second statement
will display all the object types that the user can access.
The first statement will display the distinct object types that the user can access; the second statement
will display all the object types that the user owns.

18. Which of the following privileges is related to system-level security?

a.
b.
c.
d.

Drop any table

DELETE
ALTER
INDEX

19. Evaluate the following:

CREATE ROLE supervisor;
CREATE ROLE clerk;
CREATE ROLE janitor;
CREATE USER alex IDENTIFIED BY green;
GRANT janitor TO clerk;
GRANT clerk TO supervisor;
GRANT janitor to alex;
/
How many rows will user alex have access to?
a.
b.
c.
d.

0
1
2
3

20. Mike forgot his password. Which of the following commands will set a password for user, mike?
a.
b.
c.
d.

LATER USER mike PASSWORD BY green. The command must be issued by Mike.
ALTER USER mike IDENTIFIED BY green. The command must be issued by the DBA.
ALTER USER mike IDENTIFIED BY green. The command must be issued by Mike.
CHANGE password to green WHERE user=mike; The command must be issued by the DBA.

21. You are updating the worker table. Jane has been granted the same privileges as you. You ask her to
check your work before you issue a COMMIT command. What can she do on the workers table?
a.
b.
c.
d.

Jane can access the table and verify your changes.

Jane cannot access the table.
Jane can access the table but cannot see your changes. She can make changes for you.
Jane can access the table, but cannot see your changes or make changes to the roles you are
changing.

22. Examine the following:

Name

Null?

Type

PUPIL-ID
NAME
ADDRESS
GRADUATION

NOT NULL
NOT NULL

NUMBER(3)
VARCHAR2(25)
VARCHAR2(50)
DATE

Which of these statements inserts a new row into the PUPIL table?
a.
b.
c.
d.

INSERT INTO pupil.

VALUES(121, Benson);
INSERT INTO pupil.
VALUES(121, 50 NE Oak St., 20-MAR-01, Benson);
INSERT INTO test.
VALUES(121, Benson, 50 NE Oak St., 20-MAR-01);
INSERT INTO pupil.(pupil-id,address,name,graduation)
VALUES(121, 50 NE Oak St., Benson, 20-MAR-01);

23. Examine the following:

NAME
PUPIL_ID
NAME
ADDRESS
GRADUATION

NULL
NOT NULL
NOT NULL

TYPE
NUMBER(3)
VARCHAR2(25)
VARCHAR2(50)
DATE

The GRADUATION column is a foreign key column to the table. Examine the data in the GRADE DATA
table:
Graduation

11-May-2001
13-Jan-2001
19-Dec-2001
25-Jun-2000

Which of the following statements will produce the error.ORA-02291 integrity constraint (sys_c23)
violated. Parent key not found.?
a.

b.

c.

d.

UPDATE pupil
SET pupil-id=999
Graduation=11-MAY-2001
WHERE stud-id=101;
UPDATE pupil
SET name=Benson,
Graduation=11-MAY-2001
WHERE pupil-id=101;
UPDATE pupil
SET name=Benson,
Graduation=15-AUG-200
WHERE pupil-id=101*
UPDATE pupil
SET stud-id=NULL,
Address=50 NE Oak St
WHERE graduation=18-APR-2001

24. Within SQL Plus, you issue the following:

Delete from dept_id=807
You receive an integrated constraint error indicating the child record was found. What should be done to
make the statement execute?
a.
b.
c.
d.

Delete the child record first.

Add a fourth keyword to the command.
Add the constraints cascade option to the command.
The statement cannot be executed.

25. The view WORKER-VIEW is created based on the WORKER table as follows:
CREATE OF REPLACE VIEW worker-view
AS
SELECT deptno,Sum(sal)TOT-SAL,COUNT(+)NOT-WORKER
FROM worker
GROUP BY deptno;
What happens when the following command is issued?

UPDATE worker-view
SET lot-sal=25000
WHERE deptno=8;
a.
b.
c.
d.

The base table cannot be updated through this view.

The TOT_SAL column in the WORKER table is updated to 25,000 for department 8.
The TOT_SAL column in the WORKER view is updated to 25,000 for department 8.
The SAL column in the WORKER table is updated to 25,000 for employees in department 8.

26. You view a card, ANN_SAL, that is based on the worker table. The structure of the ANN_SAL view
is:
NAME
WORKERNO
YEARLY_SAL
MONTHLY_SAL

NULL
NOT NULL

TYPE
NUMBER(4)
NUMBER(9,2)
NUMBER(9,2)

Which statement retrieves data from the ANN_SAL view?

a.
b.
c.
d.

SELECT * FROM ANN_SAL

SELECT * FROM WORKER
SELECT * FROM VIEW ANN_SAL
SELECT * FROM VIEW ANN_SAL IS DON WORKER

27. Evaluate the following:

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.
b.
c.
d.

250
500
750
1000

28. The PARTICIPANTS table contains the following columns:

ID
NAME
COACHID

NUMBER(9)
VARCHAR(2)
NUMBER(9)

Evaluate the following statements:

SELECT
FROM
WHERE

p.name,c.name
participant p,participant c
c-id= c.coach-id;

SELECT
FROM

p.name,c.name
participant p,player c

WHERE

c.coach-id=p.id;

How will the results of the two statements differ?

a.
b.
c.
d.

The first statement will not execute; the second statement will.
The first statement will execute; the second statement will not execute.
The first statement is a self join; the second statement is not.
The results will be the same, but will be displayed differently.

29. How would you declare a PL/SQL table of records to hold the rows selected from the WORKER table?
a.

DECLARE
worker-table is TABLE OF worker%ROWTYPE
b. BEGIN
TYPE worker-table is TABLE of worker%ROWTYPE
worker-table worker-table-type;
c. DECLARE
TYPE worker-table is TABLE of worker%ROWTYPE
INDEX BY WHOLE NUMBER:
worker-table worker-table-type;
d. DECLARE
TYPE worker-table is TABLE of worker%ROWTYPE
INDEX BY BINARY INTEGER.
worker-table worker-table-type;
30. Which type of cursor should be created when you want to create a cursor that can be used several
times in a block, selecting a different active set each time it is opened?
a.
b.
c.
d.

A loop cursor.
A multiple selection cursor.
A cursor for each active set.
A cursor that uses parameters.

31. Which of the following is true when writing a cursor for loop?
a.
b.
c.
d.

You must explicitly fetch the rows within a cursor for loop.
You must explicitly open the cursor prior to the cursor for loop.
You must explicitly close the cursor prior to the end of the program.
You do not explicitly open, fetch, or close a cursor within a cursor for loop.

32. The structure of the HOUSE table is as follows:

Name
HOUSE NO
DNAME
LOC

Null?
Not NULL

Type
Number(25)
VARCHAR2(14)
VARCHAR2(13)

Examine the following:

DECLARE
house-rec house%ROWTYPE:
BEGIN
SELECT*
INTO house-rec
FROM house.
WHERE houseno=10;
END;

Which PL/SQL statement displays the location of a selected department?

a.
b.
c.
d.

DBMS-OUTPUT.PUT_LINE(house-rec):
DBMS.OUTPUT.PUT_LINE(house-rec-loc);
DBMS.OUTPUT.PUT_LINE(house-rec(1).loc);
You cant display a single file in the record because they are not specially identified in the declarative
section.

33. Which of the following statements about implicit cursors is true?

a.
b.
c.
d.

They are declared implicitly only for DML statements.

The are declared implicitly for all DML and SELECT statements.
Implicit cursors must be closed before the end of PL/SQL programs.
Implicit cursors can be declared using the cursor type in the declaration section.

34. Evaluate the following:

DECLARE
v-result
BEGIN
DELETE
FROM
WHERE
v-result:=
COMMIT;
END;

NUMBER(2);
worker
division-id

IN(10,20,30);
SQL/ROWCOUNT;

What will be the value of v_result if no rows are deleted?

a.
b.
c.
d.

0
1
True
Null

35. Which two conditions in a PL/SQL block cause an exception error to occur?
a.
b.
c.
d.

Select statement does not return a row.

Select statement returns more than one row.
Select statement contains a group by clause.
Select statement does not have a WHERE clause.

36. You must create a program to insert records into the worker table. Which of the following
successfully uses the INSERT command?
a.

b.

c.

DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO worker(workernp, wname, hiredate, divisionno)
VALUES(workerno-sequence.nextval, and name, v_hiredate and divisionno)
DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO worker(workernp, wname, hiredate, divisionno)
DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO worker(workernp, wname, hiredate)
VALUES(workerno-sequence.nextval, and name, v_hiredate and divisionno)

d.

END:
DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO worker(wordernp, wname, v_hiredate and divisionno)
Job=Clerk
END:

37. Evaluate the following:

BEGIN
FOR i IN 1..10 LOOP
IF I=4 OR I=6 THEN
null;
ELSE
INSERT INTO
test(result)
VALUES
END IF;
COMMIT;
END LOOP;
ROLL BACK;
END.

(I);

How many values will be inserted into the test table?

a.
b.
c.
d.
e.

0
4
6
8
12

38. You issue the following command:

CREATE public synonym WORKER for ed.worker;
What is the result of the command?
a.
b.
c.
d.

The object can be accessed by all users.

All users are given object privileges to the table.
The need to qualify the object name with its schema is eliminated only for the commanding issuer.
The need to qualify the object name with its schema is eliminated for all users.

39. In which order does an Oracle Server evaluate clauses?

a.
b.
c.
d.

HAVING, WHERE, GROUPBY

WHERE, GROUPBY, HAVING
GROUPBY, HAVING, WHERE
WHERE, HAVING, GROUPBY

40. You query a database with the following command:

SELECT section_no,AVG(MONTHS_BETWEEN(SYSDATE,hire-data))
FROM worker WHERE AVG(MONTHS_BETWEEN(SYSDATE,hire_date))>60
GROUP BY by section_no
ORDER BY AVG(MONTHS_BETWEEN(SYSDATE,hire_date));
Why does the command 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.
41. The path table contains the following columns:
ID NUMBER(7) PK
COST NUMBER(7,2)
PRODUCT_ID NUMBER(7)
Evaluate the following 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
GROUPBY product_id;
How will the results of the two statements differ?
a.
b.
c.
d.

The results will be the same, but displayed differently.

The first statement will only display one row of results; the second statement can display more than
one row of results.
The first statement will display a result for each part; the second statement will display a result
for each product.
One of the statements will cause an error.

42. In which section of a PL/SQL block is a user-defined exception written?

a.
b.
c.
d.

Heading
Executable
Declarative
Exception handling

43. Examine the following:

SET SERVER OUTPUT ON
DECLARE
v_char_val varchar2(100);
BEGIN
v_char_val:= Welcome Home,
DBMS_OUTPUT.PUT_LINE(v_char_val);
END
SET SERVER OUTPUT OFF
This code is stored in a script file named welcome.sql. Which of the following statements will execute the
code in the script file?
a.
b.
c.
d.

welcome.sql
RUN welcome.sql
START welcome.sql
EXECUTE welcome.sql

44. Which of the following statements regarding nesting blocks is true?

a.
b.
c.
d.

Variable names must be unique between blocks.

A variable defined in the outer block is visible in the inner block.
A variable defined in the inner block is visible in the outer block.
A variable in an inner block may have the same name as a variable in an outer block only if the data
types are different.

45. Which of the following statements is valid within the executable section of a PL/SQL block?
a.
b.
c.

d.

BEGIN
Worker_rec worker%ROWtype
END;
WHEN NO_DATA FOUND THEN
DBMS_OUTPUT PUT.LIN(Nothing found);
SELECT wname,sal
INTO w_ename,w_sal
FROM worker
WHERE
workno=106;
Procedure cal_max(n1 NUMBER n2 NUMBER, p_max OUT NUMBER)
IS
BEGIN
If n1>n2 then
p_max:=n1;
Else
p_max=n2;
END.

46. What command will send the output of an SQL* Plus session to a text file named LOG.LST?
a.
b.
c.
d.

SAVE LOG.LST
SPOOL LOG.LST
PRINT LOG.LST
SEND LOG.LST

47. The merchandise table contains the following columns:

CODE
COST
SALE_PRICE

NUMBER(9)
NUMBER(7,2)
NUMBER(7,2)

PK

Your supervisor asks you to calculate net revenue per unit for each product if the cost of each product is
increased 10% and the sale price of each product is increased 25%. You issue the following:
SELECT code, sale_price * 1.25 - cost * 1.10
FROM merchandise;
What conclusion can be drawn from the results?
a.
b.
c.
d.

Only the required results are displayed.

The results provide more information than management requested.
A function needs to be included in the SELECT statement to achieve the desired result.
The order of the statement must be changed to get the requested results.

48. You have been instructed to create a report that shows different jobs in each division within your
company. No duplicate roles can be displayed. Which of the following SELECT statements should be
used?

a.
b.
c.
d.

SELECT divisionno, job

FROM worker;
SELECT no duplicate divisionno, job
FROM worker;
SELECT distinct divisionno, job
FROM worker;
CREATE report
DISPLAY divisionno, job

49. Which of the following SELECT statements displays worker names, salary, division numbers, and
average salaries for all workers who earn more than the average salary in their department?
a.

SELECT wname, sal, divsionno, AVG(sal)

FROM workers
GROUPBY wname, sal, divisionno

(other multiple choice answers not available)

50. Mrs. Jensen is president of her company. Four managers report to her, and all other employees report
to the four managers. Examine the following:
SELECT worker.wname
FROM work worker
WHERE worker, workno not in
SELECT manager.mgr
FROM work manager;
The above statement returns no rows. Why?
a.
b.
c.
d.

All employees have a manager.

None of the employees have a manager.
A null value is returned from the subquery.
An operator is not allowed in subqueries.

51. Which of the following statements regarding column subqueries 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 query, the values returned from the subquery are compared individually to the value in
the outer query.
d. In a non-pair wise query, the values returned from the subquery are compared as a group to the values
in the outer query.
52. You query a database with the following command:
SELECT dept_no, AVG (MONTHS_BETWEEN (SYSDATE, hire_date))
FROM worker
WHERE AVG (MONTHS_BETWEEN (SYSDATE, hire_date)) > 60
GROUP BY dept_no
ORDER BY AVG (MONTHS_BETWEEN (SYSDATE, hire_date) )
Where does the statement cause an error?
a.
b.
c.
d.

A SELECT clause cannot contain a group function.

A WHERE clause cannot be used to restrict groups.
An ORDER BY clause cannot contain a group function.
A group function cannot contain a single row function.

53. A group function ______.

a.
b.
c.
d.

Produces a group of results from each row.

Produces one result from each row of a table.
Produces one result from many rows per group.
Produces many results from many rows per group.

54. The worker table contains the following columns:

ID_NUMBER(9)
LAST_NAME
DEPT_ID

PK
VARCHAR2(25) NN
NUMBER(9)

Evaluate the following:

DEFINEid_3=93011
SELECT
FROM worker
WHEREid = (% id_3)
Which change should be made to the script so that it will execute?
a.
b.
c.
d.

Remove the ampersand.

Use the ACCEPT account.
Close the cursor.
No change is needed.

55. Evaluate the following:

SELECT

w.id, (.15* w.salary) + (.25* w.bonus))

(w.sale_amount * (.15* w.commision_pct))
FROM
worker w , sales
WHEREw.id = s.worker_id;
What would be the result of removing all parentheses from the calculation?
a.
b.
c.
d.

Results will be higher.

Results will be lower.
The results will be the same.
The statement will not execute.
56. Which of the following is not an SQL Plus command?
a.
b.
c.
d.

DESCRIBE
UPDATE
CHANGE
ACCEPT

57. When selecting data, projection ______.

a.
b.
c.
d.

Allows you to choose rows.

Allows you to choose columns.
Allows you to join tables together.
Allows you to add columns to a table.

58. The worker table contains the following columns:

ID
LAST_NAME
FIRST_NAME
COMMISSION

NUMBER(9)
VARCHAR2(25)
VARCHAR2(25)
NUMBER(7,2)

You must display commission calculations for employees, and are given the following guidelines:

Display commission multiplied by 1.5.

Exclude employees with zero commission.
Display a zero for employees with null commission value.

Evaluate the following statement:

SELECT
FROM
WHERE

id, last_name, first_name, commission*1.5

worker
commission <>0;

How many guidelines are met by the statement?

a.
b.
c.
d.

3
2
1
The statement generates an error.

59. Click EXHIBIT and examine the trace instance chart for the worker table (exhibit not available).
What SQL statement must be used to display each worker hire date from earliest to last?
a.

SELECT
FROM
b. SELECT
FROM
ORDER BY
c. SELECT
FROM
ORDER BY
d. SELECT
FROM
ORDER BY

hire_date
worker;
hire_date
worker
hire_date;
worker
worker
hire_date;
hire_date
worker
hire_date DESC;

60. The division table is structured as follows:

Name
DIVISIONNO
DNAME
LOC

Null
NOT NULL

Type
NUMBER(2)
VARCHAR2(14)
VARCHAR2(13)

Examine the following declaration:

DECLARE
TYPE division_table_type IS TABLE OF division &ROWTYPE
INDEX BY BINARYINTEGER
division_table
division_table_type;
You need to assign LOC file in record 13 of value Boston. Which of the following statements should be
used?
a.
b.

division_table.loc.13
division_table[13].loc

:=
:=

Boston;
Boston;

c.
d.

division_table(13).loc
division_table_type(13).loc

:=
:=

Boston;
Boston;

61. You need to change the job title Secretary to Administrative Assistant for all secretaries. Which
of the following statements accomplishes this?
a.
b.
c.
d.

UPDATE worker
UPDATE worker
Job :=
Administrative Assistant
WHERE UPPER(job) = Secretary
UPDATE worker
SET job = Administrative Assistant
WHERE UPPER(job) = Secretary;
UPDATE worker
SET values job = Administrative Assistant
WHERE UPPER(job) = Secretary;

62. You must remove all data from the color table while leaving the table definition intact. What
command should you issue, if you must be able to undo the operation?
a.
b.
c.
d.

DROP TABLE color.

DELETE FROM color.
TRUNCATE TABLE color.
This cannot be done.

63. In which section of a PL/SQL block is a user-defined exception raised?

a.
b.
c.
d.

Heading.
Execution.
Declarative.
Exception handling.

64. In nesting blocks, ______.

a.
b.
c.
d.

A variable name must be unique between blocks.

A variable defined in the outer block is visible in the inner blocks.
A variable defined in the inner block is visible in the outer blocks.
A variable in an inner block may have the same name as a variable in an outer block if the data types
are different.

65. Examine the following:

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

DECLARE
i NUMBER := 0;
x_date DATE ;
BEGIN
i := i + 1;
LOOP
i := v_date + 5;
i := i + 1;
EXIT WHEN i = 5;
END LOOP;
END

You encounter unexpected results while executing the above code. How can you trace the values of
counter variable 1 and date variable x_date in an SQL* PLUS environment?

a.
b.

Set the SQL* PLUS session variable DEBUGGER=TRUE.

Insert the statement:
DBMS_OUTPUT .PUT_LINE (I, x_date);
Between lines 8-9
c. Insert the statement:
DBMS_OUTPUT .DEBUG_VAR (I, x_date);
Between lines 8-9
d. Insert the statement:
DBMS_OUTPUT .PUT_LINE (I |||| TO_CHAR(v_date));
Between lines 8-9
66. Examine the following:
SET
SERVEROUTPUT
ON
DECLARE
v_name
worker.wname%TYPE;
v_num
NUMBER;
v_sal
NUMBER(8,2);
BEGIN
--- This code displays salaries if larger than 10,000.
SELECT wname, sal
INTO v_name, v_sal
FROM worker
WHERE workerno=101;
IF(v_sal.GT.10000) THEN
DBMS_OUTPUT.PUT_LINE(Salary is || v_sal
|| for worker || v_name);
END IF;
END
SET SERVER OUTPUT OF
This statement produces a compilation error when which PL/SQL block is executed?
a.
b.
c.
d.

v_num
NUMBER;
v_name
NUMBER;
IF (v_sal.GT.10000) THEN
--- This code displays salaries if larger than 10,000.

67. You query the worker database with this command:

SELECT
FROM
WHERE

last_name, first_name
worker
SALARY_IN
(SELECT salary)
FROM worker
WHERE division_no=3 OR division_no=5);

Which values are displayed?

a.
b.
c.
d.

Last name and first name of workers in division numbers 3 and 5.

Last name and first name of all workers except those working in divisions 3 and 5.
Last name and first name of all workers with the same salary as workers in divisions 3 and 5.
Last name and first name of workers whose salaries fall in the range of salaries in divisions 3 and 5.

68. Which operator is not appropriate in the joined condition of a non-equijoin select statement?
a.
b.

In operators.
Like operators

c.
d.

Equal operators.
Greater than and equal to operators.

69. You must permanently remove all data from the INVOICE table, but will need the table structure in
the future. What single command should be issued?
a. DROP TABLE invoice
b. TRUNCATE TABLE invoice
c. DELETE
FROM invoice
d. TRNCATE TABLE invoice
KEEP STRUCTURE;

70. You issue the following command:

CREATE PUBLIC SYNONYM work
FOR ed, worker;
Which task has been accomplished?
a. The object is now accessible to all users.
b. All users are given object privileges to the table.
c. The need to qualify object names with the schema is eliminated only for you.
d. The need to qualify object names with the schema is eliminated for all users.
71. Which data type should be used for calculating statistical probabilities with varying decimal
placements (ie, 5.1236, 5, 5.1, 5.001)?
a.
b.
c.
d.

LONG
NUMBER
NUMBER(p,s)
INTEGER

72. If a DROP TABLE command is executed on a table, ______.

a.
b.
c.
d.

Any appending transactions are rolled back.

The structure of the table remains in the database and the indexes are deleted.
The DROP TABLE command can be executed on a table on which there are pending transactions.
The table structure and its deleted data cannot be rolled back and restored once the DROP
TABLE command is executed.

73. Examine the structure of the PUPILS table:

Name
PUPIL ID
NAME
ADDRESS
GRADUATION

Null
NOT NULL
NOT NULL

Type
NUMBER(3)
VARCHAR2(25)
VARCHAR2(50)
DATE

What statement will add a new column after the NAME column to hold phone numbers?
a.
b.
c.

ALTER TABLE pupils

ADD COLUMN3(phone varchar2(9))
ALTER TABLE pupils
ADD COLUMN3(phone varchar2(9)AS COLUMN3;
ALTER TABLE pupils

ADD COLUMN3(phone varchar2/9)POSITION 3;

d. Position cannot be specified when a new column is added.
74. Which three SQL arithmetic expressions will return a date?
a.
b.
c.
d.
e.
f.

09-dec-99 + 6
09-dec-99 - 12
09-dec-99 + (12/24)
09-dec-99 - 10-dec-99
(09-dec-99 - 10-dec-99) /6
(09-dec-99 - 10-dec-99) /12

75. Which statement should be used to add and immediately enable a primary key constraint to the
customer table using the id-number column?
a. This cannot be done.
b. ALTER TABLE customer
ADD CONSTRAINT cus-id-pk PRIMARY key(id-number);
c. ALTER TABLE customer
ADD (id-number CONSTRAINT cus-id-pk PRIMARY KEY);
d. ALTER TABLE customer
MODIFY(id-number CONSTRAINT cus-id-pk PRIMARY KEY);
76. Which of the following SELECT statements will query the worker table and retrieve the last name
and salary of the employee whose idea is 5?
a.
b.
c.
d.

SELECT last-name,salary
FROM worker;
SELECT last-name,salary
FROM worker;
WHERE id=5;
SELECT last-name,salary
INTO v-last-name,v-salary
WHERE id=5;
SELECT last-name,salary
FROM worker;
INTO v-last-name,v-salary
WHERE id=5;

77. The structure of the division table is as follows:

Name
DIVISION NO
DNAME
LOC

Null?
Not NULL

Type
Number(20)
VARCHAR2(12)
VARCHAR2(13)

Examine the following declaration:

DECLARE
TYPE division-record-type is RECORD
(dno NUMBER,
name VARCHAR(20));
division-recdivision-record-type;
How can you retrieve an entire row of the division table using the division-rec variable?
a.

SELECT*
INTO division-rec
FROM division

b.

c.
d.

WHERE division no=10;

SELECT divisionno,dname,loc
INTO division-rec
FROM division
WHERE division no=10;
You cant retrieve an entire row using the division-rec variable declared in the code.
SELECT*
INTO division-rec.dno,division-rec.name,division-rec
FROM division
WHERE division no=10;

78. Examine the following:

DECLARE.
CURSOR worker-cursor IS
SELECT wname,divisionno
FROM worker;
worker-rec worker-cursor %ROWTYPE
BEGIN
OPEN worker-cursor
LOOP
FETCH worker cursor
INTO worker-rec
EXIT WHEN worker-cursor NOT FOUND;
INSERT INTO temp-worker(namedno)
VALUES(worker-rec.wname,worker-rec divisionno);
END LOOP;
CLOSE worker-cursor;
END;
Using a cursor FOR loop, which PL/SQL block is equivalent to the above code?
a.

b.

c.

DECLARE
CURSOR work-cursor 1S
SELECT wname,divisionno
FROM work;
BEGIN
FOR work-rec IN work-cursor LOOP
INSERT INTO temp-work(name,dno)
VALUES (work-rec.wname,
work-re.divisionno);
END LOOP
END;
DECLARE
CURSOR work-cursor 1S
SELECT wname,divisionno
FROM work;
BEGIN
FOR work-rec IN work-cursor LOOP
OPEN work-cursor;
INSERT INTO temp-work(name,dno)
VALUES (work-rec.wname,
work-re.divisionno);
END LOOP
END;
DECLARE
CURSOR work-cursor 1S
SELECT wname,divsisionno

d.

FROM work;
BEGIN
FOR work-rec IN work-cursor LOOP
OPEN work-cursor;
INSERT INTO temp-work(name,dno)
VALUES (work-rec.wname,
work-re.divisionno);
END LOOP
CLOSE work-cursor;
END;
The above code cannot be simulated with a LOOP.

79. An explicit cursor must be used ______.

a.
b.
c.
d.

When any DML or select statement is used in a PL/SQL block.

When a delete statement in a PL/SQL block deletes more than one row.
When a select statement in a PL/SQL block is more than one row.
When an update statement in a PL/SQL block has to modify more than one row.

80. Examine the following:

DECLARE
CURSOR query_cursor (v_salary) IS
SELECT LAST_NAME, SALARY, DIVISION_NO
FROM WORKER
WHERE SALARY>V_SALARY;
Why does this statement cause an error?
a.
b.
c.
d.

The parameter mode was not defined.

A WHERE clause is not allowed in a cursor statement.
The INTO clause is missing from the SELECT statement.
A scalar type was not specified for the parameter.

81. Using SQL Plus, you create a user with the following command:
CREATE USER Joshua IDENTIFIED BY jyd205
What must you do to allow Joshua database access?
a.
b.
c.
d.

Use the ALTER USER command to assign default table space to Joshua.
Grant Joshua the CREATE SESSION privilege.
Use the ALTER USER command to assign Joshua a default profile.
Database access is granted by default.

82. A DBA has added privileges to Randalls account to create tables and procedures on a database.
Which of the following can Randall perform?
a.
b.
c.
d.

He can create tables, drop tables, and create procedures in any schema of the database.
He can create any table or procedure in his schemas only. He can drop any table from his schema
only.
He can create a table in any schema of the database but can drop tables from and create
procedures only within his own schemas.
He can create a table or procedure in any schema of a database and can also drop tables in any
schema of the database.

83. Which data dictionary view contains the definition of a view?

a.
b.
c.
d.

MY_VIEWS
USER_VIEWS
SYSTEM_VIEWS
USER_TAB_VIEWS

84. You create the worker table using the following command:
CREATE VIEW division-salary-vu
AS SELECT division-no,salary,last-name
FROM worker
WHERE salary>45000
WITH CHECK OPTION;
Click on the EXHIBIT button and examine the worker table (exhibit not available). For which employee
can you update the dept no column using this view?
a.
b.
c.
d.

Brown
South
Chizza
None

85. Click on the exhibit button and examine the table instance chart of the patient table
Column name
Key type
Nulls/Unique
FK table
FK column
Data type
Length

Id_number
PK
NN, UU
ID_NUMBER
NUM
10

last_name

first_name

NN

NN

VARCHAR2
25

VARCHAR2
25

birth_date

physician_id
PHYSICIAN

DATE

NUM
10

You create the patient_vu view based on the id number and last name columns from the patient table.
How should you modify the view to contain only patients born in 1998?
a.
b.
c.
d.

Replace the view, adding a WHERE clause.

Use the ALTER command to add a WHERE clause to verify the date.
Drop the patient_vu, then create a new view with a WHERE clause.
Drop the patient_vu, then create a new view with a HAVING clause.

86. Which of the following statements regarding the use of a sub query in the FROM clause is true?
a.
b.
c.
d.

A sub query cannot be used in the FROM clause.

The need to create a new view or table is eliminated by placing a sub query in the FROM clause.
The need to grant SELECT privileges is eliminated by placing a sub query in the FROM clause.
Placing a sub query in the FROM clause defines a data source for future SELECT statements.

87. Examine the following chart:

Column name
Key type
Nulls/Unique
FK table
FK column
Data type
Length

Id_number
PK
NN, UU
ID_NUMBER
NUM
10

last_name

first_name

NN

NN

VARCHAR2
25

VARCHAR2
25

birth_date

physician_id
PHYSICIAN

DATE

NUM
10

You create the patient_id_seq sequence to be used with the patient tables primary key column. The
sequence begins at 1000 and has a maximum value of 99999999 and increments by 1. You must write a
script to insert a row into the patient table and use the sequence you created. Which script should be
used?
a.
b.

This cannot be done.

INSERT INTO patient(id_number, last_name, first_name, Birth_date)
VALUES(patient_id_seq, last_name, first_name, birth_date)
/
c. INSERT INTO patient(id_number, last_name, first_name, Birth_date)
VALUES(patient_id_seq.NEXTVALUE, &last_name,&first_name, & birth_date)
/
d. INSERT INTO patient(id_number, last_name, first_name, Birth_date)
VALUES(patient_id_seq.NEXTVAL, &last_name,&first_name, & birth_date)
/
88. The fruit table has ten columns. Because you query the table with conditions based on four or more
columns, you create an index on all columns in the table. Which of the following will occur?
a.
b.
c.
d.

Updates on the table will be slower.

Inserts will be faster.
All queries will be faster.
The size of the fruit table will increase.

89. Examine the worker table:

WORKER
Column name
Key type
Nulls/unique
FK table
FK column
Data type
Length

ID_NO
PK
NN, UU

NAME

NUM
9

VARCHAR2
25

SALARY

NN
NUM
8,2

DEPT_NO
FK

DEPARMENT
DEPT_NO
NUM
DATE
3

You must display hire_date values in the following format:

16 of January 20001
Which SELECT statement must be used?
a.

SELECT hire_date(fmDD ofMONTH YYYY) Date Hired

FROM worker;
b. SELECT hire_date(DD ofMONTH YYYY) Date Hired
FROM worker;
c. SELECT TO_CHAR (hire_date,DDspth of MONTH YYYY) Date Hired
FROM worker;
d. SELECT TO_CHAR(hire_date,fmDD of MONTH YYYYY)DATE HIRED
FROM worker;
90. Examine the worker table:
WORKER
Column name
HIRE_DATE
Key type
Nulls/unique
FK table

ID_NO

NAME

PK
NN, UU

NN

SALARY

HIRE_DATE

DEPT_NO
FK
DEPARMENT

FK column
Data type
Length

NUM
9

VARCHAR2
25

NUM
8,2

DEPT_NO
NUM
3

DATE

Which SQL statement will display employee hire date from earliest to latest?
a.

SELECT hire_date.
FROM worker;
b. SELECT hire_date.
FROM worker
ORDER BY hire_date;
c. SELECT hire_date;
FROM worker
GROUP BY hire_date;
d. SELECT hire_date.
FROM worker
ORDER BY hire_date DESC;
91. Evaluate the following PL/SQL block:
BEGIN
FROM I IN 1 . . 5 LOOP
IF i=1 THEN NULL;
ELSIF i=3 THEN COMMIT;
ELSIF i=5 THEN ROLLBACK;
ELSE INSERT INTO calculate(results);
VALUES(i);
END IF;
END LOOP;
COMMIT;
END;
How many values will be permanently inserted into the calculate table?
a.
b.
c.
d.
e.
f.

0
1
2
3
4
5

92. Which of the following scripts could be used to query the data dictionary to view only the names of
the primary key constraints using a substitution parameter for the table name?
a.

b.

c.

d.

ACCEPT TABLE PROMPT(table to view primary key constraint:)

SELECT constraint_name
FROM user_constraint
WHERE table_name=upper(&table)
AND constraint_type= P;
ACCEPT TABLE PROMPT(table to view primary key constraint:)
SELECT constraint_name
FROM user_constraint
WHERE table_name=upper(&table) AND constraint_type= PRIMARY;
ACCEPT TABLE PROMPT(table to view primary key constraint:)
SELECT constraint_name,constraint_type
FROM user_constraint
WHERE table_name=upper(&table);
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;
93. Match the Constraint Name to its appropriate Definition:
Constraint Name
CHECK
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY

Definition
The column must contain a value in each row.
Each value must be different in a column.
The value must be unique and present.
Defines a condition that each row must satisfy.
Establishes a relationship between columns.

ANSWER : The correct matching is

CHECK:
NOT NULL:
UNIQUE:
PRIMARY KEY:
FOREIGN KEY:

Defines a condition that each row must satisfy.

The column must contain a value in each row.
Each value must be different in a column.
The value must be unique and present.
Establishes a relationship between columns.

94. What statement would be used to add a primary key constraint to the patient table using the
id_number column, immediately enabling the constraint?
a.
b.

This cannot be done.

ALTER TABLE patient
MODIFY(id_number CONSTRAINT pat_id_pk PRIMARY KEY);
c. ALTER TABLE patient
ADD (id_number CONSTRAINT pat_id_pk PRIMARY KEY);
d. ALTER TABLE patient
ADD CONSTRAINT pat_id_pk PRIMARY KEY(id_number);
95. You attempt to create the salary table with this command:
CREATE TABLE TENURE.
(worker_id NUMBER(9)
CONSTRAINT tenure_pk PRIMARY KEY,
1995_salary NUMBER(8,2),
NUMBER manager_name VARCHAR2(25)
CONSTRAINT mgr_name_nn NOT NULL,
$ salary_96 NUMBER(8,2));
Which two lines of the statement will return errors?
a.
b.
c.
d.
e.
f.
g.

1
2
3
4
5
6
7

96. Which SELECT statement displays the Order ID, Product ID, and quantity of items in the
merchandise table that matches both the Product ID and quantity of an item, order(20)?
a.

SELECT ordeid,prodid,qty
FROM merchandise

WHER (prodid,qty) IN
(SELECT prodid,qty
FROM merchandise
WHERE ordid=20)
b. SELECT ordeid,prodid,qty
FROM merchandise
WHERE (prodid,qty) =
(SELECT prodid,qty
FROM merchandise
WHERE ordid=20);
ANDordid<>20;
c. SELECT ordeid,prodid,qty
FROM merchandise
WHERE (prodid,qty) IN
(SELECT ordid,prodid,qty
FROM item
WHERE ordid=20);
AND ordid<>20;
d. SELECT ordeid,prodid,qty
FROM merchandise
WHERE (prodid,qty) IN
(SELECT prodid,qty
FROM merchandise
WHERE ordid=20);
AND ordid<>20;
97. Which of the following SELECT statements displays all workers without a subordinate?
a.

SELECT
w.wname
FROM work w
WHERE w.mgr IS NOT NULL;
b. SELECT w.wname
FROM work w
WHERE w.workno NOT IN (select m.mgr
FROM work w
WHER m.mgr IS NOT NULL);
c. SELECT w.wname
FROM work w
WHERE w.workno IN (select m.mgr
FROM work m);
d. SELECT w.wname
FROM work w
WHERE w.workno NOT IN (select m.mgr
FROM work m);
98. Examine the following cursor statement:
DECLARE
CURSOR query_cursor(v_salary)IS
SELECT last_name,salary_divison_no
FROM worker
WHERE SALARY>v_salary;
Why does this statement cause an error?
a.
b.

The INTO clause is missing.

A WHERE clause cannot be used in a cursor statement.

c.
d.

A scalar data type was not specified by the parameter.

The parameter mode is not defined in the statement.

99. Examine the structure of the EMP table:

EMP TABLE
NAME
EMP NUMBER
VARCHAR2
JOB
MGR
HIREDATE
SALARY
COMM
DEPT NO
TAX TABLE
NAME
TAX GRADE
LOWSAL
HIGHSAL

NULL?
NOT NULL
VARCHAR2

NOT NULL
NULL?

TYPE
NUMBER(4)
NUMBER(10)
NUMBER(2,9)
NUMBER(4)
DATE
NUMBER(7,2)
NUMBER(7,2)
NUMBER(2)
TYPE
NUMBER
NUMBER
NUMBER

You must create a report that displays employee details along with the tax category of each employee. The
tax category is determined by comparing the salary of the employee from the emp table to the upper and
lower salary values in the tax table. Which of the following SELECT statements will perform the
necessary comparisons?
a.
b.
c.
d.

SELECT e.name,e.salary,e.tax grade

FROM emp e,tax t
WHERE e.salary between t.lowsal and t.highsal;
SELECT e.name,e.salary,e.tax grade
FROM emp e,tax t
WHERE e.salary>=t.lowsal and <= t.highsal;
SELECT e.name,e.salary,e.tax grade
FROM emp e,tax t
WHERE e.salary in t.lowsal and t.highsal.
SELECT e.name,e.salary,e.tax grade
FROM emp e,tax t
WHERE e.salary<=t.lowsal and >= t.highsal;

100.Examine the structure of the product and part tables:

PRODUCT
Id PK

Name

PART
Id PK

name

Product_id

cost

You issue the following statement:

SELECT
pt.name
FROM part pt,product.printer
WHERE pt.product_id(+)=pr.id;
What will occur?
a.
b.
c.

A list of product names will be displayed.

A list of products is displayed for parts that have products assigned.
An error will be generated.*

d.

A list of all products is displayed for products with parts.

101.A group function produces ______.

a.
b.
c.
d.

A group of results from one row.

One result from each row in a table.
Many results from many rows per group.
One result from many rows per group.

102.Examine the structure of the division and worker tables below:

DIVISION
id PK

Name

WORKER
id PK

Last_name

First_name

Divion_id

Evaluate the following statement:

CRATE INDEX
ON

worker_division_id_idx
worker(divison_id);

What will be the result of the statement?

a.
b.
c.
d.

Store and index the worker table.

Increase the chance of full table scans.
Reduce disk I/O for SELECT statements.
Reduce disk I/O for INSERT statements.

103.Examine the patient table:

Column name
Key type
Nulls/Unique
FK table
FK column
Data type
Length

id_number
PK
NN, U
ID_NUMBER
NUM
10

last_name

first_name

NN

NN

VARCHAR2
25

VARCHAR2
25

birth_date

Physician_id
PHYSICIAN

DATE

NUM
10

You must create the patient_id_seq sequence to be used with the patient tables primary key column. The
sequence will begin with 1000, have a maximum value of 9999999, not reuse numbers, and increment in
quantities of 1. Which of the following statements will accomplish the task?
a.

b.

c.

CREATE SEQUENCE patient_id_seq

START WITH 1000
MAXVALUE 9999999
NO CYCLE:
CREATE SEQUENCE patient_id_seq
START WITH 1000
MAXVALUE 9999999
STEP BY 1;
CREATE SEQUENCE patient_id_seq
ON PATIENT(patient_id)
MINVALUE 1000
MAXVALUE 9999999
INCREMENT BY 1
NO CYCLE;

d.

This cannot be done.

104.You issue the following command:

CREATE SYNONYM work
FOR ed.employee;
Because of the command, the need to qualify an object name with its schema has been eliminated for
______.
a.
b.
c.
d.

All users.
Only yourself.
User Ed.
Users with access.

105.You must create a report that gives, per division, the number of workers and total salary as a
percentage of all divisions. Examine the results of the report:
DIVISION
10
20
30

%WORKERS
21.4
35.71
42.86

%SALARY
30.15
37.47
32.39

Which of the following SELECT statements will produce the above report?
a.

b.

c.

d.

SELECT divsionno
division,
(COUNT(*)/count(workno))* 100
%workers,
(SUM(sal)count(*))* 100
%salary
FROM scott.work GROUP BY divisiono;
SELECT divisionno
division,
PCT(workno)
%workers,
PCT(sal)
%salary
FROM scott.work
GROUP BY divisionno;
SELECT a.divisionno
division,
(a.num_work/COUNT(*))* 100
%workers,
(a.sal_sum/COUNT(*))*100
%salary.
FROM
(SELECT divisionno,COUNT(*)num_work,SUM(SAL)sal_sum
FROM scott.work
GROUP BY divisionno)a;
SELECT
division,
a.divisionno.
ROUND(a.num_work/b.total_count * 100,2)%workers
ROUND(a.sal_sum/b.total_sal * 100,2)
%salary%
FROM
(SELECT divisionno,COUNT(*)num_work,SUM(SAL)sal_sum
FROM scott.work
GROUP BY divisionno)b;

106.In which situation would an outer query be used?

a. The worker table has two columns that correspond.
b. The worker table column corresponding to the region table contains null values for rows that
need to be displayed.
c. The worker and region tables have no corresponding columns.
d. The worker and region tables have corresponding columns.

107.The worker table has three columns:

LAST_NAME
FIRST_NAME
SALARY

VARCHAR2(23)
VARCHAR2(23)
NUMBER(7,2)

Your manager requests that you write a statement to display all workers earning more than the average
salary of all workers. Evaluate the following SQL statement:
SELECT
FROM
WHERE

last_name
worker
salary > AVG(salary);

What change should be made to the statement?

a.
b.
c.
d.

Move the function to the SELECT clause and add a GROUP BY clause.
Use a sub query in the WHERE clause to compare the salary value.
Change the function in the WHERE clause.
The statement requires no modification.

108.You attempt to query the worker database with the following command:
SELECT name,salary
FROM worker
Where salary=
(SELECT salary
FROM worker
WHERE last_name= Johnson OR dept_no=43)
The statement will cause an error because ______.
a.
b.
c.
d.

Sub queries cannot be used with the WHERE clause.

A multiple-row sub query has been used with a single row comparison operator.
A single row query has been used with a multiple-row comparison operator.
Logical apparatus are not allowed in the WHERE clause.

109.Which statement will provide the view definition of the work_view that is created based on the
worker table?
a.
b.
c.
d.

Describe work
DESCRIBE view work_view
SELECT TEXT
FROM user_views
WHERE view_name= WORK_VIEW;
SELECT view_text
FROM my_views
WHERE view_name= WORK_VIEW

110.Examine the structure of the movie title, copy, and check_out tables:
MOVIE
IdPK

Title

Director

COPY
IdPK

Title id PK

Available

CHECK_OUT
IdPK Copy_id

Title_id

Check_out_date

Expected_return_date

Customer-id

You need to create the MOVIES_AVAILABE view, and have the following parameters:

Include the title of each movie.

Include the availability of each movie.
Order the results by director.

Evaluate the following statement:

CREATE VIEW
movies_available
AS
SELECT
b.title,c.available
FROM movie_title b,copy c
WERE b.id=c.title_id
ORDER BY b.director;
Which of the parameters are met?
a.
b.
c.
d.

All
Two
One
A syntax error results.

111. There are three divisions within your company and each division has at least one worker bonus
program and at least one worker. Bonus values do not exceed 500; not all employees receive bonuses.
Evaluate the following block:
DECLARE
V_bonus worker.bonus%TYPE:=270;
BEGIN
UPDATE worker
SET bonus=bonus+v_bonus
WHERE division_id IN (10,20,30);
COMMIT;
END;
What is the result of the statement?
a.
b.
c.
d.

All employees will be given a bonus of 270.

A subset of 270 employees will be given a bonus of 270.
All employees will be given a 270 increase in bonus.
A subset of employees will be given a 270 increase in bonus.*

112.You have been given update privileges on the last_name column of the worker table. Which data
dictionary view would you query to display the column? The privileges were granted on the schema
that owns the worker table.
a.
b.
c.
d.

ALL_TABLES
TABLE_PRIVILEGES
ALL_COL_PRIVS_RECD
This cannot be retrieved from a single view.

113.Which of the following ALTER commands reinstates a disabled primary constraint?

a.
b.

ALTER TABLE FRUIT

ENABLE PRIMARY KEY(ID)
ALTER TABLE FRUIT
Enable PRIMARY KEY(id)CASCADE;

c.
d.

ALTER TABLE FRUIT

ENALBE CONSTRAINT fruit_id_pk;
ALTER TABLE FRUIT
ADD CONSTRAINT fruit_id_pk PRIMARY KEY(id);

114.You have been assigned the task of making major updates to the worker table. You disable the
primary key constraint on the workid column and the check constraint on the job column. What
happens when you try to enable the constraint after the update is completed?
a.
b.
c.
d.

Existing rows that dont conform with the constraints are automatically deleted.
Indexes on both columns with the primary key constraint and the check constraints are automatically
recreated.
All existing column values are verified to conform with the constraints and an error message is
narrated if any existing values are not confirmed.
The constraints must be recreated once they are disabled.

115.Which of the following is a valid table name?

a.
b.
c.
d.

#_9
24_bottles
colors-1999
Slipper_#66*

116.Examine the structure of the pupil table:

NAME
PUP_ID
NAME
PHONE
ADDRESS
GRADUATION

NULL?
NOT NULL
NOT NULL
NOT NULL

TYPE
NUMBER(3)
VARCHAR2(25)
VARCHAR2(9)
VARCHAR2(50)
DATE

There are over two hundred records in the pupil table. You want to change the name of the graduation
column to grad_date. Which of the following is true?
a.

You can use the ALTER TABLE command with the MODIFY COLUMN clause to modify the
column.
b. You can use the ALTER TABLE command with the RENAME COLUMN clause to rename the
column.
c. You can use the ALTER TABLE command with the MODIFY clause to rename the column.
d. You cannot rename the column.
117.Examine the automobile table:
AUTOMOBILE
Column name
Key type
Nulls/Unique
FK table
FK column
Data type
Length

ID
PK
NN, UU

MODEL

STYLE

Color

NN

NN

NN

NUM
9

CHAR
20

CHAR
20

CHAR
20

LOT_NO
FK
NN
LOT
LOT_NO
NUM
3

Which SELECT statement will display the style, color, and lot number for all cars based on model?
a.

SELECT style,color,lot_no
FROM automobile
WHERE model=UPPER(%model);

b.
c.
d.

SELECT style,color,lot_no
FROM automobile
WHERE UPPER model=(&model);
SELECT style,color,lot_no
FROM automobile
WHERE UPPER model=UPPER(&model);
SELECT style,color,lot_no
FROM automobile
WHERE model=&model;

118.Examine the following DECLARE statement:

DECLARE
CURSOR work_cursor(p_divisionno NUMBER, p_job VARCHAR2)
IS
SELECT WORKNO, WNAME
FROM WORK
WHERE WORKNO=p_divisionno
AND JOB=p_job
BEGIN
...
Which statement opens the cursor successfully?
a.
b.
c.
d.

OPEN work_cursor.
OPEN work_cursor(clerk;,10);
OPEN work_cursor(10, manager);
OPEN work_cursor(p_divisionno,p_job);

119.As DBA, you use the CREATE USER command to create an account for user, Davis. Davis must
create tables and packages in his own schema. What command must be executed next to grant him
these privileges?
a.

GRANT CREATE TABLE, CREATE PACKAGE

TO davis;
b. GRANT CREATE CONNECT, CREATE TABLE, CREATE PROCEDURE
TO davis;
c. GRANT CREATE TABLE, CREATE PROCEDURE
TO davis;
d. GRANT CREATE SESSION,CREATE TABLE, CREATE PROCEDURE
TO davis;
120.The WORK table has columns designated for the birth date and hire date of all workers. Both
columns are defined with the DATE data type. You want to insert a row with the details of employee
Wallace, who was born in 1952 and hired in 2001. Which of the following statements will insert the
values into the table in the correct century?
a.
b.

c.

d.

INSERT INTO WORK(workno,wname,birthdate,hiredate)

VALUES(WORKNO_SEQ.NEXTVAL, Wallace, 10-nov-52, 13-jan-01)
INSERT INTO WORK(workno,wname,birthdate,hiredate)
VALUES(WORKNO_SEQ.NEXTVAL, Wallace,
TO_DATE(10-nov-52, DD-MON-YY),
TO_DATE(13-jan-01, DD-MON-YY));
INSERT INTO WORK(workno,wname,birthdate,hiredate)
VALUES(WORKNO_SEQ.NEXTVAL, Wallace,
TO_DATE(10-nov-52, DD-MON-RR),
TO_DATE(13-jan-01, DD-MON-RR));
d. INSERT INTO WORK(workno,wname,birthdate,hiredate)

VALUES(WORKNO_SEQ.NEXTVAL, Wallace,
TO_DATE(10-nov-52, DD-MON-YYYY),
TO_DATE(13-jan-01, DD-MON-RR));
121.You must retrieve worker details from the work table and process them in a PL/SQL block. Which
variable type must be created in the PL/SQL block to retrieve all rows and columns using a single
SELECT statement from the work table?
a.
b.
c.
d.

PL/SQL record.
PL/SQL table of records.
%ROWTYPE variable.
PL/SQL table of scalars.

122.Examine the following table:

ID NO
7
6
4
3
2
5
1
8

LAST_NAME
Brown
Warner
West
Chalmers
Landers
Brunswick
Lauder
Ott

FIRST_NAME
Jerry
James
Dawn
Mack
Jillian
Kate
Susan
Trixie

SALARY
30000
25000
50000
32000
55000

DEPT_NO
255
233
102
145
233
145

You query the database using the following command:

SELECT dept_no,last_name,SUM(salary)
FROM worker
WHERE salary<50000
GROUP BY dept_no
ORDER BY last_name;
Which clause causes an error?
a.
b.
c.
d.

FROM employee
WHERE salary<50000
GROUP BY dept_no
ORDER by last_name

123.Which of the following will display the average salary of divisions 3 and 6, but only if the
departments have an average salary of at least 3100?
a.

b.

c.

SELECT divisionno, AVG(sal)

FROM work
WHERE divisionno IN(3,6)
GROUP BY divisionno
HAVING AVG (sal)>=3100;
SELECT divisionno, AVG(sal)
FROM work
WHERE divisionno IN (3,6)
AND AVG (sal)>=3100
GROUP BY divisionno;
SELECT divisionno, AVG(sal)
FROM work
WHERE divisionno IN (3,6)
GROUP BY AVG(sal)
HAVING AVG(sal)>=3100

d.

SELECT divisionno, AVG(sal)

FROM work
GROUP BY divisionno
HAVNG AVG (sal)>=2000;
Divisionno IN (3,6);

124. Examine the automobile table:

AUTOMOBILE
Column name
Key type
Nulls/Unique
FK table
FK column
Data type
Length

ID
PK
NN, UU

MODEL

STYLE

Color

NN

NN

NN

NUM
9

CHAR
20

CHAR
20

CHAR
20

LOT_NO
FK
NN
LOT
LOT_NO
NUM
3

You query the database with the following command:

SELECT lot_number lot number,count(*) number of cars available
FROM automobile
WHERE model= e300
GROUP BY lot_no
HAVING COUNT (*)>10
ORDER BY COUNT (*);
Which clause restricts which groups are displayed?
a.
b.
c.
d.

SELECT lot_number lot number,count(*) number of cars available

WHERE model= e300
GROUP BY lot_no
HAVING COUNT (*)>10

125.You need to create a report to display the ship date and order totals of your inventory table. If an
order has not been shipped, the report must indicate not shipped. If a total is not available, the
report must indicate not available. In the inventory table, the ship date column has a data type of
date and the total column has a data type of number. Which of the following statements should be
used to create the report?
a.

Select inventory, ship date Not shipped,

Total Not available
FROM order.
b. Select inventory, To-CHAP (ship date, Not ship)
To-CHAR (total, Not available)
FROM order;
c. Select inventory, NVL (Ship date Not),
NVL (total, Not available)
FROM order;
d. Select inventory, NVL(TO=CHAR(shipdate) Not
NVL (To char(total), not available)Shipped)
FROM order.
126.You want to display data about all workers with the last name Randall, but are not sure what case last
names are stored in. What statement will be successful?
a.

Select last name, first name.

FROM work
WHERE last name=randall;

b.

Select last name, first name.

FROM work
WHERE last name=UPPER (randall);
c. Select last name, first name.
FROM work
WHERE UPPER(last name)=(randall);
d. Select last name, first name.
FROM work
WHERE LOWER(lastname)=(smith);
127.Your manager requests that you analyze the time taken between when orders are taken and when they
are shipped. You must create a report that displays the customer number, date of 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 meets these required results?
a.

b.
c.
d.

SELECT custid, orderdate, shipdate,

ROUND(MONTHS-BETWEEN(shipdate,orderdate))
Time Taken
FROM ord;
SELECT custid, orderdate, shipdate
ROUND(DAYS-BETWEEN(shipdate,orderdate))/30.
FROM ord;
SELECT custid, orderdate, shipdate,
MONTHS-BETWEEN (shipdate,orderdate) Time Taken.
FROM ord;
SELECT custid, orderdate, shipdate
ROUND OFF(shipdate-orderate) Time Taken
FROM ord;

128.The worker table has the following columns:

FIRST NAME
COMMISSION

VARCHAR2(20)
NUMBER(3,2)

Evaluate the following statement:

SELECT
FROM
WHERE

first-name,commission
worker
commission
(SELECT commission
FROM employee
WHERE UPPER(first-name)=Charles)

Which of the following will cause this statement to fail?

a.
b.
c.
d.

The first name values in the database are in lower case.

There is no employee with the first name Charles.
Charles has zero commission.
Charles has null commission.

129.You create the invoice table with the following command:

CREATE TABLE invoice
(purchase_no
NUMBER(8)
CONSTRAINT invoice-purchase-no-pk
Customer_id
NUMBER(8)
CONSTRAINT invoice-customer-id-nk

PRIMARY KEY,
NOT NULL);

Which index or indexes are created for the invoice table?

a.
b.
c.
d.

No indexes are created.

An index is created for each column.
An index is created for the purchase_no column.
An index is created for the customer_no column.

130.How would a foreign key constraint be added on the division_no column in the worker table, referring
to the ID column in the division table?
a.
b.
c.
d.

Use the ALTER TABLE command with the ADD clause in the DETP table.
Use the ALTER TABLE command with the ADD clause on the EMP table.
Use the ALTER TABLE command with the MODIFY clause on the DEPT table.
This cannot be done.

131.Examine the structure of the pupil table:

Name
PUP ID
NAME
ADDRESS
GRADUATION

Null
NOT NULL

Type
NUMBER(4)
VARCHAR2(20)
VARCHAR2(40)
DATE

The table is currently empty. Which statement prevents NULL values from being entered into the Name
column?
a.

ALTER TABLE pupil

ADD CONSTRAINT name(NOT NULL);
b. ALTER TABLE pupil
MODIFY CONSTRAINT name(NOT NULL)
c. ALTER TABLE pupil
ADD CONSTRAIONT NOT NULL (name);
d. ALTER TABLE pupil
MODIFY(name varchar2(20) NOT NULL);
132.Examine the table instance chart for the invoice table:
INVOICE
Column name
Key type
Nulls/Unique
FK table
FK column
Data type
Length

PURCHASE_NO
PK
NN, U
NUM
8

CUSTOMER_ID
FK
NN
CUSTOMER
ID
NUM
8

You issue the following command:

INSERT INTO invoice(purchase_no, customer_id, cars_id)
VALUES(1234,345,6);
If this statement fails, which condition explains the failure?
a.
b.
c.
d.

Too many foreign keys in the table.

Invalid data types in the statement.
Missing mandatory column value.
This statement will not fail.

CAR_ID
FK
NN
CAR
ID
NUM
8

SALES_ID
FK
NN
EMPLOYEE
ID
NUM
8

133.Examine the patient table:

PATIENT
Column name
Key type
Nulls/Unique
FK table
FK column
Data type
Length

Id_number
PK
NN, UU
ID_NUMBER
NUM
10

last_name

first_name

NN

NN

VARCHAR2
25

VARCHAR2
25

birth_date

physician_id
PHYSICIAN

DATE

NUM
10

Which of the following DELETE statements will del ete a patient from the table by prompting the user for
the id_number of the patient to be deleted.
a.

b.

c.

d.

DELETE
FROM patient
WHERE id_number=&id_number
/
DEFINE: id_number
DELETE
FROM patient
WHERE id_number=&id_number
/
DELETE
DEFINE & id_number
FROM patient
WHERE id_number=&id_number
This cannot be done.

134.You must retrieve worker names and salaries from the work table. They must be displayed in
descending order. If two names match for a salary, then the two names will be displayed in
alphabetical order. Which statement should be used?
a.

SELECT wname,sal
FROM work
ORDER BY sal,wname;
b. SELECT wname,sal
FROM work
ORDER BY sal,DESC,wname;
c. SELECT wname,sal
FROM work
ORDER BY sal,wname;
d. This cannot be done.
135.For which three of these tasks would the WHERE clause be used?
a.
b.
c.
d.
e.
f.

To display unique data.

To designate table location.
To compare two values.
To restrict rows to be displayed.
To restrict the output of a group function.
To only display data greater than a specified value.