Oracle Database 12c R2: SQL and PL/SQL Fundamentals Ed 2

loading..
(Course eligible for SATVs)

loading..

This Database SQL & PL/SQL Fundamentals training teaches you the fundamentals of relational databases, SQL & PL/SQL programming language. Learn to write queries against single and multiple tables, manipulate data in tables & create database objects.

  • Introduction to SQL and its development environments
  • The HR schema and the tables used in this course
  • Overview of Oracle Database 12c and related products
  • Overview of relational database management concepts and terminologies
  • Oracle Database documentation and additional resources
  • Accessing Cloud Database using SQL Workshop
  • Connecting to Exadata Express using Database Clients
  • Introduction to Oracle Database Exadata Express Cloud Service
  • Invoke Column aliases
  • Generate a report of data from the output of a basic SELECT statement
  • Concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
  • Use arithmetic expressions and NULL values in the SELECT statement
  • List the capabilities of SQL SELECT statements
  • Display the table structure using the DESCRIBE command
  • Describe the comparison operators and logical operators
  • Write queries with a WHERE clause to limit the output retrieved
  • Write queries with an ORDER BY clause
  • Sort the output in descending and ascending order
  • Describe the rules of precedence for comparison and logical operators
  • Usage of character string literals in the WHERE clause
  • Substitution Variables
  • Manipulate dates with the DATE functions
  • Perform arithmetic with date data
  • List the differences between single row and multiple row functions
  • Manipulate numbers with the ROUND, TRUNC, and MOD functions
  • Manipulate strings using character functions
  • Describe implicit and explicit data type conversion
  • Describe the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Nesting multiple functions
  • Usage of conditional IF THEN ELSE logic in a SELECT statement
  • Apply the NVL, NULLIF, and COALESCE functions to data
  • Divide the data in groups by using the GROUP BY clause
  • How to handle Null Values in a group function?
  • Exclude groups of date by using the HAVING clause
  • Usage of the aggregation functions in SELECT statements to produce meaningful reports
  • Describe the AVG, SUM, MIN, and MAX function
  • Join a table to itself by using a self join
  • Join Tables Using SQL:1999 Syntax
  • Create Cross Joins
  • View data that does not meet a join condition by using outer joins
  • Write SELECT statements to access data from more than one table
  • Use the ANY and ALL Operator in Multiple-Row Subqueries
  • Use a Subquery to Solve a Problem
  • Group Functions in a Subquery
  • Single-Row Subqueries
  • Multiple-Row Subqueries
  • Use the EXISTS Operator
  • Use the ORDER BY Clause in Set Operations
  • Describe the UNION, UNION ALL, INTERSECT, and MINUS Operators
  • Use a SET operator to combine multiple queries into a single query
  • Describe the SET operators
  • Add New Rows to a Table
  • Change the Data in a Table
  • How to save and discard changes with the COMMIT and ROLLBACK statements
  • Describe the FOR UPDATE Clause
  • Use the DELETE and TRUNCATE Statements
  • Implement Read Consistency
  • Describe the data types
  • Create Tables
  • How to drop a table?
  • Understand Constraints
  • How to alter a table?
  • Create a table using a subquery
  • Categorize Database Objects
  • How to drop a view?
  • Create and drop synonyms
  • Create and drop indexes
  • Create, use, and modify a sequence
  • Create, modify, and retrieve data from a view
  • Perform Data manipulation language (DML) operations on a view
  • Create a Simple Anonymous Block
  • Generate the Output from a PL/SQL Block
  • List the benefits of PL/SQL Subprograms
  • PL/SQL Overview
  • Overview of the Types of PL/SQL blocks
  • List the different Types of Identifiers in a PL/SQL subprogram
  • Bind Variables
  • %TYPE Attribute
  • Usage of the Declarative Section to Define Identifiers
  • Sequences in PL/SQL Expressions
  • Scalar Data Types
  • Use of variables to store data
  • SQL Functions in PL/SQL
  • Basic PL/SQL Block Syntax Guidelines
  • How to comment code?
  • Nested Blocks
  • Operators in PL/SQL
  • Data Type Conversion
  • The SQL Cursor concept
  • Data Manipulation in the Server Using PL/SQL
  • How to save and discard transactions?
  • Learn to use SQL Cursor Attributes to Obtain Feedback on DML
  • SELECT Statements in PL/SQL to Retrieve data
  • The Continue Statement
  • Conditional processing Using CASE Statements
  • While Loop Statement
  • Simple Loop Statement
  • For Loop Statement
  • Conditional processing Using IF Statements
  • Associative Arrays (INDEX BY Tables)
  • INDEX BY Table of Records
  • Insert and Update with PL/SQL Records
  • The %ROWTYPE Attribute
  • PL/SQL Records
  • INDEX BY Table Methods
  • Fetching data from the Cursor
  • FOR UPDATE Clause and WHERE CURRENT Clause
  • Cursor FOR loop
  • Understand Explicit Cursors
  • How to open the Cursor?
  • How to close the Cursor?
  • Explicit Cursor Attributes
  • Declare the Cursor
  • Propagate Exceptions
  • RAISE_APPLICATION_ERROR Procedure
  • Trap User-Defined Exceptions
  • Trap Predefined Oracle Server Errors
  • Trap Non-Predefined Oracle Server Errors
  • Handle Exceptions with PL/SQL
  • What are Exceptions?
  • What are Stored Procedures and Functions?
  • Execute a Simple Procedure
  • Create a Simple Procedure with IN parameter
  • Execute a Simple Function
  • Create a Simple Procedure
  • Differentiate between anonymous blocks and subprograms
  • Create a Simple Function

Students who attend this training should have experience as:

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

Familiarity with programming concepts
Familiarity with data processing concepts and techniques

After completing this course, students will be able to:

  • Write queries against single and multiple tables, manipulate data in tables and create database objects.
  • Use single row functions to customize output.
  • Invoke conversion functions and conditional expressions.
  • Use group functions to report aggregated data.
  • Create PL/SQL blocks of application code that can be shared by multiple forms, reports and data management applications.
  • Develop anonymous PL/SQL blocks, stored procedures and functions.
  • Declare identifiers and trap exceptions.
  • Use DML statements to manage data.
  • Use DDL statements to manage database objects.
  • Declare PL/SQL Variables.
  • Conditionally control code flow (loops, control structures).
  • Describe stored procedures and functions.
  • Retrieve row and column data from tables.

Register

Traditional Classroom   

    Show all

    Traditional Classroom   

      Show all

      Traditional Classroom   

        Show all

        Traditional Classroom   

          Show all

          OnlineLive   

            Show all

            Connected Classroom   

              Show all

              Flexible Mentored Learning   

                Show all

                Flexible Mentored Learning   

                  Show all

                  Flexible Mentored Learning   

                    Show all

                    Flexible Mentored Learning   

                      Show all

                      Not currently scheduled

                      Fast track the availability of this course.
                      Add to watch list or call 1300 794 006.

                      Need your team upskilled?
                      Request a Quote

                      blah is false
                      top