SQL & PL/SQL Fundamentals

Course Fees: $5110.00 excl. GST
Printed Manual: $175.00 excl. GST
Course Duration: 5 days
Course Manual

Sorry, no course dates found

In this course, students learn the fundamentals of SQL and PL/SQL and understand the benefits of the programming languages. Students learn the concepts of relational databases.

This course provides the essential SQL skills that allow developers to write queries against single and multiple tables, manipulate data in tables, and create database objects. Students also learn to use single row functions to customize output, use conversion functions and conditional expressions and use group functions to report aggregated data. Additionally, students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications.

Students learn to create anonymous PL/SQL blocks and are introduced to stored procedures and functions. They learn about declaring identifiers and trapping exceptions. Demonstrations and hands-on practice reinforce the fundamental concepts. Students use Oracle SQL Developer to develop these program units. SQL*Plus and JDeveloper are introduced as optional tools. This is appropriate for a 10g audience too. There are few minor changes between 10g and 11g features.


Learn To:

Retrieve row and column data from tables with the SELECT statement
Create reports of sorted and restricted data
Run data manipulation statements (DML) to update data in the Oracle Database 11g
Define and declare PL/SQL Variables
Write PL/SQL blocks of code
Conditionally control code flow (loops, control structures)

Application Developers
Forms Developer
Functional Implementer
PL/SQL Developer
Portal Developer
Reports Developer
Technical Consultant
Familiarity with programming concepts
SQL Fundamentals 2; Program with PL/SQL units
  • Identify the major structural components of the Oracle Database 11g
  • Retrieve row and column data from tables with the SELECT statement
  • Create reports of sorted and restricted data
  • Employ SQL functions to generate and retrieve customized data
  • Display data from multiple tables using the ANSI SQL 99 JOIN syntax
  • Create reports of aggregated data
  • Run data definition language (DDL) statements to create and manage schema objects
  • Run data manipulation statements (DML) to update data in the Oracle Database 11g
  • Design PL/SQL anonymous block that execute efficiently
  • Describe the features and syntax of PL/SQL
  • Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
  • Handle runtime errors
  • Describe stored procedures and functions
  • Use cursors to process rows

Introduction
Listing the features of Oracle Database 11g
Discussing the basic design, theoretical and physical aspects of a relational database
Describing the development environments for SQL
Describing Oracle SQL Developer
Describing the data set used by the course


Retrieving Data Using the SQL SELECT Statement
Listing the capabilities of SQL SELECT statements.
Generating a report of data from the output of a basic SELECT statement
Using arithmetic expressions and NULL values in the SELECT statement
Using Column aliases
Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
Displaying the table structure using the DESCRIBE command


Restricting and Sorting Data

Writing queries with a WHERE clause to limit the output retrieved
Using the comparison operators and logical operators
Describing the rules of precedence for comparison and logical operators
Using character string literals in the WHERE clause
Writing queries with an ORDER BY clause to sort the output
Sorting output in descending and ascending order
Using the Substitution Variables


Using Single-Row Functions to Customize Output
Differentiating between single row and multiple row functions
Manipulating strings using character functions
Manipulating numbers with the ROUND, TRUNC and MOD functions
Performing arithmetic with date data
Manipulating dates with the date functions


Using Conversion Functions and Conditional Expressions

Describing implicit and explicit data type conversion
Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
Nesting multiple functions
Applying the NVL, NULLIF, and COALESCE functions to data
Using conditional IF THEN ELSE logic in a SELECT statement


Reporting Aggregated Data Using the Group Functions
Using the aggregation functions in SELECT statements to produce meaningful reports
Using AVG, SUM, MIN, and MAX function
Handling Null Values in a group function
Creating queries that divide the data in groups by using the GROUP BY clause
Creating queries that exclude groups of date by using the HAVING clause


Displaying Data From Multiple Tables

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


Using Subqueries to Solve Queries

Using a Subquery to Solve a Problem
Executing Single-Row Subqueries
Using Group Functions in a Subquery
Using Multiple-Row Subqueries
Using the ANY and ALL Operator in Multiple-Row Subqueries


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


Manipulating Data
Adding New Rows to a Table Using the INSERT statement
Changing Data in a Table Using the UPDATE Statement
Using DELETE and TRUNCATE Statements
Saving and discarding changes with the COMMIT and ROLLBACK statements
Implementing Read Consistency
Using the FOR UPDATE Clause


Using DDL Statements to Create and Manage Tables
Categorizing Database Objects
Creating Tables using the CREATE TABLE Statement
Describing the data types
Describing Constraints
Creating a table using a subquery
Altering and Dropping a table


Creating Other Schema Objects

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


Introduction to PL/SQL

PL/SQL Overview
Benefits of PL/SQL Subprograms
Overview of the Types of PL/SQL blocks
Creating and Executing a Simple Anonymous Block
Generating Output from a PL/SQL Block


Declaring PL/SQL Identifiers

Different Types of Identifiers in a PL/SQL subprogram
Using the Declarative Section to Define Identifiers
Storing Data in Variables
Scalar Data Types
%TYPE Attribute
Bind Variables
Using Sequences in PL/SQL Expressions


Writing Executable Statements

Describing Basic PL/SQL Block Syntax Guidelines
Commenting Code
SQL Functions in PL/SQL
Data Type Conversion
Nested Blocks
Operators in PL/SQL


Interacting with the Oracle Server

Including SELECT Statements in PL/SQL to Retrieve data
Manipulating Data in the Server Using PL/SQL
The SQL Cursor concept
Using SQL Cursor Attributes to Obtain Feedback on DML
Saving and Discarding Transactions


Writing Control Structures

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


Working with Composite Data Types
Using PL/SQL Records
Using the %ROWTYPE Attribute
Inserting and Updating with PL/SQL Records
INDEX BY Tables
INDEX BY Table Methods
INDEX BY Table of Records


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


Handling Exceptions
Understanding Exceptions
Handling Exceptions with PL/SQL
Trapping Predefined Oracle Server Errors
Trapping Non-Predefined Oracle Server Errors
Trapping User-Defined Exceptions
Propagate Exceptions
RAISE_APPLICATION_ERROR Procedure


Creating Stored Procedures and Functions

Understanding Stored Procedures and Functions
Differentiate between anonymous blocks and subprograms
Create a Simple Procedure
Create a Simple Procedure with IN parameter
Create a Simple Function
Execute a Simple Procedure
Execute a Simple Function