Oracle SQL/PLSQL Training Institute In Bangalore :

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.

8. 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.

2. 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.

3. 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.

5. Exception Handling.

•                 Declaring, Raising, Handling Exceptions.
•                 EXCEPTION_INTI Pragma ,  RAISE_APPLICATION_ERROR procedure.
•                 Exception Propagation(with examples).

6. 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.

7. 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.

8. 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.

9. Dynamic SQL.

•                 Why Dynamic SQL?
•                 Usage of DBMS_SQL package.
•                 Native Dynamic SQL (EXECUTE IMMEDIATE etc.)
•                 Examples with Demo.

10.Interview Perspective discussion.