Online and Offline / In-Person Classroom
Training Course: MASTER ORACLE SQL PLSQL with ChatGPT
Eligibility : Any One
Trainer : Mr.Santhosh (10+Years of
Experience: Completed 120+batches successfully)
Duration : 2 Months
Group Training Fee : 10000/- Offer: 5000/-
One-On-One Training Fee :
20,000/- Offer: 10000/-
An Oracle database is a collection of data treated as a unit. The purpose of a database is to store
and retrieve related information. A database server is the key to solving the problems of
information management. In general, a server reliably manages a large amount of data in a multiuser
environment so that many users can concurrently access the same data. All this is accomplished while
delivering high performance. A database server also prevents unauthorized access and provides
efficient solutions for failure recovery.
Oracle Database is the first database designed for enterprise grid computing, the most flexible and
cost effective way to manage information and applications. Enterprise grid computing creates large
pools of industry-standard, modular storage and servers. With this architecture, each new system can
be rapidly provisioned from the pool of components. There is no need for peak workloads, because
capacity can be easily added or reallocated from the resource pools as needed.
Learn To :
- Understand the basic concepts of relational databases ensure refined code by developers.
- Create reports of sorted and restricted data.
- Run data manipulation statements (DML).
- Control database access to specific objects.
- Manage schema objects.
- Manage objects with data dictionary views.
- Retrieve row and column data from tables.
- Control privileges at the object and system level.
- Create indexes and constraints; alter existing schema objects.
- Create and query external tables.
Benefits To You :
Gain expertise in relational database data management as you learn how to effectively use SQL
commands against your business data. These features will help you query and manipulate data within
the database, use the dictionary views to retrieve metadata and create reports about their schema
objects.
Learn Advanced Features of SQL
In order to query and manipulate data within the database, use the dictionary views to retrieve
metadata and create reports about their schema objects, you'll learn to understand the advanced
features of SQL. Some of the date-time functions available in the Oracle Database are also covered.
This course also discusses how to use the regular expression support in SQL.
Development Tools
In this course, the main development tool used is Oracle SQL Developer. SQL*Plus is available as an
optional development tool. This is appropriate for a 10g and 11g audience. There are minor changes
between 10g and 11g features in SQL.
DBMS CONCEPTS
- What is Database
- What is Database Management System
- Different Types of Database Models
- Normalization
- RDBMS
- ORDBMS
- Difference Between DBMS, RDBMS & ORDBMS
INTRODUCING ORACLE
- Different versions of Oracle
- Features of Oracle
SQL
- What is SQL
- Role of SQL in RDBMS
- Data Types of Oracle
- Naming rules in Oracle
- What is SQL*PLUS
- What is Metadata
- What is Schema
- Data Dictionary Tables
SQL COMMANDS
- Data Definition Language (DDL)
- Data Retrieval Language (DRL)
- Data Manipulation Language (DML)
- Transaction Control Language (TCL)
- Database Security and Privileges (DCL)
- Introduction to SQL Database Object:
- Oracle Pre Defined Data types
- DDL Commands
- Create, Alter (add, modify, rename, drop)Columns, Rename, truncate, drop
- DML-Insert, update, delete, merge
- DCL-Grant, Revoke
- TCL-Commit, Rollback, Savepoint
Writing Basic SQL SELECT Statements
- Objectives
- Capabilities of 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
- Displaying Table Structure
- 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 Condition
- Using the LIKE Condition
- Using the NULL Conditions
- Logical Conditions
- Using the AND Operator
- Using the OR Operator
- Using the NOT Operator
- Rules of Precedence
- ORDER BY Clause
- Sorting in Descending Order
- Sorting by Column Alias
- Sorting by Multiple Columns
- Set Operators (UNION, UNION ALL, INTERSECT, MINUS)
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
- Adding a Constraint Syntax
- Adding a Constraint
- Dropping a Constraint
- Disabling Constraints
- Enabling Constraints
- Cascading Constraints
- Viewing Constraints
- Viewing the Columns Associated with Constraints
Functions
- SQL Functions
- 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
- 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
- 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
- Aggregate functions
Joins
- Obtaining Data from Multiple Tables
- Cartesian Products
- Generating a Cartesian Product
- Types of Joins
- Joining Tables Using Oracle Syntax
- What is an Equijoin?
- 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
- Practice 4, Part One: Overview
- 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
Subqueries
- 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
Database Objects
- Views
- Why use Views?
- Simple Views and Complex Views
- Creating a View
- Retrieving Data from a View 1
- 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
- Sequence
- 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
- 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
- Synonyms
- Creating and Removing Synonyms
PL/SQL
- Introduction to PL/SQL
- Advantages of PL/SQL
- The PL/SQL Block
- Basic PL/SQL Block Structure
- The Declaration Section
- The Execution Section
- The Exception Section
- Generation Output
Variables And Constants
- Data types
- Scalar Data types
- Composite Data types
- Variables (%type, %rowtype)
- Variable Assignment
- Scope of Variables
- Constants
- Writing Sample programs demonstrating all above Functionalities
Conditions and Loops
- Conditional Control
- Iterative Control
- Guidelines
- Sample, While, and For Loops
- While Loop
- For Loop
Cursors
- Implicit and Explicit Cursors
- Cursors Actions
- Declaring a Cursors
- Opening a Cursors
- Fetching Data from ad Cursor
- Closing a Cursor
- Cursor Attributes
- Cursors and Loops
- Cursors For Loops
Exception Handling
- Guidelines
- Types of Exceptions
- Named System Exceptions
- Named User-Defined Exceptions
- Unnamed System Exceptions
- Unnamed User-Defined Exceptions
- SQL ERRM and SQL Code
Procedures
- Writing Procedures using Oracle SQL,PLSQL
- Types of Program Units
- Named and Unnamed Block Structures
- Diff between Anonymous Blocks and named Blocks
- Procedures Parameters
- Parameter Modes
- IN
- Out
- IN Out
- Testing Procedure from anonymous blocks
- Calling procedure from another procedure
User Defined Functions
- Using Functions within Oracle
- Difference ways of Callings a function
- Named Notation
- Functions
- Function Parameters
- Referencing a Function
Packages
- Defining and Using Packages
- Advantages of Using Packages
- Overloading
- Dependency
- Abstraction โ Information Hiding
- Inheritance
- Defining Global Variables
Database Triggers
- Trigger Types
- Trigger Events
- Trigger Restrictions
- Trigger Body
- Complication of Database Triggers
- Trigger Syntax
- Usage of Instead of Triggers
An Overview of Oracle Large Objects
- BLOB
- CLOB
- PL/SQL Table
- VARRAY
- BFILE
- Nested Tables