Oracle SQL/PLSQL Training Institute In Bangalore :

Oracle-PL-SQL-Training-Institute

Oracle SQL Database Course Content:

1.Introduction to Database and SQL.

  •        Overview of Database.
  •         Why SQL?
  •         Architecture overview .
  •         What is – Instance , Database  & Schema
  •         Usage of SQL*PLUS tool.

2.Basic Elements of Oracle SQL

  •             What is a Table? Table Creation, Data types
  •              Usage of SELECT statement.(with examples)
  •              Usage of SQL Operators(Arithmetic, Concatenation etc.)
  •              NULL values in SQL, Aliases, Elimination of duplicates
  •              Usage of WHERE clause,(comparison operators, BETWEEN,LIKE etc.).
  •              Usage of Logical Operators (AND,OR,Not), ORDER by clause.
  •              Examples with Demo.

3.Functions .(single Row and Conversion)

  •                 Character Functions(SUBSTR,INSTR, LENGTH,UPPER,LOWER etc.)
  •                 Numeric Functions(ROUND,TRUNC,MOD etc.)
  •                 Date Functions(MONTHS_BETWEEN,LAST_DAY etc.)
  •                TO_CHAR, TO_DATE , TO_NUMBER.
  •                NVL, NVL2, NULLIF, COALESCE
  •                 Working with CASE and DECODE and Differences.
  •                 Examples with Demo.

 

 

4.Joins,Group By

  •                 What is Cartesian Product & why Joins?
  •                 Equi & Non-Equi Joins, Self Joins, Outer Joins.
  •                 SQL 1999 joins : NATURAL,CROSS,LEFT/RIGHT/FULL OUTER JOIN.
  •                 Usage of GROUP BY Clause.
  •                 working with Group Functions(AVG,SUM,COUNT,MAX,MIN etc).
  •                 Working with ANALYTICAL Functions(RANK,DENSE_RANK etc.).
  •                 DISTINCT clause, HAVING Clause.
  •                Examples with Demo.

5.Subqueries

  •                 Simple Subqueries.
  •                 Scalar Subqueries.
  •                 Correlated Subqueries.
  •                 Examples with Demo.

6.DDL , DML & TCL statements and Constraints

  •                 Usage of UPDATE,INSERT ,DELETE ,  MERGE.
  •                 Usage of CREATE,ALTER,RENAME,DROP,TRUNCATE.
  •                 What are constraints and types(PRIMARY,UNIQUE,NOTNULL,FOREIGN KEY,CHECK ).
  •                 Transactional design of Oracle with Demo(COMMIT,ROLLBACK,SAVEPOINT).
  •                 SET UNUSED keyword.
  •                 Examples with Demo.

 

7.Other Database Objects.

  •                 Why VIEWS? Types of Views (Simple, Complex, Inline Views)
  •                 TOP-N Analysis Queries, WITH CHECK OPTION Clause.
  •                 What is SEQUENCE, usage with Examples.
  •                 What is SYNONYM, usage with Examples.
  •                 What are Indexes?Types (Simple B-Tree, Function Based, Bitmap indexes)
  •                 Examples with Demo.
  1. DCL statements and additional concepts.

  •                 User Creation, Changing Password.
  •                 Types of Privileges.(Database and System).Granting & Revoking(GRANT,REVOKE).
  •                 WITH GRANT OPTION,WITH ADMIN OPTION,PUBLIC keywords.
  •                 Usage of Database Links.
  •                 Examples with Demo.

9.Other ORACLE Utilities and Concepts

  •                 Working with SQL*LOADER.
  •                 EXPORT    &  IMPORT  utilities.
  •                 Working with EXTERNAL Tables.
  •                 Why GLOBAL Temporary Tables? Usage.
  •                 Common ORACLE-SQL Errors.
  •                 FLASHBACK Queries.
  •                 Examples with Demo.

11.Demo on usage of  TOAD.

12.Interview Perspective Discussion.

 

Oracle –PLSQL  Course Structure:

  1. Introduction to PLSQL.

  •                 Introduction to Programming Languages.
  •                 PLSQL vs.  SQL.
  1. PLSQL Basics.
  •                 PLSQL Block Structure, types of blocks, PLSQL data types, Variables & Expressions.
  •                 Control statements. (IF, GOTO, Simple loop, While loop, For loop)
  •                 Examples with Demo.
  1. SQL in PLSQL.

  •                 SELECT & DML statements in PLSQL.
  •                 Why DDL statements are not allowed in PLSQL?
  •                 Usage of Pseudo Columns(Rowid ,Rownum).
  •                 SQL Functions in PLSQL.
  •                 Examples with Demo.

4.Cursors.

  •                 How Cursors work?
  • Implicit Cursors.
  • Explicit Cursors.
  • Examples with Demo.
  1. Exception Handling.
  •                 Declaring, Raising, Handling Exceptions.
  •                 EXCEPTION_INTI Pragma ,  RAISE_APPLICATION_ERROR procedure.
  •                 Exception Propagation(with examples).
  1. Records & Collections.
  •                 Why Records?
  • Working with Records.
  • Why Collections? Types of Collections.
  • Working with Index-By-Tables, Nested Tables , VARRAYs.
  • Collection Methods and their usage.(COUNT,DELETE,EXISTS,EXTEND,FIRST,LAST,NEXT etc.)
  • Examples with Demo.
  1. Using Procedures, Functions and Packages.
  •                 Stored and Local Subprograms && Stored  vs. Local Subprorgams.
  •                 Procedures and their usage.
  •                 Functions and their usage.
  •                 Procedure vs. Function
  •                 Why Packages?
  •                 Usage of Packages( About Package Spec, Body ).
  •                Usage of NOCOPY, AUTHID and Subprogram Dependencies.
  •                 Usage of Stored Functions in SQL Statements.
  •                 Subprogram Overloading.
  •                 Examples with Demo.
  1. Database Triggers.
  •                 Why triggers?  Types of Triggers (DML ,Instead-of , System triggers).
  •                 Usage of (DML ,Instead-of , System triggers).
  •                 what is Mutating Table? Workaround for the Mutating problem.
  •                 Examples with Demo.
  1. Dynamic SQL.
  •                 Why Dynamic SQL?
  •                 Usage of DBMS_SQL package.
  •                 Native Dynamic SQL (EXECUTE IMMEDIATE etc.)
  •                 Examples with Demo.

10.Interview Perspective discussion.

Best Training Institute In Bangalore
User Rating
1.5 based on 3 votes