Oracle (SQL/PL/SQL)

• Oracle Corporation Products
• What is sever Software
• What is Application Software?
• Package Vs. Language
• What is Database?
• Objective of Database

• What is DBMS
• Oracle Database Architecture
• Oracle Database
• Oracle Instance/Engine
• Importance of RAC(Real Application Cluster)
• Usage of Listener Service
• What is Database Application?
• Frontend Vs. backend
• What is ODBC?
• Role of ODBC Drivers in Database System
• Client/Server Technology

• OLTP DB Server
• OLAP DB Server
• OLTP Vs OLAP

• FMS
• Drawbacks of FMS
• DBMS
• Drawbacks of DBMS
• RDBMS
• E.F. CoddRules(12)
• What is Entity and Attribute?
• Drawbacks of RDBMS
• ORDBMS
• Difference between DBMS, RDBMS and ORDBMS

• What is SQL?
• Procedural vs. Non-procedural Languages
• Role of SQL in RDBMS
• Features of SQL
• What is SQL*PLUS
• SQL Vs. SQL*PLUS
• PLUS Commands

• DDL Commands : Create, Alter, Drop
• Truncate, Rename, Purge, Flashback
• DML Commands : Insert, Insert all, Upadate, Delete, Merge
• DRL Commands : Select
• TCL Commands : Commit, Rollback, Savepoint
• DCL Commands : Grant , Revoke
• System Control Commands : Alter System
• Session Control Commands: Alter session

Creation of Table(Create Command)

• Naming Rules
• Creating a table
• Creating a table based on another table
• Creating table with Default Values
• Viewing Table structure
• What is Data Dictionary
• Metadata
• Schema
• User
• What is SQL Buffer

• Insertion of data
• Insertion of data at runtime [address method]
• Insertion of nulls [implicit &Explicit method]
• What is null
• Overriding nulls with user defined values
• Multiple inserts
• Insert all Commands

• Selection of data
• Projection
• Creating column alias name
• Usage of column alias names

• Copying Rows fromone table to another table
• Creating table based on another table with /without data

• Eliminating Duplicate Values

• Sorting data based on single column
• Sorting data based on multiple columns
• Sorting columns with Column posting and alias names
• Sorting null values

• Filtering Data

• Arithmetic Operators
• Relational Operators
• Relational Negation Operators
• Logical Operators
• Set Operators( Vertical Joins)

Single Row Functions

Numeric Functions

Character Functions

Date Functions

Data Conversion Functions

Miscellaneous/General Functions

OLAP Functions

Rank(),dense_rank(), row_number(),rollup(),cube()

New Functions

Nvl2(),null if(),coalesce(),extract(),case,………

Group/Aggregate Functions

Max(),min(),count(),sum(),avg()

 

Integrity Constraints

Data Integrity

Data Redundancy

Data Inconsistency

Normalization

Function Vs Transitive Dependency

Types of constraints

Domain Integrity Constraints

Not Null,Check

Entity Integrity Constarints

Unique, Primary Key

Referential Integrity Constraints

References

Levels of Constraints

Column Level Constraint

Table level Constraint

Composite primary Key Constraint

Unique vs. primary Key

Primary Key vs. Foreign Key

Using Alter Command (DDl)

Enabling/Disabling Constraints

Adding/Dropping Constraints

Viewing Constraints

 

Alter, Drop, truncate, Rename, purge, Flashback

Flash Back Queries

Flashback Tables

Recyclebin Command

Purge Command

Read only Tables

Read/write tables

 

Joins

What is Join

Join Vs. Reference Constraint

Types of joins

Cross Join (Cartesian Join)

Inner Join

Inner Equi Join

Inner Non- Equi Join

Self-Join

Self Equi Join

Self Non-Equi Join

Outer Joins

LeftOuter Join

Right Outer Join

Full Outer Join

Natural Join

Join…..Using

Join….on

 

Sub Queries

What is Sub Query?

Types of Sub Queries

Single Row Sub Query

Multi Row Sub Query

Multi Colum Sub Query

In Line Query

Correlated Sub Query

Sub Query With Multiple tables

Usage of <all, >all, <any, >any operators in sub query

Sub Query Vs. Self join

Sub Query Vs. Inner join Sub queries with Updates Sub queries with Delete

Pseudo Columns

User, uid,rownum, rowed,sysdate,currents_date,nextval,cuur_val,new,old,….

 

Grouping the result of a query

Usage of group by clause

 

Having Clause Order of execution of all Clauses

 

• Data Updating
• Updating data using sub queries

• Commit
• Rollback
• Save Point

What is Privilege?

What is Role

Types of Privilege

System level Privileges

Object level Privileges

Granting Privileges Revoking Privileges

Viewing Privileges

 

 

 

PLUS Commands

Environment Setting Commands

Screen Formatting Commands

 

 

Database Objects

Index

What is Index?

Importance of Index

Btree index

Unique index

Composite index

Function based index

Bitmap index

Viewing indexes

Dropping indexes

Cluster

What is Cluster?

Importance of Cluster

Viewing Cluster

Dropping Cluster

Sequence

What is Sequence?

Importance of Sequence

Usage of nextval and currval

Viewing Sequences

Dropping/altering sequences

Synonym

What is synonym?

Importance of synonym

Types of Synonyms

Private Synonym

Public Synonym

Viewing/dropping synonyms

 

Views

What is View

Importance of views

Types of views

Simple views

Complex Views

Forced views

View with CHECK OPTION Clause

View With READ ONLY Clause

DML operations on simple views

Instead of trigger

Compiling Views

Finding invalid views

Viewing/dropping views

 

Materialized View

What is materialized view

Importance of materialized view

Viewing/dropping materialized views

 

Locks

Rows level locks

Table level locks

Shared locks

Exclusive locks

Partitions

Types of partitions

Range Partition

List partition

Hash partition

PL/SQL

Introduction to programming languages

Introduction to Oracle PL/SQL

Procedural Vs Non –procedural Languages

Usage of PL/SQL in production Database

Key Features of PL/SQL over SQL

Architecure of PL/SQL

Types of PL/SQL Blocks

Anonymous PL/SQLBlock

Nested PL/SQL Block

Named Pl/SQL Block

Labeled PL/SQL Block

Structure of PL/SQL Block

 

Assignment Operator Concentration Operator PL/SQL Comments Debugging Statements

Variables

What is Variable?

Variable declaration

Variable with Default values

Not null Variables

Constant Variables

Data types

Oracle data types

ANSI Data types

PL/SQL Data types

Sub types

User defined data types

Flow Control Statements

Simple if

If..else…end if

Nested if

If…elsif…end if

Exit

Raise

Goto

Looping Control statements

While loop

Simple/infinite loop

For loop

Nested loops

Embedded SQL

What is embedded SQL

Attribute/Anchor data types

%type

%rowtypeUsage of Embedded SQL

Cursor Management

What is cursor

Types of cursors

Implicit Cursor

Explicit Cursor

Cursor Attributes

Cursor using simple loop

Cursor using While loop

Cursor for loop

Cursor parameter

Ref Cursor

Dyanamic Ref Cursor

Static Ref Cursor

PL/SQL Records

PL/SQL Tables

Bulk Collect/Bulk Binding

Dynamic SQL

Advanced PL/SQL

Subprograms

Anonymous Vs. Named PL/SQL

Procedure Vs.Function

 

Stored procedures

What is stored procedure

How to Execute stored procedure

Procedure without parameters

Procedure with parameters

Procedure with Cursors

Procedure with in a procedure

Procedure with Default Arguments

Passing arguments in positional and Named method

Bind Variables

 

Types of parameters

In, Out, in out parameters

Host/Binding Variables

Viewing procedures

Granting privileges on procedures

Dropping procedure

 

Stored Functions

User Defined Functions

Nested Functions

Viewing Functions

Dropping Functions

 

Packages

What is package

Importance of package

Creating package specification

Creating package body

Private and public objects in packages

Function Overloading mechanism package

 

Exception Handling

What is exception

Exception types

Oracle predefined Exception

Oracle Non- predefined Exception

User Defined Exceptions

Raise

Raise_application_error

Usage of PRAGMA exception_init ()

SQL Error Code Values

 

Triggers

What is trigger

Usage of trigger

Types of Triggers

Row Level Triggers

Statement Level Triggers

Trigger events

New and old pseudo columns usage

Instead of trigger

DDL Triggers

Trigger auditing

Enabling/Disabling Triggers

Dropping Triggers

Viewing Triggers

 

Object Technology

What is Object Technology

Creating objects

Creating user defined Objects

Creating Object tables

InsertingRows in a table using objects

Retrieving data from object based tables

 

Collections

Ref Cursor

Static Ref Cursor

Dynamic Ref Cursor

Nested tables

VARRAYS or VARYING arrays

Creating tables using nested tables

Inserting, updating& deleting nested tables

Records

Introduction to SQL*Loader

Introduction to Toad