Click
here for important notes
|
More Resources by
Google: |
|
|
|
|
Case Study for
Hands-On PL/SQL
The Case Study for “Hands-On
PL/SQL” consists of more than 13 basic questions that we believe you must
know. In this Case Study, we look
at certain elementary questions, which will test your basic fundamental
understanding of PL/SQL statements. These
questions and their answers in the “Hands-On Training” enhance your skills
in using Oracle PL/SQL statements.
Hands-On 01(PL/SQL)
- Manuscript
Q:
How do you use the command line editor which it is native to SQL*PLUS?
-
How do you use the following command?
- "L" command
- "I" command
- “Del” command
- "C" command
- SAVE command
- GET command
Hands-On 02(PL/SQL)
- Manuscript
Q:
How do you use the notepad editor?
Hands-On 03(PL/SQL)
- Manuscript
Q:
What is the default directory of “SQLPLUS”?
Q:
What does the “set serveroutput on?”
Q:
Write a PL/SQL block, to output the "Hello iSelfSchooling"
message.
Q:
Write a PL/SQL block, to
declare a department name variable with the same datatype of the department
name.
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 = 30.
Check if no record was found then print “Record was not found” else
print the department name only.
Hands-On 04(PL/SQL)
- Manuscript
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 name 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)
Hands-On 05(PL/SQL)
- Manuscript
Q:
Modify the previous PL/SQL block to use the “FOR LOOP” statement vs
the simple “LOOP” statement. Also, list only the department name that their total number
of employees are more than 4.
Hands-On 06(PL/SQL)
- Manuscript
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.
Then
write a PL/SQL block to populate the department statistics table (dept_stat)
into the “dept_stat” table.
Hands-On 07(PL/SQL)
- Manuscript
Q:
Use the previous PL/SQL block and change the cursor that only calculate
the department statistics for a specific department.
For example the department number 10.
Hands-On 08(PL/SQL)
- Manuscript
Q:
Modify the previous PL/SQL block 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.”
