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.
-
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:
-
Introduction to PLSQL.
- Introduction to Programming Languages.
- PLSQL vs. SQL.
- 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.
-
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.
- Exception Handling.
- Declaring, Raising, Handling Exceptions.
- EXCEPTION_INTI Pragma , RAISE_APPLICATION_ERROR procedure.
- Exception Propagation(with examples).
- 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.
- 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.
- 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.
- 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