Oracle PL SQL Classroom Training

Chapter 1 : INTRODUCTION TO DBMS

• What is Data and Information?
• Purpose of Data Management
• File and File System For Data Storage
• Disadvantages of File Data Management
• Database : Purpose, Basic Terminology
• Advantages of Database for Data Storage
• Possible Operations on the Database
• Database Models – Hierarchal Model
• Network Model and Relational Model

CHAPTER 2 : Introduction to RDBMS

• Features of RDBMS
• Advantages of RDBMS over FMS & DBMS
• E.F Codd Rules for RDBMS
• Normalization Process
• Client server Technology
• Oracle Corporation Products
• Oracle Versions about SQL & SQL*PLUS
• Difference between OLTP & OLAP

CHAPTER 3 : Sub Language Commands

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

CHAPTER 4 : Introduction to SQL

• Schema & Schema Objects
• Oracle Data Types
• DDL Commands
• DML Command DRL Commands Operators
• Arithmetic and Logical Operators
• Set Operators (UNION, UNION ALL, INTERSECT, MINUS)
• Special Operators – IN (NOT IN), BETWEEN (NOT BETWEEN),
• LIKE (NOT LIKE), IS NULL (IS NOT NULL)

CHAPTER 5 : Built-in Functions

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

CHAPTER 6 : Grouping the result of a query

• Using Group by and Having Clause of DRL Statement
• Using Order by clause
• Working with, Sequences, Working with Synonyms,
• Working with Index and Clusters, Creating Cluster Tables,
• implementing Locks, Working with roles

CHAPTER 7 : Pseudo Columns in Oracle

• Understanding Pseudo Columns in Oracle
• Type of Pseudo Columns in Oracle
• CURRVAL AND NEXTVAL, LEVEL, ROWID, ROWNUM

CHAPTER 8 : Working with Integrity Constraints

• Importance of Data integrity
• Working with different types of integrity constraints
• NOT NULL constraint, UNIQUE constraint,
• PRIMARY KEY constraint, REF constraint
• Understanding ON DELETE clause in referential integrity constraint
• Working with multiple constraints upon a column
• Adding constraints to a table
• Dropping of constraints, Enabling /
• disable constraints
• Querying for constraints information

CHAPTER 9 : Working with Sub Queries

• Understanding the practical approach
• to Sub Queries/Nested Select/Sub Select/Inner Select/Outer Select
• Usage of 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 Date Retrieval with EXISTS and NOT EXISTS Operators

CHAPTER 10 : Locks

• Row level Locks
• Table level Locks
• Shared Lock
• Dead Lock
• Implicit Locking

CHAPTER 11 : Querying Multiple Tables (Joins)

• Equi Join/Inner Join/Simple Join,
• Cartesian Join, Non-Equi Join, Self Join

CHAPTER 12 : Data Partitions & Parallel Process

• Range Partitions, Hash Partition
• List Partition, Composite Partition, Parallel
• Query Process

CHAPTER 13 : Working with DCL, TCL Commands

• Grant, Revoke,
• Commit, Rollback, Savepoint
• Read Consistency

CHAPTER 14 : PL/SQL (Procedure Language – SQL)

• introduction to Programming Languages
• Introduction PL/SQL, The Advantages of
• PL/SQL
• PL/SQL Architecture, PL/SQL Data types
• Variable and Constants, Using Built-in
• Functions
• Conditional and Unconditional Statement
• 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.

CHAPTER 15 : Advanced PL/SQL

• Procedures in PL/SQL: STORES PROCEDURE,
• PROCEDURE with Parameters (IN, OUT and IN OUT
• POSITIONAL Notation and NAMED Notation,
• Procedure with Cursors, Dropping a Procedure
• Functions in PL/SQL: Differences between
• Procedures and Functions,
• User Defined Functions, Nested Functions,
• using stored function in SQL statements
• Pragma_Autonomous_Transaction(),
• Returning into clause, Bulk Collect,
• For All, Definer/Invoker Rights
• About flash Back Queries, Dynamic SQL,
• Flash back command, Purge Command, Recycle bin, Regular expressions,
• DML Error Logging, Virtual Columns, Read only tables,
• Cross tab Views using, Pivot/Unpivot Operators Follows Clause,
• Compound Triggers, New data types
• Working with LARGE Objects

CHAPTER 16 : Maintaining Database Objects

• VIEWS IN ORACLE
• Understanding the Standards of VIEWS
• in Oracle
• Types of VIEWS: Relational Views,
• Practical approach of SIMPLE VIEWS and COMPLEX VIEWS,
• Column definitions in VIEWS, Using VIEWS for DML Operations,
• forced Views, Putting CHECK Constraint upon
• VIEWS, Creation of READ ONLY VIEWS
• TOP-N Analysis, About Materialized
• Understanding the IN LINE VIEWS
• Views, View Triggers
• Outer Joins – Types, Advantages and Usage
• Cross Joins – Advantages and Limitations
• Self Joins, Merge Joins and Sub Queries
• Purity Levels, User _Source Dictionary table Packages in PL/SQL:
• Creating PACKAGE Specification
• PACKAGE Body, Private and Public Objects in PACKAGE
• XCEPTIONS in PL/SQL: Types of Exceptions: User Defined Exceptions
• SQL Error Code Values

CHAPTER 17 : Database Triggers in PL/SQL

• Types of Triggers, Row Level Triggers,
• Statement Level Triggers, DML Triggers, DDL Triggers, Schema Triggers
• Using OLD and NEW References, Trigger Auditing,
• Enabling / Disabling Triggers, Dropping Triggers

CHAPTER 18 : Implementing Object Technology

• What is Object Technology?
• OOPS-Object instances, Creation of objects
• Creating user Defined Date Types
• Creating Object Tables
• Inserting rows in a table using object
• Retrieving date from Object based
• Tables
• Calling a Method, Indexing Abstract
• Data type Attributes

CHAPTER 19 : Using Collections

• Advantages of collection
• Ref Cursor (Dynamic Cursor), Weak Ref Cursor,
• Strong Ref Cursor, Nested Tables
• Associative Arrays, VARRAYS or
• VARYING arrays
• Creating tables using nested tables
• Inserting, Updating & Deleting
• Nested, Table Records

CHAPTER 20 : Advanced Features

• 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 Transaction

CHAPTER 21 : Oracle Built-in Packages

• DBMS_OUTPUT Package
• DBMS_DDL Package
• DBMS_JOB Package
• DBMD_SCHEDULER Package
• UTL_FILE Package
• UTL_MAIL Package
• Architecture
• Physical Structures Logical structures
• DB Memory Structures

CHAPTER 22 : Oracle Utilities

• Oracle Data Pump
• SQL * Loader
• External Tables

CHAPTER 1 : Introduction to Script

• What is Script
• What is a program?
• Types of Scripts
• Difference between Script & Programming Languages
• Features of Scripting
• Limitation of Scripting
• Types of programming Language Paradigms

CHAPTER 2 : Introduction to Python

• What is Python?
• Why Python?
• Who Uses Python?
• Characteristics of Python
• History of Python
• What is PSF?
• Python Versions
• How to Download Python
• How to Install Python
• Install Python with Diff IDEs
• Features of Python
• Limitations of Python
• Python Applications
• Creating Your First Python Program
• Printing to the Screen
• Reading Keyboard Input
• Using Command Prompt and GUI or IDE
• Python Distributions

CHAPTER 3 : Different Modes in PYTHON

• Execute the Script
• Interactive Mode
• Script Mode
• Python File Extensions
• SETTING PATH IN Windows
• Clear screen inside python
• Learn Python Main Function
• Python Comments
• Quit the Python Shell
• Shell as a Simple Calculator
• Order of operations
• Multiline Statements
• Quotations in Python
• Python Path Testing
• Joining two lines
• Python Implementation Alternatives
• Python Sub Packages
• Uses of Python in Data Science
• USES OF PYTHON IN IOT
• Working with Python in Unix/Linux/Windows/Mac/Android..!!

CHAPTER 4 : PYTHON NEW IDEs

• PyCharm IDE
• How to Work on PyCharm
• PyCharm Components
• Debugging process in PyCharm
• PYTHON Install Anaconda
• What is Anaconda?
• Coding Environments
• Spyder Components
• General Spyder Features
• Spyder Shortcut Keys
• Jupyter Notebook
• What is Conda?
• Conda List?
• Jupyter and Kernels
• What is PIP?

CHAPTER 5 : Variables in Python

• What is Variable?
• Variables in Python
• Constants in Python
• Variable and Value
• Variable names
• Mnemonic Variable Names
• Values and Types
• What Does “Type” Mean?
• Multiple Assignment
• Python different numerical types
• Standard Data Types
• Operators and Operands
• Order of Operations
• Swap variables
• Python Mathematics
• Type Conversion
• Mutable Versus Immutable Objects

CHAPTER 6 : String Handling

• What is string?
• String operations
• String indices
• Basic String Operations
• String Functions, Methods
• Delete a string
• String Multiplication and concatenation
• Python Keywords
• Python Identifiers
• Python Literals
• String Formatting Operator
• Structuring with indentation in Python
• Built-in String Methods
• Define Data Structure?
• Data Structures in PYTHON

CHAPTER 7: Python Operators and Operands

• Arithmetic Operators
• Relational Operators
• Comparison Operators
• Python Assignment Operators
• Short hand Assignment Operators
• Logical Operators or Bitwise Operators
• Membership Operators
• Identity Operators
• Operator precedence
• Evaluating Expressions

CHAPTER 8 : Python Conditional Statements

• How to use “if condition” in conditional structures
• if statement (One-Way Decisions)
• if .. else statement (Two-way Decisions)
• How to use “else condition”
• if .. elif .. else statement (Multi-way)
• When “else condition” does not work
• How to use “elif” condition
• How to execute conditional statement with minimal code
• Nested IF Statement

CHAPTER 9 : Python LOOPS

• How to use “While Loop”
• How to use “For Loop”
• How to use For Loop for set of other things besides numbers
• Break statements in For Loop
• Continue statement in For Loop
• Enumerate function for For Loop
• Practical Example
• How to use for loop to repeat the same statement over and again
• Break, continue statements

CHAPTER 10 : Learning Python Strings

• Accessing Values in Strings
• Various String Operators
• Some more examples
• Python String replace() Method
• Changing upper and lower case strings
• Using “join” function for the string
• Reversing String
• Split Strings

CHAPTER 11 : Sequence or Collections in PYTHON

• Strings
• Unicode Strings
• Lists
• Tuples
• buffers
• xrange

CHAPTER 12 : Python Lists

• Lists are mutable
• Getting to Lists
• List indices
• Traversing a list
• List operations
• List slices
• List methods
• Map, filter and reduce
• Deleting elements
• Lists and strings

CHAPTER 13 : Python TUPLE

• Advantages of Tuple over List
• Packing and Unpacking
• Comparing tuples
• Creating nested tuple
• Using tuples as keys in dictionaries
• Deleting Tuples
• Slicing of Tuple
• Tuple Membership Test
• Built-in functions with Tuple
• Dotted Charts

CHAPTER 14 : Python Sets

• How to create a set?
• Iteration Over Sets
• Python Set Methods
• Python Set Operations
• Union of sets
• Built-in Functions with Set
• Python Frozenset

CHAPTER 15 : Python Dictionary

• How to create a dictionary?
• PYTHON HASHING?
• Python Dictionary Methods
• Copying dictionary
• Updating Dictionary
• Delete Keys from the dictionary
• Dictionary items() Method
• Sorting the Dictionary
• Python Dictionary in-built Functions
• Dictionary len() Method
• Variable Types
• Python List cmp() Method
• Dictionary Str(dict)

CHAPTER 16 : Python Functions

• What is a function?
• How to define and call a function in Python
• Types of Functions
• Significance of Indentation (Space) in Python
• How Function Return Value?
• Types of Arguments in Functions
• Default Arguments
• Non-Default Arguments
• Keyword Arguments
• Non-keyword Arguments
• Arbitrary Arguments
• Rules to define a function in Python
• Various Forms of Function Arguments
• Scope and Lifetime of variables
• Nested Functions
• Call By Value, Call by Reference
• Anonymous Functions/Lambda functions
• Passing functions to function
• map(), filter(), reduce() functions
• What is a Docstring?

CHAPTER 17 : Python Modules

• What is a Module?
• Types of Modules
• The import Statement
• The from…import Statement
• ..import * Statement
• Underscores in Python
• The dir( ) Function
• Creating User defined Modules
• Command line Arguments
• Python Module Search Path

CHAPTER 18 : Packages in Python

• What is a Package?
• Introduction to Packages?
• py file
• Importing module from a package
• Creating a Package
• Creating Sub Package
• Importing from Sub-Packages
• Popular Python Packages

CHAPTER 19 : Python Date and Time

• How to Use Date & DateTime Class
• How to Format Time Output
• How to use Timedelta Objects
• Calendar in Python
• datetime classes in Python
• How to Format Time Output?
• The Time Module
• Python Calendar Module
• Python Text Calendar
• Python HTML Calendar Class
• Unix Date and Time Commands

CHAPTER 20 : File Handling

• What is a data, Information File?
• File Objects
• File Different Modes
• file Object Attributes
• How to create a Text File
• How to Append Data to a File
• How to Read a File
• Closing a file
• Read, read line ,read lines, write, write lines…!!
• Renaming and Deleting Files
• Directories in Python
• Working with CSV files
• Working with CSV Module
• Handling IO Exceptions

CHAPTER 21 : Python OS Module

• Shell Script Commands
• Various OS operations in Python
• Python File System Shell Methods

CHAPTER 22 : Python Exception Handling

• Python Errors
• Common RunTime Errors in PYTHON
• Abnormal termination
• Chain of importance Of Exception
• Exception Handling
• Try … Except
• Try .. Except .. else
• Try … finally
• Argument of an Exception
• Python Custom Exceptions
• Ignore Errors
• Assertions
• UsingAssertionsEffectively

CHAPTER 23 : More Advanced PYTHON

• Python Iterators
• Python Generators
• Python Closures
• Python Decorators
• Python @property

CHAPTER 24 : Python Class and Objects

• Introduction to OOPs Programming
• Object Oriented Programming System
• OOPS Principles
• Define Classes
• Creating Objects
• Class variables and Instance Variables Constructors
• Basic concept of Object and Classes
• Access Modifiers
• How to define Python classes
• Python Namespace
• Self-variable in python
• Garbage Collection
• What is Inheritance? Types of Inheritance?
• How Inheritance works?
• Python Multiple Inheritance
• Overloading and Over Riding
• Polymorphism
• Abstraction
• Encapsulation
• Built-In Class Attributes

CHAPTER 25 : Python Regular Expressions

• What is Regular Expression?
• Regular Expression Syntax
• Understanding Regular Expressions
• Regular Expression Patterns
• Literal characters
• Repetition Cases
• Example of w+ and ^ Expression
• Example of \s expression in re.split function
• Using regular expression methods
• Using re.match()
• Finding Pattern in Text (re.search())
• Using re.findall for text
• Python Flags
• Methods of Regular Expressions

CHAPTER 26 : Python XML Parser

• What is XML?
• Difference between XML and HTML
• Difference between XML and JSON and Gson
• How to Parse XML
• How to Create XML Node
• Python vs JAVA
• XML and HTML

CHAPTER 27 : Python-Data Base Communication

• What is Database? Types of Databases?
• What is DBMS?
• What is RDBMS?
• What is Big Data? Types of data?
• Oracle
• MySQL
• SQL server
• DB2
• Postgre SQL
• Executing the Queries
• Bind Variables
• Installing of Oracle Python Modules
• Executing DML Operations..!!

CHAPTER 28 : Multi-Threading

• What is Multi-Threading
• Threading Module
• Defining a Thread
• Thread Synchronization

CHAPTER 29 : Multi-Threading

• What is Multi-Threading
• Threading Module
• Defining a Thread
• Thread Synchronization

CHAPTER 30 : Web Scrapping

• The components of a web page
• BeautifulSoup
• Urllib2
• HTML,CSS,JS,jQuery
• Dataframes
• PIP
• Installing External Modules Using PIP

CHAPTER 31 : Unit Testing with PyUnit

• What is Testing?
• Types of Testings and Methods?
• What is Unit Testing?
• What is PyUnit?
• Test scenarios, Test Cases, Test suites

CHAPTER 32 : Introduction to Python Web Frameworks

• Django – Design
• Advantages of Django
• MVC and MVT
• Installing Django
• Designing Web Pages
• HTML5, CSS3, AngularJS
• PYTHON Flask
• PYTHON Bottle
• PYTHON Pyramid
• PYTHON Falcon

CHAPTER 33 : GUI Programming-Tkinter

• Introduction
• Components and Events
• Adding Controls
• Entry Widget, Text Widget, Radio Button, Check Button
• List Boxes, Menus, ComboBox

CHAPTER 34 : Data Analytics

• Introduction to data Big Data?
• Introduction to NumPY and SciPY
• Introduction to Pandas and MatPlotLib

CHAPTER 35 : Introduction to Machine Learning with PYTHON

• What is Machine learning?
• Machine Learning Methods
• Predictive Models
• Descriptive Models
• What are the steps used in Machine Learning?
• What is Deep Learning?

CHAPTER 36 : Data Science

• What is Data Science?
• Data Science Life Cycle?
• What is Data Analysis
• What is Data Mining
• Analytics vs Data Science

CHAPTER 37 : Internet of Things

• IMPACT OF THE INTERNET
• What is IOT
• History of IoT
• What is Network?
• What is Protocol?
• What is smart?
• How IoT Works?
• The Future of IoT