Oracle Unleashed

Module 1: Relational Model

Introduction

  • Database Systems Applications
  • Database Systems versus File Systems
  • View of Data
  • Data Models
  • Database Languages
  • Database Users and Administrators
  • Transaction management
  • Database System Structure
  • Application Architectures
  • History of Database Systems

Entity Relationship Model

  • Basic Concepts
  • Constraints
  • Keys
  • Design Issues
  • Entity-Relationship Diagram
  • Weak Entity Sets
  • Extended E-R Features
  • Design of an E-R Database Schema
  • Reduction of E-R Schema to tables

Relational Model

  • Structure of Relational Databases
  • The relational Algebra
  • Extended Relational-Algebra Operations.
  • Modification of the database
  • Views
  • The Tuple Relational Calculus
  • The Domain Relational Calculus

Relational Database Design

  • First Normal Form
  • Pitfalls in Relational Database Design
  • Functional Dependencies
  • Decomposition
  • Boyce-Codd Normal Form
  • Third Normal Form
  • Fourth Normal Form
  • More Normal Forms
  • Overall Database Design Process

Oracle 10g as a RDBMS

  • System Development Life cycle
  • Data storage in different media
  • Relational database concepts
  • Definition of Relational Database
  • Data models
  • Entity Relationship Model
  • Entity Relationship Modelling conventions
  • Relational Database Terminology
  • Relating multiple tables
  • Relational Database Properties
  • Communicating with RDBMS using SQL
  • Relational Database Management System
  • Oracle 10g – Object Relational Database Management System
  • Oracle 10g – Internet Platform Database for Internet Computing Features
  • Oracle Internet Platform
  • SQL statements
  • About PL/SQL
  • PL/SQL environment
Top
Module 2: SQL

Writing Basic Select Statement

  • Capabilities of SQL SELECT Statement
  • Basic SELECT statement
  • Writing SQL Statements
  • Selecting all columns
  • Selecting Specific Columns
  • Column heading defaults
  • Arithmetic Expressions
  • Using Arithmetic Operator
  • Operator Precedence
  • Using Parentheses
  • Defining a NULL Value
  • NULL Values in Arithmetic Expressions
  • Defining a Column Aliases
  • Concatenation Operator
  • Using Concatenation Operator
  • Literal Character Strings
  • Duplicate Rows
  • Eliminating Duplicate Rows
  • SQL and SQL * Plus Interaction
  • SQL Statements Versus SQL *Plus Commands
  • Overview of SQL*Plus
  • Logging In to SQL *Plus
  • Displaying Table Structure
  • SQL *Plus Editing commands
  • SQL *Plus File Commands

Restricting and Sorting Data

  • Limiting Rows Using a Selection
  • Limiting Rows Selected
  • Using the WHERE Clause
  • Character Strings and Dates
  • Comparison Operators
  • Using BETWEEN, IN, LIKE, IS NULL operator
  • Using Logical Operators
  • Rules of Precedence
  • ORDER BY Clause

Single-Row Functions

  • SQL Functions
  • Two Types of SQL Functions
  • Single-Row Functions
  • Character Functions
  • Case Conversion Function
  • Character Manipulation Function
  • Number Function
  • Working with Dates
  • Conversion Function
  • NVL function
  • DECODE function

Displaying Data from Multiple Tables

  • Obtaining Data from multiple tables
  • What is a join?
  • Cartesian Product
  • Generating a Cartesian product
  • Type of joins
  • Equijoin
  • Non-equijoin
  • Self Join
  • Outer join

Aggregating Data Using Group Functions

  • What are group functions?
  • Types of group functions
  • Using group functions
  • Creating Groups of Data
  • Group by clause on multiple columns
  • Illegal queries using group function
  • Excluding group results
  • Nesting group functions

Subqueries

  • Using a subquery to solve a problem
  • Using a subquery
  • Guidelines for using a subquery
  • Types of subqueries
  • Executing single row subquery
  • Using group function in a subquery
  • Having clause with subqueries
  • Multiple row subquery
  • Using ANY/ALL operator

Multiple Column Subqueries

  • Using multiple column subqueries
  • Column Comparison
  • Non-pairwise comparison subquery
  • Pair wise subquery
  • NULL values ina subquery
  • Using subquery in the FROM clause

DML Statement

  • Data Manipulation Language
  • Adding a New Row to a Table
  • The INSERT Statement
  • Inserting New Rows
  • Inserting Rows with NULL values
  • Inserting Special Values
  • Inserting Specific Date Values
  • Inserting Values by Using Substitution variables
  • Creating a Script with customized Prompt
  • Copying rows from another table
  • Copying data in a table


  • The UPDATE statement
  • Updating rows in a table
  • Updating with multiple-column subquery
  • Updating rows based on another table
  • Updating rows – integrity constraint error
  • Removing arrow from the table
  • The DELETE Statement
  • Deleting Rows from a table
  • Deleting rows based on another table
  • Deleting rows :Integrity constraint error

Database Transaction

  • Advantages of COMMIT & ROLLBACK statements
  • Controlling Transactions
  • Implicit Transaction Processing
  • State of Data before COMMIT or ROLLBACK.
  • State of Data after COMMIT or ROLLBACK.
  • Rolling Back Changes to a Marker
  • Statement level roll back
  • Read Consistency
  • Implementation of Read Consistency
  • Locking

Creating Tables

  • Database Objects
  • Naming Conventions
  • The CREATE TABLE Statement
  • Referencing Another User’s Tables
  • The DEFAULT option
  • Creating Table
  • Tables in ORACLE database
  • Querying the data dictionary
  • Datatypes
  • Creating a Table by Using a subquery
  • The ALTER TABLE statement
  • Adding a column
  • Modifying a column
  • Dropping a Tables
  • Changing the name of an Object
  • Truncating a table
  • Adding comments to a table

Including Constraints

  • What are constraints?
  • Constraint guidelines
  • Defining constraints
  • The NOT NULL Constant
  • The UNIQUE KEY Constraint
  • The PRIMARY KEY Constraint
  • The FOREIGN KEY Constraint
  • FOREIGN KEY Constraint Keywords
  • The CHECK Constraints
  • Adding a constraint
  • Dropping a Constraint
  • Enabling/disabling a constraint
  • Cascading constraint
  • Viewing the columns associated with constraints.

Creating Views

  • Database Objects
  • What is a view?
  • Why use views?
  • Simple views and complex views
  • Crating a view
  • Retrieving Data from a view
  • Querying a view
  • Modifying a view
  • Creating a complex view
  • Rules for performing DML operations on a view
  • Using the with CHECK option clause
  • Denying DML operations
  • Removing a view
  • Inline view
  • “TOP-N” analysis

Other Database Objects

  • Database Objects
  • What is sequence?
  • CREATE SEQUENCE statement
  • What is an index?
  • Creating an index
  • Function based index
  • Removing an index
  • Synonyms
  • Creating and removing synonyms

Controlling user access

  • Controlling user access
  • Privileges
  • System privileges
  • Creating users
  • User System Privilege
  • Granting System Privilege
  • What is a Role?
  • Creating and Granting privileges to a Role
  • Changing your password
  • Object Privileges
  • Granting Object Privilege
  • Using WITH GRANT OPTION and PUBLIC Keywords
  • Confirming Privileges Granted
  • Revoking privileges
Top
Module – 3: P/L SQL

Declaring Variables

  • About PL/SQL
  • Benefits of PL/SQL
  • PL/SQL Block Structure
  • Block types
  • Program constructs
  • Use of variables
  • Handling variable in PL/SQL
  • Types of variables
  • Declaring PL/SQL variables
  • Naming Rules
  • Assigning Values to Variables
  • Varchar initialisation and keywords
  • Scalar datatypes
  • Base Scalar Datatypes
  • Scalar variable declarations
  • The %TYPE Attribute
  • Declaring Variables with the %TYPE Attribute
  • Declaring Boolean Variables
  • PL/SQL record structure
  • LOB Datatype Variables
  • Bind Variables
  • Referencing Non-PL/SQL Variable
  • DBMS_OUTPUT.PUT_LINE

Writing Executable Statements

  • PL/SQL Block Syntax and Guidelines
  • Commenting Code
  • SQL Function in PL/SQL
  • PL/SQL function
  • Datatype Conversion
  • Nested Blocks and Variable Scope
  • Operators in PL/SQL
  • Using Bind Variable
  • Programming Guidelines
  • Code naming conventions
  • Indenting Code
  • Determining Variable Scope

Interacting with Oracle Server

  • SQL Statements in PL/SQL
  • SELECT statement in PL/SQL
  • Retrieving Data in PL/SQL
  • Manipulating Data using PL/SQL
  • Inserting Data
  • Updating Data
  • Naming Conventions
  • COMMIT & ROLLBACK Statements
  • SQL Cursor
  • SQL Cursor Attributes

Writing Control Structures

  • Controlling PL/SQL Flow of Execution
  • IF Statements
  • Simple IF Statement
  • IF-THEN-ELSE Statement Execution Flow
  • IF-THEN-ELSE Statement
  • IF-THEN-ELSEIF Statement
  • IF-THEN-ELSEIF Statement
  • Building Logical Conditions
  • Logic Table
  • Boolean Condition
  • Iterative Control: LOOP Statements
  • Basic Loop
  • FOR Loop
  • WHILE Loop
  • Nested Loops and Labels

Working with Composite Datatypes

  • PL/SQL Records
  • Composite Datatypes
  • PL/SQL Records
  • Creating a PL/SQL Record
  • PL/SQL Record Structure
  • The %ROWTYPE Attribute
  • PL/SQL Tables
  • Creating a PL/SQL table
  • PL/SQL Table Structure
  • Creating a PL/SQL Table
  • Using PL/SQL Table Methods
  • PL/SQL Table of Records
  • PL/SQL Table of Records

Writing Explicit Cursors

  • Explicit Cursor Functions
  • Controlling Explicit Cursors
  • Declaring the Cursor
  • Opening the Cursor
  • Fetching Data from the Cursor
  • Closing the cursor
  • Explicit Cursor attribute

Advanced Explicit Cursor Concepts

  • Cursors with Parameters
  • The FOR UPDATE Clause
  • The WHERE CURRENT OF clause
  • Cursor with Subqueries

Handling Exceptions

  • Handling Exceptions with PL/SQL
  • Handling Exceptions
  • Exception Types
  • Trapping Exceptions
  • Trapping Exception Guidelines
  • Predefined Exceptions
  • Non-predefined exception
  • User-defined exception
  • Calling Environment
  • RAISE_APPLICATION_ERROR Procedure

Overview of PL/SQL

  • Oracle Complete Solution
  • PL/SQL Program Construct
  • Block Structure for Anonymous PL/SQL Blocks
  • Block Structure for PL/SQL Subprograms
  • Benefit of Subprograms
  • Development Environment

Creating Procedures

  • Overview of Procedures
  • Syntax for creating Procedures
  • Procedural Parameter Mode

Creating Functions

  • Overview of Stored Functions
  • Syntax for creating functions
  • Creating a function
  • Creating a Stored Function using SQL * Plus.
  • Executing Functions
  • Invoking Functions in SQL Expressions: Restrictions
  • Removing Functions

Creating Package

  • Component of a package
  • Referencing Package Objects
  • Developing a Package
  • Creating the Package Specification
  • Declaring Public Constructs
  • Creating package specification
  • Public and private constructs
  • Creating package body
  • Invoking package constructs
  • Global Variables
  • Referencing a public variable from a stand alone procedure
  • Removing package
  • Developing packages
  • Advantages of packages

More package concepts

  • Overloading
  • Forward Declarations
  • Creating a One-Time-Only Procedure
  • Restrictions on Package Functions used in SQL
  • PL/SQL Compilation checking
  • Purity level of a package function
  • PRAGMA RESTRICT_REFERENCES
  • Invoke a User-Defined Package Function from a SQL Statement
  • Persistent State: Package Variable
  • Persistent State: Package PL/SQL Tables and Records

Oracle Supplied Packages

  • Using DBMS_PIPE
  • Dynamic SQL
  • Execution flow
  • DBMS_SQL
  • DBMS_DDL
  • DBMS_JOB
  • DBMS_OUTPUT

Creating Database Triggers

  • Designing triggers
  • Creating triggers
  • Trigger component
  • Firing sequence
  • INSTEAD OF trigger
  • DROP Trigger
Top
Module – 4: Project Work
  • Designing and Deploying a Database Solution

Comments are closed.