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 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 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 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
CHAPTER 5.SQL BASICS
- 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 6.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 7: 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
CHAPTER 8 : BUILT-IN FUNCTIONS in SQL
- Arithmetic Functions
- Character Functions
- Date & Time Functions
- Character, String Functions
- Conversion Functions
- General Functions
- OLAP Functions
- Rank(), Dense_Rank(),Row_Number()
- NVL(),NVL2(), NullIF(),Coalesce()
- Other Functions Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
- Nest multiple functions
CHAPTER 9 : CONSTRAINTS & KEYS – Level I
- 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
CHAPTER 10 : CONSTRAINTS & KEYS (Level II)
- Multiple Constraints on Columns
- Adding Constraints to Tables
- Enabling - Dropping Constraints
- Disabling Constraints on Tables
- Querying for Constraints Information
- Data Validations using Constraints
- Constraints Limitations and Usage
- System Table – User_Constraints
- Column Level Constraints
- Table level Constraints
- Self Referential Key Constraints
CHAPTER 11 : 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
- 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 12 : 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
CHAPTER 13 : SUB QUERIES, CORRELATED QUERIES
- 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
- Mulitple 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
- Data Retrieval with EXISTS, NOT EXISTS
|
CHAPTER 14 : 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?
- Types of Synonym
- Public Synonym and Private Synonyms
- All_Synonyms and Dba_Synonyms System Tables
- Creating Index Tables – Purpose
CHAPTER 15 : 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
- Materialized Views - Indexed Views
- Composite Index
- Function Based Index
- User_indexes System Table
- Rebuild Index
- Partitioning Tables using PL/SQL
- Working with Very Large Tables
- Range Partitions and Hash Partition
- Partion “VALUES LESS THAN “ Clause
- List Partition and Composite Partition
- Parallel Query Process with Partitions
- Performance Tuning Advantages
CHAPTER 16 : PL/SQL (PROCEDURE LANGUAGE – SQL)
- Introduction to Programming Languages
- Introduction & Advantage of PL/SQL
- PL/SQL Architecture, PL/SQL Data types
- Difference between SQL and PL/SQL
- PL-SQL Programming Structure
- How PL/SQL Works?
- Example of PL/SQL Features
- Simple PL/SQL Programs
- Printing Variables
- DBMS_Output Package Usage
- The %TYPE Attribute and %ROWTYPE Attribute
- Variable and Constants, Using Built-in Functions
- Conditional and Unconditional Statements
CHAPTER 17 : 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
- Overview of PL/SQL Control Structures
- PL/SQL Select statements
- PL/SQL Delimiters
- Iterations in PL/SQL, Simple LOOP, WHILE
- FOR LOOP and NESTED LOOPS in PLSQL
CHAPTER 18 : PL/SQL – (CONTROL STRUCTURE) – Level II
- 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
- Types of Cursors
- Implicit Cursors
- Explicit Cursors
- Parameterized Cursors
- REF Cursors
- Record and PL/SQL Table Types. Real-time Use
- What are Oracle-Supplied Packages?
- Examples of Some of the Oracle-Supplied Packages
- How Does the DBMS_OUTPUT Package Work?
CHAPTER 19: 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 how many locks are occurred?
- LOCK Hints and Query Blocking
CHAPTER 20 : 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 21 : 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 22 : 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 Recycle Bin Table?
- How to Delete Recycle Bin?
- SQL Loader
- Abstract Data types
- DML Error Logging and Virtual Columns
CHAPTER 23 : ADVANCED PL/SQL - 4
- 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
CHAPTER 24: 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
CHAPTER 25: IMPLEMENTING OBJECT TECHNOLOGY – Level II
- 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
- 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
|
