Ch 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 – Hierarchical Model
• Network Model and Relational Model
• Introduction to DBMS
• Introduction to RDBMS
• Database Design
• Most Popular RDBMS Products
• History, Real-time Database Examples (LIVE)

Ch 2: ORACLE INSTALLATION

• Oracle 2021 Installation Guidance
• Oracle 2019 Installation
• Installation Pre-Requisites and Precautions
• Oracle Sql Command prompt
• Oracle Versions and Editions Comparisions
• Connect Sql Developer Tool
• Creating a new User
• Grant permission
• Lock and Unlock User Account
• How to Reset User Account Password
• SQL* Plus
• How to Establish the Oracle Connection
• Default System user in Oracle
• Default Sysdba user in Oracle

Ch 3: INTRODUCTION TO RDBMS

• Features of RDBMS
• Advantages of RDBMS
• F Codd Rules for RDBMS
• Normalization Concepts & Process
• Client Server Communication
• Oracle Corporation Products
• RDBMS from Other Vendors: Microsoft, IBM,etc
• Oracle Versions about SQL & SQL*PLUS
• Database Types: OLTP and OLAP Databases
• Real-world Advantage of OLTP & OLAP
• Why a database is called as Relational Database Model
• Database Entites and Attributes
• Applicative use of RDBMS Databases
• What are the 4 features of RDBMS?

Ch 4 : SQL LANGUAGE COMMANDS

• Structured Query Language (SQL) Basics
• What is SQL?, What Can SQL do?
• Data Definition Language (DDL)
• Data Retrieval Language (DRL)
• Data Manipulation Language (DML)
• Transaction Control Language (TCL)
• Database Security and Privileges (DCL)
• Rules of SQL Queries and Statements
• Real-world applicative uses of SQL
• Creating Users and Tables
• Table Data Inserts and Validations
• Oracle Data Types and DESCRIBE Commands
• Difference between Truncate and Delete commands
• Single Line Comments & Multi Line Comments

Ch 5.SQL SPECIAL OPERATORS

• What are special operators in SQL?
• SET, AND, OR, NOT, IN
• Special Operators – IN (NOT IN)
• BETWEEN (NOT BETWEEN),
• Arithmetic and Logical Operators
• Understand Operator Precedence
• UNION, UNION ALL, INTERSECT, MINUS
• LIKE (NOT LIKE), IS NULL (IS NOT NULL)
• Understanding DCL and TCL Commands
• Transaction Concepts in Databases with SQL
• Transaction Types and Uses
• Commit and Rollback Operations
• Nested Transactions with Savepoints
• Database Read Consistency with SQL

Ch 6: GROUPING QUERY RESULTS

• Identify Distinct Values in Tables
• Group Function or Aggregate function
• Group By Syntax
• Group By Operations in Queries
• Having Clause of DRL Statements
• Aggregate Functions with Group By
• Order By Clause and Group By
• SQL Group By Statement
• Query Execution Order with Group By
• Arithmetic Functions,Character Function
• Date & Time Functions,String Function
• Conversion Functions,Analytical Function
• Rank(), Dense_Rank(),Row_Number()
• NVL(),NVL2(), NullIF(),Coalesce()

Ch 7 : CONSTRAINTS & KEYS

• Importance of Data Integrity
• Working with Integrity constraints
• Types of Integrity Constraints
• Domain Integrity Constraint and Usage
• Entity Integrity Constraints and Usage
• Referential Integrity Constraint and Usage
• Check Constraints and Usage
• NOT NULL Constraint, UNIQUE Constraint
• PRIMARY KEY Constraint and Usage
• FOREIGN KEY Constraints and Relations
• Column Level & Table Level Constraints
• Adding Constraints to Tables, User_Constraints
• Enabling – Dropping Constraints, Self Referential Integrity
• Disabling Constraints on Tables

Ch 8 : QUERIES & JOINS

• Need for Joins and Table Comparisons
• SET Operations on Tables and Joins
• How do Joins work in SQL?
• Join Types : Equi Join, Simple Join
• Inner Join and Query Conditions
• Cross Join (Cartesian Join), Conditions
• Join syntax – Sql syntax and Ansi Syntax
• Non-Equi Join and Self Join
• Outer Joins – Types, Advantages and Usage
• Cross Joins – Advantages and Limitations
• Self Joins, Merge Joins and Sub Queries
• Using Aliases in Joins and Conditions
• Using Inner Self Joins with HAVING
• JOINS and GROUP BY Queries

Ch 9 : VIEWS, SYNONYMS,SEQUENCES

• VIEWS IN ORACLE, Understanding Views & Use
• Relational Views and Standard Views
• SIMPLE VIEWS and COMPLEX VIEWS in PLSQL
• Column Definitions in VIEWS
• Using VIEWS for DML Operations
• Forced Views, CHECK Constraints in Views
• Creation of READ ONLY VIEWS – Realtime Use
• TOP-N Analysis, (Indexed) Materialized Views
• SequenWorking ces, with Synonyms
• What is the Use of Synonym in Oracle?
• Difference between View and Synonym in Oracle
• When should Table Synonyms be used?
• Public Synonym and Private Synonyms
• How do I find synonyms in oracle?

Ch 10 : SUB QUERIES, NESTED QUERIES

• Sub Queries in Real-world
• Dynamic Conditions with Sub Queries
• Sub Queries and Nested Sub Queries
• How does oracle Execute Nested Sub Queries?
• Inner Select and Outer Select Queries
• Usage of Sub Queries with WHERE, HAVING
• Impact of Having Clause in Sub Queries
• Advantages of Oracle Sub Query
• Subquery in the Select Clause Examples
• Subquery in the From Clause Examples
• Execution of Correlated Sub Queries in SQL
• IN, ANY SOME, ALL Operators in Sub Queries
• PAIR WISE and NON PAIR WISE in Sub Queries
• Single Row Subquery & Multiple Row Subquery
• Difference between Correlated and Non-Correlated Subquery

Ch 11: LOCKS in ORACLE TABLES

• Open Transactions and Lock Concepts
• Lock Types and Locking Mechanism
• Row Level Locks for Table Data Access
• Table Level Locks and Query Blocking
• Shared Lock and Real-time Use
• Shared Update Lock and Real-time Use
• Exclusive Lock and Real-time Use
• DeadLock : Detection, Prevention, Avoidance
• Implicit Locking Concepts in Oracle with PLSQL
• How to check howmany locks are occured?
• LOCK Hints and Query Blocking
• What is row exclusive lock in Oracle?
• What is lock in SQL?
• What are different types of locks?

Ch 12 : PERFORMANCE (QUERY) TUNING

• Indexes – Definition and Architecture
• B Tree Concept in Indexes
• Clustered and Non clustered Indexes
• Primary Key and Clustered Index
• Unique Key and Non Clustered Indexes
• Simple Index, Rebuild Index
• Materialized Views – Indexed Views
• Composite Index, Function Based Index
• User_indexes System Table
• Range Partitions and Hash Partition
• Partion “VALUES LESS THAN “ Clause
• List Partition and Composite Partition
• Parallel Query Process with Partitions
• Performance Tuning Advantages

Ch 13 : PL/SQL – (CONTROL STRUCTURE) – Level I

• Simple If, If..Else. Nested If..Else Statements
• Ladder, Selection, Simple Case Statements
• GOTO Label and EXIT Statements in PL/SQL
• Iterations in PL/SQL, Simple LOOP, WHILE
• FOR LOOP and NESTED LOOPS in PLSQL
• PL/SQL Select statements
• SQL within PL/SQL, Composite Data Types
• Cursor Variables and Management in PL/SQL
• Implicit & Explicit Cursors and Attributes
• Cursor with Parameters and Nested LOOPs
• Cursors with Sub Queries, Reference Cursors
• Implicit Cursors,Explicit Cursor
• Parameterized Cursors,Ref Cursors
• REF Cursors

Ch 14: ADVANCED PL/SQL – 1

• Procedures in PL/SQL: STORED PROCEDURE
• PROCEDURES @ Parameters (IN, OUT, IN OUT)
• Procedure with Cursors and Sub Queries
• ALTER and DROP of Stored Procedures
• Functions in PL/SQL: Real-time Usage
• User Defined Functions, Nested Functions
• Using Functions in SQL Statements
• Working with Procedures and Functions
• Comparing Stored Procedures and Functions
• Using SPs with Table Value Functions
• Using SPs for Dynamic SQL Statements
• Loops and Table Variables in SQL Programs
• Merge, NVL2(), NULLIF(), COALESCE()
• CASE & Temporary /Global Tables

Ch 15 : ADVANCED PL/SQL – 2

• Cross Tab Views using Pivot/Unpivot Operators
• Follows Clause and READONLY Tables
• IN-LINE VIEWS. Manipulations with Triggers
• Purity Levels in Oracle – with Examples
• User_Source Dictionary Table Packages
• Creating PACKAGE Specifications with PLSQL
• PACKAGE Body – Examples with Big Data Tables
• Private and Public Objects in PACKAGE
• Types of Exceptions: User Defined Exceptions
• EXCEPTIONS in PL/SQL with Real-time
• Event Handling and Error Handling Techniques
• Important Error Code Values in PL/SQL
• RAISE_APPLICATION_ERROR Procedure
• What is Oracle package VS procedure?

Ch 16 : ADVANCED PL/SQL – 3

• Pragma_Autonomous_Transaction() with SPs
• Returning into clause, Bulk Collect
• For All, Definer/Invoker Rights & Usage
• About Flash Back Queries, Dynamic SQL
• Flash Back Command, Purge Command
• Regular Expressions in PLSQL
• Recycle Bin and It’s Usage
• SQL Loader – Flat file into Oracle table
• DML Error Logging and Virtual Columns
• Types of Triggers, Row Level Triggers
• Statement Level Triggers, DML Triggers
• DDL Triggers and Schema Level Triggers
• Using OLD & NEW References, Trigger Auditing
• Enabling / Disabling Triggers, Dropping Triggers
• DML and DDL Events with Triggers
• Compound Triggers, New data types

Ch 17: IMPLEMENTING OBJECT TECHNOLOGY – Level I

• Object Technology and Applicative Use
• OOPS-Object Instances, Creation of Objects
• Creating User Defined Data Types
• Creating Object Tables in Oracle
• Inserting rows in Table using Object
• Retrieving data from Object Based Tables
• Calling a Method, Indexing Abstract
• Data type Attributes in PLSQL
• Advantages of Collections
• Ref Cursor (Dynamic Cursor), Weak Ref Cursor
• Strong Ref Cursors and Nested Tables
• Associative Arrays, VARRAYS/VARYING arrays
• Creating tables using Nested Tables
• Inserting, Updating & Deleting

Ch 18: IMPLEMENTING OBJECT TECHNOLOGY – Level II

• Perform a Basic Search using the REGEXP_LIKE function
• patterns using the REGEXP_INSTR function
• Extract Substrings using the REGEXP_SUBSTR function
• Replace Patterns Using the REGEXP_REPLACE
• Invisible Columns in Oracle Database 12c
• What is fetch in SQL?
• Can we use limit in Oracle?
• What is offset in Oracle?
• Is PL SQL object oriented?
• What is Oracle object type?
• What is object type in Oracle PL SQL?
• Is Oracle object oriented database?
• What is PL SQL in Oracle?
• What is Oracle constructor?

Ch 19 : ORACLE DATABASE ARCHITECTURE

• What is Oracle Database 21c? * What is Database Instance?
• Oracle Database Instance Memory Architecture (SGA & PGA)
• Subcomponents of Shared/System Global Area (SGA)
• Oracle Database Process Architecture
• Oracle Database Storage Structure
• Oracle DB Logical Storage Structure
• Oracle DB Physical Storage Structure
• Oracle Database 21c Multitenant Architecture
• Oracle Database 21c Container & Non Container Database
• What is a flashback in Oracle?
• How does Oracle flashback query work?
• How to flashback a transaction in Oracle?
• How to enable flashback in Oracle?
• How to check flashback time in Oracle?

Ch 20 & 21 : PL/SQL – BANKING PROJECTS

Phase 1: DATABASE DESIGN

• Understanding Project Requirements
• End to End Project Work Flow
• Naming Conventions in Real-time
• control Files and data files
• Table users : Creation and Use
• Implementing Normal Forms (OLTP)
• Computed Columns and Data Types
• Test Data Insertions, Options

Phase 2: QUERY DESIGN

• Join Types and Join Options For Reports
• Views @ JOIN Options For Performance
• Implementing Functions for Calculations
• Using PIVOTing Operations in Queries
• Dynamic Conditions in Queries
• Parameterized Queries in T-SQL
• Dynamic Joins and Conditions
• Using User Defined Functions (UDF)
• Using Stored Procedures for T-SQL
• Merge Option for Faster Queries

Phase 3: PLSQL PROGRAMMING

• Event Handling , Error Handling
• Stored Procedures with Transactions
• Error Handling, Event Handling Options
• Transaction Nesting, Save Points
• Stored Procedures with Tables, Views, Functions

Project Solution Explanation

• Resume Points from the Project
• Interview FAQs from Project
• Interview FAQs For Each Concept