|
PL/SQL Fundamental Exam Questions
|
“All
men are caught in an inescapable network of mutuality.”
Martin
Luther King Jr.
|
Lesson 01
Q:
What is PL/SQL?
Q:
Where can you store a PL/SQL procedure?
Q:
What is the PL/SQL body section?
Q:
What is the PL/SQL declaration section?
Q:
What does the SET SERVEROUTPUT command?
Q:
What does the DBMS_OUTPUT.PUT_LINE procedure?
Q:
How do you define a variable or variables in the PL/SQL declaration
section?
Q:
How do you save a PL/SQL block in the client environment?
Q:
How do you use the %TYPE keyword?
Q:
How do you open a saved PL/SQL block?
Q:
How do you run a saved PL/SQL block?
Q:
What does the %ROWTYPE keyword in the PL/SQL language?
Q:
What is an implicit cursor in the PL/SQL language?
Q:
An implicit cursor must have _________ on its SELECT SQL statement?
Q:
What does the SQL%NOTFOUND reserved PL/SQL word?
Q:
What does the “SET SERVEROUTPUT ON?”
Q:
Write a PL/SQL block
, to output the "Hello iSelfSchooling"
message.
Q:
Use the %TYPE keyword, to declare a variable as the same datatype and
size of the department name column of the dept table.
Q:
Use the implicit cursor to query the department table information where
deptno is 30. Check, if no record
was found then print “Record was not found.” Else print the department name
only.
|
“In
the end, we will remember not the words of our enemies, but the silence of
our friends.”
Martin
Luther King Jr.
|
Lesson 02
Q: Describe that why do we need to use a solid naming
convention in our PL/SQL program.
Q: What is the explicit cursor in the PL/SQL language?
Q: What are the differences between the explicit and
implicit cursors?
Q: Where do you declare an explicit cursor in the PL/SQL
language?
Q: Where do you declare an implicit cursor in the PL/SQL
language?
Q: What is a simple loop in the PL/SQL language?
Q: How do you open an explicit cursor in the PL/SQL
language?
Q: What does the FETCH statement in the Oracle PL/SQL
language?
Q: How do you terminate from a simple loop in the PL/SQL
language?
Q: How do you OPEN or CLOSE a cursor in the PL/SQL
language?
Q:
Declare a cursor to list the department name (dname), total number of
employees (ttemp), total salary (ttsal), and average salary (avsal) for each
department from the department table and employee table order by the
department name.
Write
all department names with their total number of employees for each department
using the notepad editor.
For
example: ACCOUNTING has 3 employees.
(Note:
Don’t use the ttemp, ttsal, and avsal item at this time)
|
“It
may be true that the law cannot make a man love me, but it can stop him
from lynching me, and I think that's pretty important.”
Martin
Luther King Jr.
|
Lesson 03
Q:
What does the FOR LOOP statement in the PL/SQL language?
Q:
What are the differences between a SIMPLE LOOP and FOR LOOP?
Q:
What are the advantages of using the FOR LOOP statement?
Q:
What does the SHOW ERRORS statement in the PL/SQL language?
Q:
What is the IF-THEN-ELSE statement?
Q:
Modify the previous PL/SQL block
and use
the “FOR
LOOP
” statement vs the simple “LOOP” statement. Also, list only the department name that their total number
of employees is more than 4.
|
“Like
an unchecked cancer, hate corrodes the personality and eats away its vital
unity. Hate destroys a man's sense of values and his objectivity. It
causes him to describe the beautiful as ugly and the ugly as beautiful,
and to confuse the true with the false and the false with the true.”
Martin
Luther King Jr.
|
Lesson 04
Q:
Create a table
named "dept_stat".
The table should have four columns:
department name (dname), total number of employees (total_empno), total
salary of employees (total_sal), and average salary of employees (avg_sal).
And the department name should be a primary key. The following are its
columns, datatypes and index constraint:
dname
VARCHAR2(20) primary key
total_empno
NUMBER(3)
total_sal
NUMBER (8,2)
avg_sal
NUMBER (8,2)
Q:
Write a PL/SQL block
to
populate the department table statistics into the “dept_stat” table.
Statistics
Information:
The
Department Number,
The
total number of employees in each department,
The
total salary paid in each department, and
The
average salary paid in each department.
|
“Our
lives begin to end the day we become silent about things that matter.”
Martin
Luther King Jr.
|
Lesson 05
Q:
What is the cursor parameter in the PL/SQL language?
Q:
Where do you define a cursor parameter in the PL/SQL language?
Q:
Write a PL/SQL block
to
populate the department table statistics into the “dept_stat” table for a
specific department.
Statistics
Information:
The
Department Number,
The
total number of employees in each department,
The
total salary paid in each department, and
The
average salary paid in each department.
|
“Segregation
is the adultery of an illicit intercourse between injustice and
immorality.”
Martin
Luther King Jr.
|
Lesson 06
Q:
What is the EXCEPTION section in the PL/SQL language?
Q:
What do you use the EXCEPTION section for?
Q:
What would be happen if you don’t define your exception in the PL/SQL
procedure?
Q:
What is an Oracle Defined EXCEPTION?
Q:
What is a User Defined EXCEPTION?
Q:
What are the differences between a User Defined and an Oracle defined
exceptions?
Q:
Modify the previous PL/SQL block
--last assignment in the previous hands-on
practice--to add a user defined exception, to check the total number of
employees in a department. Check
if the total number of employees less than 10 then the procedure raises an
exception and print a message – “We need more good employees.”
|
“Ten
thousand fools proclaim themselves into obscurity, while one wise man
forgets himself into immortality.”
Martin
Luther King Jr.
|
Lesson 07
Q:
How do you write a PL/SQL language using NOTEPAD?
Q:
Create a table to keep your customer’s portfolio statistics and name it
CUST_STAT. You should populate into
this table a customer last name, his/her traded date,
and total stock market value for the traded date.
See
the following columns and datatypes:
customer_lname
VARCHAR2(20)
trade_date
DATE
portfolio_value
NUMBER(8,2)
Q:
Write a stored procedure to populate the
customer statistics table. Declare a cursor to query all the customer last
names, the traded date, and the total stock market value for the traded date.
Use a sub-query with a MAX (trade_date) function to guarantee the current stock
market value for the traded date. In
the PL/SQL body, use the “FOR
LOOP
” statement to read the cursor information one record at a time.
Then insert the summary statistics data into the customer statistics table.
Use “commit” to save the transaction.
In the exception section, add the “no data found” exception and use
the “dbms_output” package to display the error message. Add the “invalid
number” exception to detect any invalid input data into the insert command.
Add the “Others” exception to detect other problems. Always use the
“others” exception in case you miss some other exceptions.
Q: Then run your created procedure.
Q: Verify that your table was populated.
|
“We
must learn to live together as brothers or perish together as fools.”
Martin
Luther King Jr.
|
Lesson 08
Q:
What is the Procedure Builder Tool?
Q:
What is the listener in the Oracle database?
Q:
How do you start or stop your listener?
Q:
What is the Object Navigator in the Procedure Builder tool?
Q:
How to you open a database using the Procedure Builder tool?
Q:
What is a user’s schema?
Q:
What type of objects can you have under a schema?
Q:
How do you create a procedure using the Procedure Builder Tool?
Q:
What is a Program Unit?
Q:
Write a PL/SQL stored procedure to add a record
into the department table (dept). You use three input parameters to pass
the department's columns (Department number “DEPTNO,” department name “DNAME,”
and department location “LOC”); and use one output parameter to check the
status of the insert transaction. You should use the Procedure Builder.
Note
that you should use the "p_" prefix to name the parameters. You use
this parameter as an output parameter to check the status of your transaction.
Use comments in your programs. Use
double dashes for a single line comment. In addition, use “/*” ended with
“*/” for a multiple lines comment. In the “EXCEPITON” section, define
the exception. Use the “duplicate value on index” exception, the “invalid
number” exception, and the “OTHERS” exception. Use the others in case you
are missing other exceptions.
Q:
Write a stored procedure to test the ‘add_department’ procedure.
Declare a status variable and make sure to call the “add_department”
procedure. Enter an invalid department number to see the exception error
message. To display the status of
your transaction value, use the TEXT_IO
instead
of the DBMS_OUTPUT, when you run the procedure locally.
Q:
What is the client side environment?
Q:
What is the server side environment?
Q:
How do you save the above PL/SQL procedure in your local library?
|
“When
you are right you cannot be too radical; when you are wrong, you cannot be
too conservative.”
Martin
Luther King Jr.
|
Lesson 09
Q:
Write
a procedure to remove a department record. Make sure to define one input
parameter for the department number; and an output parameter as a status
parameter. You will use this parameter to test the status of the deleted
transaction.
In
the PL/SQL body, delete the department record where its department number
matches with the input department number parameter. Save the deleted transaction
and assign "OK" to the status output parameter for a successful
deleted transaction.
Q: Write a PL/SQL procedure to test the above-created
PL/SQL procedure.
Q: What does the TEXT_IO package?
Q: Name one procedure that is in the TEXT_IO package.
Q:
What are the differences between the TEXT_IO and DBMS_OUTPUT packages?
|
“The
hope of a secure and livable world lies with disciplined nonconformists
who are dedicated to justice, peace and brotherhood.”
Martin
Luther King Jr., "Strength to Love"
|
Lesson 10
Q:
What is the PL/SQL function?
Q:
What are the differences between the PL/SQL function and procedure?
Q: When
do you create the PL/SQL function?
Q:
write a PL/SQL Function to concatenate the
customer's last name and first name to be separated by a comma. For example:
Kazerooni, John. Name the function "Full_Name,” and declare a datatype
for the Function return value. Declare a first name and last name input
parameters. Their datatypes should match with the datatype of the firstname and
lastname in the customers table.
In the PL/SQL body,
return the customers’ concatenated name. Write the exception. In the exception
section, do nothing in the case of an error handling exception.
Q:
How do you execute the above-created PL/SQL function in the SQLPLUS tool?
Q:
What is the PL/SQL interpreter?
Q:
How do you execute a PL/SQL procedure in the PL/SQL interpreter?
|
“All
progress is precarious, and the solution of one problem brings us face to
face with another problem.”
Martin
Luther King Jr., 'Strength to Love,' 1963
|
Lesson 11
Q:
Write
a PL/SQL Function to return the department name (dname). You use one input
parameter to pass the department number (DEPTNO) and return its department name.
Q: In the “PL/SQL interpreter” section, use the
“select” statement and use the department number 10 to test the function.
Q: To test the exception, call the function again using the
department number that does not exist in the department table.
Q: Query the department name function against the employee
table sorted by the employee name.
|
“The
good neighbor looks beyond the external accidents and discerns those inner
qualities that make all men human and, therefore, brothers.”
Martin
Luther King Jr., 'Strength to Love,' 1963
|
Lesson 12
Q:
How do you debug a PL/SQL procedure?
Q:
How do you move a PL/SQL procedure to the PL/SQL interpreter’s source
area?
Q:
What is the BREAKPOINT indicator in the PL/SQL interpreter?
Q:
How do you create a BREAKPOINT in the PL/SQL interpreter?
Q:
How do you activate the Step Into, Step Out, and Reset icon in the PL/SQL
interpreter?
Q:
What does the Step Into icon in the PL/SQL interpreter?
Q:
What does the Step Out icon in the PL/SQL interpreter?
Q:
What does the Reset icon in the PL/SQL interpreter?
Q:
What does the STACK section contain?
Q:
How can you see the columns and variables values in the PL/SQL
program using the PL/SQL interpreter?
Q:
How do you store a PL/SQL procedure in the PL/SQL library?
Q:
Can you have multiple versions of a PL/SQL procedure in the PL/SQL
library?
Q:
How do you store a PL/SQL procedure in your database server?
Q:
How can you copy a PL/SQL procedure to your database server?
Q: What would be happen if you move or copy a locally
PL/SQL procedure with its local packages into the database server?
|
“I
believe that unarmed truth and unconditional love will have the final word
in reality. That is why right, temporarily defeated, is stronger than evil
triumphant.”
Martin
Luther King Jr., Accepting Nobel Peace Prize, Dec. 10, 1964
|
Lesson 13
Q:
What is an Object Privilege?
Q:
What are System Privileges?
Q:
How do you create a user in the Oracle database?
Q:
How do you assign a default and temporary tablespace to a user in the
Oracle database?
Q:
What are the System Privileges in the RESOURCE and CONNECT roles?
Q:
How do you grant an object privilege to a user?
Q:
How do you grant a system privilege to a user?
Q:
What is the Public Synonym in the Oracle database?
Q:
How do you create a PUBLIC SYNONYM?
Q:
Why do you need a PUBLIC SYNONYM?
Q:
What is the EXECUTE privilege? Is it a system privilege or an object
privilege?
Q:
Can you grant the EXECUTE privilege to a table?
Q:
What is the Private Synonym in the Oracle database?
Q:
What are the differences between a private synonym and public synonym?
Q:
How do you revoke a system privilege from an Oracle user?
Q:
How do you revoke an object privilege from an Oracle user?
Q:
Mr. A granted to Mr. B an object privilege with a ‘WITH GRANT OPTION’
and then Mr. B granted the same privilege to Mr. C. You decide to revoke the Mr.
B’s object privilege. What would be happen to Mr. C’s granted object
privilege?
Q:
Mr. A granted to Mr. B a system privilege with a ‘WITH ADMIN OPTION’
and then Mr. B granted the same privilege to Mr. C. You decide to revoke the Mr.
B’s system privilege. What would be happen to Mr. C’s granted system
privilege?
Q:
How do you know that a privilege is the system privilege or object
privilege?
Q: On the GRANT ALL statement, what “ALL” means if
your grant is on a PL/SQL procedure?
|
“Nonviolence
is the answer to the crucial political and moral questions of our time;
the need for mankind to overcome oppression and violence without resorting
to oppression and violence. Mankind must evolve for all human conflict a
method which rejects revenge, aggression, and retaliation. The foundation
of such a method is love.”
Martin
Luther King Jr., December 11, 1964
|
Lesson 14
Q:
What is an object dependency in the Oracle database?
Q:
What is a timestamp?
Q:
How do you query all the objects that was create by you (your schema)?
Q:
How do you change a datatype of a column in a table?
Q:
How do you compile a PL/SQL function?
|
“Injustice
anywhere is a threat to justice everywhere.”
Martin
Luther King Jr., Letter from Birmingham Jail, April 16, 1963
|
Lesson 15
Q:
What is the PL/SQL package?
Q:
What are the components of a PL/SQL package?
Q:
What is a package body in the PL/SQL language?
Q:
What is a package specification in the PL/SQL language?
Q:
Where do you save the package body and its package specification?
Q:
Can you store a PL/SQL package in a client environment?
Q:
How do you create a package specification and body?
Q:
What are the dependencies between a package body and its package
specification?
Q:
Write a PL/SQL package to have all your created PL/SQL functions and
procedures.
Q:
What is a public PL/SQL procedure or function in a PL/SQL package?
Q:
What is a private PL/SQL procedure or function in a PL/SQL package?
Q:
What are the differences between a public or private PL/SQL procedure?
Q:
How do you run a PL/SQL procedure or function in a PL/SQL package?
|
“Now,
I say to you today my friends, even though we face the difficulties of
today and tomorrow, I still have a dream. It is a dream deeply rooted in
the American dream. I have a dream that one day this nation will rise up
and live out the true meaning of its creed: - 'We hold these truths to be
self-evident, that all men are created equal.' “
Martin
Luther King Jr., Speech at Civil Rights March on Washington, August 28,
1963
|
Lesson 16
Q:
What is a database trigger?
Q:
How do you create a trigger?
Q:
If you drop a table that contains a trigger, does its trigger drop?
Q:
Create a trigger to audit department table
(dept) to keep track of all the insert, update, and delete transactions and
insert the audited transaction to a table.
Q:
How do you compile a trigger?
Q:
How do you disable or enable a trigger?
Q:
How do you test your created trigger?
Q:
How do you modify a trigger?
Q:
How do you drop a trigger?
Q:
When you drop a trigger, does its table drop?
|
“I
submit to you that if a man hasn't discovered something he will die for,
he isn't fit to live.”
Martin
Luther King Jr., Speech in Detroit, June 23, 1963
|
Lesson 17
Q:
How do you increase the size of SERVEROUTPUT buffer?
Q:
Can you perform a DDL statement in the PL/SQL block?
Q:
How can you compile an object in a PL/SQL block?
Q:
What does the DBMS_DDL package?
Q:
What does the ANALZE_OBJECT procedure in the DBMS_DDL package and how can
you verify that the object was ANALYZED?
Q:
What does the ALTER_COMPILE procedure in the DBMS_DDL package and how can
you verify that the object was compiled?
|
“...And
I've looked over, and I've seen the promised land. I may not get there
with you, but I want you to know tonight that we as a people will get to
the promised land. So I'm happy tonight. I'm not worried about anything.
I'm not fearing any man.”
Martin
Luther King Jr., Speech in Memphis, April 3, 1968, the day before King was
assassinated
|
Lesson 18
Q:
What is a Native Dynamic SQL statement?
Q:
Write a stored procedure to pass the table name and get back the number
of records that table contains. The SELECT statement must be created
dynamically, since you don’t know what table you are getting statistics from.
You should write your function so that your client can display the tables’
name, plus the number of records contained each table.
|
“Nothing
in all the world is more dangerous than sincere ignorance and
conscientious stupidity.”
Martin
Luther King Jr., Strength to Love, 1963
|
Lesson 19
Q:
How do you check that you have the JAVA tool installed in your server?
Q:
What should it be at least size for the JAVA pool memory usage?
Q:
How do you create a JAVA class?
Q:
How do you publish a JAVA class?
Q:
How do you test a JAVA function?
Q:
How do you drop a JAVA source and Function?
|
“Our
scientific power has outrun our spiritual power. We have guided missiles
and misguided men.”
Martin
Luther King Jr., Strength to Love, 1963
|
Lesson 20
Q:
How do you add a column to a table?
Q:
What does the EMPTY_BLOB() function?
Q:
How do you create a directory in the Oracle database?
Q:
Does everyone can create a directory in the Oracle database?
Q:
Write a stored procedure to read the employee number and its photo file
name and then store the employee’s picture into the EMP table.
Q:
How do you test that there is a picture in a column?
Q:
What does the DBMS_LOB package?
Q:
What does the GETLENGTH() function in the DBMS_LOB package?
Q:
How do you drop a directory from your Oracle database?
Q:
How and when do you grant the CREATE ANY DIRECTORY privilege to a user?
Q:
How do you revoke the CREATE ANY DIRECTORY privilege from a user?
|
“The
ultimate measure of a man is not where he stands in moments of comfort and
convenience, but where he stands at times of challenge and controversy.”
Martin
Luther King Jr., Strength to Love, 1963
|
In
General:
Lesson 21
Q:
Describe the command line technique.
Q:
How do you use the define_editor command?
Q:
How many categories of PL/SQL blocks are there?
Q:
What is an anonymous block?
Q:
What is a named block?
Q:
What does a PL/SQL block contain?
Q:
What is the EXCEPTION section in the PL/SQL block?
Q:
What are the differences between an implicit cursor and explicit cursor?
Q:
How do you obtain data from a cursor?
Q:
What is a LOOP in the PL/SQL block and how many types are there?
Q: What is advantage of the FOR LOOP statement verses of
the simple LOOP statement?
Q: How do you pass a parameter to a PL/SQL procedure or
function?
Q:
What does the procedure builder tool?
Q:
Describe the server-side and client-side machine.
Q:
What is the Breakpoint in the Oracle Procedure builder tool?
Q:
What is the “DBA_DEPENDENCIES” dictionary view?
Q:
What is a PL/SQL package?
Q:
What is a package specification?
Q:
What is a package body?
Q:
What is a public and private procedure in the PL/SQL package?
Q:
In what sequence do you compile a package body and specification?
Q:
What is a database trigger?
Q:
What are the differences between the statement and row triggers in the
Oracle database?
Q:
What do the UPDATING, DELETING, or INSERTING keywords?
Q:
How do you enable, disable, and drop a trigger in the Oracle database?
Answers
|
