Oracle 9i: SQL, PLSQL, and SQL *Plus

Oracle 9i: SQL, PLSQL, and SQL *Plus

Course Description: This course introduces students to data server technology, and explores the concepts of relational databases and the powerful SQL and PL/SQL programming languages. Students will learn and practice how to create and maintain database objects. You will also learn to store, retrieve, and manipulate data. Finally, you will learn how to create PL/SQL blocks of application code that can be used in Oracle forms, reports, and other Oracle applications.

Prerequisite: Before starting with this course, you should have a minimum of 6 months experience. If you are not familiar with Oracle and DBMS , please click on the link below to understand the concepts before we start.

Professional Exams: This course will help the student to prepare for Oracle's exam: Introduction to Oracle SQL & PL/SQL (IZO - 001)

At Course Completion:
At the end of the course, you will be able to:
• Use SQL, PL/SQL. and SQL *Plus
• Use SQL *Plus Commands
• Restrict and sort data
• Use built-in SQL functions
• Work with multiple tables
• Create and modify tables and constraints
• Create and use other database objects
• Build a PL/SQL block
• Use PL/SQL control structures
• Process cursors in PL/SQL
• Handle runtime exceptions
• Work with collections of data
• Develop procedures and functions
• Develop Oracle packages
• Create Oracle trigger

Tutorial 1: Introduction to Oracle 9i:SQL, PL/SQL. and SQL *Plus
Introduction to SQL *Plus - Using SQL *Plus to communicate with Oracle Databases; Introduction to SQL - Types of SQL Commands, Writing Simple SELECT Statements; Introduction to PL/SQL - A Simple PL/SQL Block, Generating Output on Screen

Tutorial 2: SQL *Plus Commands
Using SQL *Plus for Editing - Edit; List and Change; Input, Append, and Del
Using SQL *Plus to Work with Files - Save; Get and Start; Spool
Using SQL *Plus for Formatting Output - Formatting Column Headings; Clear; Set Commands
Using SQL *Plus for Storing Temporary Values

Tutorial 3: Restricting and Sorting Data
WHERE Clause - Comparison Operators; Logical Operator; Using the Logical Operator, AND; Using the logical Operator, OR; Using the Logical Operator, NOT; Nesting Logical Operators; Other Comparison Keywords; Using the Keyword, LIKE; Using the Keywords, BETWEEN...AND; Using the Keyword, IN; Using the Keyword, IS NULL
ORDER BY Clause - Using the ORDER BY Clause

Tutorial 4: SQL Functions
Expressions in a SELECT Statement - Expressions in the WHERE Clause; Expressions in the ORDER BY Clause
Single Row Functions - Numeric Functions; Character Functions; Date Functions; Conversion Functions
Group Functions - Group Function Options - DISTINCT and ALL; Using GROUP BY and HAVING Clauses

Tutorial 5: Working with Multiple Tables
Joins - Inner Join; Using Table Aliases; Outer Join; Self Join; Non-Equi Join
Subqueries - Simple Subqueries; Multilevel Subqueries; Correlated Subqueries; Predicates
Set Operators - Set Operator Examples

Tutorial 6: Tables and Constraints
Creating and Modifying Tables - Datatypes; The CREATE TABLE Statement; The ALTER TABLE Statement; The RENAME, TRUNCATE, and DROP Statements
Integrity Constraints - Primary Key; Foreign Key; Unique Key; Not Null; Check Constraint; Modifying Constraints
Managing Data in Tables - Inserting Rows; Updating Rows; Deleting Rows
Transaction Control Language Commands - Commit; Rollback; SAVEPOINT

Tutorial 7: More Database Objects
Sequences, Synonyms, and Indexes - Sequences; Synonyms; Indexes
Views - Create Views; Data Dictionary View
Controlling Database Objects - GRANT Command; REVOKE Command; Roles and Privileges

Tutorial 8: Building PL/SQL Blocks
Creating PL/SQL Blocks - Declaration Section; Variables; Scope of Variables; Constants; Records
Manipulating Data in PL/SQL - Using DML in PL/SQL

Tutorial 9: PL/SQL Control Structures
Conditional Control Structures - IF-THEN-ELSE Statements; Using Multiple IF Statements
Iterative Control Structures - Simple Loop; WHILE Loop; FOR Loop

Tutorial 10: Cursors
Introduction to Cursors - Explicit or Implicit?
Implicit Cursors - Using Implicit Cursors
Explicit Cursors - Using Explicit Cursors; Explicit Cursor Attributes
Cursor Loops - Cursors and Loops

Tutorial 11: Exception Handling
Introduction to Exception Handling - Propagation of Errors
Types of Exceptions - Named System Exceptions; Unnamed System Exceptions; User-Defined Exceptions; RAISE_APPLICATION_ERROR()

Tutorial 12: PL/SQL Collections
Defining and Using Collections - Declaring, Initializing, and Referencing PL/SQL Collections
Collection Methods - Using the Collection Methods

Tutorial 13: Procedures and Functions
Developing Procedures and Functions - Creating a Procedure; Executing a Procedure; Creating a Function; Executing a Function
Passing Parameters - IN Parameters; OUT Parameters; IN OUT Parameters; Purity of a User-Defined Function; Positional and Named Notations

Tutorial 14: Oracle Triggers
Building a Trigger - Trigger Execution
Trigger Restrictions - Testing Mutating Tables

Oracle 9i Tutorials

Index of Complete Oracle 9i Tutorials are available here

riti4 said:

how to acces the oracle 9i course...do we have to pay money or simply by being a registered user will help.
ritika

November 09, 2005

sarika kumra said:

pls tell me how can i access this course and what are the procedures for appearing for the ORACLE certification tests..

let me knw as soon as possible

June 20, 2006

Henry Stinson said:

This web page just seems to be an announcement. The actual tutorial seems to start at:
http://www.exforsys.com/content/view/1168/267/

July 11, 2006

CHIRAG N. SHAH said:

oracle 9i dba training

July 24, 2006

sesh naryain mishra said:

oralcle is one of my faverite subject.i would ilke to work on oracle database in future

December 15, 2006

suraj_mutum said:

I am a B.com Graduate looking for a course in Oracle DBA course. So Being a B.co Student can I be an Oracle DBA . Could you pl tell me the above detail and jod prospect in the It Market and where to do the course.
I loked forward exchangind idea with you concerning a carer as an Oracle DBA professinal. looking for a Positive response from you soon.

Thanking You

With Regard

Mutum Suraj Singh

December 21, 2006

zainuddin52 said:

being done my graduation in commerce and at present working as accountant, a course in oracle dba course is worthwhile
i look forward from the sources for the actual requirement for the course

looking forward for a positive response from you soon

December 29, 2006

SUJATHA G said:

Pl tell me the topics covered by DBA utilities in Oracle 9i

January 02, 2007

shalmaliD said:

please any one will mail me the fees of the course oracle9i is it free or it has been charged so that i can proceed with my respective course. i have done oracle9i certification course.so from which topic i have to proceed.

January 09, 2007

Lalit Mohan said:

HI,
Its wonderful.
I will read out all the material provided here.

January 12, 2007

lokeshm said:

Oracle 9i SQL, PLSQL, and SQL *Plus - Introduction Tutorials as absolutely free
You can access Oracle 9i SQL, PLSQL, and SQL *Plus - Introduction tutorials at http://www.exforsys.com/conten...7/220/267/

January 16, 2007

Krishna Ambekar said:

Hello Sir,
My name is Krishna and working as a faculty. I have a problem regarding installation of Oracle9i on Windows XP. Would you please send me the installtion guide of Oracle 9i on Windows XP operating system.
Looking forward for your soon reply.

January 18, 2007

techguru said:

Hello Krishna ,

Installation should be pretty stright forward .. are ou getting any errors ?

Thanks,

April 11, 2007

PavaniJai said:

Hello Sir,
I am trying to install Oracle 9i on Windows XP-32 bit downloaded from the Oracle website. But I am getting many errors while trying to log in to the SQL*PLUS window. One error says TNS Listener not present and another says Protocol error.
Please help.

June 18, 2007

rasmakrishna said:

subject is great whenever u want to learn you can go to net open the site and start reading the information its really great

November 02, 2007

mj said:

hello sir,
i become appreciated orcle 9i sql commands, that are well explained in simple language, i pleased with sql queries
i expect many more in future with u..
thanking u.

December 23, 2007

R.Padma said:

hello sir, i am very much interested to learn oracle 9i. so yesterday i installed oracle, so when i given unername scott and password tiger, later asking hostring. so what i have to give hostring. can u please help me sir.

January 12, 2008