iSelfSchooling - Manuscripts for SQL and PL/SQL

iSelfSchooling.com Since 1999 References | Search more | Oracle Syntax | Free Online Oracle Training

Home .Services Login Start Learning Certification . .Share your BELIEF(s)...

. Online Accounting     .Copyright & User Agreement   |

    .Vision      .Biography     .Acknowledgement
.Contact Us      .Comments/Suggestions       Email2aFriend    |

SQL-PL/SQL

Hands-On Oracle Installation

Hands-On 01-Introduction 1(Important notes to remember)

Learn about the important notes to remember when you do Oracle installation. The Oracle database and Developer are very easy to install. The only difficulty that you may face is: How to establish connectivity between the Oracle developer tools and the Oracle database on the same machine such as windows98.

Hands-On 02-Basic Introduction

Understanding of the basic definition of elements, fields, and items, columns, records, and rows, tables, datatypes, primary keys and foreign keys.

Hands-On 03-Installation and connectivity (TEXT)

Learn how to establish a connection from Developer to Oracle Database Personal Edition.

Hands-On 04-Installation and connectivity

The Oracle database and Developer are very easy to install. The only difficulty that you may face is: How to establish connectivity between the Oracle developer tools and the Oracle database on the same machine such as windows98. Learn how to make this connectivity.

Hands-On 05-De-installation-Remove Oracle (TEXT)

How to remove all Oracle products, Oracle services, and Oracle registry entries from your computer.

Hands-On 06-Create "iself" user

Learn how to run the script to create a user named "iself" with password "schooling" and all its tables.

Hands-On SQL

Hands-On 01(SQL)

Learn how to select data from Oracle

Hands-On 02(SQL)

Learn how to select data from Oracle using advanced data selection in Oracle

Hands-On 03(SQL)

Learn how to create, drop, and maintain Oracle Database objects.

Hands-On 04(SQL)

Learn more how to create, drop, and maintain other Database Objects in Oracle

Hands-On 05(PL/SQL)

Learn how to use command line editor which it is native to SQL*PLUS.

Hands-On 06(PL/SQL)

Learn how Oracle makes provisions for you to utilize your favorite text editor to edit the statement created in "afiedt.buf," the file into which SQL*PLUS stores the most recently executed SQL statement.

Hands-On PL/SQL I

Hands-On 01(PL/SQL)

Learn how to use command line editor which it is native to SQL*PLUS.

Hands-On 02(PL/SQL)

Hands-On 03(PL/SQL)

Learn the basic fundamentals of PL/SQL block.

Hands-On 04(PL/SQL)-Explicit Cursor Handling

Learn how to 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.

Hands-On 05(PL/SQL)-Controlling Process Flow

Learn how to modify the previous PL/SQL block to use the "FOR LOOP" statement vs simple "LOOP."

Hands-On 06(PL/SQL)-Populating table using PL/SQL

Learn how to create a table with a primary key constraint and then populate the table.

Hands-On 07(PL/SQL)-Cursor Parameter

Learn how to use the “test_for_loop2” file from the previous Hands-On. Then modify the PL/SQL block that the cursor only calculate the department statistics for a specific department. For example the department number 10.

Hands-On 08(PL/SQL)-How to use EXECPTION

Learn how to modify the “test_for_loop3” file from previous Hands-On. You need to add exception to the PL/SQL block.

Hands-On PL/SQL II (Program Units)

Hands-On 01 (PL/SQL)-Create PL/SQL Procedure

Learn how to create a table called "cust_stat." This table keeps the customers last name (customer_lname), the traded date (trade_date), and the total stock market value (portfolio_value) for that traded date.

Hands-On 02 (PL/SQL)-Creating PL/SQL Stored Procedure

Learn how to 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.

Hands-On 03 (PL/SQL)-Creating PL/SQL Stored Procedure

Learn how to write a PL/SQL stored procedure to remove a record from the department table (dept). You use one input parameter to pass the department number (deptno); and use one another output parameter to check the status of the delete transaction.

Hands-On 04 (PL/SQL)-Creating PL/SQL Function

Learn how to write a PL/SQL Function to concatenate the customer's last name and first name to be separated by a comma. For example (lastname, firstname).

Hands-On 05 (PL/SQL)-Creating PL/SQL Function using parameters

Learn how to 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.

Hands-On 06 (PL/SQL)-Debugging a PL/SQL Stored Procedure

Learn how to debug a procedure that is supposed to calculate the factorial of a number. You should pause and playback the movie as much as it is needed.

Hands-On 07 (PL/SQL)-Granting Object Privileges

Learn how to create, copy, alter, and drop a table, view, synonym, and user. How to grant, revoke object and system privileges.

Hands-On 08 (PL/SQL)-Managing Objects Dependency

Learn how to view some of the Oracle directory views and experience the Oracle object dependencies (timestamp).

Hands-On 09 (PL/SQL)-Creating Package

Learn how to create a package by copy and pasting an existing PL/SQL stored procedures. Then save it in the local library or the database server.

Hands-On 10 (PL/SQL)-Developing and using Database triggers

Learn how to create a table and name it "audit_dept" (audit department table). The table contains only one column (audit_line) and it should be big enough to fit 80 characters.