Oracle SQL and PLSQL Training - 100% Lab Access | AKSWAVE

Course Overview

What will you learn?

  • Identify the major structural components of the Oracle Database 12c
  • Create reports of aggregated data
  • Write SELECT statements that include queries
  • Retrieve row and column data from tables
  • Run data manipulation statements (DML) in Oracle Database 12c
  • Create tables to store data
  • Utilize views to display data
  • Control database access to specific objects
  • Manage schema objects
  • Display data from multiple tables using the ANSI SQL 99 JOIN syntax
  • Manage objects with data dictionary views
  • Write multiple-column sub-queries
  • Employ SQL functions to retrieve customized data
  • Use scalar and correlated sub-queries
  • Create reports of sorted and restricted data

Who should go for this training?
The following professionals can go for this course:

  • Administrator
  • Analyst
  • Developer
  • Systems Administrator

What are the pre-requisites for this Course?

Basic Computer Knowledge

Course Content


  • Creating Subprograms
  • Modifying Subprograms
  • Removing Subprograms
  • Application Maintenance
  • Exercise Title
  • Parameters
  • Cursors as Parameters
  • Subprogram Development Techniques
  • Addressing Compilation Errors
  • Directives for Debugging
  • Issues with Booleans
  • Integrated Development Environments
  • PL/SQL Subprograms with Parameters

Brief History of SQL

Relational Databases

        - Tables

        - Rows

        - Columns

        - Relationships

        - Datatypes

        - Primary Keys

        - Foreign Keys

        - Relational Database Management System

Popular Databases

        - Commercial Databases

        - Popular Open Source Databases

        - Valid Object References

SQL Statements

        - Database Manipulation Language (DML)

        - Database Definition Language (DDL)

        - Database Control Language (DCL)

Introduction to the Northwind Database

Some Basics

        - Comments

        - Whitespace and Semi-colons

        - Case Sensitivity

SELECTing All Columns in All Rows

Exploring the Tables

SELECTing Specific Columns

Sorting Records

        - Sorting By a Single Column

        - Sorting By Multiple Columns

        - Sorting By Column Position

        - Ascending and Descending Sorts

The WHERE Clause and Operator Symbols

        - Checking for Equality

        - Checking for Inequality

        - Checking for Greater or Less Than

        - Checking for NULL

        - WHERE and ORDER BY

Using the WHERE clause to check for equality or inequality

Using the WHERE clause to check for greater or less than

Checking for NULL

Using WHERE and ORDER BY Together

The WHERE Clause and Operator Words

        - The BETWEEN Operator

        - The IN Operator

        - The LIKE Operator

        - The NOT Operator

More SELECTs with WHERE

Checking Multiple Conditions

        - AND

        - OR

        - Order of Evaluation

Writing SELECTs with Multiple Conditions

Calculated Fields

        - Concatenation

        - Mathematical Calculations

        - Aliases

Calculating Fields

Aggregate Functions and Grouping

        - Aggregate Functions

        - Grouping Data

        - Selecting Distinct Records

Working with Aggregate Functions

Built-in Data Manipulation Functions

        - Common Math Functions

        - Common String Functions

        - Common Date Functions

Data Manipulation Functions

Subqueries

Joins

        - Table Aliases

        - Multi-table Joins

Using Joins

Outer Joins

Unions

        - UNION ALL

        - UNION Rules

Working with Unions

Conditional Processing with CASE

INSERT, UPDATE, DELETE

Student Challenges - Design your own reports

Introduction to Users and Schemas

        - Definitions and Client Tools

        - SQL Developer Login

About the HR Schema

        - Sample Schemas

        - HR Schema Entities

Creating An Application Schema

Exercise: Create a User

  • Using The Dual Table
  • Functions
  • Exercise: Use the SYSCONTEXT function
  • Pseudo-Columns
  • Using Rowid
  • Using Rownum
  • Exercise: Use ROWNUM

Simple Subqueries

        - Definition

        - Nested Subquery

Inline Views

        - Subquery in the HAVING clause

Correlated Subqueries

Scalar Subquery

Exercise: Subqueries

  • Review of Joins
  • Equijoins

        - Inner Joins

        - Outer Joins

  • Cross Joins
  • Reflexive Join
  • Non-Key Join
  • Natural Joins
  • Semijoins and Antijoins
  • Using Named Subqueries
  • Exercise: Join Exercises
  • About Group Processing
  • EMP_DETAILS_VIEW
  • Simple GROUP BY
  • ROLLUP Function
  • GROUPING Function
  • Using Cube
  • Exercise: Practice GROUP BY, ROLLUP and CUBE
  • Set Operators Defined
  • Relationship to Mathematical Set Theory
  • Restrictions on Set Operators
  • Exercise: Set Operators Exercise

The DECODE Function

The Case Expression

Exercise: Conditional Processing

What Are SQL Functions?

Character Functions

        - CONCAT

        - LENGTH

        - INSTR

        - REPLACE

        - UPPER

        - LOWER

        - INITICAP

        - LPAD

        - RPAD

        - TRIM

        - TO_CHAR

        - SOUNDEX

Regular Expressions

Exercise: Character Functions

  • Numeric Functions

        - The TO_CHAR Function with numbers

  • Date/Time Functions
  • Date Format Functions
  • Date Arithmetic Functions
  • Null Value Functions
  • Exercise: SQL Non-Character Functions
  • The INSERT Statement

        - INSERT Statement Categories

  • The UPDATE Statement
  • The DELETE Statement
  • Transactions
  • Complex Table References
  • The MERGE statement
  • Exercise: Data Manipulation Language (DDL)
  • Prerequisites

        - File Naming Conventions

  • Connecting to Oracle

        - SQL*Developer

        - SQL*Plus

  • Executing PL/SQL Code
  • SQL Developer Configuration
  • The PL/SQL Development Cycle
  • PL/SQL Block
  • Anonymous Block Structure
  • Named Block Structure
  • Executing Blocks
  • Calling PL/SQL Functions
  • Executing PL/SQL Blocks and Functions
  • Variable Usage
  • Variable Data Types
  • Variable Naming
  • Variable Assignment

        - Variable Display

  • Complex Variable Types
  • More Information
  • Variable Declaration, Initialization and Display

Conditional Processing

        - The IF Statement

        - The CASE Statement

Iterative Processing

        - The LOOP Statement

        - The WHILE Statement

        - The FOR Statement

Salary Classification using Conditional Statements

Salary Increases using Loops

  • Overview of Exceptions
  • Causing System Generated Exceptions
  • Handling System Generated Exceptions
  • Identifying System Generated Exceptions
  • OTHERS Exception Handler
  • User Defined Exceptions
  • Exception Handling for Invalid Salary
  • Implicit Cursors
  • %TYPE and %ROWTYPE Attributes
  • EXECUTE IMMEDIATE statement
  • Cursors Attributes
  • Implicit Cursor
  • Explicit Cursors
  • Explicit Cursor
  • Cursor FOR loop
  • Nesting Blocks
  • Scope of Variables
  • Scope of Exceptions
  • Nested Subprograms
  • Exception Propagation in Nested Blocks
  • Block Variable Visibility
  • Anonymous blocks
  • Types of Subprograms
  • Finding Subprograms through SQLDeveloper
  • Finding Subprograms in the Oracle Data Dictionary
  • Object Dependencies
  • Subprogram information in the Oracle Data Dictionary
  • Structure of Packages
  • Purpose of Packages
  • Wrap Utility
  • Positional vs Named Parameter Notation
  • Subprograms Omitted from Specifications
  • Using PL/SQL Packages
  • Purpose of Triggers
  • Invocation of Triggers
  • Coding Triggers
  • Validation Trigger
  • Modifying Triggers
  • Viewing Triggers
  • Enabling/Disabling Triggers
  • Trigger Errors
  • System and User Event Triggers
  • Logon Counter Trigger
  • Cursor Review
  • Cursor Parameters
  • Cursor Variables
  • Dynamic SQL
  • Employee Report by State/Province

Modes of Training

Classroom Training

Live interactive sessions delivered in our classroom by our expert trainers with real-time scenarios.

Online Training

Learn from anywhere over internet, joining the live sessions delivered by our expert trainers.

Self-pace Training

Learn through pre-recorded video sessions delivered by experts with your own pace and timing.

Program Benefits

MULTIPLE BENEFITS C
CLASSROOM
O
ONLINE
R
RECORDINGS

Live Sessions by expert trainer

30 hrs session recorded videos

Study and Practice course material

Lab practice environment (dedicated machine)

Help for preparing lab setup in students laptop
Guidance for oracle certification exams
100% learning satisfaction guaranteed

Upcoming Batches

Classroom Sessions

30-Sep 
10:00am - 11:30am
Mon - Sat
05-Oct 
10:00am - 11:30am
Mon - Sat
09-Oct 
10:00am - 11:30am
Mon - Sat

Duration: 20 sessions

10,000/-
12,000/- save 16%

Online Sessions

30-Sep 
8:00am - 9:30am
Mon - Fri
05-Oct 
8:00am - 9:30am
Mon - Sat
09-Oct 
8:00am - 9:30am
Mon - Sat

Duration: 20 sessions

16,000/- $212
18,000/- save 11%

Recorded Sessions

Recorded Videos : 30 hrs Videos
Material : Study and Lab Material
Lifetime
14,000/-
12,000/- save 12%

Our Trainers

Demo Video

No Records Found

Interview Questions

Test

test

test

test

test

test

Read More

Certification Guidence

No Records Found

Faq

Our trainer is an OCP & OCM certified consultant and has significant amount of experience in working with the technology, having 8yrs of experience.

Once you get registered, our back-end team will share you the details to join the session live over online portal which can be accessed through a browser.

Each of our live sessions are recorded. In case if you miss any, you can request us to share the link of that 

particular session.

For practical execution, our trainer/technical team will provide server access details to the student

Yes. We do provide the step-by-step document which you can follow and if required our technical team will assist you.

Live-Online training is where you can have live session with the trainer and clarify queries parallely.

Pre-recorded sessions are the recorded videos that will be provided to you that you can see, listen and learn anytime at ur feasible place. For doubts in the videos you can mail the trainer regarding the same.

You can contact our support team, or just drop an email to [email protected] with your queries.

The course material and recorded videos which are provided during the course period. You can download it anytime.

Visit our website regularly to check discount offers time to time. However, we provide discount for single participant

& special discount for 2 or more participants.

* If request for cancelation is made within 2 days of enrolment for class, 100% refunded.

* If request made after 2 days, then Refund is made after deduction of administration fee.

Combo Offers

Pay less and learn more with our best combo courses. Avail more discount on multiple top trending technologies

Related Courses

Testimonials

Recently I took Oracle Golden gate and Exadata-X6 classroom training from AKSWAVE ORACLE TRAINIGS by Mr.Akal Singh good teaching and good Lab Environment.One of the Best Institute in Hyderabad.

-Venkatesh

Oracle DBA

This institute is a very good platform to build your career. Mr Akal Singh is one of the best teacher for Oracle DBA. in this institute lab facilities is very good.it is the best institute for 12c DBA and RAC.

-Sandeep Singh

DBA

Google Ratings

4.6

Facebook Ratings

4.9

Twitter Ratings

4.8

Course Enquiry

The Secret of getting ahead is getting started.

Classroom Sessions Online Sessions Recorded Sessions