SQL + PLSQL COMBO COURSE Training in Pune/ Online
Duration of Training : 60 hrs
Batch type : Weekdays/Weekends
Mode of Training : Classroom/Online/Corporate Training
COURSE CONTENT :
For whom SQL is? :
IT or NON IT people can easily enter into leading Database Technology which is commonly used for all domain application software as back-end. There are so many reason to go ahead :
- Oracle SQL is basic learning platform to get in IT, which is easy to learn.
- On completion Oracle SQL training you can attempt certification exam.
- Certified candidate can take benefit as IT document and can get good opportunity in IT world.
- Oracle SQL is easy for any support project for initial stage which get into IT carrier. In case you want to grow for development environment than it’s entry for programming.
- Now a days SQL is basic skill set for any IT/NON IT folks, It’s always helpful for any functional, support and technical consultant.
Oracle 12c SQL :
 Introduction to Oracle DB 12c
Oracle Database 12c: Focus Areas, Fusion Middle-ware, Oracle Cloud, Services, Deployment Methods, RDBMS, Data Model, ER Diagram, Relation DB Terminology, SQL Statements and Development Environment, HR Schema Tables, DB Documentation.
 Retrieving Data using SQL SELECT statement
Basic Select Statement, Arithmetic Expression, Defining Null values, Concatenation Operator & Literal, Duplicate Rows, Displaying table structure. Practice Overview.
 Restricting & Sorting Data
Use of Where clause, Character Strings & Date Data, Comparison, Logical, Range, Pattern, & Other operators, Rules Precedence, Order By Clause, Substitution variables. Practice Overview
 Single Row Function & Customized Output
SQL function, Single Row Function, Character/Case Functions, Number Functions, Nesting Function, Date Functions etc. Practice Overview.
 Conversion & Conditional Expressions
Conversion Function, Implicit/Explicit Data Conversion, General Function with covering NVL, NVL2, NULLIF, COALESCE, Conditional Expression with Decode function and Case Expression. Practice Overview.
 Reporting Aggregated Data using Group Function
Usages of Group function, Nesting Group Function, Creating Group Data, Restricting group data using Having Clause. Practice Overview.
 Displaying Data from Multiple Tables using Join
Type of Joins, Explaining with Natural Join, Using Clause, On Clause, SQL 99 Syntax, Three-Way Join, Self Join, NonEqui Join, Inner Versus Outer Join, Cross Join etc. Practice overview.
 Using Sub-queries to solve queries
Scenario to use Sub-query, Rules for Sub-queries, Type of Sub-queries, Single Row Sub-queries and Multi Row Sub-queries. Null value in Sub-query etc. Practice Overview.
SQL Advanced :
 Using Set Operator
Set Operator Rules, Covering Set Operator as UNION, UNION ALL, INTERSECT, MINUS etc. Practice Overview.
 Managing Tables using DML Statements
Data Manipulation Language, Covering INSERT, UPDATE, DELETE & DB Transaction control using COMMIT, ROLLBACK, SAVEPOINT. Use of For update clause in SELECT Statement.
 Introduction to Data Definition Language
Database Object, Naming Rules, Various Data Types, CREATE TABLE statement, Constraint guidelines, Defining constraints as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY & CHECK etc. ALTER TABLE, DROP TABLE Statement. Create Table using Sub-query. Practice Overview.
 Introduction to Data Dictionary Views
Data Dictionary Structure, How to use Data Dictionary, USER_OBJECTS, ALL_OBJECT, USER_CONSTRAININTS. TABLE Information, Column Information etc. Practice overview.
 Creating Sequence, Synonyms & Indexes
Importance of Sequence, Synonyms & Indexes. Defining Sequence, Synonyms & Indexes & DROP statement etc. Practice overview.
 Creating Views
What’s View? Advantage of Views, CREATE Simple & Complex views, Rules for performing DML operation on views. Modifying & Removing a view etc. Practice Overview.
 Retrieving Data by using Sub-Query
Multiple Column Sub-query, Column Comparison, Pair & Non Pair Sub-queries, Scalar Sub queries, Co-related Sub-queries, Use of WITH clause. Practice Overview.
PLSQL Basic :
1] Introduction to Oracle PLSQL
- Oracle PLSQL: Course Agenda, Course HR Data set, PLSQL Environment, Benefits and Block structure, Create & Execute Anonymous Block, Test output of PLSQL Block. Practice Overview.
2] Declaration PLSQL Variables
- Use of Variables, Identifier, Handling Variable in PLSQL, Declaration & Initialization of PLSQL variable, Type of Variable, Guideline for Declaration & Initialization PLSQL Variables, Scalar Data Type, Bind Variables, Substitution Variables, Composite and LOB Data Type Variables. Practice Overview.
3] Writing Executable Statement
- Lexical Unit of PLSQL Block, Commenting Code, Usage of SQL Function in PLSQL, Nested Block, Variable Scope & Visibility, Operator in PLSQL, Indenting PLSQL Code. Practice Overview
4] Interacting with Oracle Server
- SQL Statement in PLSQL, Naming Convention, Manipulating Data using PLSQL, Inserting, Updating, Deleting, & Merging Data in PLSQL, SQL Cursor & Attribute. Practice Overview.
5] Writing Control Structure
- Controlling Flow of Execution, IF statement, IF THEN ELSE Statement, IF ELSIF ELSE clause, NULL values in IF Statements, Case Expression, Loop statements, BASIC Loop, WHILE Loop, FOR Loop, Guidance while using loop, Nested Loop and Labels. Practice Overview.
6] Working with Composite Data Type
- Composite Data Type, PLSQL Records, PLSQL Records Structure, The %ROWTYPE Attribute, Advantages of %ROWTYPE & Sample Program using Record %ROWTYPE. Practice Overview.
- About Cursors, Explicit cursor operations, Curosr Life Cycle , Cursor & Records, Cursor FOR loop, Explicit Cursor Attributes, Usage of FOR UPDATE & WHERE CURRENT OF Clause, Cursor with Sub-Queries. Practice overview.
8] Handling Exception
- Handling Exception using PLSQL, Exception Types, Trapping Exceptions, Guidelines for Trapping Exception, Trapping Predefined & Non Predefined Oracle Server Errors, Function of Trapping Exception, Propagate Exception in Sub-Block, RAISE_APPLICATION_ERROR Procedure . Practice Overview.
9] Introduction to Stored Procedure & Function
- Stored Procedure & Function, Difference between Anonymous Block and Sub-programs, Procedure Syntax, Example, Execution, Function Syntax, Example, Execution, Passing Parameter to Function etc. Practice Overview.
PLSQL Advanced :
10] Creating Stored Procedures
- Developing Procedure, What is parameters? Formal & Actual Parameters, Procedural Parameter Modes, Example of IN, OUT, INOUT parameter passing modes, Invoking Procedure, Handle Exception, Remove Procedure, Viewing Procedure in the Data Dictionary, Benefit of Subprograms, Practice Overview.
11] Creating Stored Functions
- Developing Function, Way to execute Function, Advantages of User Defined Function in SQL statements, Function in SQL Expression, Location to Call User Defined Function, Restriction on Calling from SQL Expressions, Controlling side effect Calling Functions from SQL Expression, Remove Function, Viewing Function in the Data Dictionary, Procedure Versus Function. Practice Overview.
12] Introduction to Collection
- Why Collection? Oracle Collection, Associative Array, Varray, Nested Table, Scenario for Collection When to use What, Ref Cursors, Type Ref Cursors, Advantage/Disadvantage of Ref Cursor. Practice overview.
13] Creating Packages
- PLSQL Package, Component of PLSQL Package, Visibility of Package Component, Developing PLSQL Package, Creating Package Specification & Package Body, Invoking Package Subprograms, Creating & Using Bodiless Package, Removing Package, Viewing Package in the Data Dictionary, Guideline to writing Package, Advantage of Using Package. Practice overview.
14] Advancement on Package Concept
- Overloading Subprograms, Overloading the Standard Package, Use of Forward Declaration, Package Initialization Block, Use Package Function in SQL & Restriction, Persistent State of Package/Variables, Package using Collection, Wrapper Utility, Guideline for Wrapping. Practice Overview.
15] Design Consideration for PLSQL Code
- Standardizing Constant & Exception, Exception Handling, Local Subprgrams, Definer’s Rights Versus Invoker’s Right, Features of Autonomous Transaction, Bulk Binding, Bulk Binding FORALL Example , Using BULK COLLECT INTO with Queries/Cursors . Practice Overview.
16] Creating Triggers
- Type of Triggers, Guidelines of Designing Triggers, Creating DML Trigger, Type of DML Triggers, Trigger Timing, Trigger Firing Sequences, DML Statement Triggers, Using Conditional Predicates, DML ROW Trigger, Old, New Qualifier, Restricting Row Trigger, Implementing Integrity Constraint with a Trigger, Comparison of Database Triggers and Stored Procedure, Comparison of DB Trigger and Oracle Forms Triggers, Managing, Removing, Testing Trigger. Practice Overview.
17] Application for Triggers
- Creating Database Triggers, Creating Triggers on DDL Statements, Creating Triggers on System Events, LOGON, LOGOFF Triggers Examples, CALL Statements, Reading Data From a Mutating Table, Benefit of DB Triggers, Business Application Scenario for Implementing Triggers, Viewing Triggers in Data Dictionary using USER_TRIGGERS. Practice Overview.
18] Project Work
- It covers real time project scenario including all deliver conceptual session based on Database 10g/11g. It includes Data Import, Validation, Audit Trail, Performance based Program etc.