Course Oracle SQL

Duration: 2 Hours

Topics Covered:

• Introduction to Oracle Database
• Features of Oracle Database 11g
• Relational Database Concepts
• Database Architecture Overview
  • Logical Database Design
  • Physical Database Design
• Tables, Rows, Columns and Relationships
• Introduction to RDBMS Concepts
• Types of SQL Statements
  • DDL (Data Definition Language)
  • DML (Data Manipulation Language)
  • DQL (Data Query Language)
  • DCL (Data Control Language)
  • TCL (Transaction Control Language)
• Introduction to Course Dataset
• Installing and Configuring SQL Developer
• Connecting to Oracle Database
• Executing SQL Scripts

Saving and Managing SQL Files

Duration: 2 Hours

Topics Covered:

• Understanding SELECT Statement
• Selecting All Columns
• Selecting Specific Columns
• Column Aliases
• Default Column Headings
• Using Arithmetic Operators
• Operator Precedence
• Understanding NULL Values
• DESCRIBE Command
• Writing Basic SQL Queries

Practical:

• Employee and Department Data Analysis Queries

Duration: 2 Hours

Topics Covered:

• Filtering Data Using WHERE Clause
• Comparison Operators
  • =
  • < 
  • =
  • <=
  • <> 
• Logical Operators
  • AND
  • OR
  • NOT
• BETWEEN Operator
• IN Operator
• LIKE Operator
• NULL Conditions
• Operator Precedence
• Sorting Data Using ORDER BY
• Ascending and Descending Order

Practical:

• Employee salary filtering

Department-wise reporting

Duration: 3 Hours

Topics Covered:

Character Functions

• UPPER()
• LOWER()
• INITCAP()
• LENGTH()
• SUBSTR()
• INSTR()
• CONCAT()
• REPLACE()

Number Functions

• ROUND()
• TRUNC()
• MOD()

Date Functions

• SYSDATE
• ADD_MONTHS()
• MONTHS_BETWEEN()
• NEXT_DAY()
• LAST_DAY()

General Functions

• NVL()
• NVL2()
• NULLIF()
• COALESCE()

Practical:

• Data cleaning and formatting using SQL functions

Duration: 2 Hours

Topics Covered:

• Implicit Data Conversion
• Explicit Data Conversion

Conversion Functions:

• TO_CHAR()
• TO_DATE()
• TO_NUMBER()

Conditional Expressions:

• CASE Expression
• DECODE Function
• Nested Functions

Practical:

• Creating business rules using CASE statements

Duration: 2 Hours

Topics Covered:

• Aggregate Functions
  • COUNT()
  • SUM()
  • AVG()
  • MIN()
  • MAX()
• GROUP BY Clause
• HAVING Clause
• Difference Between WHERE and HAVING
• Grouping Multiple Columns

Practical:

• Department salary reports
• Employee statistics

Duration: 3 Hours

Topics Covered:

Basic Joins

• Understanding Relationships Between Tables
• Equi Join
• Non-Equi Join

Advanced Joins

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• SELF JOIN
• CROSS JOIN

Practical:

Employee and Department reporting system

Duration: 3 Hours

Topics Covered:

• Introduction to Subqueries
• Single Row Subqueries
• Multiple Row Subqueries
• Nested Subqueries
• Multiple Column Subqueries
• Pairwise Comparison
• Non-Pairwise Comparison
• Scalar Subqueries
• Correlated Subqueries
• EXISTS and NOT EXISTS
• WITH Clause
• Recursive WITH Clause

Practical:

• Finding highest salary employees
• Department-wise comparison

Duration: 1 Hour

Topics Covered:

• UNION
• UNION ALL
• INTERSECT
• MINUS
• Rules of Set Operators
• Controlling Result Order

Practical:

• Combining multiple reports

Duration: 3 Hours

Topics Covered:

• INSERT Statement
• UPDATE Statement
• DELETE Statement
• MERGE Statement
• COMMIT
• ROLLBACK
• SAVEPOINT
• Transaction Management
• Read Consistency Concept

Practical:

• Real-time employee data modification scenarios

Duration: 3 Hours

Topics Covered:

Tables

• Creating Tables
• Altering Tables
• Dropping Tables
• Data Types

Constraints

• Primary Key
• Foreign Key
• Unique Constraint
• NOT NULL Constraint
• Check Constraint

Schema Objects:

• Views
• Sequences
• Indexes
• Synonyms

Practical:

• Designing database objects for applications

Duration: 2 Hours

Topics Covered:

Views

• Simple Views
• Complex Views
• Creating and Managing Views

Sequences

• Creating Sequence
• NEXTVAL
• CURRVAL
• Sequence Management

Indexes

• Creating Indexes
• Maintaining Indexes
• Function-Based Indexes

Synonyms

• Private Synonyms
• Public Synonyms

Duration: 2 Hours

Topics Covered:

• Creating Users
• Changing Passwords
• System Privileges
• Object Privileges
• Roles
• Creating Roles
• Grant Privileges
• Revoke Privileges
• Passing Privileges

Duration: 2 Hours

Topics Covered:

• Adding Columns
• Modifying Columns
• Dropping Columns
• Adding Constraints
• Removing Constraints
• Enable and Disable Constraints
• Flashback Operations
• External Tables
  • ORACLE_LOADER

ORACLE_DATAPUMP

Duration: 2 Hours

Topics Covered:

• Understanding Data Dictionary
• USER Views
• ALL Views
• DBA Views Overview

Important Views:

• USER_OBJECTS
• ALL_OBJECTS
• USER_TABLES
• USER_TAB_COLUMNS
• USER_CONSTRAINTS
• USER_INDEXES
• USER_VIEWS
• USER_SEQUENCES

Additional Topics:

• Adding Comments to Tables
• Querying Metadata Information

Topics Covered:

• Using Subqueries in DML
• Insert Using Subquery
• Update Using Subquery
• Delete Using Subquery
• WITH CHECK OPTION
• Multi Table Insert
• MERGE Statement
• Tracking Data Changes

Duration: 3 Hours

Topics Covered:

• Oracle Date Handling
• DATE vs TIMESTAMP
• Time Zones
• CURRENT_DATE
• CURRENT_TIMESTAMP
• LOCALTIMESTAMP
• DBTIMEZONE
• SESSIONTIMEZONE

Advanced Date Functions:

• EXTRACT()
• TZ_OFFSET()
• FROM_TZ()
• TO_TIMESTAMP()
• TO_YMINTERVAL()
• TO_DSINTERVAL()

INTERVAL Data Types:

• YEAR TO MONTH

DAY TO SECOND

Duration: 2 Hours

Topics Covered:

• Introduction to Regular Expressions
• Meta Characters
• Pattern Matching

Functions:

• REGEXP_LIKE()
• REGEXP_INSTR()
• REGEXP_SUBSTR()
• REGEXP_REPLACE()
• REGEXP_COUNT()

Advanced Concepts:

• Sub Expressions
• Complex Pattern Searching