BANCO DE DADOS II
LINGUAGEM SQL - STRUCTURED QUERY LANGUAGE
CAPITULO 6: FUNÇÕES DE GRUPO...4
6.1 OBJETIVOS DESTE CAPÍTULO...4
6.2 FUNÇÕES DISPONÍVEIS...4
6.3 USANDO FUNÇÕES DE GRUPO...4
6.4 A CLÁUSULA GROUPBY ...5
6.5 EXCLUINDO LINHAS DOS GRUPOS...5
6.6 GRUPOS DENTRO DE GRUPOS...5
6.7 FUNÇÕES DE GRUPO E RESULTADOS INDIVIDUAIS...5
6.8 A CLÁUSULA HAVING ...6
6.9 ORDEM DAS CLÁUSULAS...7
6.10 EXERCÍCIOS...7
CAPÍTULO 7: EXTRAINDO DADOS DE MAIS DE UMA TABELA ...8
7.1 OBJETIVOS DESTE CAPÍTULO...8
7.2 JUNÇÕES EQUIVALENTES...8
7.3 SUMÁRIO DA SINTAXE...8
7.4 EXERCÍCIOS...9
CAPÍTULO 8: OUTROS MÉTODOS DE JUNÇÃO ...10
8.1 OBJETIVOS DESTE CAPÍTULO...10
8.2 JUNÇÕES EXTERNAS...10
8.3 JUNÇÃO DE UMA TABELA COM ELA MESMO...10
8.4 JUNÇÕES VERTICAIS...10
8.4.1 União ...11
8.4.2 Interseção...11
8.4.3 Subtração ...11
8.4.4 Combinação de operadores ...11
8.4.5 A cláusula ORDER BY...11
8.4.6 Regras para utilizar junções verticais ...12
8.5 EXERCÍCIOS...12
CAPÍTULO 9: CONSULTAS ANINHADAS ...13
9.1 OBJETIVOS DESTE CAPÍTULO...13
9.2 DEFINIÇÃO DE CONSULTAS ANINHADAS...13
9.3 CONSULTAS INTERNAS QUE RETORNAM APENAS UM VALOR...13
9.4 COMO AS CONSULTAS ANINHADAS SÃO EXECUTADAS...13
9.5 CONSULTAS INTERNAS QUE RETORNAM MAIS DE UM VALOR...14
9.6 CLÁUSULA HAVING COM CONSULTAS ANINHADAS...15
9.7 O OPERADOR EXISTS ...15
9.8 EXERCÍCIOS...16
CAPÍTULO 10 - VISÕES...17
10.1 OBJETIVOS DESTE CAPÍTULO...17
10.2 O QUE É UMA VISÃO...17
10.3 CLASSIFICAÇÃO DAS VISÕES...17
10.3.1 Visões simples ...17
10.3.2 Visões complexas...17
10.4 O COMANDO CREATEVIEW...18
10.5 USANDO UMA VISÃO PARA OPERAÇÕES DE DML...18
10.6 ALTERANDO DADOS ATRAVÉS DAS VISÕES...20
CAPÍTULO 11 - ÍNDICES...23
11.1 OBJETIVOS DESTE CAPÍTULO...23
11.2 FINALIDADES DOS ÍNDICES...23
11.3 ESTRUTURA DOS ÍNDICES...23
11.4 TIPOS DE ÍNDICES...23
11.5 CRIAÇÃO DOS ÍNDICES...24
11.5.1 Criação de um índice para melhorar o acesso. ...24
11.5.2 Criação de um índice para garantir unicidade...24
11.6 ELIMINAÇÃO DOS ÍNDICES...24
11.7 QUANDO UM ÍNDICE É UTILIZADO...24
11.8 ÍNDICES E JUNÇÕES...25
11.9 SUGESTÕES PARA CRIAÇÃO DOS ÍNDICES...25
11.10 EXERCÍCIOS...25
CAPÍTULO 12: SEQÜÊNCIAS...26
12.1 OBJETIVOS DESTE CAPÍTULO...26
12.2 O GERADOR DE SEQÜÊNCIAS...26
12.3 GERAÇÃO DE NÚMEROS SEQÜENCIAIS COM NEXTVAL...26
12.4 VALOR ATUAL DA SEQÜÊNCIA...27
12.5 REGRAS PARA UTILIZAR CURRVALENEXTVAL...27
12.6 ALTERANDO UMA SEQÜÊNCIA...28
12.7 ELIMINANDO UMA SEQÜÊNCIA...28
Capitulo 6: Funções de Grupo
6.1 Objetivos deste capítulo
Este capítulo explica como informações sumarizadas (média, máximo, mínimo,...) podem ser obtidas para grupo de linhas.
As funções vistas anteriormente operavam sobre cada linha, enquanto que as funções mostradas neste capítulo operam sobre um grupo de linhas.
6.2 Funções disponíveis
Função Valor retornado
AVG
([DISTINCT|ALL]n)
Valor médio de 'n', ignorando os valores nulos. COUNT
([DISTINCT|ALL]exp*)
Número de ocorrências não nulas da expressão. O '*' obriga a contagem total, incluindo valores duplicados e nulos.
MAX
([DISTINCT|ALL]exp)
Valor máximo da expressão. MIN
([DISTINCT|ALL]exp)
Valor mínimo da expressão. STDDEV
([DISTINCT|ALL]n)
Desvio padrão de 'n', ignorando os valores nulos. SUM
([DISTINCT|ALL]n)
Soma dos valores de 'n', ignorando os valores nulos. VARIANCE
([DISTINCT|ALL]n)
Variância de 'n', ignorando os valores nulos.
Quando DISTINCT é especificado, somente os valores não duplicados são considerados pela função, caso contrário ALL é assumido, considerando todos os valores, inclusive os duplicados.
Todas as funções de grupo, exceto COUNT(*), ignoram os valores nulos.
Podem ser fornecidas expressões dos tipos caracter, número ou data quando 'exp' estiver indicado. Quando 'n' estiver indicado, somente valores numéricos são aceitos.
6.3 Usando funções de grupo
Para calcular o salário médio de todos os empregados: SQL> SELECT AVG(VL_SALARIO)
2 FROM EMPREGADO
Uma função de grupo pode ser aplicada sobre um subconjunto das linhas usando a cláusula WHERE.
Para encontrar o salário mínimo recebido por um BALCONIST: SQL> SELECT MIN(VL_SALARIO)
2 FROM EMPREGADO
3 WHERE TP_CARGO='BALCONIST'
Para contar o número de empregados que trabalham no departamento 20: SQL> SELECT COUNT(*)
2 FROM EMPREGADO
3 WHERE CD_DEPARTAMENTO = 20
6.4 A cláusula GROUP BY
A cláusula GROUP BY é utilizada para dividir as linhas das tabelas em grupos. Funções de grupo podem ser utilizadas para retornar informações sumarizadas para cada grupo. Para calcular o salário médio de cada cargo:
SQL> SELECT TP_CARGO, AVG(VL_SALARIO) 2 FROM EMPREGADO
3 GROUP BY TP_CARGO
6.5 Excluindo linhas dos grupos
A cláusula WHERE pode ser utilizada para excluir linhas, antes que estas sejam grupadas.
Para mostrar o salário médio de cada cargo, excluindo os gerentes: SQL> SELECT TP_CARGO, AVG(VL_SALARIO)
2 FROM EMPREGADO
3 WHERE TP_CARGO <> 'GERENTE' 4 GROUP BY TP_CARGO
6.6 Grupos dentro de grupos
Os grupos podem conter subgrupos, fornecendo resultados para grupos dentro de grupos. Para listar o salário médio de cada cargo em cada departamento:
SQL> SELECT CD_DEPARTAMENTO, TP_CARGO, AVG(VL_SALARIO) 2 FROM EMPREGADO
3 GROUP BY CD_DEPARTAMENTO, TP_CARGO
SQL> SELECT MAX(VL_SALARIO) 2 FROM EMPREGADO
3 GROUP BY TP_CARGO
Também não há necessidade de colocar as colunas a serem grupadas antes das outras: SQL> SELECT MAX(VL_SALARIO), TP_CARGO
2 FROM EMPREGADO 3 GROUP BY TP_CARGO
Lembre-se que quando funções de grupo são incluídas na cláusula SELECT, todas as colunas que não contém função de grupo devem aparecer na cláusula GROUP BY. Grupando-se por CD_DEPARTAMENTO o comando processa corretamente.
SQL> SELECT CD_DEPARTAMENTO, MIN(VL_SALARIO) 2 FROM EMPREGADO
3 GROUP BY CD_DEPARTAMENTO
6.8 A cláusula HAVING
Somente os grupos que atendem as condições especificadas na cláusula HAVING são selecionados. A cláusula HAVING opera sobre a cláusula GROUP BY de maneira semelhante a que a cláusula WHERE opera sobre a cláusula SELECT.
Para mostrar o salário médio dos departamento que possuem mais de três funcionários: SQL> SELECT CD_DEPARTAMENTO, AVG(VL_SALARIO)
2 FROM EMPREGADO
3 GROUP BY CD_DEPARTAMENTO 4 HAVING COUNT(*) > 3
Para mostrar os cargos com salário máximo acima de 3000: SQL> SELECT TP_CARGO, MAX(VL_SALARIO)
2 FROM EMPREGADO
3 HAVING MAX(VL_SALARIO) >= 3000 4 GROUP BY TP_CARGO
Embora a cláusula HAVING possa ser escrita antes da cláusula GROUP BY, a leitura torna-se mais fácil quando a cláusula HAVING aparece após a cláusula GROUP BY. Os grupos são formados e as funções de grupo são calculadas antes da cláusula HAVING ser aplicada.
A cláusula WHERE é utilizada para aplicar restrições sobre linhas individualmente, enquanto a cláusula HAVING é utilizada para aplicar restrições sobre grupos de linhas.
Para calcular o salário médio de todos os cargos, exceto o cargo de gerente: SQL> SELECT TP_CARGO, AVG(VL_SALARIO)
2 FROM EMPREGADO
3 WHERE TP_CARGO <> 'GERENTE' 4 GROUP BY TP_CARGO
5 HAVING AVG(VL_SALARIO) > 2000
6.9 Ordem das cláusulas
SELECT lista_de_colunas FROM lista_de_tabelas WHERE condições_de_linha GROUP BY lista_de_colunas HAVING condições_de_grupo ORDER BY lista_de_colunas São avaliados:
WHERE para ficar somente as linhas que atendem a cláusula GROUP BY para montagem dos grupos
HAVING para ficar somente os grupos que atendem a cláusula
6.10 Exercícios
a) Encontrar o menor salário entre todos os empregados.
b) Encontrar o salário maior, menor, e médio, entre todos os empregados. c) Listar o maior e o menor salário por cada tipo de cargo.
d) Calcular o número de gerentes sem listar seus nomes.
e) Encontrar o salário médio e a remuneração anual média de cada tipo de cargo, lembrando-se que os vendedores recebem comissão.
f) Encontrar a diferença entre o maior e o menor salários.
g) Listar todos os departamentos que possuem mais de três empregados. h) Verificar se não há duplicidade no número dos empregados
i) Listar os empregados que recebem o menor salário em cada departamento, relacionando os seus gerentes. Excluir os grupos onde o menor salário é inferior a 1000. Ordenar os resultados pelo salário
Capítulo 7: Extraindo dados de mais de uma tabela
7.1 Objetivos deste capítulo
Mostrar como obter informações de mais de uma tabela através das junções.
Linhas de uma tabela podem ser juntadas com linhas de outra tabela, de acordo com valores comuns existentes em colunas correspondentes.
7.2 Junções equivalentes
Para levantar, manualmente, o nome do departamento em que um funcionário trabalha, primeiro seria levantado na tabela EMPREGADO o número do departamento do empregado, e, em seguida, seria levantado na tabela DEPARTAMENTO o nome correspondente ao número do departamento. Este relacionamento entre as duas tabelas é chamado de junção equivalente (equi-join), uma vez o número do departamento nas duas tabelas é o mesmo.
A condição de junção é especificada na cláusula WHERE usando o operador '='. SQL> SELECT NM_EMPREGADO , TP_CARGO, NM_DEPARTAMENTO
2 FROM EMPREGADO, DEPARTAMENTO
3 WHERE EMPREGADO.CD_DEPARTAMENTO = DEPARTAMENTO.CD_DEPARTAMENTO
As linhas da tabela EMPREGADO foram combinadas com as linhas da tabela DEPARTAMENTO para obtenção dos números dos departamentos.
O nome da tabela é colocado antes do nome da coluna, para diferenciar colunas com mesmo nome em tabelas diferentes.
Todas as colunas podem ser prefixadas com o nome da tabela, porém este procedimento só é obrigatório quando existe ambigüidade.
SQL> SELECT DEPARTAMENTO.CD_DEPARTAMENTO, NM_EMPREGADO , TP_CARGO, NM_DEPARTAMENTO
2 FROM EMPREGADO, DEPARTAMENTO
3 WHERE EMPREGADO.CD_DEPARTAMENTO = DEPARTAMENTO.CD_DEPARTAMENTO 4 ORDER BY DEPARTAMENTO.CD_DEPARTAMENTO
Aliás para nomes de tabelas podem ser usado de forma semelhante à que foi usada para aliás de nomes de colunas. O nome do aliás é escrito após o nome da tabela. No exemplo abaixo foi utilizado o aliás 'E' para a tabela EMPREGADO, e o aliás 'D' para a tabela DEPARTAMENTO.
SQL> SELECT E.NM_EMPREGADO , D.CD_DEPARTAMENTO, D.NM_DEPARTAMENTO 2 FROM EMPREGADO E, DEPARTAMENTO D
3 WHERE E.CD_DEPARTAMENTO = D.CD_DEPARTAMENTO 4 ORDER BY D.CD_DEPARTAMENTO
SELECT [DISTINCT] {[tabela].*|expressão [aliás],...} FROM tabela [alias],...
WHERE [condição de junção]... AND [condição de linha]... OR [outra condição de linha] GROUP BY {expressão|coluna} HAVING {condição de grupo}
ORDER BY {expressão|coluna} [ASC|DESC]
7.4 Exercícios
a) Mostrar o nome dos empregados e dos departamentos onde trabalham, ordenados pelo nome do departamento.
b) Mostrar os nomes dos empregados, juntamente com os números e nomes dos departamentos onde trabalham
c) Listar o nome, o local de trabalho e o departamento dos empregados com salário superior a 1500.
f) Listar todos os empregados em ORLEANS.
g) Listar os nomes dos empregados, o cargo, o salário e o nome do departamento para todos na companhia, exceto os BALCONISTs. Ordenar pelo salário, com os maiores primeiro.
h) Listar os seguintes detalhes para os empregados que ganham 36.000 por ano ou que são BALCONISTs.
NM_EMPREGADO, TP_CARGO, SALARIO_ANUAL, CD_DEPARTAMENTO, NM_DEPARTAMENTO
Capítulo 8: Outros métodos de junção
8.1 Objetivos deste capítulo
Mostrar métodos alternativos para construção de junções.
8.2 Junções externas
Se uma linha não satisfaz a condição de junção, então a linha não é mostrada no resultado da consulta. De fato, quando fazemos a junção equivalente (equi-join) das tabelas EMPREGADO e DEPARTAMENTO, o departamento 40 não aparece, uma vez que não há nenhum empregado lotado neste departamento.
As linhas excluídas podem ser retornadas se um operador de junção externa (outer join) for utilizado na condição de junção. O operador é um sinal de '+' colocado entre parênteses, que deve ser ficar ao lado da tabela onde as informações que não seriam incluídas devem aparecer.
No exemplo o operador está colocado ao lado da tabela DEPARTAMENTO, forçando listar os dados dos departamentos mesmo que não haja correspondência em EMPREGADO.
SQL> SELECT E.NM_EMPREGADO, D.CD_DEPARTAMENTO, D.NM_DEPARTAMENTO 2 FROM EMPREGADO E, DEPARTAMENTO D
3 WHERE E.CD_DEPARTAMENTO (+) = D.CD_DEPARTAMENTO 4 AND D.CD_DEPARTAMENTO IN (30,40)
O operador de junção externa (outer join) só pode aparecer em um dos lados da expressão, causando uma junção externa esquerda (left outer join), ou uma junção externa direita (right outer join).
8.3 Junção de uma tabela com ela mesmo
É possível utilizar rótulos nas tabelas (aliases) para fazer a junção de uma tabela com ela mesmo, como se fossem duas tabelas distintas.
No exemplo abaixo são mostrados todos os funcionários que recebem menos que seus gerentes.
SQL> SELECT E.NM_EMPREGADO EMP_NOME, E.VL_SALARIO EMP_SAL, 2 G.NM_EMPREGADO GER_NOME, G.VL_SALARIO GER_SAL 3 FROM EMPREGADO E, EMPREGADO G
4 WHERE E.CD_EMPREGADO_GERENTE = G.CD_EMPREGADO 5 AND E.VL_SALARIO < G.VL_SALARIO
Note que EMPREGADO aparece na cláusula FROM duas vezes, uma com o aliás 'E', para os empregados, e outra com o aliás 'G', para os gerentes.
Os operadores UNION, INTERSECT e MINUS são úteis para construir consultas que se referem a tabelas diferentes. Estes operadores combinam os resultados de dois ou mais SELECTs em um único resultado.
8.4.1 União
O operador UNION retorna todas as linhas distintas das consultas unidas por este operador. No exemplo são listados todos os cargos dos departamentos 10 e 30 sem repetição. SQL> SELECT TP_CARGO 2 FROM EMPREGADO 3 WHERE CD_DEPARTAMENTO = 10 4 UNION 5 SELECT TP_CARGO 6 FROM EMPREGADO 7 WHERE CD_DEPARTAMENTO = 30 8.4.2 Interseção
O operador INTERSECT retorna apenas as linhas comuns às duas consultas. Para listar os cargos existentes tanto no departamento 10 quanto no departamento 30:
SQL> SELECT TP_CARGO 2 FROM EMPREGADO 3 WHERE CD_DEPARTAMENTO = 10 4 INTERSECT 5 SELECT TP_CARGO 6 FROM EMPREGADO 7 WHERE CD_DEPARTAMENTO = 30 8.4.3 Subtração
O operador MINUS retorna as linhas presentes na primeira consulta mas não presentes na segunda consulta. Para listar os cargos existentes no departamento 10 mas não existentes no departamento 30: SQL> SELECT TP_CARGO 2 FROM EMPREGADO 3 WHERE CD_DEPARTAMENTO = 10 4 MINUS 5 SELECT TP_CARGO 6 FROM EMPREGADO 7 WHERE CD_DEPARTAMENTO = 30 8.4.4 Combinação de operadores
É possível a construção de consultas com operadores UNION, INTERSECT e MINUS combinados. Quando isto é feito a execução é efetuada de cima para baixo, porém
Como as colunas das tabelas podem ter nomes diferentes quando usamos UNION, INTERSECT e MINUS, o nome da coluna não pode ser usado na cláusula ORDER BY quando estes operadores são utilizados. Ao invés do nome deve ser usado o número da coluna, como mostrado abaixo:
SQL> SELECT CD_EMPREGADO , NM_EMPREGADO , VL_SALARIO 2 FROM EMPREGADO
3 UNION
4 SELECT CD_EMPREGADO , NM_EMPREGADO , VL_SALARIO 5 FROM EMPREGADO
6 ORDER BY 2
A cláusula ORDER BY deve ser sempre a última.
8.4.6 Regras para utilizar junções verticais
• As declarações SELECT devem ter todas o mesmo número de colunas. • Os tipos de dados das colunas correspondentes devem ser idênticos.
• Linhas duplicadas são automaticamente eliminadas (DISTINCT não pode ser usado). • Os nomes das colunas da primeira consulta são os que aparecem no resultado.
• A cláusula ORDER BY deve ser sempre a última.
• A cláusula ORDER BY só pode conter os números das colunas, não os nomes. • Declarações SELECT são executadas de cima para baixo.
• Múltiplas junções verticais podem ser utilizadas, com parênteses, se necessário, para alterar a ordem de execução.
8.5 Exercícios
a) Listar os departamentos que não possuem empregados.
b) Listar os números e os nomes dos empregados juntamente com os números e os nomes de seus gerentes.
c) Alterar a solução do exercício b para mostrar KING que não possui gerente.
d) Mostrar os cargos que foram preenchidos no primeiro semestre de 1983 e no mesmo período em 1984.
e) Listar todos os empregados admitidos antes de seus gerentes. f) Encontrar outra maneira de resolver o exercício a.
Capítulo 9: Consultas aninhadas
9.1 Objetivos deste capítulo
Mostrar consultas declaradas nas cláusulas Where e Having.
9.2 Definição de consultas aninhadas
Uma consulta aninhada, ou subconsulta, é aquela que está contida dentro de uma outra consulta, e que retorna valores intermediários.
Por exemplo:
SELECT coluna1, coluna2 FROM tabela
WHERE coluna1 = (SELECT coluna FROM tabela WHERE condição) Consultas aninhadas são muito úteis quando é necessário selecionar linhas de uma tabela sob uma condição que depende dos dados da própria tabela.
9.3 Consultas internas que retornam apenas um valor
Para encontrar o empregado com o menor salário da empresa são necessárias duas etapas:
a) achar qual o menor salário
SQL> SELECT MIN(VL_SALARIO) 2 FROM EMPREGADO
b) localizar o empregado que recebe o menor salário SELECT NM_EMPREGADO, TP_CARGO, VL_SALARIO FROM EMPREGADO
WHERE VL_SALARIO = salário encontrado na etapa anterior Os dois comandos podem ser combinados em uma consulta aninhada:
SQL> SELECT NM_EMPREGADO, TP_CARGO, VL_SALARIO 2 FROM EMPREGADO
3 WHERE VL_SALARIO = (SELECT MIN(VL_SALARIO) 4 FROM EMPREGADO)
Antes da consulta interna que retorna apenas um valor, podem ser usados os operadores =, <, >, <=, >=, <>.
executado primeiro, produzindo o resultado: 800. Em seguida o bloco principal é executado, utilizando o valor retornado pelo bloco interno.
Para listar todos os empregados com o mesmo cargo do BLAKE: SQL> SELECT NM_EMPREGADO , TP_CARGO
2 FROM EMPREGADO
3 WHERE TP_CARGO = (SELECT TP_CARGO 4 FROM EMPREGADO
5 WHERE NM_EMPREGADO = 'BLAKE')
O cargo de BLAKE é obtido pela consulta interna e utilizado pela consulta principal.
9.5 Consultas internas que retornam mais de um valor
A consulta abaixo tenta localizar os empregados com o menor salário em cada departamento.
SQL> SELECT NM_EMPREGADO, VL_SALARIO, CD_DEPARTAMENTO 2 FROM EMPREGADO
3 WHERE VL_SALARIO IN (SELECT MIN(VL_SALARIO) FROM EMPREGADO
GROUP BY CD_DEPARTAMENTO)
Note que a consulta interna tem uma cláusula GROUP BY, e portanto retorna mais de um valor. Neste caso o operador IN deve ser usado para tratar a lista de valores.
O resultado da consulta interna não indica o departamento, não havendo assim qualquer correspondência entre o salário retornado e o departamento do funcionário.
Para haver correspondência entre o salário e o departamento a consulta foi escrita novamente:
SQL> SELECT NM_EMPREGADO, VL_SALARIO, CD_DEPARTAMENTO 2 FROM EMPREGADO
3 WHERE (VL_SALARIO, CD_DEPARTAMENTO) IN
4 (SELECT MIN(VL_SALARIO), CD_DEPARTAMENTO 5 FROM EMPREGADO
6 GROUP BY CD_DEPARTAMENTO)
Nesta nova consulta, é retornado um par de colunas que é comparado com um par de colunas da consulta principal.
Note que as colunas à esquerda da condição de procura estão entre parênteses e são separadas por vírgulas.
As colunas listadas na consulta interna devem estar na mesma ordem das colunas à esquerda da condição de procura.
As colunas retornadas pela consulta interna devem ser em mesmo número e do mesmo tipo de dados das colunas à esquerda da condição de procura.
Se uma consulta interna retornar mais de uma linha e a condição de procura utilizar um operador de uma linha apenas gera o erro mostrado abaixo:
SQL> SELECT NM_EMPREGADO, VL_SALARIO, CD_DEPARTAMENTO 2 FROM EMPREGADO
3 WHERE VL_SALARIO = (SELECT MIN(VL_SALARIO) 4 FROM EMPREGADO
5 GROUP BY CD_DEPARTAMENTO) ERROR:
ORA-01427: single-row subquery returns more than one row no rows selected
Se a consulta interna não retornar nenhuma linha: SQL> SELECT NM_EMPREGADO, TP_CARGO 2 FROM EMPREGADO
3 WHERE TP_CARGO = (SELECT TP_CARGO 4 FROM EMPREGADO
5 WHERE NM_EMPREGADO = 'SMYTHE') ERROR:
ORA-01426: sigle-row subquery returns no row no rows selected
9.6 Cláusula HAVING com consultas aninhadas
Para mostrar os departamentos com salário médio superior ao salário médio do departamento 30, escrevemos:
SQL> SELECT CD_DEPARTAMENTO, AVG(VL_SALARIO) 2 FROM EMPREGADO
3 GROUP BY CD_DEPARTAMENTO
4 HAVING AVG(VL_SALARIO) > (SELECT AVG(VL_SALARIO) 5 FROM EMPREGADO
6 WHERE CD_DEPARTAMENTO = 30)
Para descobrir qual o cargo com maior salário médio: SQL> SELECT TP_CARGO, AVG(VL_SALARIO) 2 FROM EMPREGADO
3 GROUP BY TP_CARGO
4 HAVING AVG(VL_SALARIO) = (SELECT MAX(AVG(VL_SALARIO)) 5 FROM EMPREGADO
6 GROUP BY TP_CARGO)
9.7 O operador EXISTS
O operador EXISTS é freqüentemente usado com consultas aninhadas correlacionadas, testando se o valor existe. Se o valor existir, retorna verdadeiro, caso contrário retorna falso.
2 FROM EMPREGADO E
3 WHERE EXISTS (SELECT CD_EMPREGADO 4 FROM EMPREGADO A
5 WHERE A.CD_EMPREGADO_GERENTE = E.CD_EMPREGADO ) 6 ORDER BY CD_EMPREGADO
Uma outra forma para encontrar os departamentos sem funcionários é: SQL> SELECT CD_DEPARTAMENTO, NM_DEPARTAMENTO
2 FROM DEPARTAMENTO D
3 WHERE NOT EXISTS (SELECT 1
4 FROM EMPREGADO E
5 WHERE E.CD_DEPARTAMENTO = D.CD_DEPARTAMENTO)
Note que neste exemplo a consulta interna não precisa retornar nada, porém a cláusula SELECT precisa de uma coluna, mesmo que seja um literal.
9.8 Exercícios
a) Listar os empregados com o maior salário por cargo, em ordem descendente de salário.
b) Listar os empregados com os menores salário por cargo, em ordem ascendente de salário.
c) Listar os últimos empregados contratados em cada departamento.
d) Listar o nome, o salário e o número do departamento dos funcionários que recebem acima da média de seus departamentos. Ordenar pelo número do departamento.
e) Listar os departamentos sem funcionários.
f) Mostrar o departamento que tem mais despesas com seus funcionários. g) Quais os três empregados que ganham mais ?
h) Em que ano a empresa contratou mais ?
i) Modificar o exercício d para mostrar o salário médio do departamento junto com as outras informações.
j) Escreva uma consulta que mostre um '*' junto do funcionário contratado mais recentemente
Capítulo 10 - Visões
10.1 Objetivos deste capítulo
Mostrar a criação e utilização de visões (views).
10.2 O que é uma visão
• Uma visão é como uma janela através da qual os dados das tabelas podem ser vistos e alterados.
• Uma visão é derivada de uma tabela ou de outra visão, a qual é chamada de tabela ou visão base. - uma tabela real com os dados fisicamente armazenados.
• Uma visão é armazenada na forma de um comando SELECT apenas. É uma tabela virtual, ou seja, uma tabela que não existe fisicamente no banco de dados, mas parece existir.
• Uma visão não tem dados próprios. Os dados são manipulados a partir das tabelas base.
Visões são úteis pelas seguintes razões:
• Restringir o acesso ao banco de dados. Permite enxergar apenas parte das tabelas. • Permite aos usuários executar consultas simples para obter resultados de
consultas complexas. Podem ser recuperados dados de várias tabelas como se fosse uma única tabela.
• Provê independência de dados, permitindo alterar as tabelas base sem afetar as aplicações dos usuários.
10.3 Classificação das visões 10.3.1 Visões simples
• dados derivados de uma única tabela • não contém funções ou dados grupados.
10.3.2 Visões complexas
• dados derivados de múltiplas tabelas • contém funções ou dados grupados.
10.4 O comando CREATE VIEW
Sintaxe:
CREATE VIEW nome_da_view [(coluna1, coluna2,...)] AS
SELECT cláusula_select
[WITH CHECK OPTION [CONSTRAINT nome_da_restrição]]
Para criar uma visão simples chamada D10EMP, a partir da tabela EMPREGADO, contendo certos detalhes dos funcionários do departamento 10:
SQL> CREATE VIEW D10EMP 2 AS
3 SELECT CD_EMPREGADO, NM_EMPREGADO, CD_DEPARTAMENTO 4 FROM EMPREGADO
5 WHERE CD_DEPARTAMENTO = 10 6 WITH CHECK OPTION
View created.
Para recuperar os dados através da visão: SQL> SELECT *
2 FROM D10EMP
Para criar uma visão complexa, chamada DEPARTAMENTO_SOMADO, contendo funções de grupo e dados de mais de uma tabela:
CREATE VIEW DEPARTAMENTO_SOMADO ( NAME, MINSAL, MAXSAL, AVGSAL ) AS
SELECT NM_DEPARTAMENTO, MIN(VL_SALARIO), MAX(VL_SALARIO), AVG(VL_SALARIO)
FROM EMPREGADO, DEPARTAMENTO
WHERE EMPREGADO.CD_DEPARTAMENTO = DEPARTAMENTO.CD_DEPARTAMENTO GROUP BY NM_DEPARTAMENTO
Note que nomes alternativos para as colunas foram especificados na visão, o que é necessário quando os itens da cláusula Select não estão de acordo com as regras para nomes de colunas, ou se alguma coluna é derivada de uma função ou expressão.
Quando um aliás é utilizado para o nome da coluna na cláusula Select, não é necessário colocar um nome para a coluna na cláusula Create View.
CREATE VIEW DEPARTAMENTO20 AS
SELECT NM_EMPREGADO , VL_SALARIO*12 SALANO FROM EMPREGADO
WHERE CD_DEPARTAMENTO = 20
Visões são poderosas porque permitem realizar verificação de integridade referencial nos dados modificados através delas.
A cláusula WITH CHECK OPTION especifica que inserções e atualizações realizadas através da visão não podem gerar linhas que a visão não pode enxergar. Se na visão D10EMP tentarmos inserir um funcionário do departamento 20 um erro é gerado.
SQL> INSERT INTO D10EMP(CD_EMPREGADO, NM_EMPREGADO, CD_DEPARTAMENTO) 2 VALUES ( 9999, 'MICHAEL', 20 );
INSERT INTO D10EMP ( CD_EMPREGADO , NM_EMPREGADO , CD_DEPARTAMENTO ) *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
A visão EMP_DADOS só permite inserções ou atualizações se o salário estiver na faixa 1000 a 2000, o gerente estiver cadastrado na tabela de empregados, e o departamento estiver cadastrado na tabela de departamentos.
SQL> CREATE VIEW EMP_DADOS 2 AS
3 SELECT CD_EMPREGADO, NM_EMPREGADO, TP_CARGO,
4 CD_EMPREGADO_GERENTE, VL_SALARIO, CD_DEPARTAMENTO 5 FROM EMPREGADO
6 WHERE VL_SALARIO BETWEEN 1000 AND 2000
7 AND CD_EMPREGADO_GERENTE IN ( SELECT DISTINCT CD_EMPREGADO 8 FROM EMPREGADO )
9 AND CD_DEPARTAMENTO IN ( SELECT CD_DEPARTAMENTO 10 FROM DEPARTAMENTO ) 11 WITH CHECK OPTION
View created.
A visão EMP_DETALHES restringe o acesso aos dados no período das 7 às 17 horas, de segunda a sexta-feira.
SQL> CREATE VIEW EMP_DETALHES 2 AS
3 SELECT CD_EMPREGADO, NM_EMPREGADO, TP_CARGO, CD_DEPARTAMENTO 4 FROM EMPREGADO
5 WHERE TO_CHAR(SYSDATE,'HH24') BETWEEN 7 AND 17 6 AND TO_CHAR(SYSDATE,'D') BETWEEN 2 AND 6
7 WITH CHECK OPTION View created.
Quando a visão é criada, o comando SELECT não é executado, o comando SELECT é simplesmente armazenado no dicionário de dados.
SQL> DESC USER_VIEWS
Name Null? Type --- --- ---- VIEW_NAME NOT NULL CHAR(30) TEXT_LENGTH NUMBER TEXT LONG
SQL> SELECT *
FROM USER_VIEWS
10.6 Alterando dados através das visões
As seguintes restrições se aplicam quando os dados são alterados através das visões: Eliminação e atualização é proibida de a visão contém:
• Junção.
• Funções de grupo. • Cláusula GROUP BY. • Cláusula DISTINCT. • Coluna ROWNUM.
• Sub-consulta correlacionada. • Colunas definidas por expressão. Inserção é proibida se a visão contém:
• Alguma das condições acima.
• Coluna NOT NULL da tabela que não consta da visão.
10.7 Eliminação de visões
As visões são eliminadas através do comando “DROP VIEW nome_da_visão”. SQL> DROP VIEW D10EMP
View dropped.
SQL> DROP VIEW FUNCIONARIOS View dropped.
SQL> DROP VIEW EMP_DADOS View dropped.
View dropped.
10.8 Exercícios
a) Criar uma visão que produza o seguinte resultado. SQL> SELECT * FROM V_RESUMO_EMPREGADO
CD_DEPARTAMENTO VL_MEDIA_SAL VL_MAX_SAL VL_MIN_SAL VL_SUM_SAL QT_SAL QT_COMISSAO --- --- --- --- --- --- --- 10 2916.67 5000 1300 8750 3 0 20 2175 3000 800 10875 5 0 30 1566.67 2850 950 9400 6 4
b) Utilizando a visão do exercício anterior, extrair as seguintes informações do empregado 7902.
CD_EMPREGADO NM_EMPREGADO TP_CARGO VL_SALARIO DT_ADMISSAO VL_MIN_SAL VL_MAX_SAL VL_MEDIA_SAL
--- --- --- --- --- --- --- --- 7902 FORD ANALYST 3,000 05/12/1983 800 3,000 2,175
c) Criar uma visão que garanta as seguintes restrições ao se inserir dados na tabela ATRIBUICAO:
- número do projeto menor do que 2000. - Término do projeto após o início do projeto. - TP_ATRIBUICAO válidos são PF, WT e ED.
- VL_TAXA menor do que 50 para TP_ATRIBUICAO PF. VL_TAXA menor do que 60 para TP_ATRIBUICAO WT. VL_TAXA menor do que 70 para TP_ATRIBUICAO ED. - O número do empregado deve ser válido.
- Não esqueça a cláusula WITH CHECK OPTION SQL> SELECT * FROM V_ATRIBUICAO
CD_PROJETO CD_EMPREGADO DT_INICIO DT_FIM VL_TAXA TP_ATRIBUICAO QT_HORAS --- --- --- --- --- --- --- 1 7369 01/01/1988 03/01/1988 50 WT 15 2 7844 01/01/1989 10/01/1989 45.5 PF 30 1 7902 04/01/1988 07/01/1988 55 WT 20
TP_ATRIBUICAO = ED QT_HORAS = 20
Resultado
ERROR at line 3:
ORA-01402: view WITH CHECK OPTION where-clause violation
CD_PROJETO = 2 CD_EMPREGADO = 7698 DT_INICIO = 01/02/1989 DT_FIM = 20/02/1989 VL_TAXA = 55.00 TP_ATRIBUICAO = WT QT_HORAS = 30 Resultado 1 row created. CD_PROJETO = 2 CD_EMPREGADO = 8000 DT_INICIO = 01/03/1989 DT_FIM = 31/12/1989 VL_TAXA = 69.00 TP_ATRIBUICAO = ED QT_HORAS = 40 Resultado ERROR at line 3:
ORA-01402: view WITH CHECK OPTION where-clause violation e) Consulte o dicionário de dados para ver a cláusula SELECT da visão.
Capítulo 11 - Índices
11.1 Objetivos deste capítulo
Mostrar a criação e a utilidade dos índices.
11.2 Finalidades dos índices
Os índices do Oracle têm duas finalidades principais. - Otimizar o tempo de resposta de uma consulta.
- Garantir unicidade de valores para uma coluna ou conjunto de colunas.
A utilização de índices é altamente recomendada para obter melhor desempenho, e geralmente um dos primeiros índices a serem criados na tabela é o da chave primária. Os índices são criados, normalmente, pelos donos das tabelas, mas qualquer usuário que tenha privilégio de índice sobre a tabela também pode criar índices para a tabela.
Uma vez criado, o índice será utilizado pelo Oracle, sempre que for possível, para acelerar o acesso aos dados. Note que os índices são utilizados automaticamente, sem requerer qualquer ação por parte do usuário, que nem precisa saber da existência dos índices.
11.3 Estrutura dos índices
O Oracle utiliza árvores binárias balanceadas para os índices, o que garante o mesmo tempo, aproximadamente, para o acesso a qualquer linha da tabela, independente de sua posição. O tempo de acesso também é bastante independente do volume de dados indexados.
11.4 Tipos de índices
UNIQUE
Garante que os valores especificados para a(s) coluna(s) são únicos. NON UNIQUE
É o padrão, usado para melhorar o tempo de acesso. SINGLE COLUMN
11.5 Criação dos índices
Os índices são criados juntamente com as tabelas, ou através do comando: CREATE [UNIQUE] INDEX nome-do-índice
ON nome-da-tabela (coluna1, [,coluna2...])
11.5.1 Criação de um índice para melhorar o acesso.
Para criar um índice chamado NM_EMPREGADO_IDX para melhorar o tempo de acesso das consultas feitas através do nome do funcionário:
CREATE INDEX NM_EMPREGADO_IDX ON EMPREGADO (NM_EMPREGADO )
11.5.2 Criação de um índice para garantir unicidade.
Para evitar a duplicação do nome do departamento:
CREATE UNIQUE INDEX NM_DEPARTAMENTO_IDX ON DEPARTAMENTO(NM_DEPARTAMENTO)
11.6 Eliminação dos índices
Os índices são eliminados através do comando: DROP INDEX nome-do-índice
11.7 Quando um índice é utilizado
O Oracle decide quando é apropriado utilizar um índice. O Oracle sabe quais colunas estão indexadas e o tipo do índice, e decide de acordo com regras específicas.
a) A coluna indexada deve ser referenciada na cláusula Where.
A consulta mostrada abaixo não utiliza índice porque não possui cláusula Where. SELECT NM_EMPREGADO
FROM EMPREGADO
A consulta abaixo utiliza o índice criado para a coluna NM_EMPREGADO : SELECT NM_EMPREGADO
FROM EMPREGADO
b) O índice não é utilizado se a coluna referenciada na cláusula Where é parte de uma função ou de uma expressão.
No exemplo abaixo o índice não é utilizado porque a coluna NM_EMPREGADO é parte de uma função:
SELECT NM_EMPREGADO FROM EMPREGADO
WHERE UPPER(NM_EMPREGADO ) = ‘JONES’
No exemplo abaixo o índice não é utilizado porque a coluna é parte de uma expressão: SELECT NM_EMPREGADO
FROM EMPREGADO
WHERE DT_ADMISSAO+7 = TO_DATE(‘01/01/1984’, ‘DD/MM/YYYY’)
11.8 Índices e Junções
Se não existirem índices nas colunas utilizadas para realizar a junção equivalente, o Oracle é obrigado a realizar uma operação de SORT/MERGE para responder a consulta. Isto significa que cada tabela é ordenada separadamente, e depois as duas são unidas de acordo com a condição de junção.
11.9 Sugestões para criação dos índices
As colunas que não permitem valores duplicados devem ser indexadas.
As colunas que são utilizadas habitualmente na cláusula Where devem ser indexadas. As colunas utilizadas nas condições de junção equivalente devem ser indexadas.
Não devem ser utilizados mais de 3 índices por tabela para não prejudicar as operações realizadas através das operações de DML.
11.10 Exercícios
a) Criar um índice único na coluna DS_PROJETO da tabela PROJETO. Teste o índice inserindo um valor para DS_PROJETO já existente.
b) Criar um índice não único na coluna CD_PROJETO da tabela ATRIBUICAO. c) Consulte o dicionário de dados para obter as informações de seus índices.
Capítulo 12: Seqüências
12.1 Objetivos deste capítulo
Mostrar a criação e a utilização das seqüências.
12.2 O gerador de seqüências
O gerador de seqüências do Oracle é utilizado para gerar automaticamente seqüências de números para as linhas das tabelas.
Para gerar os números seqüenciais automaticamente, primeiro a seqüência deve ser definida utilizando a declaração CREATE SEQUENCE, conforme a sintaxe mostrada abaixo:
CREATE SEQUENCE [esquema.]nome-da-seqüência [INCREMENT BY n]
[START WITH n]
[MAXVALUE n | NOMAXVALUE] [MINVALUE n | NOMINVALUE]
esquema identificação do dono da seqüência.
nome-da-seqüência nome válido para a seqüência.
INCREMENT BY incremento positivo ou negativo. Padrão: 1. START WITH primeiro número a ser gerado. Padrão: 1.
MINVALUE|NOMINVALUE menor valor a ser gerado. Padrão: 1 para seqüências ascendentes, 10^27-1 para seqüências descendentes. MAXVALUE|NOMAXVALUE maior valor a ser gerado. Padrão: 1 para seqüências
descendentes, 10^27-1 para seqüências ascendentes. Para um usuário poder criar uma seqüência deve possuir o privilégio de ‘resource’. O comando mostrada abaixo cria uma seqüência para a coluna CD_DEPARTAMENTO da tabela DEPARTAMENTO.
CREATE SEQUENCE DEPARTAMENTO_SEQ INCREMENT BY 10
START WITH 10 MAXVALUE 10000
12.3 Geração de números seqüenciais com NEXTVAL
A pseudo-coluna NEXTVAL é utilizada para gerar números seqüenciais sucessivos de uma seqüência especificada. Quando a coluna NEXTVAL é especificada um novo número seqüencial é gerado.
SELECT DEPARTAMENTO_SEQ.NEXTVAL FROM SYS.DUAL
NEXTVAL --- 10
Se o comando for executado novamente, o valor é incrementado de 10. SELECT DEPARTAMENTO_SEQ.NEXTVAL FROM SYS.DUAL NEXTVAL --- 20
Todo os valores subsequentes serão incrementados de 10. Notas:
A coluna NEXTVAL sempre deve ser prefixada pelo nome da seqüência.
Se a coluna NEXTVAL for referenciada diversas vezes dentro do mesmo comando SQL, todas as referências retornam o mesmo valor.
A coluna NEXTVAL é mais útil em comandos de DML. Por exemplo, quando são inseridas linhas na tabela, a seqüência pode ser utilizada para gerar valores únicos para a chave primária.
INSERT INTO DEPARTAMENTO
VALUES (DEPARTAMENTO_SEQ.NEXTVAL, ‘CONTABILIDADE’, ‘SÃO LUDGERO’) Quando o número seqüencial é gerado, a seqüência é incrementada independentemente de haver um Commit ou um Rollback para a transação. Quando dois usuários acessam a mesma seqüência ao mesmo tempo, os dois usuários podem ver descontinuidades na seqüência, devido a valores gerados para o outro usuário que ainda não receberam o Commit. Os números produzidos pelo gerador de seqüências podem ser saltados devido a Rollbacks.
12.4 Valor atual da seqüência
Para se referir ao valor atual da seqüência, pode ser utilizada a pseudo-coluna CURRVAL. Toda vez que a pseudo-coluna NEXTVAL é utilizada, o valor gerado é armazenado em CURRVAL, que só pode ser utilizada após NEXTVAL ser referenciado na sessão atual do usuário.
INSERT INTO DEPARTAMENTO
VALUES (DEPARTAMENTO_SEQ.CURRVAL, ‘CONTABILIDADE’, ‘SÃO LUDGERO’)
12.5 Regras para utilizar CURRVAL E NEXTVAL
Cláusula SET da declaração UPDATE. Currval e Nextval não podem ser utilizados:
Em visões.
Com a palavra chave DISTINCT.
Com as cláusulas Order By, Group By, Connect By, ou Having da declaração SELECT.
Com os operadores Intersect, Union e Minus.
Seqüências são tratadas de maneira similar às tabelas, podendo ser alteradas e eliminadas. O dono de uma seqüência pode conceder privilégios a outros usuários.
12.6 Alterando uma seqüência
O comando ALTER SEQUENCE é utilizado para modificar uma seqüência existente. ALTER SEQUENCE [esquema.]nome-da-sequencia
[INCREMENT BY n]
[MAXVALUE n | NOMAXVALUE] [MINVALUE n | NOMINVALUE]
Por exemplo, para modificar o valor máximo para a seqüência DEPARTAMENTO_SEQ:
ALTER SEQUENCE DEPARTAMENTO_SEQ MAXVALUE 100000
Notas:
Somente os números a serem gerados no futuro são alterados pelo comando Alter Sequence. Os números que já foram gerados não são alterados.
Validações são realizadas. Não pode ser especificado, por exemplo, um novo valor máximo menor que o valor corrente.
O parâmetro START WITH não pode ser alterado pelo comando Alter Sequence. A seqüência deve ser eliminada e recriada para ser iniciada com um novo número.
12.7 Eliminando uma seqüência
O comando Drop Sequence remove a definição da seqüência do dicionário de dados. DROP SEQUENCE [esquema.]nome-da-seqüência
Apenas o dono da seqüência e o DBA podem utilizar este comando.
12.8 Listando seqüências
As visões USER_SEQUENCES ou ALL_SEQUENCES podem ser utilizadas para descobrir as seqüências existentes.