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