SQL PL/SQL Course Overview

Oracle SQL PL/SQL Course Overview

Introduction to DBMS: 

  • Approach to Data Management
  • Introduction to prerequisites
  • File and File system
  • Disadvantages of file
  • Review of Database Management Terminology
  • Database Models
    • Hierarchal Model
    • Network Model
    • Relational Model 

Introduction to RDBMS

  • Feature of RDBMS
  • Advantages of RDBMS over FMS ad DBMS
  • The 12 rules (E.F Codd’s Rules –RDBMS)
  • Need for Database Design
  • Support of Normalization Process for Data Management
  • Client server Technology
  • Oracle Corporation Products
  • Oracle Versions
  • About SQL&SQL*PLUS

Sub Language Commands: 

  • Data Definition Language  (DDL)
  • Data Retrieval Language (DRL)
  • Data Manipulation Language (DML)
  • Transaction Control Language (TCL)
  • Database Security and Privileges (DCL) 

Introduction to SQL Database Object

  • Oracle Pre Defined Data types
  • DDL Commands
  • Create, Alter (add, modify, rename, drop)Columns, Rename, truncate, drop
  • DML-Insert, update, delete
  • DQL-SELECT Statements using WHEREclause
  • Comparison and Conditional Operators
  • Arithmetic and Logical Operators
  • Set Operators (UNION, UNION ALL, INTERSECT, MINUS)
  • Special Operators – IN (NOT IN), BETWEEN (NOT BETWEEN), LIKE (NOTLIKE), IS NULL (IS NOT NULL)
  • Working with DML, DRL Commands
  • Operators Support 

Built in Functions

  • Arithmetic Functions, Character Functions, Date Functions, Conversion Functions
  • Aggregate Functions, OLAP Functions & General Functions 

Grouping the Result of a Query

  • Using Group by and Having Clause of DRL Statement
  • Using Order by clause 

Working with Integrity Constraints

  • Importance of Data Integrity
  • Support of Integrity Constraints for Relating Table in RDBMS
    • NOT NULL constraint
    • UNIQUE constraint
    • PRIMARY KEY constraint
    • FOREIGN KEY constraint
    • CHECK constraint
  • Working with different types of integrity Constraints 

REF constraint 

  • Understanding ON DELETE clause in referential integrity constraint
  • Working with composite constraint
  • Applying DEFAULT option to columns
  • Working with multiple constraints upon a column
  • Adding constraints to a table
  • Dropping of constraints
  • Enabling / Disable constraints
  • Querying for constraints information 

Querying Multiple Tables (Joins)

  • Equi Join/Inner Join/Simple Join
  • Cartesian Join
  • Non-Equi Join
  • Outer Joins
  • Self Join

Working with Sub Queries

  • Understanding the practical approach to Sub Queries/Nested Select/Sub Select/Inner Select/Outer Select
  • What is the purpose of a Sub Query?
  • Sub Query Principle and Usage
  • Type of Sub Queries
  • Single Row
  • Multiple Row
  • Multiple Column
  • Applying Group Functions in Sub Queries
  • The impact of Having Clause in Sub Queries
  • IN, ANY/SOME, ALL Operators in Sub Queries
  • PAIR WISE and NON PAIR WISE Comparison in Sub Queries
  • Be … Aware of NULL’s
  • Correlated Sub Queries
  • Handling Data Retrieval with EXISTS and NOT EXISTS Operators 

Working with DCL, TCL Commands

  • Grant, Revoke
  • Commit, Rollback, Savepoint
  • SQL Editor Commands
  • SQL Environment settings 

Maintaining Database Objects
VIEWS in Oracle

  • Understanding the Standards of VIEWS in Oracle
  • Types of VIEWS
  • Relational Views
  • Object Views
  • Prerequisites to work with views
  • Practical approach of SIMPLE VIEWS and COMPLEX VIEWS
  • Column definitions in VIEWS
  • Using VIEWS for DML Operations
  • In-Line View
  • Forced Views
  • Putting CHECK Constraint upon VIEWS
  • Creation of READ ONLY VIEWS
  • Understanding the IN LINE VIEWS
  • About Materialized Views
  • View Triggers

Working with Sequences 
Working with Synonyms 
Working with Index and Clusters 
Creating Cluster Tables, Implementing Locks, working with roles 
Pseudo Columns in Oracle

  • Understanding Pseudo Columns in Oracle
  • Types of Pseudo Columns in Oracle
  • CURRVAL and NEXTVAL
  • LEVEL
  • ROWID
  • ROWNUM 

Data Partitions & Parallel Process

  • Types of Partitions
  • Range Partitions
  • Hash Partitions
  • List Partition
  • Composite Partition
  • Parallel Query Process

Locks

  • Row level Locks
  • Table Level Locks
  • Shared Lock
  • Exclusive Lock
  • Dead Lock

SQL * Loader

  • SQL * Loader Architecture
  • Data file (Input Datafiles)
  • Control file
  • Bad file
  • Discard file
  • Log file
  • .txt to base table
  • .csv to base table
  • From more than one file to single table 

PL-SQL (Procedure Language – SQL) 

  • Introduction to Programming Languages
  • Introduction to PL/SQL
  • The Advantages of PL/SQL
  • PL/SQL Architecture
  • PL/SQL Data types
  • Variable and Constants
  • Using Built_in Functions
  • Conditional and Unconditional Statements
  • Simple if, if… else, nested if..else, if..else Ladder
  • Selection Case, Simple Case, GOTO Label   and EXIT
  • Iterations in PL/SQL
    • Simple LOOP,WHILE LOOP,FOR LOOP   and NESTED LOOPS
    • SQL within PL/SQL
    • Composite Data types (Complete)
    • Cursor Management in PL/SQL
    • Implicit Cursors
    • Explicit Cursors
    • Cursor Attributes
    • Cursor with Parameters
    • Cursors with LOOPs Nested Cursors
    • Cursors with Sub Queries
    • Ref. Cursors
      • Record and PL/SQL Table Types 

Advanced PL/SQL

  • Procedures in PL/SQL
  • STORED PROCEDURES
  • PROCEDURE with Parameters (IN,OUT  and IN OUT)
  • POSITIONAL Notation and NAMED Notation
  • Procedure with Cursors
  • Dropping a Procedure
    • Functions in PL/SQL
    • Difference between Procedures and Functions
    • User Defined Functions
    • Nested Functions
    • Using stored function in SQL statements
      • Packages in PL/SQL
      • Creating PACKAGE Specification and  PACKAGE Body
      • Private and Public Objects in PACKAGE

EXCEPTIONS in PL/SQL 

  • Types of exceptions 
  • User Defined Exceptions
  • Pre Defined Exceptions
  • RAISE_APPLICATION_ERROR
  • PRAGMA_AUTONOMOUS_TRANSACTION
  • SQL Error Code Values

Database Triggers in PL/SQL

  • Types of Triggers
  • Row Level Triggers
  • Statement Level Triggers
  • DDL Triggers
  • Trigger Auditing

File Input/Output

  • PL/SQL file I/O (Input/Output)
  • Using UTL_FILE Package

Implementing Object Technology

  • What is Object Technology ?
  • OOPS-Object Instances
  • Creation of objects
  • Creating User Defined Data Types
  • Creating Object Tables
  • Inserting rows in a table using Objects
  • Retrieving data from Object based Tables
  • Calling a Method
  • Indexing Abstact Data type Attributes

Using LOBS

  • Large Objects (LOBS)
  • Creating Tables-LOB
  • Working with LOB values
  • Inserting, Updating & Deleting Values in LOBs
  • Populating Lobis DBMS_LOB Routines
  • Using B-FILE

Using Collections

  • Advantages of collection
  • Ref cursor (Dynamic Cursor)
  • Weak ref cursor
  • Strong ref cursor
  • Nested Tables VARRAYS or VARYING arrays
  • Creating tables using nested tables
  • Inserting, updating & deleting Nested
  • Table records
  • Nested table in PL/SQL

Oracle Database Architecture

  • Introduction to Oracle Database Architecture
  • Physical structures Logical structures
  • DB Memory Structures Background Process
  • 2 Tire, 3 Tire, N-Tier Architecture

Advanced Features

  • 9i Joines
  • New Date function
  • Rename column
  • Inner Join/Natural Join
  • Left Outer Join/Right Outer Join
  • Full Outer Join
  • Multiple Inserts
  • Insert All Command
  • Merge statement
  • NVL2(), NULLIF(), COALESCE()
  • CASE expression of Select Command
  • Temporary Tables/Global Tables
  • New Function EXTRACT()
  • Autonomous Traction
  • Pragma _ Autonomous_ Transaction()
  • Bulk Collect
  • About Flash Back Queries
  • Dynamic SQL
  • New data types, Flash back Command
  • Purge Command, Recyclebin
  • Regular expressions, DML Error Logging
  • Data Pump, Virtual Columns
  • Read only tables, Cross tab Views using
  • Pivot/Unpivot operators, Follows Clause
  • Compound triggers, New data types