TREINAMENTO DE TI

Este curso é voltado para desenvolvedores com habilidades básicas de linguagemSQL. Neste curso, os participantes aprendem a controlar os privilégios no nível do objeto e do sistema. Além disso, aqui, a criação de índices, restrições e alteração de objetos existentes do esquema também são abordados. Os participantes aprendem acriar e consultar tabelas externas e usar os recursos avançados de SQL para consultar e manipular dados dentro do banco de dados.

Os participantes ganham a habilidade de usar o dicionário vistas a recuperarmetadados e criar relatórios sobre os objetos do seu esquema. Os participantestambém aprendem a escrever instruções SQL que incluem as novas funçõesintroduzidas no Oracle Database 11g para melhorar a funcionalidade de suporte de expressões regulares.

Neste curso, os alunos utilizam o Oracle SQL Developer como a principal ferramenta eSQL * Plus está disponível como uma ferramenta opcional.

Este é apropriado para um público 10g e 11g. Há pequenas alterações entre 10g e11g no SQL.

·         Administrador de Banco de Dados

·         Consultor Técnico

Pré-requisitos Obrigatórios:

·         Oracle Database: SQL Fundamentals I

·         Oracle Database: SQL Fundamentals I

·         Manage schema objects.

·         Manage objects with data dictionary views.

·         Manipulate large data sets in the Oracle database by using subqueries.

·         Manage data in different time zones.

·         Write multiple-column subqueries.

·         Use scalar and correlated subqueries.

·         Use the regular expression support in SQL.

·         Add new users with different levels of access privileges.

·         Control database access to specific objects.

Introduction

·         Course Prerequisites

·         Course Agenda

·         Human Resources (HR) Schema

·         Review of Using Oracle SQL Developer

·         SQL Statements in SQL*Plus

·         Review of some basic concepts of SQL

Control User Access

·         Identify the System and Object Privileges

·         Create Users

·         Grant System Privileges

·         Create and Grant Privileges to a Role

·         Change Your Password

·         Grant Object Privileges

·         How to Pass On Your Privileges?

·         Revoke Object Privileges

Management of Schema Objects

·         Add, Modify, and Drop a Column

·         Add, Drop, and Defer a Constraint

·         Enable and Disable a Constraint

·         Create and Remove Indexes

·         Create a Function-Based Index

·         Perform Flashback Operations

·         Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP

·         Query External Tables

Manage Objects with Data Dictionary Views

·         What is the Data Dictionary?

·         How to Use the Dictionary Views?

·         USER_OBJECTS and ALL_OBJECTS Views

·         View Table and Column Information

·         Query the dictionary views for constraint information

·         Query the dictionary views for view, sequence, index and synonym information

·         Add a comment to a table

·         Query the dictionary views for comment information

Manipulate Large Data Sets

·         Use Subqueries to Manipulate Data

·         Retrieve Data Using a Subquery as Source

·         Insert data Using a Subquery as a Target

·         Use the WITH CHECK OPTION Keyword on DML Statements

·         List the types of Multitable INSERT Statements

·         Identify Multitable INSERT Statements

·         Merge rows in a table

·         How to track Changes in Data over a period of time?

Data Management in Different Time Zones

·         What are Time Zones?

·         The CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP functions

·         Compare Date and Time in a Session’s Time Zone

·         Describe DBTIMEZONE and SESSIONTIMEZONE

·         List the differences between DATE and TIMESTAMP

·         Identify the INTERVAL Data Types

·         Usage of EXTRACT, TZ_OFFSET and FROM_TZ functions

·         Use TO_TIMESTAMP, TO_YMINTERVAL, and TO_DSINTERVAL

Retrieve Data Using Subqueries

·         Multiple-Column Subqueries

·         Pairwise and Nonpairwise Comparison

·         Usage of Scalar Subquery Expressions

·         Solve problems with Correlated Subqueries

·         Update and Delete Rows Using Correlated Subqueries

·         Use the EXISTS and NOT EXISTS operators

·         Use the WITH clause

·         Use Recursive WITH clause

Regular Expression Support

·         Invoke Regular Expressions Functions and Conditions in SQL

·         Implement Meta Characters with Regular Expressions

·         Perform a Basic Search using the REGEXP_LIKE function

·         Find patterns using the REGEXP_INSTR function

·         Extract Substrings using the REGEXP_SUBSTR function

·         Replace Patterns Using the REGEXP_REPLACE function

·         How to use Sub-Expressions with Regular Expression Support?

·         The REGEXP_COUNT function

Duração: 02 Dias