BANCO DE DADOS

Advanced SQL & Database Tuning

BANCO DE DADOS

Advanced SQL & Database Tuning

 SALVIO PADLIPSKAS
CONSULTOR DE TI

Engenheiro de Software pelo IPT USP, com experiência de 29 anos em

ambientes corporativos e 24 anos ministrando aulas em diversas
disciplinas na área de TI.

Atualmente trabalhando como Consultor de TI, atuando na área de

dados com Business Intelligence (BI), Arquitetura de DW, Modelagem
Dimensional e ERP.

Profissional Oracle OCP SQL e PL/SQL.

salvio@[Link]

[Link]/in/salvio-padlipskas-a866a2
CONSIDERAÇÕES INICIAIS: SQL AVANÇADO & DATABASE TUNING

• Adquirir proficiência em Oracle SQL avançado e realizar tuning em instruções SQL são
habilidades essenciais para resolver problemas complexos, sendo o primeiro passo para se
tornar um verdadeiro profissional de tuning.

• Este curso irá abordar temas avançados em instruções SQL, tratando desde junções simples
e complexas, passando por subconsultas e visões, até desvendar o trabalho feito pelo
processador de consultas sobre as instruções SQL, concluindo com técnicas de ajustes
visando melhorar o desempenho das consultas.
AGENDA

1 AULA 1 Instruções SQL complexas

2 AULA 2 Instruções SQL: Subconsultas e Views

3 AULA 3 Aperfeicoando o conhecimento em Instruções SQL: Explorando o processador de consultas

4 AULA 4 Instruções SQL: Estatísticas dos objetos e técnicas de Tuning

Instruções SQL Complexas

Depois de completar essa 1ª aula, você poderá fazer o seguinte:

▪ Compreender a estrutura completa de instruções SQL

▪ Utilizar funções de desvio de fluxo (IF) em consultas SQL

▪ Indo além do básico, utilizando funções relevantes em consultas SQL

▪ Trabalhar com mais de duas tabelas (JOIN)

▪ Aprender a agrupar valores com funcões de grupo

▪ Filtrar os resultados exibidos utilizando WHERE e HAVING

▪ Exercícios práticos hands on

 INSTRUÇÃO SQL
SINTAXE COMPLETA

Sequência de avaliação das cláusulas:

▪ cláusula WHERE

▪ cláusula GROUP BY

▪ cláusula HAVING
 INSTRUÇÃO SQL COMPLETA
DICAS DUCAS

▪ Instruções SQL sem distinção entre maiúsculas e minúsculas.

▪ Instruções SQL podem estar em uma ou mais linhas.

▪ Palavras chaves não podem ser abreviadas ou divididas entre as linhas.

▪ Normalmente as cláusulas são colocadas em linhas separadas.

▪ A endentação é utilizada para aperfeiçoar a legibilidade.

INSTRUÇÃO SQL COMPLETA
EXEMPLOS PRÁTICOS: PROJETO DBURGER
 INSTRUÇÃO SQL COMPLETA
IMPORTÂNCIA DAS FUNÇÕES SQL

Funções de Uma Única Linha Funções de Várias Linhas

 INSTRUÇÃO SQL COMPLETA
EXEMPLO PRÁTICO: PROJETO DBURGER

Crie uma consulta SQL que exiba o

código do cliente “pessoa física”,
nome, primeiro nome, sobrenome
e a quantidade de estrelas.
Classifique a consulta por nome
do cliente.

Analise o poder da função INSTR,

LENGTH e SUBSTR para resolver
esse problema complexo.
 INSTRUÇÃO SQL COMPLETA
EXEMPLO PRÁTICO: PROJETO DBURGER

Crie uma consulta SQL que exiba o código do cliente “pessoa física”, o nome completo, primeiro
nome, sobrenome e a quantidade de estrelas. Classifique a consulta por número de estrelas.

Para a quantidade de estrelas utilize a seguinte regra:

▪ Se o número de estrelas = 5 então deve-se exibir o texto "Cliente Diamante"
▪ Se o número de estrelas = 4 então deve-se exibir o texto "Cliente Ouro"
▪ Se o número de estrelas = 3 então deve-se exibir o texto "Cliente Prata"
▪ Se o número de estrelas = 2 então deve-se exibir o texto "Cliente Bronze"
▪ Se o número de estrelas = 1 então deve-se exibir o texto "Cliente Iniciante"
▪ Caso não seja nenhum desses, exiba o texto "Cliente sem estrelas"

Analise o poder da função DECODE para resolver esse

problema complexo.
 INSTRUÇÃO SQL COMPLETA
EXEMPLO PRÁTICO: PROJETO DBURGER

Crie uma consulta SQL que exiba o código do cliente “pessoa física”, o nome completo, primeiro
nome, sobrenome e a quantidade de estrelas. Classifique a consulta por número de estrelas.

Para a quantidade de estrelas utilize a seguinte regra:

▪ Caso o número de estrelas esteja e ntre null e 1,99 temos "Cliente Start"
▪ Se o número de estrelas esteja entre 2 e 3,99 "Cliente Potencial“
▪ Se o número de estrelas esteja entre 4 e 5 "Cliente VIP"

Analise o poder da função CASE para resolver esse

problema complexo que trata de comparação de
valores dispersos.
 INSTRUÇÃO SQL COMPLETA
EXEMPLO PRÁTICO: PROJETO DBURGER * EXTRAÇÃO DE CONTEÚDOS SIGNIFICATIVOS

Crie uma consulta SQL que exiba o código do cliente

pessoa física, nome completo, data de nascimento
do cliente e qual é a sua idade nesse momento.
Classifique a consulta por clientes que tenham a
maior idade e depois por ordem alfabética de nome
completo.

Analise o poder da função

MONTHS_BETWEEN e SYSDATE para
resolver esse problema complexo que
trata de cálculos.
 INSTRUÇÃO SQL COMPLETA
EXEMPLO PRÁTICO: PROJETO DBURGER * EXTRAÇÃO DE CONTEÚDOS SIGNIFICATIVOS

Crie uma consulta SQL que exiba o código do

cliente pessoa física, nome completo, data de
nascimento do cliente e qual é a sua idade
nesse momento. Exiba também em qual dia da
semana o cliente nasceu. Classifique a consulta
por dia da semana.

Analise o poder da função TO_CHAR

para resolver esse problema complexo
que trata de extração de valores.
EXIBINDO DADOS DE VÁRIAS TABELAS
JUNÇÕES
INSTRUÇÃO SQL COMPLETA
CONCEITO DE JUNÇÕES
 INSTRUÇÃO SQL COMPLETA
EXEMPLO PRÁTICO: PROJETO DBURGER * INNER JOIN

Crie uma consulta SQL que exiba o código do cliente, nome, data de nascimento do cliente e qual é a
sua idade nesse momento, somente para clientes pessoas físicas. Exiba também em qual dia da
semana o cliente nasceu. Classifique a consulta por dia da semana.

SQL99 SQL92
SELECT SELECT
CLI.NR_CLIENTE CODIGO_CLIENTE, CLI.NR_CLIENTE CODIGO_CLIENTE,
CLI.NM_CLIENTE "NOME DO CLIENTE", CLI.NM_CLIENTE "NOME DO CLIENTE",
CLIF.DT_NASCIMENTO DATA_NASCIMENTO, CLIF.DT_NASCIMENTO DATA_NASCIMENTO,
TO_CHAR(CLIF.DT_NASCIMENTO, 'DAY') DIA_SEMANA_NASCIMENTO, TO_CHAR(CLIF.DT_NASCIMENTO, 'DAY') DIA_SEMANA_NASCIMENTO,
TRUNC(MONTHS_BETWEEN(SYSDATE,CLIF.DT_NASCIMENTO) / 12 ,0) IDADE_ATUAL TRUNC( MONTHS_BETWEEN( SYSDATE, CLIF.DT_NASCIMENTO) / 12 ,0) IDADE_ATUAL
FROM DB_CLIENTE CLI INNER JOIN DB_CLI_FISICA CLIF FROM DB_CLIENTE CLI,
ON (CLI.NR_CLIENTE = CLIF.NR_CLIENTE) DB_CLI_FISICA CLIF
ORDER BY TO_CHAR(CLIF.DT_NASCIMENTO, 'D') ASC; WHERE CLI.NR_CLIENTE = CLIF.NR_CLIENTE
ORDER BY TO_CHAR(CLIF.DT_NASCIMENTO, 'D') ASC;
INSTRUÇÃO SQL COMPLETA
CONCEITO DE PRODUTO CARTESIANO
 INSTRUÇÃO SQL COMPLETA
EXEMPLO 1: GERANDO UM PRODUTO CARTESIANO (AUSÊNCIA DE JUNÇÃO)

Crie uma consulta SQL que exiba o código do

cliente pessoa física, nome, data de nascimento
do cliente e qual é a sua idade nesse momento.
Classifique a consulta por clientes mais velhos.
Exiba também em qual dia da semana o cliente
nasceu. Classifique a consulta por código do
cliente.
 INSTRUÇÃO SQL COMPLETA
EXEMPLO 2: GERANDO UM PRODUTO CARTESIANO (NECESSIDADE NEGÓCIO)

Vamos analisar todas as formas de pagamento que

uma loja Dburger pode oferecer ao seu cliente.
Exiba o Código, o nome da loja e a forma de
pagamento. Exiba somente as formas de
pagamento que estão com status = 'A'(ativo).
 INSTRUÇÃO SQL COMPLETA
JUNÇÕES EXTERNAS (LEFT JOIN e RIGTH JOIN)
Crie uma consulta SQL que exiba todas as categorias, subcategorias e descrição dos produtos. Caso não exista
produto cadastrado para a subcategoria ou categoria, favor exibir essas categorias e subcategorias. Classifique a
consulta por código da categoria e código da subcategoria.

SQL99 Padrão Oracle (SQL92)

 INSTRUÇÃO SQL COMPLETA
JUNÇÕES EXTERNAS (SELF JOIN)
Crie uma consulta SQL que exiba as informações da loja número 1 e todos os funcionários cadastrados. Exiba o
número e nome da loja, o código e nome do funcionário, seu cargo e os seguintes dados do superior imediato.
código e nome do superior e o cargo ocupado por ele. Classifique o resultado em ordem de código do funcionário.

SQL99 Padrão Oracle (SQL92)

AGREGANDO DADOS USANDO
FUNÇÕES DE GRUPO
 INSTRUÇÃO SQL COMPLETA
FUNÇÕES DE GRUPO

Depois de completar esses comandos, será possível fazer:

▪ Identificar as funções de grupo disponíveis

▪ Descrever o uso de funções de grupo

▪ Agrupar dados usando a cláusula GROUP BY

▪ Incluir ou excluir linhas agrupadas usando a cláusula HAVING

INSTRUÇÃO SQL COMPLETA
TIPOS DE FUNÇÕES DE GRUPO

▪ AVG
▪ COUNT
▪ MAX
▪ MIN
▪ STDDEV
▪ SUM
▪ VARIANCE
INSTRUÇÃO SQL COMPLETA
USANDO FUNÇÕES DE GRUPO: SINTAXE
INSTRUÇÃO SQL COMPLETA
USANDO FUNÇÕES DE GRUPO: SINTAXE
 INSTRUÇÃO SQL COMPLETA: CLÁUSULA GROUP BY
EXEMPLO PRÁTICO: CRIANDO GRUPO DE DADOS

Crie uma consulta SQL que exiba o valor total

gasto* mensal com funcionários da loja
número 11, agrupado por departamento. Exiba
o código e nome do departamento, quantos
funcionários tem cada departamento e qual é
o gasto mensal que temos agrupado por
departamento. Exiba as informações
classificadas pelos departamentos que gastam
mais. Caso o departamento não tenha
funcionário cadastrado, exiba o valor ZERO (0).

*o valor gasto mensal pode ser considerado como sendo a soma

do valor do salario bruto + o valor do salário família.
 INSTRUÇÃO SQL COMPLETA: CLÁUSULA HAVING
EXEMPLO PRÁTICO: CRIANDO GRUPO DE DADOS E RESTRINGINDO AGRUPAMENTOS

Crie uma consulta SQL que exiba o valor total gasto*

mensal com funcionários da loja número 11, agrupado por
departamento. Exiba o código e nome do departamento,
quantos funcionários tem cada departamento e qual é o
gasto mensal que temos agrupado por departamento.
Exiba SOMENTE os departamentos que tem mais de 1
funcionário cadastrado. Exiba as informações classificadas
pela quantidade de funcionários que tem no
departamento.

*o valor gasto mensal pode ser considerado como sendo a soma

do valor do salario bruto + o valor do salário família.
INSTRUÇÃO SQL COMPLETA
RESUMO
SQL ANALÍTICO
SQL ANALÍTICO
CONCEITO

▪ SQL Analitico é um conjunto de recursos visando a extração de dados, principalmente, em

ambientes envolvendo data warehouse e também em ambientes transacionais, visando a
obtenção de informações com maior grau de valor.

▪ A implementação de SQL Analítico é dada através de funções utilizadas principalmente em

consultas.

▪ O comitê ANSI , regulamenta a construção de funções analíticas.

SQL ANALÍTICO
CONCEITO

▪ As funções analíticas calculam um valor para cada linha que varia com os valores
das outras linhas do grupo.

▪ Diferem das funções de grupo pelas seguintes razões:

▪ Devolvem um valor para cada linha, em vez de um valor para todo o grupo.
▪ O grupo de linhas é escolhido por uma cláusula "order by" ou partição de tabela.
▪ A função analítica utilizam as linhas do grupo para efetuar cálculos "deslizantes" que
têm em conta os valores das outras linhas do grupo e as posições relativas das linhas
entre si.

▪ O conjunto de operações que permitem o cálculo do valor da função analítica são

as últimas a serem efetuadas na execução de uma consulta.
SQL ANALÍTICO
ALGUMAS FUNÇÕES ANALÍTICAS

▪ ROW_NUMBER( )
▪ RANK( )
▪ RATIO_TO_REPORT( )
▪ CUBE()
 SQL ANALÍTICO
EXEMPLO FUNÇÃO ROW_NUMBER()

Crie uma consulta SQL que exiba o valor médio de

venda agrupado por categoria e subcategoria de
produto durante o ano de 2021. Exiba os nomes
das categorias e subcategorias e o valor médio de
venda classificado pelas maiores vendas dentro da
categoria. Não se esqueça também da sequência de
numeração para cada linha exibida.
Analise o poder da função analítica ROW_NUMBER()
para resolver esse problema complexo que trata de
ranking de valores. Essa função atua depois da
extração dos dados, numerando as linhas segundo o
critério indicado na expressão da função analitica.
Torna possível que no mesmo comando haja
diferentes critérios de ordenação.
 SQL ANALÍTICO
EXEMPLO FUNÇÃO RANK()

Crie uma consulta SQL que exiba o valor médio de venda

agrupado por categoria e subcategoria de produto
durante o ano de 2021. Exiba os nomes das categorias e
subcategorias e o valor médio de venda classificado
pelas maiores vendas dentro da categoria. Não se
esqueça também da sequência de numeração para cada
linha exibida e o ranking da subcategoria dentro de sua
respectiva categoria.

Analise o poder da função analítica RANK()

para resolver esse problema complexo que
trata de ranking.
 SQL ANALÍTICO
EXEMPLO FUNÇÃO RATIO_TO_REPORT()

Crie uma consulta SQL que exiba o valor médio de

venda agrupado por categoria e subcategoria de
produto durante o ano de 2021. Exiba os nomes das
categorias e subcategorias e o valor médio de venda
classificado pelas maiores vendas dentro da
categoria. Não se esqueça também da sequência de
numeração para cada linha exibida e o ranking da
subcategoria dentro de sua respectiva categoria. Por
fim, exiba tambem o percentual do valor da
subcategoria diante dos valores médios totais da
DBurger.
Analise o poder da função analítica
RATIO_TO_REPORT() para resolver esse problema
complexo que trata de % sobre valores totais.
 SQL ANALÍTICO
AGRUPAMENTO AVANÇADO
 SQL ANALÍTICO
AGRUPAMENTO AVANÇADO

▪ O agrupamento básico de dados em SQL é dado pela clausula GROUP BY em uma consulta. O
agrupamento permite que dados possam ser consolidados e apresentados em uma consulta.

▪ Neste tópico abordaremos o agrupamento avançado de dados através de funções

incorporadas a clausula GROUP BY.

▪ Veremos as extensões CUBE e ROLLUP e GROUPING SETS para o comando GROUP BY.
SQL ANALÍTICO
AGRUPAMENTO AVANÇADO: CUBE

▪ Este operador permite visualizar dados totalizados e seus respectivos agrupamentos.

▪ O resultado de uma operação envolvendo CUBE pode ocasionar um relatório estilo Matriz com
quebras

▪ O operador CUBE pode ser utilizado em consultas que envolvam agregação e totalização de dados.

▪ A sintaxe para o operador CUBE é a seguinte:

 SQL ANALÍTICO
AGRUPAMENTO AVANÇADO: CUBE

Crie uma consulta SQL que exiba o valor médio de venda agrupado
por categoria e subcategoria de produto durante o ano de 2021.
Exiba os nomes das categorias e subcategorias e o valor médio de
venda classificado pelas maiores vendas dentro da categoria.
Exiba também os valores subtotais por categoria e subcategoria de
produto.

Analise o poder do agrupamento avançado CUBE() para resolver esse

problema complexo que trata de valor subtotal.
 ADVANCED SQL
EXERCÍCIOS AULA 1 HANDS ON
EXERCÍCIOS REVISÃO JUNÇÕES
ADVANCED SQL
EXERCÍCIOS HANDS ON

Você foi convidado para atuar na lista de exercícios abaixo.

▪ Reuna-se em grupo e proponha soluções para resolver a lista

▪ O tempo total do exercício será de 20 minutos e 10 minutos para o professor apresentar a correção

▪ Dúvidas consulte o professor para que possa apoia-lo

 ADVANCED SQL
EXERCÍCIOS AULA 1 HANDS ON
ADVANCED SQL
EXERCÍCIOS AULA 1 HANDS ON

1) Crie uma instrução SQL que exiba a somatória do Valor total de lucro obtido de todas as
vendas agrupado por ano e mes e também exiba o percentual sobre o valor total? Filtre os
dados restringindo somente as vendas realizadas entre 2020 e 2021.

Uma sugestão de resultado é apresentado abaixo:

ADVANCED SQL
EXERCÍCIOS AULA 1 HANDS ON

2) Crie uma instrução SQL que exiba o valor total de vendas agrupado por Estado, Cidade e
Bairro, organizando a saída pelos melhores Bairros de Venda. Utilize a clausula RANK() e
RATIO_TO_REPORT (coluna PERC_SOBRE_VALOR_TOTAL).

Um exemplo de resultado é apresentado abaixo:

ADVANCED SQL
EXERCÍCIOS AULA 1 HANDS ON

3) Crie uma instrução SQL que crie um cubo de dados tratando do tema “Informações das
Vendas agrupado por Tipo de Embalagem durante o ano de 2020”. Utilize a clausula CUBE.

Um exemplo de resultado é apresentado abaixo:

ADVANCED SQL
EXERCÍCIOS HANDS ON

4) Crie uma instrução SQL que exiba as seguintes informações:

Número do pedido, codigo e nome do cliente que fez o pedido, a data e o dia da semana
em que foi feito o pedido, bem como, o valor total, o numero do item, o codigo e nome
do produto, a quantidade pedida, o valor unitario e o valor do item, somente para o
pedido numero 1000, pertencente a loja 15. O resultado esperado é apresentado
abaixo:
REFERÊNCIAS BIBLIOGRÁFICAS

Oracle Database Release 19c: SQL Language Reference.

[Link]

Oracle Database Release 19c : Analytics Functions

[Link]
[Link]#GUID-527832F7-63C0-4445-8C16-307FA5084056

Oracle Database Release 19c: Data Warehousing Guide

[Link]
BIBLIOGRAFIA BÁSICA

• MACHADO, Felipe Nery R. Banco de Dados - Projeto e Implementação. Érica,

2004.
• Páginas: 330, 331.

• ELMASRI, R.; NAVATHE, S.B. Sistemas de Banco de Dados: Fundamentos e

Aplicações. Pearson, 2005. Páginas: 153, 154.

• PRICE, JASON, ORACLE DATABASE 11 g – SQL Domine SQL e PL-SQL no banco

de Dados Oracle, Bookman, 2008. Capítulos: 2.

Outros:
• Manual Oficial Oracle Introdução ao Oracle 19c (SQL) Oracle Corporation.
BIBLIOGRAFIA BÁSICA

ISBN-13: 978-1558607538 ISBN-10: 0070182442

ISBN-10: 1558607536
 OBRIGADO

Copyright © 2020 | Professor Salvio Padlipskas

Todos os direitos reservados. A reprodução ou divulgação total ou parcial deste documento é expressamente proibida sem
o consentimento formal, por escrito, do professor/autor.