Oracle SQL And PL/SQL Programming
Course Duration: 30 Hours
Required Prerequisites
- Basic Computer Knowledge
Target Audience
- Fresher who wants to make career as Oracle SQL, PL/SQL Developer.
Students Enquiry Form
Course Highlights
- Introduction to SQL
- DDL Statements
- Managing Tables using DML statements using TC Commands
- Retrieving Data using the SQL SELECT Statement
- Restricting and Sorting Data
- Using Single-Row Functions to Customize Output
- Using Conversion Functions and Conditional Expressions
- Reporting Aggregated Data Using the Group Functions
- Displaying Data from Multiple Tables Using Joins
- Using Sub queries to Solve Queries
- Using the SET Operators
- Creating Sequences, Synonyms, Indexes & Views
- Controlling User Access
- Introduction to PL/SQL
- Declaring PL/SQL Variables
- Writing Executable Statements
- Using SQL Statements within a PL/SQL Block
- Writing Control Structures
- Using Explicit Cursors
- Handling Exceptions, Creating Stored Procedures and Functions, Creating Triggers and Packages
Course Content
SQL
Section 1: Introduction
- Introduction to SQL
- Installation of Oracle Database Software 12c
- Creation of Oracle Database 12c
- SQL*Plus
- Oracle SQL Developer
Section 2: Introduction to Data Definition Language
- Creation of Table
- Alteration of Table / Column / Renaming Column or Table Name.
- Truncation of Table
- Dropping Table
Section 3: Managing CDBSection 3: Managing Tables using DMLstatements using TC Commands. and PDBs
- Inserting Data
- Updating Data
- Deleting Data
Section 4: Retrieving Data using the SQL SELECT Statement
- Capabilities of the SELECT statement
- Arithmetic expressions and NULL values in the SELECT statement
- Column aliases
- Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
- Use of the DESCRIBE command
Section 5: Restricting and Sorting Data
Limiting the Rows
Rules of precedence for operators in an expression
Section 6: Using Single-Row Functions to Customize Output
- Describe the differences between single row and multiple row functions
- Manipulate strings with character function in the SELECT and WHERE clauses
- Manipulate numbers with the ROUND, TRUNC and MOD functions
- Perform arithmetic with date data
- Manipulate dates with the date functions
Section 7: Using Conversion Functions and Conditional Expressions
- Describe implicit and explicit data type conversion
- Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
- Nest multiple functions
- Apply the NVL, NULLIF, and COALESCE functions to data
Section 8: Reporting Aggregated Data Using the Group Functions
- Group Functions
- Creating Groups of Data
- Restricting Group Results
Section 9: Displaying Data from Multiple Tables Using Joins
- Introduction to JOINS
- Types of Joins
- Natural join
- Self-join
- Non equijoins
- OUTER join
Section 10: Using Sub queries to Solve Queries
- Introduction to Subqueries
- Single Row Subqueries
- Multiple Row Subqueries
Section 11: Using the SET Operators
- Set Operators
- UNION and UNION ALL operator
- INTERSECT operator
- MINUS operator
- Matching the SELECT statements
- Using ORDER BY clause in set operations
Section 12: Creating Sequences, Synonyms, Indexes& Views
- sequences
- synonyms
- indexes
- Views
Section 13: Controlling User Access
- System privileges
- Creating a role
- Object privileges
- Revoking object privileges
Section 14: Generating Reports by Grouping Related Data
- Use the ROLLUP operation to produce subtotal values
- Use the CUBE operation to produce crosstabulation values
- Use the GROUPING function to identify the row values created by ROLLUP or CUBE
- Use GROUPING SETS to produce a single result set
Section 15: Hierarchical Retrieval
- Interpret the concept of a hierarchical query
- Create a tree-structured report
- Format hierarchical data
- Exclude branches from the tree structure
Section 16: SQL New Features
- IDENTITY Columns
- Default column value to a sequence in Oracle 12c
- Support for 32K VARCHAR2
- Row limit using FETCH FIRST
- Invisible Columns
- Truncate table cascade
PL/SQL
Section 1: Introduction
- Introduction to PL/SQL Development Environments
Section 2: Introduction to PL/SQL
- Understanding the benefits and structure of PL/SQL
- Examining PL/SQL Blocks
- Generating output messages in PL/SQL
Section 3: Declaring PL/SQL Variables
- Identify valid and invalid identifiers
- Declare and initialize variables
- List and describe various data types
- Identify the benefits of using the %TYPE attribute
- Declare, use, and print bind variables
Section 4: Writing Executable Statements
- Use SQL Functions in PL/SQL
- Use Data Type Conversion
- Use Nested Blocks as Statements
- Reference an Identifier Value in a Nested Block
- Use Operators in PL/SQL
- Using Sequences in PL/SQL Expressions
Section 5: Using SQL Statements within a PL/SQL Block
- Identify and use the SQL Statements in PL/SQL
- Retrieve Data in PL/SQL with the SELECT statement
- Manipulate Data in the Server Using PL/SQL
- The SQL Cursor concept
- Use SQL Cursor Attributes
Section 6: Writing Control Structures
- Control PL/SQL Flow of Execution
- Conditional processing Using IF and CASE Statements
- Handle Nulls to Avoid Common Mistakes
- Build Boolean Conditions with Logical Operators
- Use Iterative Control with Looping Statements
Section 7: Using Explicit Cursors
- Define Cursors
- Explain Explicit Cursor Operations
- Controlling Explicit Cursors
- Use Explicit Cursors to Process Rows
- Cursors and Records
- Cursor FOR Loops Using Subqueries
- Explicit Cursor Attributes
- The %NOTFOUND and %ROWCOUNT Attributes
Section 8: Handling Exceptions
- Functions that Return Information on
Encountered Exceptions - Trapping User-Defined Exceptions
- Propagate Exceptions
- Basic RAISE Statement
- Define Exception
- Handling Exceptions with PL/SQL
- Predefined Exceptions
- Trapping Predefined and Non-predefined Oracle Server Errors
Section 9: Creating Stored Procedures and Functions
- Create Simple Procedures and Functions
- Create a Simple Procedure with an IN Parameter
- Execute a Procedure and a Function
- Overview of Stored Procedures and Functions
- Differentiate between anonymous blocks and subprograms
- Show the CREATE OR REPLACE PROCEDURE | FUNCTION
- Understand the Header Area of a Stored Procedure and Function
Section 10: Creating Triggers and Packages
- Creation of Triggers on Tables and check the events
- Creation of Packages and execution of Package.
- Overview of Triggers and Packages.
- Types of Triggers