Cursos | BUAP - ORACLE PL/SQL DEVELOPER CERTIFIED ASSOCIATE (OCA) (PUEBLA) SEPT 2017 CANCELADO | Colegio de Postgrado en Desarrollo de Software AC

Módulos / Temario

ORACLE DATABASE: SQL FUNDAMENTALS I

What you will learn

This SQL Fundamentals training teaches you how to write SQL queries to access data stored in an Oracle Database schema. Learn to manipulate data and use various types of functions for customizing data output.

This course introduces you to the fundamentals of SQL using Oracle Database technology. Learn the concepts of relational databases and the powerful SQL programming language. You will learn essential SQL skills that allow developers to write queries against single and multiple tables, manipulate data in tables, and create database objects.

Participants learn to use single row functions to customize output, use conversion functions and conditional expressions. In addition, the usage of group functions to report aggregated data is also dealt with. Demonstrations and hands-on practice reinforce the fundamental concepts.

Learn To:

Retrieve row and column data from tables with the SELECT statement.

Create reports of sorted data.

Display data from multiple tables.

Use DML statements to manage data.

Use DDL statements to manage database objects.

Create reports of restricted data.

Benefits To You:

Gain expertise in relational database data management as you learn how to effectively use SQL commands against your business data. These features will help you query and manipulate data within the database, use the dictionary views to retrieve metadata and create reports about their schema objects.

This is appropriate for a 10g and 11g audience. There are minor changes between 10g and 11g features in SQL.

Audience

Application Developers

End Users

Forms Developer

Functional Implementer

PL/SQL Developer Portal Developer

Reports Developer

Technical Consultant

Related Training

Required Prerequisites

Familiarity with data processing concepts and techniques.

Course Objectives

Display data from multiple tables using the ANSI SQL 99 JOIN syntax.

Employ SQL functions to generate customized data.

Create reports of aggregated data.

Use the SET operators to create subsets of data.

Run data manipulation statements (DML) in Oracle Database 11g.

Run data definition language (DDL) statements to create schema objects.

Identify the major structural components of Oracle Database 11g.

Retrieve data from tables.

Create reports of sorted and restricted data.

Course Topics

Introduction

Overview of Oracle Database 11g and related products

Overview of relational database management concepts and terminologies

Introduction to SQL and its development environments

The HR schema and the tables used in this course

Oracle Database documentation and additional resources

Retrieve Data Using the SQL SELECT Statement

List the capabilities of SQL SELECT statements

Generate a report of data from the output of a basic SELECT statement

Usage of arithmetic expressions and NULL values

Implement Column aliases

Describe the concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword

Display the table structure using the DESCRIBE command

Restrict and Sort Data

Write queries with a WHERE clause to limit the output retrieved

Use the comparison operators and logical operators

Identify the rules of precedence for comparison and logical operators

Usage of character string literals in the WHERE clause

Write queries with an ORDER BY clause

Sort output in descending and ascending order

Substitution Variables

Use Single-Row Functions to Customize Output

Differentiate between single row and multiple row functions

Manipulate strings using character functions

Manipulate numbers with the ROUND, TRUNC, and MOD functions

Perform arithmetic with date data

Manipulate dates with the DATE functions

Conversion Functions and Conditional Expressions

Describe implicit and explicit data type conversion

Describe TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

Nesting multiple functions

Apply the NVL, NULLIF, and COALESCE functions to data

Use conditional IF THEN ELSE logic

Aggregated Data Using Group Functions

How aggregation functions help to produce meaningful reports?

Use the AVG, SUM, MIN, and MAX function

How to handle Null Values in a group function?

Divide the data in groups by using the GROUP BY clause

Exclude groups of date by using the HAVING clause

Display Data From Multiple Tables Using Joins

Write SELECT statements to access data from more than one table

Join Tables Using SQL:1999 Syntax

View data that does not meet a join condition by using outer joins

Join a table to itself by using a self join Create Cross Joins

Use Sub-queries to Solve Queries

Use a Subquery to Solve a Problem

Execute Single-Row Sub-queries

Deploy Group Functions in a Sub-query

Multiple-Row Subqueries

Use ANY and ALL Operator in Multiple-Row Sub-queries

Use EXISTS Operator

SET Operators

What are SET operators?

Use a SET operator to combine multiple queries into a single query

Use UNION, UNION ALL, INTERSECT, and MINUS Operator

Use ORDER BY Clause in Set Operations

Data Manipulation

Add New Rows to a Table

Change the Data in a Table

Use DELETE and TRUNCATE Statements

Save and discard changes with the COMMIT and ROLLBACK statements

Implement Read Consistency

Describe the FOR UPDATE Clause

Use DDL Statements to Create and Manage Tables

Categorize Database Objects

Create Tables using the CREATE TABLE Statement

Identify the data types

Describe Constraints

Create a table using a subquery How to alter a table?

Drop a table

Other Schema Objects

Create, modify, and retrieve data from a view

Perform Data manipulation language (DML) operations on a view

Drop a view

Create, use, and modify a sequence

Create and maintain indexes

Create and drop synonyms

ORACLE DATABASE: PROGRAM WITH PL/SQL

What you will learn

This Oracle Database: Program with PL/SQL training starts with an introduction to PL/SQL and then explores the benefits of this powerful programming language. Through hands-on instruction from expert Oracle instructors, you'll learn to develop stored procedures, functions, packages and more.

Learn To:

Conditionally control code flow (loops, control structures).

Use PL/SQL packages to group and contain related constructs.

Create triggers to solve business challenges.

Use some of the Oracle supplied PL/SQL packages to generate screen output and file output.

Create anonymous PL/SQL blocks, functions and procedures.

Declare PL/SQL Variables.

Benefits to You

Ensure fast, reliable, secure and easy to manage performance. Optimize database workloads, lower IT costs and deliver a higher quality of service by enabling consolidation onto database clouds.

Use Oracle SQL Developer

You’ll use Oracle SQL Developer to develop these program units. SQL*Plus and JDeveloper are introduced in this course as optional tools.

Course Bundle

Note: This course is a combination of Oracle Database: PL/SQL Fundamentals and Oracle Database: Develop PL/SQL Program Units courses.

Audience

Application Developers

Database Administrators

Developer

Forms Developer

PL/SQL Developer

Portal Developer

System Analysts

Technical Consultant

Related Training

Required Prerequisites

Oracle Database: Introduction to SQL

Oracle Database: SQL Workshop I NEW

Oracle Database: SQL Workshop II NEW

Suggested Prerequisites

Previous programming experience

Course Objectives

Use conditional compilation to customize the functionality in a PL/SQL application without removing any source code

Design PL/SQL packages to group related constructs

Create overloaded package subprograms for more flexibility

Design PL/SQL anonymous blocks that execute efficiently

Use the Oracle supplied PL/SQL packages to generate screen output, file output and mail output

Write dynamic SQL for more coding flexibility

Describe the features and syntax of PL/SQL

Create and debug stored procedures and functions

Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)

Manage dependencies between PL/SQL subprograms

Handle runtime errors

Create triggers to solve business challenges

Course Topics

Introduction

Course Objectives

Course Agenda

Describe the Human Resources (HR) Schema

PL/SQL development environments available in this course

Introduction to SQL Developer

Introduction to PL/SQL

Overview of PL/SQL

Identify the benefits of PL/SQL Subprograms

Overview of the types of PL/SQL blocks

Create a Simple Anonymous Block

How to generate output from a PL/SQL Block?

Declare PL/SQL Identifiers

List the different Types of Identifiers in a PL/SQL subprogram

Usage of the Declarative Section to Define Identifiers

Use variables to store data

Identify Scalar Data Types

The %TYPE Attribute

What are Bind Variables?

Sequences in PL/SQL Expressions

Write Executable Statements

Describe Basic PL/SQL Block Syntax Guidelines

Learn to Comment the Code

Deployment of SQL Functions in PL/SQL How to convert Data Types?

Describe Nested Blocks

Identify the Operators in PL/SQL

Interaction with the Oracle Server

Invoke SELECT Statements in PL/SQL

Retrieve Data in PL/SQL

SQL Cursor concept

Avoid Errors by using Naming Conventions when using Retrieval and DML Statements

Data Manipulation in the Server using PL/SQL

Understand the SQL Cursor concept

Use SQL Cursor Attributes to Obtain Feedback on DML

Save and Discard Transactions

Control Structures

Conditional processing using IF Statements

Conditional processing using CASE Statements

Describe simple Loop Statement

Describe While Loop Statement

Describe For Loop Statement

Use the Continue Statement

Composite Data Types

Use PL/SQL Records

The %ROWTYPE Attribute

Insert and Update with PL/SQL Records

INDEX BY Tables

Examine INDEX BY Table Methods

Use INDEX BY Table of Records

Explicit Cursors

What are Explicit Cursors?

Declare the Cursor

Open the Cursor

Fetch data from the Cursor

Close the Cursor

Cursor FOR loop

The %NOTFOUND and %ROWCOUNT Attributes

Describe the FOR UPDATE Clause and WHERE CURRENT Clause

Exception Handling

Understand Exceptions

Handle Exceptions with PL/SQL

Trap Predefined Oracle Server Errors

Trap Non-Predefined Oracle Server Errors

Trap User-Defined Exceptions

Propagate Exceptions

RAISE_APPLICATION_ERROR Procedure

Stored Procedures

Create a Modularized and Layered Subprogram Design

Modularize Development With PL/SQL Blocks

Understand the PL/SQL Execution Environment

List the benefits of using PL/SQL Subprograms

List the differences between Anonymous Blocks and Subprograms

Create, Call, and Remove Stored Procedures

Implement Procedures Parameters and Parameters Modes

View Procedure Information

Stored Functions and Debugging Subprograms

Create, Call, and Remove a Stored Function

Identify the advantages of using Stored Functions

Identify the steps to create a stored function

Invoke User-Defined Functions in SQL Statements

Restrictions when calling Functions

Control side effects when calling Functions

View Functions Information

How to debug Functions and Procedures?

Packages

Listing the advantages of Packages

Describe Packages

What are the components of a Package?

Develop a Package

How to enable visibility of a Package’s Components?

Create the Package Specification and Body using the SQL CREATE Statement and SQL Developer

Invoke the Package Constructs

View the PL/SQL Source Code using the Data Dictionary

Deploying Packages

Overloading Subprograms in PL/SQL

Use the STANDARD Package

Use Forward Declarations to solve Illegal Procedure Reference

Implement Package Functions in SQL and Restrictions

Persistent State of Packages

Persistent State of a Package Cursor

Control side effects of PL/SQL Subprograms

Invoke PL/SQL Tables of Records in Packages

Implement Oracle-Supplied Packages in Application Development

What are Oracle-Supplied Packages?

Examples of some of the Oracle-Supplied Packages How does the DBMS_OUTPUT Package work?

Use the UTL_FILE Package to Interact with Operating System Files

Invoke the UTL_MAIL Package

Write UTL_MAIL Subprograms

DynamicSQL

The Execution Flow of SQL What is Dynamic SQL?

Declare Cursor Variables

Dynamically Executing a PL/SQL Block

Configure Native Dynamic SQL to Compile PL/SQL Code How to invoke DBMS_SQL Package?

Implement DBMS_SQL with a Parameterized DML Statement

Dynamic SQL Functional Completeness

Design Considerations for PL/SQL Code

Standardize Constants and Exceptions

Understand Local Subprograms

Write Autonomous Transactions

Implement the NOCOPY Compiler Hint

Invoke the PARALLEL_ENABLE Hint

The Cross-Session PL/SQL Function Result Cache

The DETERMINISTIC Clause with Functions

Usage of Bulk Binding to Improve Performance

Triggers

Describe Triggers

Identify the Trigger Event Types and Body

Business Application Scenarios for Implementing Triggers

Create DML Triggers using the CREATE TRIGGER Statement and SQL Developer

Identify the Trigger Event Types, Body, and Firing (Timing)

Differences between Statement Level Triggers and Row Level Triggers

Create Instead of and Disabled Triggers

How to Manage, Test and Remove Triggers?

Creating Compound, DDL, and Event Database Triggers

What are Compound Triggers?

Identify the Timing-Point Sections of a Table Compound Trigger

Understand the Compound Trigger Structure for Tables and Views

Implement a Compound Trigger to Resolve the Mutating Table Error

Comparison of Database Triggers to Stored Procedures

Create Triggers on DDL Statements

Create Database-Event and System-Events Triggers

System Privileges Required to Manage Triggers

PL/SQL Compiler

What is the PL/SQL Compiler?

Describe the Initialization Parameters for PL/SQL Compilation

List the new PL/SQL Compile Time Warnings

Overview of PL/SQL Compile Time Warnings for Subprograms

List the benefits of Compiler Warnings

List the PL/SQL Compile Time Warning Messages Categories

Setting the Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGS Initialization Parameter, and the DBM

View Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary Views

Manage Dependencies

Overview of Schema Object Dependencies

Query Direct Object Dependencies using the USER_DEPENDENCIES View

Query an Object’s Status

Invalidation of Dependent Objects

Display the Direct and Indirect Dependencies

Fine-Grained Dependency Management in Oracle Database 12c

Understand Remote Dependencies

Recompile a PL/SQL Program Unit

BUAP - ORACLE PL/SQL DEVELOPER CERTIFIED ASSOCIATE (OCA) (PUEBLA) SEPT 2017 CANCELADO

Módulos / Temario

DURACIÓN:

COSTO DE INSCRIPCIÓN:

MENSUALIDAD:

COSTO DE TITULACIÓN:

NOTICIAS

ARTÍCULOS