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

We are excited to let you know that we are launching shortly The New Technology Learning Series. We will be starting with Oracle 9i: SQL, PLSQL, and SQL *Plus. Below is the course schedule by week. Please let us know if you have suggestions.

Oracle 9i: SQL, PL SQL, 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.

Duration : 16 Weeks (16 Tutorials)

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.

• Data Modeling Overview Tutorial
• Getting Started with Oracle part-3
• Overview of the Relational Model Tutorial
• Practice Oracle SQL*Plus Live - Part 2

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

Tutorial 15: Oracle Packages
Developing Packages - Package Specification; Package Body; Overloading
Oracle Supplied Packages - Using Dbms_output

Tutorial 16: Oracle Utilities
Export Utility, Import Utility, SQL* Loader

Oracle 9i Tutorials

Index of Complete Oracle 9i Tutorials are available here