search
    

    Home Oracle Oracle Database: SQL and PL/SQL Fundamentals NEW

    QUICK

    INFORMATION

    icon

    5 Day(s) Course

    icon

    Official Courseware

    icon

    Exam Voucher

    icon

    Certified Trainer

    icon

    1:1 Training Available on Demand

    icon

    Customize Your Batch

    ABOUT

    THE COURSE

    This Oracle Database: SQL and PL/SQL Fundamentals training provide essential knowledge to developers and technical administrators of the basics of SQL and PL/SQL. As the training is concerned with PL/SQL, participants will also explore the concepts of relational databases. This training course will make individuals explore the wide benefits of programming languages through the deployment of Oracle Database technology.

    Completion of this training course will enable candidates in:

    • Creating PL/SQL blocks of application code that permit sharing across multiple forms, reports, and data management applications.
    • Writing queries against single and multiple tables, manipulating data in tables and creating database objects.
    • Developing anonymous PL/SQL blocks, stored procedures, and functions.
    • Using DDL statements to manage database objects.
    • Using DML statements to manage data.
    • Declaring PL/SQL Variables.
    • Conditionally controlling code flow (loops, control structures).
    • Describing stored procedures and functions.
    • Retrieving row and column data from tables.

    Course Benefits:


    Attaining certification of this course will ensure fast, reliable, secure and easy to manage performance. Also, this training will help individuals to optimize database workloads, at lower IT costs and deliver a better quality of service by enabling smooth and rapid consolidation within your Data clouds.

    1. Introduction

    • Oracle Database 12c and related products overview
    • Relational database management concepts and terminologies overview
    • SQL and its development environments
    • Oracle Database documentation and additional resources

    2. Working with Oracle Cloud Exadata Express Cloud Service

    • Oracle Database Exadata Express Cloud Service
    • Access Cloud Database using SQL Workshop
    • Connect to Exadata Express using Database Clients

    3. Retrieve Data using the SQL SELECT Statement

    • Explore capabilities of SQL SELECT statements
    • Using arithmetic expressions and NULL values in the SELECT statement
    • Generating a report of data from the output of a basic SELECT statement
    • Invoking Column aliases
    • Concatenation operator, alternative quote operator, literal character strings, and the DISTINCT keyword
    • Displaying the table structure using the DESCRIBE command

    4. Restricted and Sorted Data

    • Writing queries with a WHERE clause to limit the output retrieved
    • Describing the comparison operators and logical operators
    • Utility of character string literals in the WHERE clause
    • Writing queries with an ORDER BY clause
    • Sorting the output in descending and ascending order

    5. Usage of Single-Row Functions to Customize Output

    • Listing the differences between single row and multiple row functions
    • Manipulating strings using character functions
    • Deploying numbers with the ROUND, TRUNC, and MOD functions
    • Manipulating dates with the DATE functions

    6. Aggregated Data Using the Group Functions

    • Utility of the aggregation functions in SELECT statements to produce meaningful reports
    • Describing the AVG, SUM, MIN, and MAX function
    • Techniques to handle Null Values in a group function
    • Dividing the data in groups by using the GROUP BY clause

    7. Display Data from Multiple Tables

    • Writing SELECT statements to access data from more than one table
    • Viewing data that does not meet a join condition by using outer joins
    • Joining Tables Using SQL:1999 Syntax
    • Joining a table to itself by using a self-join
    • Creating Cross Joins

    8. Usage of Subqueries to Solve Queries

    • Use a Subquery to Solve a Problem
    • Single-Row Subqueries
    • Multiple-Row Subqueries
    • Using the ANY and ALL Operator in Multiple-Row Subqueries
    • Using the EXISTS Operator

    9. SET Operators

    • SET operators Overview
    • Deploy a SET operator to combine multiple queries into a single query
    • Describing the UNION, UNION ALL, INTERSECT, and MINUS Operators
    • Using the ORDER BY Clause in Set Operations

    10. Data Manipulation

    • Adding New Rows to a Table
    • Changing the Data in a Table
    • Using the DELETE and TRUNCATE Statements
    • Save and discard changes with the COMMIT and ROLLBACK statements
    • Implementing Read Consistency
    • Describing the FOR UPDATE Clause

    11. DDL Statements to Create and Manage Tables

    • Creating Tables
    • Describing the data types
    • Understanding Constraints
    • Creating a table using a subquery
    • Altering a table
    • Dropping a table

    12. Other Schema Objects

    • Creating, modifying, and retrieving data from a view
    • Perform Data manipulation language (DML) operations on a view
    • Creating, using, and modifying a sequence
    • Creating and dropping indexes
    • Creating and dropping synonyms

    13. Introduction to PL/SQL

    • PL/SQL Overview
    • Listing the benefits of PL/SQL Subprograms
    • Types of PL/SQL blocks
    • Creating a Simple Anonymous Block
    • Generating the Output from a PL/SQL Block

    14. PL/SQL Identifiers

    • List the different Types of Identifiers in a PL/SQL subprogram
    • Utility of the Declarative Section to Define Identifiers
    • Deploying variables to store data
    • Scalar Data Types
    • Bind Variables
    • %TYPE Attribute
    • Sequences in PL/SQL Expressions

    15. Write Anonymous PL/SQL blocks

    • PL/SQL Block Syntax Guidelines
    • SQL Functions in PL/SQL
    • Operators in PL/SQL
    • Nested Blocks
    • Data Type Conversion

    16. SQL statements in PL/SQL block

    • SELECT Statements in PL/SQL to Retrieve data
    • The SQL Cursor concept
    • Data Manipulation in the Server Using PL/SQL
    • Using SQL Cursor Attributes to Obtain Feedback on DML

    17. Control Structures

    • Conditional processing Using IF Statements & CASE Statements
    • For Loop Statement
    • While Loop Statement
    • The Continue Statement
    • Simple Loop Statement

    18. Composite Data Types

    • PL/SQL Records
    • Insert and Update with PL/SQL Records
    • The %ROWTYPE Attribute
    • INDEX BY Table Methods & Table of Records

    19. Explicit Cursors

    • Understanding Explicit Cursors
    • Declaring the Cursor
    • Opening & Closing the Cursor
    • Fetching data from the Cursor
    • Cursor FOR loop
    • FOR UPDATE Clause and WHERE CURRENT Clause

    20. Exception Handling

    • Exceptions & Handling Exceptions with PL/SQL
    • Trapping Predefined Oracle Server Errors
    • Trapping Non-Predefined Oracle Server Errors
    • Trapping User-Defined Exceptions
    • Propagating Exceptions

    21. Stored Procedures and Functions

    • Describing Stored Procedures and Functions
    • Differentiating between anonymous blocks and subprograms
    • Creating & Executing a Simple Function
    • Creating a Simple Procedure
    • Creating a Simple Procedure with IN parameter

    This course is ideal for those working with the profiles of:

    • PL/SQL Developer
    • Technical Consultant
    • Portal Developer
    • Application Developers
    • Forms Developer
    • Functional Implementer
    • Reports Developer

    • Knowledge of programming concepts
    • Acquaintance with data processing concepts and techniques

    Name Code Fee

    gorm icon

    ENQUIRY FORM

    What Our Trainees Say

    Mercury Solutions Ltd. is rated 4.5 stars by www.facebook.com/mercurysol based on 17 reviews.