Retour en haut de la page

Oracle Database 11g - SQL and PL/SQL Fundamentals

5 jours - 35 heures

Objectifs

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

Participants

Developers

Prérequis

Cours pré-requis obligatoire(s) Familiarity with data processing concepts and techniques Familiarity with programming concepts

Pédagogie

La pédagogie est basée sur le principe de la dynamique de groupe avec alternance d'apports théoriques, de phases de réflexion collectives et individuelles, d'exercices, d'études de cas et de mises en situations observées. Formation / Action participative et interactive : les participants sont acteurs de leur formation notamment lors des mises en situation car ils s'appuient sur leurs connaissances, les expériences et mettront en œuvre les nouveaux outils présentés au cours de la session.

Profil de l’intervenant

Consultant-formateur expert. Suivi des compétences techniques et pédagogiques assuré par nos services.

Moyens techniques

Encadrement complet des stagiaires durant la formation. Espace d’accueil, configuration technique des salles et matériel pédagogique dédié pour les formations en centre. Remise d’une documentation pédagogique papier ou numérique à échéance de la formation.

Méthodes d’évaluation des acquis

Exercices individuels et collectifs durant la formation. La feuille d’émargement signée par demi-journée ainsi que l’évaluation des acquis de fin de stage sont adressées avec la facture.

Programme

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

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

Working With Bind Variables

Using Sequences in PL/SQL Expressions

Working With Substitution Variables

Writing Executable Statements

Describing Basic PL/SQL Block Syntax Guidelines

Commenting Code

Using SQL Functions in PL/SQL

Using Data Type Conversion

Referencing an Identifier Value in a Nested Block

Qualifying an Identifier with a Label

Using 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

Using Explicit Cursor Attributes

FOR UPDATE Clause and WHERE CURRENT Clause

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

Handling Exceptions

Understanding Exceptions

Handling Exceptions with PL/SQL

Trapping Predefined Oracle Server Errors

Trapping Non-Predefined Oracle Server Errors

Trapping User-Defined Exceptions

Propagating Exceptions

RAISE_APPLICATION_ERROR Procedure

Creating Stored Procedures and Functions

Understanding Stored Procedures and Functions

Differentiating between anonymous blocks and subprograms

Creating a Simple Procedure

Creating a Simple Procedure with IN parameter

Creating a Simple Function

Executing a Simple Procedure

Executing a Simple Function

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

Using the DISTINCT Keyword

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 the UNION, UNION ALL, INTERSECT, and MINUS Operators

Using the ORDER BY Clause in Set Operations

1206 log-526.pdf