• Presencial

Oracle Database 12c: SQL and PL/SQL Fundamentals


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

At the end of this course, participants will be able to:

  • Run data definition language (DDL) statements to create and manage schema objects
  • Run data manipulation statements (DML) to update data in the Oracle Database
  • Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
  • Use cursors to process rows
  • Create reports of sorted and restricted data
  • Describe stored procedures and functions
  • Describe the features and syntax of PL/SQL
  • Design PL/SQL anonymous block that execute efficiently
  • Display data from multiple tables using the ANSI SQL 99 JOIN syntax
  • Create reports of aggregated data
  • Employ SQL functions to generate and retrieve customized data
  • Handle runtime errors
  • Retrieve row and column data from tables with the SELECT statement

Cursos relacionados

Destinatários

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

Pré-requisitos

  • Familiarity with programming concepts
  • Familiarity with data processing concepts and techniques

Programa

  • Introduction
  • Working with Oracle Cloud Exadata Express Cloud Service
  • Retrieve Data using the SQL SELECT Statement
  • Restricted and Sorted Data
  • Usage of Single-Row Functions to Customize Output
  • Conversion Functions and Conditional Expression
  • Aggregated Data Using the Group Functions
  • Display Data From Multiple Tables
  • Usage of Subqueries to Solve Queries
  • SET Operators
  • Data Manipulation
  • DDL Statements to Create and Manage Tables
  • Other Schema Objects
  • Introduction to PL/SQL
  • PL/SQL Identifiers
  • Write Anonymous PL/SQL blocks
  • SQL statements in PL/SQL block
  • Control Structures
  • Composite Data Types
  • Explicit Cursors
  • Exception Handling
  • Stored Procedures and Functions

Introduction

  • 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

Working with Oracle Cloud Exadata Express Cloud Service

  • Accessing Cloud Database using SQL Workshop
  • Connecting to Exadata Express using Database Clients
  • Introduction to Oracle Database Exadata Express Cloud Service

Retrieve Data using the SQL SELECT Statement

  • 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

Restricted and Sorted Data

  • 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

Usage of Single-Row Functions to Customize Output

  • 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

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
  • Usage of conditional IF THEN ELSE logic in a SELECT statement
  • Apply the NVL, NULLIF, and COALESCE functions to data

Aggregated Data Using the Group Functions

  • 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

Display Data From Multiple Tables

  • 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

Usage of Subqueries to Solve Queries

  • 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

SET Operators

  • 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

Data Manipulation

  • 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

DDL Statements to Create and Manage Tables

  • 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

Other Schema 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

Introduction to PL/SQL

  • 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

PL/SQL Identifiers

  • 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

Write Anonymous PL/SQL blocks

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

SQL statements in PL/SQL block

  • 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

Control Structures

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

Composite Data Types

  • 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

Explicit Cursors

  • 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

Exception Handling

  • 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?

Stored Procedures and Functions

  • 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

Outras Edições

Inscreva-se

Dados Pessoais

Dados para faturação

Condições Gerais e Autorizações
  • Se a inscrição for cancelada com antecedência superior a 5 dias úteis da data de início do curso, a Rumos não efectuará qualquer facturação ao cliente.
  • Se a inscrição for cancelada com antecedência igual ou inferior a 5 dias, a Rumos procederá à facturação do valor da inscrição, dando no entanto ao cliente a possibilidade de transferir a sua inscrição para uma nova data. Esta garantia é válida por 6 meses, não se responsabilizando a Rumos se o curso não se repetir nesse período.
  • Pela não comparência do participante no curso sem cancelamento da inscrição a Rumos facturará o valor da inscrição, perdendo o formando o direito à participação no curso.
  • A viabilidade de realização de um curso depende de um número mínimo de inscrições. A Rumos reserva o direito de cancelar um curso por falta de viabilidade deste, notificando esse facto aos inscritos com o mínimo de 2 dias úteis de antecedência.

Os seus dados pessoais são recolhidos em conformidade com o Regulamento Geral de Proteção de Dados (RGPD).
Consente que os seus dados sejam utilizados, nos termos da nossa Politica de Privacidade, para o contacto/envio de:

Ações de informação, de marketing de produtos e serviços, como campanhas e eventos?

Para mais informações, consulte a Política de Privacidade do Grupo Rumos.
pode retirar o seu consentimento a qualquer momento através do botão Cancelar subscrição ou Unsubscribe que estão presentes em cada comunicação enviada, bem como exercer os direitos descritos na politica de privacidade

Para a subscrição da newsletter deverá desativar o bloqueio de pop-ups do seu browser.

Oracle Database 12c: SQL and PL/SQL Fundamentals

  • Datas
    23 Set a 27 Set 2019
    Porto
  • Horário
    Laboral
    das 09h30 às 17h30
  • Nº Horas
    30
  • Preço
    2250€

Oracle Database 12c: SQL and PL/SQL Fundamentals

Área

Dados

Como chegou até nós

Os seus dados pessoais são recolhidos em conformidade com o Regulamento Geral de Proteção de Dados (RGPD).
Consente que os seus dados sejam utilizados, nos termos da nossa Politica de Privacidade, para o contacto/envio de:

Ações de informação, de marketing de produtos e serviços, como campanhas e eventos?

Para mais informações, consulte a Política de Privacidade do Grupo Rumos.
pode retirar o seu consentimento a qualquer momento através do botão Cancelar subscrição ou Unsubscribe que estão presentes em cada comunicação enviada, bem como exercer os direitos descritos na politica de privacidade

Para a subscrição da newsletter deverá desativar o bloqueio de pop-ups do seu browser.