Download Introduction to Oracle - PL SQL Exam Practice Questions (1) PDF

TitleIntroduction to Oracle - PL SQL Exam Practice Questions (1)
TagsSoftware Engineering Sql Database Index Table (Database) Pl/Sql
File Size222.2 KB
Total Pages37
Document Text Contents
Page 2

What kind of join is this ?

A. Cartesian
B. Self
C. Outer
D. Equi

7) You have the statement "SELECT BirthDate, TO_CHAR(BirthDate, 'Month, ddth
"in, YyyY') Formatted". If the data is 11-NOV-46, what will Oracle return as the formatted date ?

A A. November ,11th in 1946
B. Nov 11, 1946

A C. November 11, 1946
A D. November , 01TH in 1946
A E. 11-11-46
A F. 11/11/46

8) There are four set operators, which one would show common results from queries A and B?


9) You need to return the specified setting of the current environment. Your procedure specifically needs
to receive the language and territory used by this session. What functions would you use ?

A. USERENV('TERMINAL') -- returns the OS identifier for your current session's terminal.
e.g hpmitd38
B. USERENV('INSTANCE') -- returns the instance identification number of the current
instance. e,g return 1
C. USERENV('SESSIONID') -- returns your auditing session identifier. e.g return 457
D. USERENV('ENTRYID') -- returns available auditing entry identifier. e.g return 0

F. USERENV('LANG') -- returns ISO language setting for this session. e.g return US

10) You are writing a procedure that needs to search a database for words that sound alike but are spelled
differently. What character functions

A. SOUND(char)
C. SOUNDEX (char) -- The SOUNDEX function is useful for finding strings for
which the sound is known but the precise spelling is not.
D. CHR(n) -- returns the character from ASCII code (n).
C. TRANSLATE(char,from to) -- translates char to another value.

11) You are the DBA of ABC Corp. You need to retrieve the employee names and salaries from the
employment tables, and to make the results assorted by salary, NOT in ascending order. Just in case, if
two names match for a salary are found, the two names should be shown in alphabetical order. How
do you do this?

A. ORDER BY sal NON-ASC, ename;
B. ORDER BY ename, sal; Ahmad Al-Faqeeh, 2012 Copyright ©

Page 36

162) You have a script you plan to run using SQL*Plus that contains several SQL statements that update
banking information for one person in several different tables based on name. Since the script only
changes information for one person, you want the ability to enter the name only once, and
have that information reused throughout the script. Which of the following options is the best way to
accomplish this goal in such a way that you don’t have to modify the script each time you want to run

A. Use define to capture name value for each run.
B. Use accept to capture name value for each run.
C. Using the & character to specify lexical substitution for names at runtime.
D. Hardcode names in all SQL statements, and change the value each run.

163) You are developing some code to handle transaction processing. Each of the following items signifies
the beginning of a new transaction, except one. Which is it?

A. savepoint
B. set transaction
C. Opening a new session
D. commit

164) You are coding a PL/SQL block. PROC_A calls PROC_B, which then calls PROC_C, and PROC_B
has no exception handler. If you wanted to prevent the PROC_A procedure from failing due to a \
situation in PROC_B where the divisor in a division statement was zero, how would you address this
in your code?

A. Use an if %zero_divide statement immediately following the math operation.
B. Code a when zero_divide exception handler in PROC_C.
C. Code a when others exception handler in PROC_A.
D. Code a when others exception handler in PROC_C.

165) If you wanted to define an exception that caused no Oracle errors but represented a violation of some
business rule in your application, in which of the following areas in your procedure code must you
include some support of this exception?

D. No coding or definition is required to

166) You are at the beginning of your current transaction and want to prevent your transaction from being
able to change data in the database. To prevent any statements in the current transaction from altering
database tables, which statement is used?

A. set transaction
B. rollback
C. commit
D. savepoint

167) Your application searches for data in the EMP table on the database on a nullable column indicating
whether a person is male or female. To improve performance, you decide to index it. The table
contains over 2,000,000 rows, and the column contains few NULL values. Which of the following
indexes would be most appropriate?

A. Nonunique B-tree index
B. Unique B-tree index Ahmad Al-Faqeeh, 2012 Copyright ©

Similer Documents