CHAPTER 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)
|
CHAPTER 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
|
CHAPTER 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
|
CHAPTER 2: ORACLE INSTALLATION
- Oracle 2018 Installation Guidance
- Oracle 2012 Installation
- Installation Pre-Requisites and Precautions
- Oracle Sql Developer
- 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
|
CHAPTER 8 : QUERIES & JOINS
- Need for Joins and Table Comparisons
- SET Operations on Tables and Joins
- 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
|
CHAPTER 14: ADVANCED PL/SQL - 1
- Procedures in PL/SQL: STORED PROCEDURE
- PROCEDURES @ Parameters (IN, OUT, IN OUT)
- POSITIONAL Notation and NAMED Notation
- 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
|
CHAPTER 3: INTRODUCTION TO RDBMS
- Features of RDBMS
- Advantages of RDBMS
- E.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
|
CHAPTER 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
- All_Synonyms and Dba_Synonyms System Tables
- Creating Index Tables – Purpose
|
CHAPTER 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
|
CHAPTER 4 : SQL LANGUAGE COMMANDS
- Structured Query Language (SQL) Basics
- What is SQL?
- What Can SQL do?
- Possible Operations with SQL
- 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
- DDL Commands with Examples
- CREATE, ALTER, TRUNCATE,DROP,RENAME
- Learn the DESCRIBE command to display the table structure
- DML Command DRL Commands Operators
- INSERT, UPDATE, DELETE Statements
- Difference between Truncate and Delete commands
- SELECT Statements with Multiplier
- SQL Comments
- Single Line Comments & Multi Line Comments
- WHERE Keyword for Query Conditions
|
CHAPTER 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
- Select Nth Highest salary
- Select Duplicate Records
- Delete Duplicate Records
- Advantages of Oracle Sub Query
- Subquery in the Select Clause Examples
- Subquery in the From Clause Examples
- Display Odd Rows in a table
- Display Even Rows in a table
- 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
- Multiple Column Subquery
- UnCorrelated Subquery
- Difference between Correlated and Non-Correlated Subquery
- How does Oracle Execute Correlated Subquery?
- NULLs and Correlated Sub Queries in SQL
|
CHAPTER 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
- What is the Recycle Bin?
- How to Delete RecycleBin Table?
- How to Delete Recycle Bin?
- SQL Loader - Flat file into Oracle table
- Abstract Datatypes
- 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
- Triggers in PL/SQL and Data Manipulations
- Using Memory Tables in Triggers
- DML and DDL Events with Triggers
- Compound Triggers, New data types
- Working with LARGE Tables in PLSQL
- New: EXTRACT(), Autonomous Transaction
- Pragma Exception_init in oracle
|
CHAPTER 5.SQL SPECIAL OPERATORS
- 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
- DDL and DML Operations with Transactions
|
CHAPTER 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?
|
CHAPTER 17: 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
- Nested, Table Records
|
CHAPTER 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()
|
CHAPTER 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
|
CHAPTER 18: 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?
|
