|
Click
here for
important notes
|
More Resources by
Google: |
|
|
|
|
Create
the ISELF schema
In
order to follow and practice the examples of this tutorial, you need to create a
user named “iself” along with all of its necessary tables.
There
is a script named "create_tables.sql" in your CD located in the "iscdxx\iss"
sub-directory.
This
movie shows you how you can run the script to create a user named "iself"
with the password "schooling" and all of its needed tables.
Introduction
01 ("Must to know" Oracle9i: SQL New Features)
In
the “Introduction 01” movie you will learn the Oracle9i: SQL New Features.
You
will learn the new Oracle ANSI SQL standard statement and compare it with the
original non-ANSI Oracle SQL.
Topics:
-
The
Oracle9i ANSI standard JOIN syntax vs the original join syntax.
-
The
ANSI standard NATURAL JOIN syntax
-
The
USING clause
-
The
ON clause
-
The
ANSI standard CROSS JOIN syntax
-
The
OUTER JOIN clause
Introduction
02 ("A Must to know" Oracle9i: SQL New Features)
In
the “Introduction 02” movie you will learn more about some Oracle9i: SQL New
Features such as the “MERGE” and “WITH
summary_name AS” statements.
Topics:
-
The
(WITH summary_name AS) statement
-
The
use of multiple columns in the WHERE clause
-
The
(inline view) statement
-
The
MERGE statement
Hands-On
01 (Materialized Views – The view that stores data)
Your
organization is growing, and their managers from different states are in need of
data warehousing. You should pre-compute database information into a summary
data aggregated to be easily understood.
They
are asking you to create a materialized view to pre-compute and store
information manually on the number of employees with their total salary paid for
each department (to project the employees’ payment budget). They want, when
new employees and their salaries are entered into the base tables, to be able to
manually update their materialized view with its summary, aggregated data, to
reflect these changes from the base tables.
They
should be able to download a subset of data from the company’s headquarter
table to another company associated servers, assuming that their system is not
in a distributed environment.
In
a distributed environment, they could use materialized views to replicate data
at distributed sites and synchronize updates between these sites.
Now,
the company wants the ability to create an aggregated snapshot for the
accounting department only. The required data include the total number of
employees, and the total paid salaries for the accounting department only. Once
the aggregated snapshot was created, it should define, in such a way, that it
can update the materialized view once a month without any human intervention.
The snapshot must be refreshed, based on the rule that your client asked.
Your
assignments are:
1-
To create a materialized view log,
2-
To create a materialized view,
3-
To test your materialized view,
4-
To execute or update you materialized view manually,
5-
To create materialized view and updated monthly,
6-
To test it, and
7-
To check and test your monthly job.
Topics:
-
Creating
a materialized view log
-
Listing
the snapshot logs' view
-
MLOG$_EMP
-
Creating
a materialized view
-
Granting
materialized view privilege
-
Listing
the user's materialized views
-
Executing
the REFRESH procedure
-
Using
DBMS_SNAPSHOT package
-
Executing
the REMOVE procedure
-
Listing
the materialized view in the job’s queue
-
Deleting
the materialized view’s job
-
Dropping
the created Materialized view
-
Revoking
the materialized view privilege
Hands-On
02(The break on clause)
Your
organization now wants to get a simple report from the following report layout.
Unfortunately, they don’t have the “REPORTS builder” tool and you have to
use sql*plus in order to fulfill their user requirements.
The
client asked you to query all employee names and their departments where their
salaries are greater than $3,150.00, sorted by department.
The
report layout printout:
----------------Salary-----------------
Department Employee
Salary
--------------------
-------------------- -----------
ACCOUNTING KING
$5,250.00
********************
-----------
sum
$5,250.00
SALES
ALLEN
$3,758.57
BLAKE
$3,464.20
********************
-----------
sum
$7,222.77
------------
confidential -------------
Your
assignments are:
1.
To use TTITLE, COLUMN, COMPUTE and BREAK commands,
2.
To query department name, employee names and their salaries, and
3.
To have subtotal for each department.
Topics:
Hands-On
03 (Troubleshooting – UNION, INTERSET, and MINUS statements)
Your
organization, has another table that contains employee data for the accounting
department only. You have been assigned to check the contents of the accounting
table with the base EMP table.
You
need to:
1-
List all of the records from the EMP table in the accounting department, that
are not in the accounting table.
2-
List all of the records that are common in both tables.
3-
Merge the two tables so that you can query all of the records with no duplicated
records.
Topics:
Hands-On
04 (Troubleshooting Deleting duplicated records)
Your
organization has a lot of duplicated records in their Account Employee table.
This is a daily problem in any organization and you should be prepared to fix
the problem. You have be assigned to clean all of the duplicated records.
Topics:
-
Creating
a table with lots of duplicated records
-
Writing
a simple delete (DML) statement to remove duplicated records.
-
Verifying
the removal of the duplicated records
-
Dropping
the duplicated table
Hands-On
05 (Data Partitioning)
Your
organization’s database is growing very fast. They want to have a database
that contains each department in a separate tablespace. They projected that
their employee’s table is growing to more than 100 million rows in each
department. Also, upon their requirements, they want to have each department’s
employees in a separate hard disk, and to be independent of other department
employee’s data. Also, they want to be sure all the employees are in the same
table. They want it to be partitioned in such away, that when they run a query
against an employee’s information, they would scan at most on the
department’s hard disk that he/she works, rather than the other department’s
hard disks on non-partitioned table.
You
will need to choose a partition key. You have been told that the department’s
employees are distributed equally among the partition table. There are two types
of partitioning approaches that you, as a database designer, must choose (they
are Local and Global). A Local index, is one that is partitioned exactly like
the table to which it belongs. A Global index, unlike local indexes, you should
explicitly partition range boundaries using the “VALUE LESS THAN” methods.
They want you to create a “Local Index” for the partition table.
Your
assignments are:
1-
To create folder for each department,
2-
To create tablespace with following specification for each department:
a.
Automatic Allocation for an unlimited size,
b.
Generates redo logs and recoverable, and
c.
Type must be permanent.
3-
To create the partition table,
4-
To copy the EMP table into the partition table,
5-
To check the table statistics,
6-
To create a local partition indexes, and
7-
To check the index table statistics.
Topics:
-
Creating
the OS level folders
-
Creating
a tablespace
-
Granting
the CREATE TABLESPACE
-
Granting
the DROP TABLESPACE
-
Creating
a range-based partitioning table
-
Inserting
records into the partition table
-
Analyzing
the partition table
-
Listing
the statistics on the partition data table
-
Compare
a regular table with a partition table
-
Listing
the partition table
-
Creating
a local partition index.
-
Analyzing
the partition table index.
-
Listing
the statistics on the index partition table
-
Dropping
the partition table and all of the tablespaces
-
Revoking
the CREATE TABLESPACE privilege
-
Removing
the folders
Hands-On
06 (Data Warehousing – The ROLLUP and CUBE function)
Data
warehousing is a focus in your organization. Management needs more reports in
order to truly understand their data. You have been assigned to write simple SQL
statement using the “ROLLUP” operator to create salary subtotals for each
type of job per year. Also, you should use the CUBE operator to enable a simple
statement to calculate all possible combinations of the types of jobs and year
for the research department only.
Notice:
A select statement using the ROLLUP operator returns both ‘regular rows’ and
‘super-aggregate rows.’ Super-aggregate rows are rows that contain a
sub-total value.
A
select statement using the CUBE operator returns cross-tabulation values, thus
produces totals in all possible dimensions, and is used for warehousing
aggregated data reports.
The
report layout printout:
The
ROLLUP layout output:
Year
JOB
SUM(SAL) COUNT(*)
----
--------- ---------- ----------
80
CLERK 1050
1
80
1050
1
81
ANALYST 3150
1
81
CLERK 997.5
1
81
MANAGER 9160.45
3
81
PRESIDENT 5250
1
81
SALESMAN 11609.82
4
81
30167.77
10
82
CLERK 1365
1
82
1365
1
87
ANALYST 2000
1
87
CLERK 1337.06
1
87
3337.06
2
35919.83
14
The CUBE layout output:
Year
JOB
SUM(SAL) COUNT(*)
----
--------- ---------- ----------
80
CLERK 1050
1
80
1050
1
81
ANALYST 3150
1
81
MANAGER 3123.75
1
81
6273.75
2
87
ANALYST 2000
1
87
CLERK 1337.06
1
87
3337.06
2
ANALYST 5150
2
CLERK 2387.06
2
MANAGER 3123.75
1
10660.81
5
Topics:
Hands-On
07 (Using Native Dynamic SQL)
Your
organization wants you to 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 a dynamically, since you don’t know what table you are getting
statistics from. You should write your function so that your client can display
all of the tables’ name, plus the number of records contained each table.
Your
assignments are:
1-
To create a function called “get_total_recs,”
2-
To pass a table name as a parameter,
3-
To test and query a list of all user tables with their number of rows in the
table.
Topics:
Hands-On
08 (JAVA Stream)
Your
organization is now asking you to write a simple java program to identify a
special problem that can be classified by iselfschooling’s problems only.
You
have been assigned to:
1-
Check for a successful installation of “initjvm.sql,”
2-
Check JAVA pool memory usage,
3-
Create the JAVA class, and
4-
Publish the JAVA program to SQL.
Topics:
-
Checking
the JAVA tool installation
-
Checking
the JAVA pool memory usage
-
Creating
a java class
-
Publishing
the JAVA class
-
Running
and testing the JAVA program
-
Dropping
the JAVA SOURCE
-
Dropping
the FUNCTION
Hands-On
09 (Defining Objects and Collection Types)
Your
organization is going to gather information about all the employees’ families
in order to keep track of the number of the employee kids, kids’ names and
their date of birth. You have been assigned to use the nested object tables for
the employees’ family. You should use the “CONSTRUCTOR” operator to refer
to the nested table with the “THE” syntax. Your client should be able to
insert a record into the nested table directly or update the nested table, and
be able to get individual elements from a nested object table using the object
columns.
Your
assignments are:
1-
To use nested object tables,
2-
To create a record object type,
3-
To create a table object type,
4-
To create a table for
employee’s table (EMP_FAMILY),
5-
To insert record into table,
6-
To query information from table,
7-
To update information from table,
and
8-
To delete information from table.
Topics:
-
Creating
an object type
-
Creating
a table type using object type
-
Listing
the object types
-
Creating
a nested table using a table type
-
INSERT
INTO tablename VALUES (col, table_object_type(record_object_type()))
-
Listing
the nested table
-
INSERT
INTO THE (SELECT col… FROM tablename)
-
SELECT
nested_table_id, col1, col2 FROM THE(SELECT col1 FROM tablename)
-
UPDATE
THE(SELECT col1 FROM tablename)
-
Adding
a unique index to the nested table.
-
Dropping
the nested table and types
Hands-On
10 (Inserting employees’ picture into the EMP table using BLOB / CLOB)
Your
organization wants to add their employees’ pictures into the EMP table using a
stored procedure. You have now been assigned to complete this task. They want
you to add one more column to the EMP table with a datatype of BLOB (Binary
Large Object). You should write a procedure so they can enter the employee
number and their picture’s file name, and run the procedure to add their
picture.
Your
assignments are:
1-
To Modify the EMP table,
2-
To create a physical and logical directory,
3-
To create a procedure to load the picture (BLOB),
4-
To populate the EMP table.
Topics:
-
ALTER
TABLE
-
Assigning
an empty blob to a column.
-
Creating
a directory object
-
Granting
the CREATE ANY DIRECTORY privilege
-
Granting
the DROP ANY DIRECTORY privilege
-
Writing
a stored procedure
-
Finding
where the picture's pointer is located using BFILENAME function
-
Open
the picture as read-only option using DBMS_LOB.FILEOPEN procedure
-
Loading
the picture into a column using DBMS_LOB.LOADFROMFILE procedure
-
Closing
the picture’s pointer using DBMS_LOB.FILECLOSE procedure
-
Executing
and test a stored procedure
-
Dropping
the directory object
-
Revoking
the CREATE ANY DIRECTORY privilege
-
Revoking
the DROP ANY DIRECTORY privilege
Hands-On
11 (Viewing a picture using BLOB datatype)
Since
you had inserted the employee pictures into the EMP table successfully, now the
organization wants to view the pictures that were inserted.
You
have been assigned to develop an application form to display all of the employee
information plus their picture.
Your
assignments are:
-
To
create an application form using Developer V6i or FORMS v6i
-
To
run and test the application
-
To
save the application
Topics:
-
Creating
the "Data Blocks" item
-
Using
the Data Block wizard
-
Using
the Layout wizard
-
Using
and modifying the "Content" canvas
-
Running
the application
-
Using
the "Execute Query" icon
-
Navigating
through the application
-
Saving
the application
Hands-On
12 (What is a REF CURSOR)
This
was added due to a customer’s request.
You
have been assigned to complete the process for basing a block on a stored
procedure for “single block operations.”
What
is a REF Cursor?
REF
cursors hold cursors in the same way that VARCHAR2 variables hold strings. This
is an added feature that comes with PL/SQL v2.2. A REF cursor allows a cursor to be opened on the server and
passed to the client as a unit, rather than one row at a time.
One can use a Ref cursor as a target of assignments and can be passed as
parameters to the Program Units. Ref
cursors are opened with an OPEN FOR statement and in all other ways, they are
the same as regular cursors.
What
is a table of records?
A
table of records is a new feature added in PL/SQL v2.3.
It is the equivalent of a database table in memory.
If you structure the PL/SQL table of records with a primary key (an
index) you can have array-like access to the rows.
Why
base a block on a PL/SQL Table versus a Ref Cursor?
A
table of records fetches all the rows from the table. A reference cursor fetches
only those rows that match your query criteria. If you are planning to filter
the rows with a where clause or your query returns only few records out of many,
you can choose the ref cursor rather than the table of records.
Note the block properties for the number of records set and the buffered
affected blocks, based on stored procedures.
Your
assignments are:
-
Create
a table
-
Create
a package spec at the database level
-
Create
the package body
-
Create
the Form Block
-
Create
following procedure
·
query,
·
insert,
·
query,
·
lock,
·
update,
·
delete, and
·
count procedures.
These
are required steps to complete the process for basing a block on a stored
procedure for single block operations.
Hands-On
13 (FORM BLOCK and TRANSACTIONAL TRIGGERS)
Your
organization wants to use the “managers_pkg” package that you created to
insert, delete, update, lock, and count a record.
You
have been assigned to develop an application form to manipulate the managers
table using transactional triggers.
Your
assignments are:
-
To
create an application form using Developer V6i or FORMS v6i
-
To
run and test the application
-
To
insert records
-
To
update records
-
To
delete records
-
To
save the application
Topics:
-
Creating
the "Data Blocks" item
-
Using
the Data Block wizard
-
Using
the Data Block property palette
-
Creating
transactional triggers
-
ON-INSERT
-
ON-DELETE
-
ON-UPDATE
-
ON-COUNT
-
ON-LOCK
-
Using
the Layout wizard
-
Using
and modifying the "Content" canvas
-
Running
the application
-
Using
the "Execute Query" icon
-
Navigating
through the application
-
Saving
the application
|