SQL & PL SQL

Oracle Contents

DBMS CONCEPTS

  • What is Data ( Static, Dynamic ) (3Hrs)
  • What is Database
  • What is Database Management System
  • Different Types of Database Models
  • Normalization
  • RDBMS
  • ORDBMS
  • Difference Between DBMS, RDBMS & ORDBMS
  • What is Transaction ( OLAP, OLTP )

INTRODUCING ORACLE

  • Different versions of Oracle(1Hr)
  • Features of Oracle

SQL

  • What is SQL(1Hr)
  • Role of SQL in RDBMS
  • Data Types of Oracle
  • What is Record, Column, Row, Table
  • What is Metadata
  • What is Schema
  • What is Data Dictionary

SQL COMMANDS

  • Data Definition Language (DDL)(3Hrs)
  • Data Retrieval Language (DRL)
  • Data Manipulation Language (DML)
  • Transaction Control Language (TCL)
  • Database Security and Privileges (DCL)
  • DDL-Create, Alter (add, modify, rename, drop)Columns, Rename, truncate, drop
  • DML-Insert, update, delete, merge
  • DCL-Grant, Revoke
  • TCL-Commit, Rollback, Savepoint

Writing Basic SQL SELECT Statements

  • Objectives(2Hrs)
  • Capabilities of SQL SELECT Statements
  • Basic SELECT Statement
  • Selecting All Columns
  • Selecting Specific Columns
  • Writing SQL Statements
  • Column Heading Defaults
  • Defining a Null Value
  • Defining a Column Alias
  • Using Column Aliases
  • Concatenation Operator
  • Literal Character Strings
  • Using Literal Character Strings
  • Eliminating Duplicate Rows
  • Displaying Table Structure
  • Limiting Rows Using a Selection
  • Limiting the Rows Selected

Operators

  • Arithmetic ( +, -, /, * ) (2Hrs)
  • Comparison (>, >=, <, <=, <>)
  • Logical ( AND, OR, BETWEEN, NOT )
  • Set ( UNION, UNION ALL, INTERSECT, MINUS )
  • Other ( IN, NOT IN, LIKE, NOT LIKE, NOT BETWEEN )       

Clauses

  • Where (1Hr)
  • Group By
  • Having
  • Order By

Constraints

  • What is Constraint(2Hrs)
  • Constraint Guidelines
  • Defining Constraints
  • The NOT NULL Constraint
  • The UNIQUE Constraint
  • The CHECK Constraint
  • Assign DEFAULT Value
  • The PRIMARY KEY Constraint
  • The FOREIGN KEY Constraint
  • FOREIGN KEY Constraint Keywords
  • Adding a Constraint
  • Dropping a Constraint
  • Disabling Constraints
  • Enabling Constraints
  • Cascading Constraints
  • Viewing Constraints
  • Viewing the Columns Associated with Constraints

Functions

  • SQL Functions(4Hrs)
  • Two Types of SQL Functions
  • Single-Row Functions
  • Multi-Row Functions
  • Character Functions ( Upper, Lower, Initcap)
  • Case Manipulation Functions

(Ascii, Concat, Substr, Lpad, Rpad, Ltrim, Rtrim, Trim, Replace)

  • Number Functions ( Abs, Ceil, Floor, Log, Mod, Power, Round, Trunc )
  • Date Function

(Add_Months, Current_Date, Current_Timestamp, Last_Day, Localtimestamp,

Months_Between, Next_Day, Sysdate, Systimestamp, Dbtimeszone)

  • Conversion Functions
  • Implicit Data Type Conversion
  • Explicit Data Type Conversion ( To_Char, To_Number, To_Date )
  • Nesting Functions
  • General Functions ( Nvl, Nvl2, Coalesce )
  • Conditional Expressions ( Case, Decode )
  • Grouping Functions ( Group By, Having, Order By )
  • Comparison Functions ( Greatest,Least )
  • Aggregate functions (Avg, Count, Max, Min, Sum, Rank, Dense_Rank)

Joins

  • Obtaining Data from Multiple Tables(3Hrs)
  • Types of Joins
  • Joining Tables Using Oracle Syntax
  • Equijoin / Inner Join
  • Additional Search Conditions Using the AND Operator
  • Qualifying Ambiguous Column Names
  • Using Table Aliases
  • Joining More than Two Tables
  • Non-Equijoins
  • Self-Join
  • Outer Join ( Left Join, Right Join, Outer Join )
  • Cross Joins
  • Joins with the USING Clause
  • Joins with the ON Clause
  • Retrieving Records with the ON Clause

Subqueries

  • What is Subquery (2Hrs)
  • Types of Subqueries
  • Single-Row Subqueries
  • Multiple-Row Subqueries
  • Nested Subqueries
  • Co-Related Subqueries
  • Exists, Not Exists in Subquery

Database Objects

  • Views(2Hrs)
  • Types of Views
  • Simple Views
  • Complex Views
  • Using the WITH CHECK OPTION Clause
  • Removing a View
  • Inline Views
  • Sequence
  • Parameters in Sequence
  • NEXTVAL and CURRVAL Pseudo columns
  • Using a Sequence
  • Modifying a Sequence
  • Removing a Sequence
  • Indexes Creation
  • Types of Index
  • B-Tree Index
  • Bitmap Index
  • Unique Index
  • Function-Based Indexes
  • Removing an Index
  • Adding an Index
  • Creating and Removing Synonyms

PL/SQL

  • Introduction to PL/SQL(1Hrs)
  • Advantages of PL/SQL
  • The PL/SQL Block
  • Basic PL/SQL Block Structure
  • The Declaration Section
  • The Execution Section
  • The Exception Section
  • Generation Output

Variables and Constants

  • Data types(1Hr)
  • Scalar Data types
  • Composite Data types
  • Variables (%type, %rowtype)
  • Variable Assignment
  • Scope of Variables
  • Constants

Conditions and Loops

  • Conditional Control(1Hr)
  • Iterative Control
  • Guidelines
  • Sample, While, and For Loops
  • While Loop
  • For Loop

Cursors

  • Implicit and Explicit Cursors(2Hrs)
  • Cursors Actions
  • Declaring a Cursors
  • Opening a Cursors
  • Fetching Data from Cursor
  • Closing a Cursor
  • Cursor Attributes
  • Cursors and Loops
  • Cursors For Loops

Exception Handling

  • Types of Exceptions(2Hrs)
  • Named System Exceptions
  • Named User-Defined Exceptions
  • Unnamed System Exceptions
  • Unnamed User-Defined Exceptions
  • SQL ERRM and SQL Code

Procedures

  • Writing Procedures using Oracle SQL,PLSQL(1Hr)
  • Named and Unnamed Block Structures
  • Diff between Anonymous Blocks and named Blocks
  • Parameter Modes
  • IN
  • Out
  • IN Out
  • Calling procedure from another procedure

User Defined Functions

  • Using Functions within Oracle(1Hr)
  • Functions
  • Function Parameters
  • Referencing a Function

Packages

  • Defining and Using Packages(2Hrs)
  • Advantages of Using Packages
  • Overloading
  • Dependency
  • Abstraction – Information Hiding
  • Inheritance
  • Defining Global Variables

Database Triggers

  • Trigger Types(1Hr)
  • Trigger Events
  • Trigger Restrictions
  • Trigger Body
  • Complication of Database Triggers
  • Trigger Syntax
  • Usage of Instead of Triggers

 

An Overview of Oracle Large Objects

  • BLOB(2Hrs)
  • CLOB
  • PL/SQL Table
  • VARRAY
  • BULK COLLECT
  • Nested Tables