TREINAMENTO DE TI

Este curso apresenta aos participantes os conceitos básicos de SQL utilizando tecnologia Oracle Database. Neste curso os participantes aprendem os conceitos de bancos de dados relacionais e linguagem de programação SQL. Este curso proporciona as habilidades básicas em SQL que permitem aos desenvolvedores criar consultas em tabelas simples e múltipla, manipular dados em tabelas, e criar objetos de banco de dados.

Os participantes também aprenderão a utilizar as funções de linha única para personalizar a saída, use funções de conversão e de expressões condicionais. Além disso, o uso de funções de grupo para comunicação de dados agregados é também abordado. Demonstrações e exercícios práticos reforçam os conceitos fundamentais.

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

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

·         Consultor Técnico

·         Usuário Final

·         Desenvolvedor em PL/SQL

Pré-requisitos Obrigatórios:

·         Familiarity with data processing concepts and techniques.

·         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.

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

Duração: 03 Dias