CSCI 570
Oracle SQL and
PL/SQL Programming
Very,
Very Old Sample Final Exam
NOTE - The topics and exam questions have evolved since this test was given years ago. There are many new topics covered in the course, but not on this test. It is by no means comprehensive.
Directions: Closed book, notes and computer. Multiple choice - choose the best possible
answer. Pick only one answer unless
specifically directed to do otherwise.
Use the answering sheet provided to mark your answers. Use the inventory table at the back of this
test and the description of the inventory table constraints on problems for
which it is relevant.
1)You issue the following statement.
What will be displayed if the EMPID selected is 55643 (choose one best
answer) ?
Select
DECODE( empid, 38475,
Terminated, 60494, LOA, ACTIVE) from EMP;
A) 60494
B) LOA
C) Terminated
D) ACTIVE
E) B and D
Answer
- D
2)The nvl( ) function (Choose one)
A) Assists in the distribution
of output across multiple columns.
B) Allows you to specify
alternate output for non-NULL column values.
C) Allows you to specify
alternate output for NULL column values.
D) Nullifies the value of the
column output.
E) None are correct.
Answer
C
3)Which of the following keywords are used in order by clauses? (Choose
two)
A) ABS
B) ASC
C) DESC
D) ASCEND
E) SORT
Answer
Band C
4)Which of the following are not group functions(choose two)?
A) Avg( )
B) To_char( )
C) Sum( )
D) Init_cap( )
E) Count()
Answer
B and D
5)A
user is setting up a join operation between tables EMP and PROJECT. There are some employees in the EMP table
that the user wants returned by the query, but the employees are not assigned
to projects yet. Which select statement
is most appropriate for this user (choose one)?
A) Select e.empid,
p.pno from emp e, project
p;
B) Select e.empid,
p.pno from emp e, project p
where e.pno = p.pno;
C) Select e.empid,
p.pno from emp e, project p
where e.pno = p.pno (+);
D) Select e.empid,
p.pno from emp e, project p
where e.pno (+) = p.pno;
Answer
D
6)Which one of the following uses of the having clause are appropriate
(choose one)?
A) To put returned data into
sorted order.
B) create a new index on a set of
data.
C) To include certain data
groups based on unknown criteria.
D) To include certain data
groups based on known criteria.
E) To declare an explicit
cursor
Answer
- D
7)Which of the following select statements finds the total number of employees
working on projects and counts employees only once, no matter how many times
they appear in the works_on_projects table (choose
one)?
A) Select max(emp_id) from works_on_projects;
B) Select count(emp_id), proj_no from works_on_projects
Group by proj_no;
C) Select count(distinct emp_id) from works_on_projects;
D) Select count(emp_id) from works_on_projects;
E) None of the above will give
the result desired.
Answer
C
8)What is the question being answered by the SQL statement below (choose
one)?
select last_name from instructor
where substr(last_name,
1, 1) = S;
A) Show the last name of any
instructor who teaches at least 1 course.
B) Show the last name of any
instructor who has 1 letter in their entire name and that letter is S.
C) Show the last name of any
instructor whose last name begins with an S.
D) Show the full name of any
instructor whose last name begins with an S.
E) The SQL statement has an
error, so does not answer anything.
Answer
- C
9)Which one of the following solutions adds 30 minutes to the current date and
time (choose one)?
A) Select sysdate+1/96 from
dual;
B) Select sysdate
+1/48 from dual;
C) Select sysdate
+1/128 from dual;
D) Select to_char(sysdate+1/128,
DD-MON-YYYY 24HH:MI) from dual;
E) None of the above
Answer
B
10)Find the error(s) in the following SQL statement:
1
select stud.last_name, stud.first_name,
stud.zip,
2 zip.zip, zip.state, zip.city
3
from student stud, zipcode zip
4
where stud.student_id = 102
5
and zip.zip=11419
6
and zip.zip = stud.zip;
A) Line 1, 2
B) Line 3
C) Line 4
D) Line 5, 6
E) There are no errors in this
statement.
Answer
- E
11)Determine the correct question for the following SQL statement
Select e.empid, p.pno
From emp e, project p
Where not exists
(select s.supno
from supplier s
where s.supno=p.supno and e.empid=p.empid);
A) Show the employees that work on projects for which suppliers supply parts.
B) Determine the employees and projects that have no suppliers supplying parts to that project.
C) Determine which employees are not working on projects.
D) Determine which employees are working with parts.
E) This is an invalid query
Answer - B
12)Which one the following integrity constraints automatically
creates an index when defined (choose two)?
A) Foreign keys
B) Check constraints
C) UNIQUE constraints
D) Primary keys
E) NOT NULL constraints
Answer
C and D
13)Which of the following are valid database types for
assigning to the attributes of a table in Oracle (choose all that apply)?
A) CHAR
B) NUMBER
C) VARCHAR2
D) RECORD
E) None of the above.
Answer
A, B, C
14)
Which of the following statements is true about roles? (Choose all that are
true.)
A) Roles can be granted to
other roles
B) Privileges can be granted to
roles
C) Roles can be granted to
users
D) Roles can be granted to
tables
E) Roles can be granted to
exceptions
Answer
A, B and C
15)Which of the following choices are valid parameters for
sequence creation? (Choose all that apply.)
A) Identified by
B) Cycle
C) On delete cascade
D) Minvalue
E)
Nocache
Answer
B, D, and E
16)User ELMER has granted the create any view with grant option privilege to user FUDD. User FUDD granted the same privilege with grant option to user WABBIT. User ELMER revokes the privilege from user FUDD. Which statement below is true about privileges granted to user ELMER, FUDD and WABBIT (choose one)?
A) ELMER and WABBIT have the
privilege, but user FUDD does not.
B) ELMER has the privilege, but
FUDD and WABBIT do not.
C) FUDD and WABBIT have the
privilege, but user ELMER does not.
D) FUDD and ELMER have the
privilege, but user WABBIT does not.
E) None of the above.
Answer
B
17)Mark all that are true for sequences (choose all that
apply) -
A) Sequences are useful for
generating unique values.
B) The default increment of a
sequence is 1.
C) When you drop a table, the
associated sequence is also dropped.
D) The default starting value
for a sequence is 0.
E) None of the above.
Answers
A, B
18)You are using some DML in SQL*Plus. You just updated all the salaries in the employee table and decremented them 10 percent when you should have raised them 10 percent. What should you do to correct the error (choose the best answer)?
A) Leave SQL*Plus and contact your DBA right away.
B) Issue a ROLLBACK command.
C) Issue a SAVEPOINT command.
D) Execute a DML operation to retrieve your data.
E) Ask your DBA to restore a previous version of the database.
Answer
B
19)You are a database administrator working with a database and you want to see all of the constraints that have been defined on the database tables. You should query which data dictionary view (choose one)?
A) DBA_CONSTRAINTS
B) ALL_CONSTRAINTS
C) V$CONSTRAINTS
D) MY_CONSTRAINTS
E) USER_CONSTRAINTS
Answer A
20) Which are true about views (choose all that apply)?
A) They are always updateable.
B) They can make complex queries easy
C) They can be used to restrict data access
D) They can present different views of the same data
E) You can never update a view.
Answer B, C and D
21)Which one of the statements below is not true about roles (choose one)?
A) They can be granted, but they cannot be granted with the grant option.
B) They are a collection of privileges.
C) Any user can view their privileges by looking at the view DBA_ROLE_PRIVS.
D) CREATE SESSION is a privilege.
E) CREATE DATABASE is a role.
Answer A, C, E
22)Mark all that are true regarding naming conventions for PL/SQL programs (choose all that are true)
A) It does not matter if you use the same names for you PL/SQL identifiers that you used for column names since the compiler always can determine your intent.
B) The PL/SQL compiler first assumes that an identifier is referring to a PL/SQL variable before checking that the same name is given to a column in a table.
C) There is a naming convention for PL/SQL program variables.
D) Database columns and PL/SQL identifiers should have distinct names.
E) Naming a PL/SQL identifier the same as a column in a table can lead to logical errors, but no syntax error will occur.
Answer C) and D)
23)Which of the following is true about implicit cursors (choose one) ?
A) They must be declared just like explicit cursors.
B) A memory area is set aside for them.
C) CURSOR%ROWCOUNT yields the number of rows affected by the most recent SQL statement.
D) SQL%ISFOUND evaluates to TRUE if the most recent SQL statement affects one or more rows.
E) You can only use cursor attributes in SQL queries, but not in DML.
Answer B)
24)Implicit cursors differ from explicit cursors in which of the following ways (choose all that apply) ?
A) Only explicit cursors have cursor attributes.
B) You can use the fetch command only with explicit cursors.
C) Explicit cursors are those declared by the programmer.
D) Explicit cursors have no SQL work area set aside for them.
E) You have more control as a programmer over implicit cursors than explicit cursors.
Answer B and C
25)Which of the following contains a PL/SQL syntax error (choose one) ?
IF v_ename = MILLER THEN
BEGIN
v_job :=SALESMAN
END IF;
A) IF v_ename = MILLER THEN
B) BEGIN
C) v_job :=SALESMAN
D) END IF;
E) There are no errors in this PL/SQL code.
Answer B
26)What is the value of v_flag after the following statement is executed, given that v_done_flag is NULL and v_redo_flag is TRUE?
v_flag := v_done_flag AND v_redo_flag;
A) FALSE
B) TRUE
C) the integer value of 0
D) NULL
E) None of the above
Answer D
27)Which of the following is true about the OR operator for Boolean expressions (choose all that are true)
A) TRUE OR FALSE evaluates to FALSE
B) TRUE OR FALSE evaluates to TRUE
C) TRUE OR NULL evaluates to NULL
D) FALSE OR NULL evaluates to NULL
E) TRUE OR NULL evaluates to TRUE
Answer B, D and E
28)Which of the following are true about loops in PL/SQL (choose all that apply)?
A) Basic loops must always have an EXIT condition.
B) The body of the loop in a basic loop is bracketed by LOOP and END LOOP.
C) In a FOR loop you can assign directly to the loop counter in the body of the loop to control exit of the loop.
D) The counter of a FOR loop is undefined outside the loop.
E) You can reverse the counter in a FOR loop to decrement from the upper_bound down to the lower_bound.
Answers A, B, D, E
29)Find the syntax error or errors in the following FOR loop (choose all that apply)
You may assume that all tables, columns and identifiers have been properly declared.
FOR I IN 1 to 10 DO LOOP
INSERT INTO item(ordid, itemid) values (v_ordid, I);
I := I+1;
END LOOP;
A) FOR I IN 1 to 10 DO LOOP
B) INSERT INTO item(ordid, itemid) values (v_ordid, I);
C) I := I+1;
D) END LOOP;
E) There are no errors in this FOR loop.
Answers A, C
30)Find the syntax as well as logical error or errors in the following WHILE loop (choose all that apply)
v_count NUMBER(2) :=1;
BEGIN
WHILE v_count <=&p_items LOOP
INSERT INTO item(ordid, itemid) VALUES(&p_new_order, v_count);
END LOOP;
A) WHILE v_count <=&p_items LOOP
B) INSERT INTO item(ordid, itemid) VALUES(&p_new_order, v_count);
C) END LOOP;
D) There are no errors of syntax or logic in the code shown in A through C above
E) The code is flawed in that the loop control variable (counter) is not incremented in the loop body.
Answer E
31)Find the error or errors that occur in the code below (choose all that apply)-
Assume that all necessary declarations have occurred.
IF v_number < 500 THEN v_code := 1;
ELSIF v_number <600 THEN v_code := 2;
ELSIF v_number >=600 THEN v_code :=3;
END IF;
A) IF v_number < 500 THEN v_code := 1;
B) ELSIF v_number <600 THEN v_code := 2;
C) ELSIF v_number >=600 THEN v_code :=3;
D) END IF;
E) There are no errors in the above code.
Answer E
32)Which of the following is true about PL/SQL records (choose all that apply) ?
A) The only way to declare a PL/SQL record is to first give a separate TYPE declaration.
B) Use the %DATATYPE attribute to have the fields in a record take their names and datatypes from the columns of a table or view.
C) Use the %ROWTYPE attribute to have the fields in a record take their names and datatypes from the columns of a table or view.
D) Fields in a record are accessed by index.
E) Fields in a record are accessed by name.
Answer C and E
33)Which of the following is true about PL/SQL tables (choose all that apply)?
A) PL/SQL tables are composed of two components only.
B) PL/SQL tables are composed of as many components per slot as the programmer designs and declares. The number of components per slot is not limited.
C) PL/SQL tables are limited to a fixed number of slots as set out in the table declaration.
D) A record or scalar can be a component of a PL/SQL table
E) A PL/SQL has a component that is a primary key of type LONG.