ORACLE COURSE SYLLABUS:

Database with SQL Syllabus

(Database Concepts includes)

Session 1: Getting Started with Oracle SQL

✓ Introduction to Database
✓ Introduction to Oracle SQL
✓ Installing Oracle 11g Database Server
✓ Installing Sql Developer
✓ Designing a Database
✓ Normalization
✓ Dealing with ER Diagram
✓ Dealing with Data Modelling3
✓ Loading Database into the Server
✓ Managing user and Database
✓ Data Dictionary
✓ Metadata
✓ Cardinality
✓ Is A Relationship, Has A Relationship
✓ Many to Many, One to Many, Many to One, One to One
✓ Transaction Management and Concurrency Control

Session 2: Creating and Managing Tables

✓ Database Objects
✓ Naming Rules
✓ The CREATE TABLE Statement
✓ Referencing Another Users Tables
✓ The DEFAULT Option
✓ Creating Tables
✓ Tables in the Oracle Database
✓ Querying the Data Dictionary 9-10
✓ Data Types
✓ Date Time Data Types
✓ TIMESTAMP WITH TIME ZONE Data Type
✓ TIMESTAMP WITH LOCAL TIME Data Type
✓ INTERVAL YEAR TO MONTH Data Type
✓ INTERVAL DAY TO SECOND Data Type
✓ Creating a Table by Using a Subquery Syntax
✓ Creating a Table by Using a Subquery
 ✓ The ALTER TABLE Statement
✓ Adding a Column
✓ Modifying a Column
✓ Dropping a Column
✓ The SET UNUSED Option
✓ Dropping a Table
✓ Changing the Name of an Object
✓ Truncating a Table
✓ Adding Comments to a Table

Session 3: Including Constraints

✓ What are Constraints?

✓ Constraint Guidelines
✓ Defining Constraints
✓ The NOT NULL Constraint
✓ The UNIQUE Constraint
✓ The PRIMARY KEY Constraint
✓ The FOREIGN KEY Constraint
✓ FOREIGN KEY Constraint Keywords
✓ The CHECK Constraint
✓ DEFAULT Constraint
✓ Adding a Constraint Syntax
✓ Adding a Constraint
✓ Dropping a Constraint
✓ Disabling Constraints
✓ Enabling Constraints
✓ Cascading Constraints
✓ Viewing Constraints
✓ Viewing the Columns Associated with Constraints
✓ User_Constraints Table
✓ Providing a name for a Constraint
✓ Table Level Column Level Constraints

Session 4: Manipulating Data

✓ Data Manipulation Language

✓ Adding a New Row to a Table
✓ The INSERT Statement Syntax 8-5
✓ Inserting New Rows
✓ Inserting Rows with Null Values
✓ Inserting Special Values
✓ Inserting Specific Date Values
✓ Creating a Script
✓ Copying Rows from Another Table
✓ Changing Data in a Table
 ✓ The UPDATE Statement Syntax
✓ Updating Rows in a Table
✓ Updating Two Columns with a Subquery
✓ Updating Rows Based on Another Table
✓ Updating Rows: Integrity Constraint Error
✓ Removing a Row from a Table
✓ The DELETE Statement
✓ Deleting Rows from a Table
✓ Deleting Rows Based on Another Table
✓ Deleting Rows: Integrity Constraint Error
✓ Using a Subquery in an INSERT Statement
✓ Using the WITH CHECK OPTION Keyword on DML Statements
✓ Overview of the Explicit Default Feature
✓ Using Explicit Default Values
✓ The MERGE Statement
✓ The MERGE Statement Syntax
✓ Merging Rows
✓ Database Transactions
✓ Advantages of COMMIT and ROLLBACK Statements
✓ Controlling Transactions
✓ Rolling Back Changes to a Marker
✓ Implicit Transaction Processing
✓ State of the Data Before COMMIT or ROLLBACK
✓ State of the Data after COMMIT
✓ Committing Data
✓ State of the Data After ROLLBACK
✓ Statement-Level Rollback
✓ Read Consistency
✓ Implementation of Read Consistency
✓ Locking
✓ Implicit Locking
✓ Read Consistency Example

Session 5: Writing Basic SQL SELECT Statements

✓ Basic SELECT Statement

✓ Selecting All Columns
✓ Selecting Specific Columns
✓ Writing SQL Statements
✓ Column Heading Defaults
✓ Arithmetic Expressions
✓ Using Arithmetic Operators
✓ Operator Precedence
✓ Using Parentheses
✓ Defining a Null Value
✓ Null Values in Arithmetic Expressions
 ✓ Defining a Column Alias
✓ Using Column Aliases
✓ Concatenation Operator
✓ Using the Concatenation Operator
✓ Literal Character Strings
✓ Using Literal Character Strings
✓ Duplicate Rows
✓ Eliminating Duplicate Rows

Session 6: Restricting and Sorting Data

✓ Limiting Rows Using a Selection

✓ Limiting the Rows Selected
✓ Using the WHERE Clause
✓ Character Strings and Dates
✓ Comparison Conditions
✓ Using Comparison Conditions
✓ Other Comparison Conditions
✓ Using the BETWEEN Condition
✓ Using the IN , NOT IN, ANY, ALL, EXISTS Condition
✓ Using the LIKE Condition
✓ Using the NULL Conditions
✓ Logical Conditions
✓ Using the AND Operator
✓ Using the OR Operator
✓ Using the NOT Operator
✓ Arithmetic Operators
✓ Rules of Precedence
✓ ORDER BY Clause
✓ Sorting in Descending Order
✓ Sorting by Column Alias
✓ Sorting by Multiple Columns

Session 7: Single-Row Functions

✓ SQL Function
✓ Two Types of SQL Functions
✓ Single-Row Functions
✓ Single-Row Functions
✓ Character Functions
✓ Character Functions
✓ Case Manipulation Functions
✓ Using Case Manipulation Functions
✓ Character-Manipulation Functions
✓ Using the Character-Manipulation Functions
✓ Number Functions
 ✓ Using the ROUND Function
✓ Using the TRUNC Function
✓ Using the MOD Function
✓ Working with Dates
✓ Arithmetic with Dates
✓ Using Arithmetic Operators with Dates
✓ Date Functions
✓ Using Date Functions
✓ Practice 3, Part One:
✓ Conversion Functions
✓ Implicit Data Type Conversion
✓ Explicit Data Type Conversion
✓ Using the TO_CHAR Function with Dates
✓ Elements of the Date Format Model
✓ Using the TO_CHAR Function with Dates
✓ Using the TO_CHAR Function with Numbers
✓ Using the TO_NUMBER and TO_DATE Functions
✓ RR Date Format
✓ Example of RR Date Format
✓ Nesting Functions
✓ General Functions
✓ NVL Function
✓ Using the NVL Function
✓ Using the NVL2 Function
✓ Using the NULLIF Function
✓ Using the COALESCE Function
✓ Conditional Expressions
✓ The CASE Expression
✓ Using the CASE Expression
✓ The DECODE Function
✓ Using the DECODE Function

Session 8: Aggregating Data Using Group Functions

✓ What Are Group Functions?

✓ Types of Group Functions
✓ Group Functions Syntax
✓ Using the AVG and SUM Functions
✓ Using the MIN and MAX Functions
✓ Using the COUNT Function
✓ Using the DISTINCT Keyword
✓ Group Functions and Null Values
✓ Using the NVL Function with Group Functions
✓ Creating Groups of Data
✓ Creating Groups of Data: The GROUP BY Clause Syntax
✓ Using the GROUP BY Clause
✓ Grouping by More Than One Column
 ✓ Using the GROUP BY Clause on Multiple Columns
✓ Illegal Queries Using Group Functions
✓ Excluding Group Results
✓ Excluding Group Results: The HAVING Clause
✓ Using the HAVING Clause
✓ Nesting Group Functions

Session 9: Regular Expressions

✓ Regexp_count()
✓ Regexp_instr()
✓ Regexp_like()
✓ Regexp_replace()
✓ Regexp_substr()

Session 10: Analytical Functions (Over() )

✓ Rank
✓ Dense_rank
✓ Last_value
✓ First_value
✓ Lead
✓ Lag
✓ Row_number
✓ listagg

Session 11: Displaying Data from Multiple Tables

✓ Obtaining Data from Multiple Tables

✓ Cartesian Products
✓ Generating a Cartesian Product
✓ Types of Joins
✓ Joining Tables Using Oracle Syntax
✓ What is an Equijoin?
✓ Retrieving Records with Equijoins
✓ Additional Search Conditions Using the AND Operator
✓ Qualifying Ambiguous Column Names
✓ Using Table Aliases
✓ Joining More than Two Tables
✓ Non-Equijoins
✓ Retrieving Records with Non-Equijoins
✓ Outer Joins Outer Joins Syntax
✓ Using Outer Joins
 ✓ Self Joins
✓ Joining a Table to Itself
✓ Joining Tables Using SQL: 1999 Syntax
✓ Creating Cross Joins
✓ Creating Natural Joins
✓ Retrieving Records with Natural Joins
✓ Creating Joins with the USING Clause
✓ Retrieving Records with the USING Clause
✓ Creating Joins with the ON Clause
✓ Retrieving Records with the ON Clause
✓ Creating Three-Way Joins with the ON Clause
✓ INNER Versus OUTER Joins
✓ LEFT OUTER JOIN
✓ RIGHT OUTER JOIN
✓ FULL OUTER JOIN
✓ Additional Conditions
✓ SELF JOIN

Session 12: Subqueries

✓ Objectives
✓ Using a Subquery to Solve a Problem
✓ Subquery Syntax
✓ Using a Subquery
✓ Guidelines for Using Subqueries
✓ Types of Subqueries
✓ Single-Row Subqueries
✓ Executing Single-Row Subqueries
✓ Using Group Functions in a Subquery
✓ The HAVING Clause with Subqueries
✓ What is Wrong with this Statement?
✓ Will this Statement Return Rows?
✓ Multiple-Row Subqueries
✓ Using the ANY Operator in Multiple-Row Subqueries
✓ Using the ALL Operator in Multiple-Row Subqueries
✓ Null Values in a Subquery
✓ Correlated Subquery
✓ Inline View

Session 13: Creating Views

✓ Database Objects
✓ What is a View?
✓ Why use Views?
✓ Simple Views and Complex Views
 ✓ Creating a View
✓ Retrieving Data from a View Querying a View
✓ Modifying a View
✓ Creating a Complex View
✓ Rules for Performing DML Operations on a View
✓ Using the WITH CHECK OPTION Clause
✓ Denying DML Operations
✓ Removing a View
✓ Inline Views
✓ Top-N Analysis
✓ Performing Top-N Analysis
✓ Example of Top-N Analysis
✓ Creating Materialized view
✓ Checking the Permissions on MVIEW

Session 14: Other Database Objects

✓ Database Objects
✓ What is a Sequence?
✓ The CREATE SEQUENCE Statement Syntax
✓ Creating a Sequence
✓ Confirming Sequences
✓ NEXTVAL and CURRVAL Pseudo columns
✓ Using a Sequence
✓ Modifying a Sequence
✓ Guidelines for Modifying a Sequence
✓ Removing a Sequence
✓ What is an Index?
✓ How Are Indexes Created?
✓ Creating an Index
✓ When to Create an Index
✓ When Not to Create an Index
✓ Confirming Indexes
✓ Function-Based Indexes
✓ Removing an Index
✓ Creating and Removing Synonyms

Session 15: Controlling User Access

✓ Objectives
✓ Controlling User Access
✓ Privileges
✓ System Privileges
✓ Creating Users
✓ User System Privileges
 ✓ Granting System Privileges
✓ What is a Role?
✓ Creating and Granting Privileges to a Role
✓ Changing Your Password
✓ Object Privileges
✓ Granting Object Privileges
✓ Using the WITH GRANT OPTION and PUBLIC Keywords
✓ Confirming Privileges Granted
✓ How to Revoke Object Privileges
✓ Revoking Object Privileges
✓ Database Links

Session 16: SQL Workshop

✓ Workshop Overview

Session 17: Using SET Operators

✓ The SET Operators

✓ The UNION Operator
✓ Using the UNION Operator
✓ The UNION ALL Operator
✓ Using the UNION ALL Operator
✓ The INTERSECT Operator
✓ Using the INTERSECT Operator
✓ The MINUS Operator
✓ SET Operator Guidelines
✓ The Oracle Server and SET Operators
✓ Matching the SELECT Statements
✓ Controlling the Order of Rows

Session 18: Enhancements to the GROUP BY Clause

✓ Review of Group Functions

✓ Review of the GROUP BY Clause
✓ Review of the HAVING Clause
✓ GROUP BY with ROLLUP and CUBE Operators
✓ ROLLUP Operator
✓ ROLLUP Operator Example
✓ CUBE Operator
✓ CUBE Operator: Example
✓ GROUPING Function
✓ GROUPING Function: Example
✓ GROUPING SETS
✓ GROUPING SETS: Example
✓ Composite Columns
 ✓ Composite Columns: Example
✓ Concatenated Groupings
✓ Concatenated Groupings Example

Session 19: Advanced Subqueries

✓ What Is a Subquery?
✓ Subqueries Using a Subquery
✓ Multiple-Column Subqueries
✓ Column Comparisons
✓ Pairwise Comparison Subquery
✓ Non Pairwise Comparison Subquery
✓ Using a Subquery in the FROM Clause
✓ Scalar Subquery Expressions
✓ Scalar Subqueries: Examples
✓ Correlated Subqueries
✓ Using Correlated Subqueries
✓ Using the EXISTS Operator
✓ Using the NOT EXISTS Operator
✓ Correlated UPDATE
✓ Correlated DELETE
✓ The WITH Clause
✓ WITH Clause: Example

Session 20: Hierarchical Retrieval

✓ Sample Data from the EMPLOYEES Table

✓ Natural Tree Structure
✓ Hierarchical Queries
✓ Walking the Tree
✓ Walking the Tree: From the Bottom Up
✓ Walking the Tree: From the Top Down
✓ Ranking Rows with the LEVEL Pseudocolumn
✓ Formatting Hierarchical Reports Using LEVEL and LPAD
✓ Pruning Branches

Session Extensions to DML and DDL Statements

✓ Review of the INSERT Statement

✓ Review of the UPDATE Statement
✓ Overview of Multitable INSERT Statements
✓ Overview of Multitable INSERT Statements
✓ Types of Multitable INSERT Statements
✓ Multitable INSERT Statements
✓ Unconditional INSERT ALL
 ✓ Conditional INSERT ALL
✓ Conditional FIRST INSERT
✓ Pivoting INSERT
✓ External Tables
✓ Creating an External Table
✓ Example of Creating an External Table
✓ Querying External Tables
✓ CREATE INDEX with CREATE TABLE Statement

Plsql syllabus

Part I: Programming in PL/SQL

Introduction to PL/SQL
What Is PL/SQL?
The Origins of PL/SQL
About PL/SQL Versions
Resources for PL/SQL Developers

Creating and Running PL/SQL Code

SQL*Plus
Performing Essential PL/SQL Tasks
Calling PL/SQL from Other Languages
Language Fundamentals
PL/SQL Block Structure
The PL/SQL Character Set
Identifiers
Literals
The Semicolon Delimiter
Comments
The PRAGMA Keyword
Labels

Part II: PL/SQL Program Structure

Conditional and Sequential Control
IF Statements
CASE Statements and Expressions
The GOTO Statement
The NULL Statement
Iterative Processing with Loops
Loop Basics
The Simple Loop
The WHILE Loop
The Numeric FOR Loop
The Cursor FOR Loop
Loop Labels
Tips for Iterative Processing

Exception Handlers
Exception-Handling Concepts and Terminology
Defining Exceptions
Raising Exceptions
Handling Exceptions
Building an Effective Error Management Architecture
Making the Most of PL/SQL Error Management

Part III: PL/SQL Program Data

Working with Program Data
Naming Your Program Data
Overview of PL/SQL Datatypes
Declaring Program Data
Programmer-Defined Subtypes
Conversion Between Datatypes

Strings
String Datatypes
Working with Strings
String Function Quick Reference

Numbers
Numeric Datatypes
Number Conversions
Numeric Functions

Records
Records in PL/SQL

Collections
Collections Overview
 Collection Methods (Built-Ins)
Working with Collections
Nested Table Multiset Operations
Maintaining Schema-Level Collections

Miscellaneous Datatypes
The BOOLEAN Datatype
The RAW Datatype
The UROWID and ROWID Datatypes
The LOB Datatypes
Working with LOBs
Predefined Object Types

Part IV: SQL in PL/SQL

DML and Transaction Management
DML in PL/SQL
Bulk DML with the FORALL Statement
Transaction Management
Autonomous Transactions

Data Retrieval
Cursor Basics
Working with Implicit Cursors
Working with Explicit Cursors
BULK COLLECT
SELECT ... FOR UPDATE
Cursor Variables and REF CURSORs
Cursor Expressions

Part V:Procedures, Functions, and Parameters

Procedures
Functions
Parameters
Local Modules
Module Overloading
Forward Declarations
Advanced Topics
Go Forth and Modularize!

Packages
Why Packages?
 Rules for Building Packages
Rules for Calling Packaged Elements
Working with Package Data
When to Use Packages
Packages and Object Types

Part VI : Triggers
DML Triggers
DDL Triggers
Database Event Triggers
INSTEAD OF Triggers
AFTER SUSPEND Triggers
Maintaining Triggers

Managing PL/SQL Code

Managing Code in the Database
Using Native Compilation
Using the Optimizing Compiler and Compile-Time Warnings
Conditional Compilation
Testing PL/SQL Programs
Debugging PL/SQL Programs
Tuning PL/SQL Programs
Protecting Stored Code

PLSQL WORKSHP

Workshop Overview