Caricamento...
ORA1139

Oracle Database: SQL and PL/SQL Fundamentals

Prezzo

€ 1,890.00
(Iva esclusa)

Scheda tecnica

Scarica

Giorni

4.5 gg

Questo Oracle Database: SQL e PL Fundamentals / SQL formazione eroga i fondamenti di SQL e PL / SQL con i benefici dei linguaggi di programmazione che utilizzano la tecnologia del database Oracle. Vengono esplorati i concetti di database relazionali.


OBIETTIVI

Al termine del corso gli allievi saranno in grado di:

  • Eseguire le istruzioni di manipolazione dei dati (DML) per aggiornare i dati nel database Oracle.
  • Progettazione PL / SQL blocco anonimo che si eseguono in modo efficiente.
  • Descrivere le caratteristiche e la sintassi di PL / SQL.
  • Maneggiare errori di runtime.
  • Descrivere procedure  stored e funzioni.
  • Utilizzare costrutti di programmazione PL / SQL e il controllo condizionale flusso di codice (loop, strutture di controllo, e cursori espliciti).
  • Utilizzare i cursori per elaborare le righe.
  • Identificare i principali componenti strutturali della Oracle Database 11g.
  • Recupera righe e colonne di dati da tabelle con l'istruzione SELECT.
  • Creare report di dati ordinati e ristretti.
  • Impiegare funzioni SQL per generare e recuperare dati personalizzati.
  • Visualizzazione dati da più tabelle utilizzando il ANSI SQL 99 ISCRIVITI sintassi.
  • Creare report di dati aggregati.
  • Eseguire Data Definition Language (DDL) istruzioni per creare e gestire gli oggetti dello schema.
Sviluppatori PL/SQL, Sviluppatori di applicazioni, Consulenti tecnici.

Per partecipare con profitto a questo corso è necessario che gli allievi possiedano i seguenti prerequisiti:

  • familiarità con i concetti e le tecniche di elaborazione dei dati
  • familiarità con i concetti di programmazione

Introduction

  • Overview of Oracle Database 12c 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
  • Use arithmetic expressions and NULL values in the SELECT statement
  • Invoke Column aliases
  • Concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
  • Display the table structure using the DESCRIBE command

Restricted and Sorted Data 

  • Write queries with a WHERE clause to limit the output retrieved
  • Describe the comparison operators and logical operators
  • Describe 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 the output in descending and ascending order
  • Substitution Variables

Usage of Single-Row Functions to Customize Output 

  • List the differences 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 the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Nesting multiple functions
  • Apply the NVL, NULLIF, and COALESCE functions to data
  • Usage of conditional IF THEN ELSE logic in a SELECT statement

Aggregated Data Using the Group Functions 

  • Usage of the aggregation functions in SELECT statements to produce meaningful reports
  • Describe 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

  • 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 

Usage of Subqueries to Solve Queries

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

SET Operators

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

Data Manipulation

  • Add New Rows to a Table
  • Change the Data in a Table 
  • Use the DELETE and TRUNCATE Statements
  • How to save and discard changes with the COMMIT and ROLLBACK statements
  • Implement Read Consistency
  • Describe the FOR UPDATE Clause 

DDL Statements to Create and Manage Tables

  • Categorize Database Objects
  • Create Tables 
  • Describe the data types
  • Understand Constraints
  • Create a table using a subquery
  • How to alter a table?
  • How to drop a table?

Other Schema Objects

  • Create, modify, and retrieve data from a view
  • Perform Data manipulation language (DML) operations on a view
  • How to drop a view?
  • Create, use, and modify a sequence
  • Create and drop indexes
  • Create and drop synonyms

Introduction to PL/SQL

  • PL/SQL Overview 
  • List the benefits of PL/SQL Subprograms
  • Overview of the Types of PL/SQL blocks
  • Create a Simple Anonymous Block 
  • Generate the Output from a PL/SQL Block

PL/SQL Identifiers

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

Write Executable Statements

  • Basic PL/SQL Block Syntax Guidelines
  • How to comment code?
  • SQL Functions in PL/SQL
  • Data Type Conversion
  • Nested Blocks 
  • Operators in PL/SQL

Interaction with the Oracle Server 

  • SELECT Statements in PL/SQL to Retrieve data
  • Data Manipulation in the Server Using PL/SQL 
  • The SQL Cursor concept
  • Learn to use SQL Cursor Attributes to Obtain Feedback on DML
  • How to save and discard transactions?

Control Structures

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

Usage of Composite Data Types 

  • PL/SQL Records 
  • The %ROWTYPE Attribute 
  • Insert and Update with PL/SQL Records
  • Associative Arrays (INDEX BY Tables)
  • INDEX BY Table Methods 
  • INDEX BY Table of Records 

Explicit Cursors

  • Understand Explicit Cursors
  • Declare the Cursor
  • How to open the Cursor?
  • Fetching data from the Cursor
  • How to close the Cursor?
  • Cursor FOR loop
  • Explicit Cursor Attributes
  • FOR UPDATE Clause and WHERE CURRENT Clause 

Exception Handling 

  • What are 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 and Functions

  • What are 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


Sede Data P
Milano 22/10/2018
Bologna 26/11/2018
Roma 17/12/2018