Baixe o app para aproveitar ainda mais
Prévia do material em texto
Esta obra é resultado da experiência do autor, adquirida em aulas ministradas em cursos de graduação e pós- graduação. O texto apresenta a base para entender a tecnologia de banco de dados que oferece suporte aos problemas de computação corporativa. O autor parte do princípio de que o iniciante no estudo de gerenciamento de banco de dados precisa entender os conceitos fundamentais sobre o assunto, assim como o modelo de dados relacionais. Com inúmeros exemplos e bancos de dados de amostra, o texto permite ao aluno adquirir capacidade para resolver, de forma sistemática, problemas básicos e avançados em formulação de consultas, modelagem de dados, normalização, gerenciamento de dados de aplicação e customização de aplicações de banco de dados. T Ó P I C O S I M P O R TA N T E S • Abordagem abrangente do Oracle 10g e SQL 2003, as aplicações mais recentes de banco de dados. • Informações novas sobre modelagem de dados e requisitos de sistema. • Material atualizado sobre normalização. • Enfoque especial em otimização de banco de dados físico. • Tendências mais atuais em data warehousing e data mining. • Abordagem profunda de computação distribuída. • Questões e problemas no final dos capítulos, além de resumo da sintaxe do SQL:2003. • Utilização do ER Assistant, uma ferramenta fácil de usar que oferece uma interface simples para criar e con- verter os diagramas entidade-relacionamento apresentados no livro. Projeto, Desenvolvimento de Aplicações e Administração de Banco de Dados apresenta as tecnologias fundamentais de banco de dados para cada ambiente de processamento e discute o relacionamento de cada tecnologia com os avanços do comércio eletrônico e da computação corporativa. Essas características fazem deste livro-texto uma ferramenta essencial para aquele que pretende tornar-se especialista em projeto e desenvolvimento de aplicações de banco de dados. Aplicações: Livro-texto para os cursos da área de Computação, como Engenharia de Computação, Ciência da Computação e Sistemas de Informação, nas disciplinas específicas de Banco de Dados e outras relacionadas a Desenvolvimento de Sistemas. Interessa também aos tecnólogos e aos profissionais que atuam na área de Desenvolvimento de Software utilizando as mais recentes e avançadas tecnologias de bancos de dados. Ideal para estudantes e profissionais que precisam desenvolver e man- ter aplicações baseadas em banco de dados. CRIE UMA BASE SÓLIDA PARA GERENCIAMENTO DE BANCO DE DADOS PROJETO, DESENVOLVIMENTO DE APLICAÇÕES & ADMINISTRAÇÃO DE BANCO DE DADOS PRO JETO , D ESEN VO LVIM EN TO D E A PLIC A Ç Õ ES & A D M IN ISTRA Ç Ã O D E BA N C O D E D A D O S M annino T E R C E I R A E D I Ç Ã O TRADUÇÃO DA TERCEIRA EDIÇÃO Michael V. ManninoVisite nosso site: www.mcgraw-hill.com.br9 2 6 0 2 0 1 ISBN 978-85-7726-020-1 7 8 8 5 7 7 TRADUÇÃO DA M284p Mannino, Michael V. Projeto, desenvolvimento de aplicações e administração de banco de dados [recurso eletrônico] / Michael V. Mannino ; tradução: Beth Honorato ... [et al.] ; revisão técnica: Antônio Fernandes Nunes Guardado, Sidney da Silva Viana. – 3. ed. – Dados eletrônicos. – Porto Alegre: AMGH, 2014. Editado também como livro impresso em 2008. ISBN 978-85-8055-363-5 1. Banco de dados – Gerenciamento – Programas de computador. 2. Projeto de banco de dados. 3. Software de aplicativos – Desenvolvimento. I. Título. CDD 005.74 CDU 004.658 Catalogação na publicação: Ana Paula M. Magnus – CRB 10/2052 Relembrando que a tabela Professor1 com relacionamento com a tabela Professor repre- senta um relacionamento de auto-referência com SupervisorProf como chave estrangeira. 4.2.1 Problemas de Tabela Única Começando com a instrução SELECT simples do Exemplo 4.1. Em todos os exemplos, as palavras-chave aparecem em maiúscula, enquanto as informações específicas da consulta aparecem tanto em maiúscula como minúscula. No Exemplo 4.1, apenas a tabela Aluno apa- rece listada na cláusula FROM porque as condições na cláusula WHERE e nas colunas depois da palavra-chave SELECT envolvem somente a tabela Aluno. No Oracle, no final da instrução aparece um ponto-e-vírgula (;) ou / (em uma linha separada). 84 Parte Dois Entendendo Bancos de Dados Relacionais TABELA 4.7 Amostra da Tabela Matricula NumOfer CPFAluno NotaMar 1234 123-45-6789 3,3 1234 234-56-7890 3,5 1234 345-67-8901 3,2 1234 456-78-9012 3,1 1234 567-89-0123 3,8 1234 678-90-1234 3,4 4321 123-45-6789 3,5 4321 124-56-7890 3,2 4321 789-01-2345 3,5 4321 876-54-3210 3,1 4321 890-12-3456 3,4 4321 901-23-4567 3,1 5555 123-45-6789 3,2 5555 124-56-7890 2,7 5678 123-45-6789 3,2 5678 234-56-7890 2,8 5678 345-67-8901 3,3 5678 456-78-9012 3,4 5678 567-89-0123 2,6 5679 123-45-6789 2,0 5679 124-56-7890 3,7 5679 678-90-1234 3,3 5679 789-01-2345 3,8 5679 890-12-3456 2,9 5679 901-23-4567 3,1 6666 234-56-7890 3,1 6666 567-89-0123 3,6 7777 876-54-3210 3,4 7777 890-12-3456 3,7 7777 901-23-4567 3,4 9876 124-56-7890 3,5 9876 234-56-7890 3,2 9876 345-67-8901 3,2 9876 456-78-9012 3,4 9876 567-89-0123 2,6 9876 678-90-1234 3,3 9876 901-23-4567 4,0 04manCap04.qxd 24.10.07 13:42 Page 84 A Tabela 4.8 descreve os operadores de comparação padrão. É importante observar que alguns operadores dependem do SGBD. O Exemplo 4.2 é ainda mais simples que o Exemplo 4.1. O resultado é idêntico ao da tabela Professor original da Tabela 4.4. O Exemplo 4.2 utiliza um atalho para listar todas as colunas. O asterisco * exibido na lista de colunas indica que todas as colunas das tabelas na cláusula FROM aparecem no resultado. O asterisco serve como caractere curinga, para uma busca exata de todos os nomes nas colunas. Capítulo 4 Formulação de Consultas com SQL 85 FIGURA 4.1 Janela de Relacionamento do Banco de Dados da Universidade EXEMPLO 4.1 Testar Linhas Utilizando a Cláusula WHERE Recuperar o nome, a cidade e a média geral de notas (GPA) dos estudantes com média alta (maior que ou igual a 3.7). O resultado segue a instrução SELECT. SELECT NomeAluno, SobrenomeAluno, CidadeAluno, MediaAluno FROM Aluno WHERE MediaAluno >= 3,7 TABELA 4.8 Operadores de Comparação Padrão NomeAluno SobrenomeAluno CidadeAluno MediaAluno CRISTOPHER COLAN SEATTLE 4,00 WILLIAM PILGRIM BOTHELL 3,80 Operador de Comparação Significado = igual a < menor que > maior que <= menor que ou igual a >= maior que ou igual a < > or != diferente de (verificar no SGBD) 04manCap04.qxd 24.10.07 13:42 Page 85 EXEMPLO 4.3 Expressões nas Cláusulas SELECT e WHERE (Access) Listar o nome, a cidade e os salários reajustados dos professores contratados depois de 1996. A função ano extrai parte de uma coluna com dados do tipo data. SELECT NomeProf, SobrenomeProf, CidadeProf, SalarioProf*1,1 AS SalarioReajustado, DataAdmProf FROM Professor WHERE year(DataAdmProf) > 1996 O Exemplo 4.3 descreve as expressões nas cláusulas SELECT e WHERE. A expressão na cláusula SELECT aumenta o salário em 10%. A palavra-chave AS é usada para renomear a coluna computada. Se a coluna não for renomeada, a maioria dos SGBDs gera nomes sem sentido como Expr001. A expressão na cláusula WHERE extrai o ano da data de con- tratação. Como as funções para dados referentes a datas não são padronizadas, são forneci- das formulações de Access e Oracle. Para dominar o SQL em um SGBD específico, é necessário estudar as funções disponíveis, principalmente com as colunas de datas. 86 Parte Dois Entendendo Bancos de Dados Relacionais EXEMPLO 4.2 Mostrar Todas as Colunas Listar todas as colunas e linhas da tabela Professor. A tabela resultante é mostrada em duas partes. SELECT * FROM Professor CPFProf NomeProf SobrenomeProf CidadeProf UFProf DeptoProf ClassificacaoProf SalarioProf 098-76-5432 LEONARD VINCE SEATTLE WA MS ASSISTENTE $35.000 543-21-0987 VICTORIA EMMANUEL BOTHELLWA MS CATEDRATICO $120.000 654-32-1098 LEONARD FIBON SEATTLE WA MS ASSOCIADO $70.000 765-43-2109 NICKI MACON BELLEVUE WA FINAN CATEDRATICO $65.000 876-54-3210 CRISTOPHER COLAN SEATTLE WA MS ASSISTENTE $40.000 987-65-4321 JULIA MILLS SEATTLE WA FINAN ASSOCIADO $75.000 CPFProf SupervisorProf DataAdmProf CEPProf 098-76-5432 654-32-1098 10-abr-1995 98111-9921 543-21-0987 15-abr-1996 98011-2242 654-32-1098 543-21-0987 01-mai-1994 98121-0094 765-43-2109 11-abr-1997 98015-9945 876-54-3210 654-32-1098 01-mar-1999 98114-1332 987-65-4321 765-43-2109 15-mar-2000 98114-9954 NomeProf SobrenomeProf CidadeProf SalarioReajustado DataAdmProf NICKI MACON BELLEVUE 71.500 11-abr-1997 CRISTOPHER COLAN SEATTLE 44.000 01-mar-1999 JULIA MILLS SEATTLE 82.500 15-mar-2000 04manCap04.qxd 24.10.07 13:42 Page 86 A comparação inexata apóia condições correspondentes a algum padrão em vez de bus- car uma string idêntica. Um dos tipos mais comuns de busca inexata é a busca de valores com um prefixo em comum como “SI4” (Cursos SI de nível 400). O Exemplo 4.4 utiliza o operador LIKE juntamente com um caractere de busca de padrões * para executar a busca de prefixo2. A constante de string 'SI4*' significa strings exatas começando com “SI4” e ter- minando com qualquer coisa. O caractere curinga * busca qualquer string. A formulação Oracle do Exemplo 4.4 utiliza o símbolo de porcentagem %, padrão SQL:2003 para carac- tere curinga. É importante observar que as constantes de string devem ficar entre aspas3. Capítulo 4 Formulação de Consultas com SQL 87 EXEMPLO 4.3 Expressões nas Cláusulas SELECT e WHERE (Oracle) A função to_char extrai o ano de quatro dígitos da coluna DataAdmProf e a função to_number converte a representação de caracteres do ano em número. SELECT NomeProf, SobrenomeProf, CidadeProf, SalarioProf*1.1 AS SalarioReajustado, DataAdmProf FROM Professor WHERE to_number(to_char(DataAdmProf, 'YYYY') ) > 1996 EXEMPLO 4.4 Comparação Inexata com o Operador LIKE (Access) Listar os cursos SI de nível avançado. SELECT * FROM Curso WHERE NumCurso LIKE 'SI4*' EXEMPLO 4.4 Comparação Inexata com o Operador LIKE (Oracle) Listar os cursos SI de nível avançado. SELECT * FROM Curso WHERE NumCurso LIKE 'SI4%' NumCurso DescrCurso CargaHoraCurso SI460 ANÁLISE DE SISTEMAS 4 SI470 COMUNICAÇÃO DE DADOS 4 SI480 FUNDAMENTOS DE GERENCIAMENTO DE BANCO DE DADOS 4 2 Começando com o Access 2002, os caracteres de busca de padrões podem ser utilizados especificando o modo de consulta ANSI 92 na janela Options. Como as versões anteriores do Access não suportam essa opção e ela não é padrão no Access 2002, este livro adota os caracteres de busca de padrões * e ? nas instruções SQL do Access. 3 A maioria dos SGBDs exige aspas simples, padrão SQL:2003. O Microsoft Access permite a utilização de aspas simples ou duplas para constantes de strings. 04manCap04.qxd 24.10.07 13:42 Page 87 Outro tipo comum de busca inexata é a busca de strings contendo uma substring. Para executar esse tipo de busca, é necessário utilizar um caractere curinga antes e depois da subs- tring. Por exemplo, para encontrar cursos contendo a palavra BANCO DE DADOS em qual- quer parte da descrição do curso, escrever a condição: DescrCurso LIKE '*BANCO DE DADOS*' no Access ou DescrCurso LIKE '%BANCO DE DADOS%' no Oracle. O caractere curinga não é o único para busca de padrões. O SQL:2003 especifica o caractere de sublinhado _ para buscar qualquer caractere único. Alguns SGBDs, como o Access, utilizam o ponto de interrogação ? para buscar qualquer caractere único. Além disso, a maioria dos SGBDs possui caracteres de busca de padrões para buscar uma variação de caracteres (por exemplo, os dígitos de 0 a 9) e qualquer caractere de uma lista de carac- teres. Os símbolos usados para esses outros caracteres de busca de padrões não são padronizados. Para dominar a escrita de condições de busca inexata, é necessário estudar os caracteres de busca de padrões específicos de determinado SGBD. Além de executar a busca de padrões com strings, é possível utilizar a comparação exata com o operador de comparação de igualdade =. Por exemplo, a condição NumCurso = 'SI480' corresponde a uma única linha na tabela Curso. Tanto na busca exata como na ine- xata, é importante observar a distinção entre maiúsculas e minúsculas. Alguns SGBDs, como o Microsoft Access, não observam tal distinção. No Access SQL, a condição mencionada no exemplo busca “si480”, “sI480” e “Si480” além de “SI480”. Outros SGBDs, como o Oracle, observam a correspondência exata entre maiúsculas e minúsculas. No Oracle SQL, a condição mostrada no exemplo busca apenas “SI480”, e não “si480”, “Si480” ou “sI480”. Para não confundir, é possível utilizar as funções upper ou lower do Oracle para converter as strings em maiúsculas ou minúsculas respectivamente. O Exemplo 4.5 descreve uma variação de busca de uma coluna com dados do tipo data. No Access SQL, os símbolos de cerquilha incluem as constantes de datas, enquanto no Oracle SQL, as aspas simples incluem as constantes de datas. As colunas de data podem ser comparadas do mesmo modo como são comparados os números, utilizando os operadores de comparação comuns (=, < etc.). O operador BETWEEN-AND define um intervalo fechado (inclui ponto final). No Exemplo 4.5 de Access, a condição BETWEEN-AND é um atalho para DataAdmProf >= #1/1/1999# AND DataAdmProf <= #12/31/2000#. 88 Parte Dois Entendendo Bancos de Dados Relacionais operador BETWEEN-AND um operador de atalho para testar uma coluna de data ou numérica contra uma gama de valores. O operador BETWEEN-AND retorna verdadeiro se a coluna é maior ou igual ao primeiro valor e menor ou igual ao segundo valor. EXEMPLO 4.5 Condições em Colunas de Data (Access) Listar o nome e a data de contratação dos professores contratados em 1999 ou 2000. SELECT NomeProf, SobrenomeProf, DataAdmProf FROM Professor WHERE DataAdmProf BETWEEN #1/1/1999# AND #12/31/2000# NomeProf SobrenomeProf DataAdmProf CRISTOPHER COLAN 01-mar-1994 JULIA MILLS 15-mar-2000 EXEMPLO 4.5 Condições em Colunas de Data (Oracle) No Oracle SQL, o formato-padrão de datas é DD-Mon-YYYY, onde DD é o dia, Mon é a abreviação do mês, e YYYY é o ano de quatro dígitos. SELECT NomeProf, SobrenomeProf, DataAdmProf FROM Professor WHERE DataAdmProf BETWEEN '1-Jan-1999' AND '12/31/2000' 04manCap04.qxd 24.10.07 13:42 Page 88 Além de testar as colunas comparando os valores especificados, às vezes, é necessário testar a ausência de valor. Quando não existe nenhum valor normal para uma coluna, são uti- lizados valores nulos. A nulidade pode significar valor desconhecido ou valor não aplicável à linha. Na tabela Oferecimento, um valor nulo para CPFProf significa que o instrutor ainda não foi designado. O teste para encontrar valores nulos é executado com o operador de com- paração IS NULL, assim como mostra o Exemplo 4.6. Também é possível utilizar o operador de comparação IS NOT NULL para encontrar valores normais. Capítulo 4 Formulação de Consultas com SQL 89 EXEMPLO 4.6 Teste para Encontrar Valores Nulos Listar o número do oferecimento e o número dos cursos oferecidos no verão de 2006 sem instrutor designado. SELECT NumOfer, NumCurso FROM Oferecimento WHERE CPFProf IS NULL AND TrimestreOfer = 'VERAO' AND AnoOfer = 2006 EXEMPLO 4.7 Expressão Lógica Complexa Listar o número de oferecimento, o número do curso e o número de CPF dos professores dos cursos programados para serem oferecidos no outono de 2005 ou inverno de 2006. SELECT NumOfer, NumCurso, CPFProf FROM Oferecimento WHERE (TrimestreOfer = 'OUTONO' AND AnoOfer = 2005) OR (TrimestreOfer = 'INVERNO' AND AnoOfer = 2006) mistura de AND e OR sempre utilizar parênteses para tornar explícito o agrupamento de condições. NumOfer NumCurso 1111 SI320 NumOfer NumCurso CPFProf 1234 SI320 098-76-5432 4321 SI320 098-76-5432 4444 SI320 543-21-0987 5555 FINAN300 765-43-2109 5678 SI480 987-65-4321 6666 FINAN450 987-65-4321 O Exemplo 4.7 descreve uma expressão lógica complexa envolvendo ambos os opera- dores lógicosAND e OR. Quando se misturam AND e OR em uma expressão lógica, é im- portante utilizar parênteses. Senão, o leitor da instrução SELECT pode não entender como estão agrupadas as condições AND e OR. Sem a utilização de parênteses, as condições AND e OR são agrupadas no modo padrão. 4.2.2 Junção de Tabelas O Exemplo 4.8 demonstra uma junção das tabelas Curso e Oferecimento. A condição de junção Curso.NumCurso = Oferecimento.NumCurso é especificada na cláusula WHERE. 04manCap04.qxd 24.10.07 13:42 Page 89 Existem dois outros itens interessantes no Exemplo 4.8. Em primeiro lugar, os nomes da coluna NumCurso devem ser qualificados (prefixados) com um nome de tabela (Curso ou Oferecimento). Senão, a instrução SELECT fica ambígua porque NumCurso pode se referir a uma coluna ou da tabela Curso ou da Oferecimento. Em segundo lugar, as duas tabelas devem ser listadas na cláusula FROM, embora as colunas resultantes derivem somente da tabela Oferecimento. A tabela Curso é necessária na cláusula FROM porque as condições na cláusula WHERE consultam a coluna DescrCurso da tabela Curso. O Exemplo 4.9 demonstra outra junção, mas dessa vez as colunas resultantes derivam de ambas as tabelas. Em cada tabela, existem outras condições além das condições de junção. A formulação Oracle utiliza % em vez de * como caractere curinga. 90 Parte Dois Entendendo Bancos de Dados Relacionais EXEMPLO 4.8 Juntar Tabelas, mas Mostrar Colunas de Uma Única Tabela (Access) Listar o número do oferecimento, o número do curso, os dias e os horários dos oferecimentos contendo as palavras banco de dados ou programação na descrição do curso e os cursos oferecidos na primavera de 2006. A versão Oracle deste exemplo utiliza % em vez de * como caractere curinga. SELECT NumOfer, Oferecimento.NumCurso, DiasSemanaOfer, HorarioOfer FROM Oferecimento, Curso WHERE TrimestreOfer = 'PRIMAVERA' AND AnoOfer = 2006 AND (DescrCurso LIKE '*BANCO DE DADOS*' OR DescrCurso LIKE '*PROGRAMACAO*') AND Curso.NumCurso = Oferecimento.NumCurso NumOfer NumCurso DiasSemanaOfer HorarioOfer 3333 SI320 SEG-QUA 8h30 5679 SI480 TER-QUI 15h30 EXEMPLO 4.9 Juntar Tabelas e Mostrar Colunas de Ambas as Tabelas (Access) Listar o número de oferecimento, o número do curso e o nome do professor dos cursos SI oferecidos e programados no outono de 2005, ministrados por professores assistentes. SELECT NumOfer, NumCurso, NomeProf, SobrenomeProf FROM Oferecimento, Professor WHERE TrimestreOfer = 'OUTONO' AND AnoOfer = 2005 AND ClassificacaoProf = 'ASSISTENTE' AND NumCurso LIKE 'SI*' AND Professor.CPFProf = Oferecimento.CPFProf NumOfer NumCurso NomeProf SobrenomeProf 1234 SI320 LEONARD VINCE 4321 SI320 LEONARD VINCE 04manCap04.qxd 24.10.07 13:42 Page 90 No padrão SQL:2003, a operação de junção pode ser expressa diretamente na cláusula FROM em vez de ser expressa nas cláusulas FROM e WHERE, como mostram os exemplos 4.8 e 4.9. Observe que o Oracle, começando com a versão 9i, suporta operações de junção na cláusula FROM, mas as versões anteriores não suportam operações de junção na cláusula FROM. Para executar uma operação de junção na cláusula FROM, utilizar as palavras-chave INNER JOIN, assim como mostra o Exemplo 4.10. As condições de junção são indicadas pela palavra-chave ON dentro da cláusula FROM. Note que a condição de junção não aparece mais na cláusula WHERE. Capítulo 4 Formulação de Consultas com SQL 91 EXEMPLO 4.10 Juntar Tabelas Utilizando uma Operação de Junção na Cláusula FROM (Access) Listar o número de oferecimento, o número do curso e o nome do professor dos cursos SI oferecidos e programados no outono de 2005, ministrados por professores assistentes (resultado idêntico ao do Exemplo 4.9). No Oracle, utilizar % em vez de *. SELECT NumOfer, NumCurso, NomeProf, SobrenomeProf FROM Oferecimento INNER JOIN Professor ON Professor.CPFProf = Oferecimento.CPFProf WHERE TrimestreOfer = 'OUTONO' AND AnoOfer = 2005 AND ClassificacaoProf = 'ASSISTENTE' AND NumCurso LIKE 'SI*' EXEMPLO 4.9 Juntar Tabelas e Mostrar Colunas de Ambas as Tabelas (Oracle) Listar o número de oferecimento, o número do curso e o nome do professor dos cursos SI oferecidos e programados no outono de 2005, ministrados por professores assistentes. SELECT NumOfer, NumCurso, NomeProf, SobrenomeProf FROM Oferecimento, Professor WHERE TrimestreOfer = 'OUTONO' AND AnoOfer = 2005 AND ClassificacaoProf = 'ASSISTENTE' AND NumCurso LIKE 'SI%' AND Professor.CPFProf = Oferecimento.CPFProf lembrete sobre a cláusula GROUP BY as colunas na cláusula SELECT devem ou estar na cláusula GROUP BY ou ser parte de um cálculo resumido com uma função agregada. 4.2.3 Resumo de Tabelas com GROUP BY e HAVING Até aqui, os resultados de todos os exemplos apresentados nesta seção estão relacionados a linhas individuais. Mesmo o Exemplo 4.9 está relacionado a uma combinação de colunas das linhas individuais Oferecimento e Professor. Assim como já foi mencionado no Capí- tulo 3, algumas vezes é importante mostrar resumos de linhas. As cláusulas GROUP BY e HAVING são utilizadas para mostrar os resultados relacionados a grupos de linhas e não a linhas individuais. O Exemplo 4.11 descreve a cláusula GROUP BY para resumir grupos de linhas. Cada linha resultante contém um valor do agrupamento de colunas (Especializacao), juntamente com os cálculos agregados resumindo as linhas com o mesmo valor do agrupamento de colunas. A cláusula GROUP BY deve conter cada coluna na cláusula SELECT exceto para expressões agregadas. Por exemplo, se a coluna TurmaAluno for adicionada na cláusula SELECT, o Exemplo 4.11 torna-se inválido a menos que a coluna TurmaAluno também seja adicionada na cláusula GROUP BY. 04manCap04.qxd 24.10.07 13:42 Page 91 A Tabela 4.9 mostra as funções agregadas padrão. No caso de cálculos estatísticos que não podem ser realizados com essas funções, verificar o SGBD específico. A maioria dos SGBDs possui muitas funções além desses padrões. As funções COUNT, AVG e SUM suportam a palavra-chave DISTINCT para restringir a computação de valores únicos de coluna. O Exemplo 4.12 demonstra a palavra-chave DISTINCT para a função COUNT. Este exemplo recupera a quantidade de oferecimentos em um ano e o número de cursos distintos ministrados. Alguns SGBDs, como o Microsoft Access, 92 Parte Dois Entendendo Bancos de Dados Relacionais TABELA 4.9 Funções Agregadas Padrão Função Agregada Significado e Comentários COUNT(*) Computa o número de linhas. COUNT(column) Conta os valores válidos contidos na coluna; é possível utilizar DISTINCT para contar os valores únicos contidos na coluna. AVG Computa a média de uma expressão ou coluna numérica excluindo os valores nulos; é possível utilizar DISTINCT para computar a média de valores únicos contidos na coluna. SUM Computa a soma de uma expressão ou coluna numérica excluindo os valores nulos; é possível utilizar DISTINCT para computar a média de valores únicos contidos na coluna. MIN Computa o menor valor. Para colunas de string, a seqüência de intercalação é utilizada para comparar strings. MAX Computa o maior valor. Para colunas de string, a seqüência de intercalação é utilizada para comparar strings. EXEMPLO 4.11 Agrupar em uma Única Coluna Resumir a média geral de notas de estudantes por área de especialização. SELECT Especializacao, AVG(MediaAluno) AS MediaGeral FROM Aluno GROUP BY Especializacao Especializacao MediaGeral CONTB 3,39999997615814 FINAN 2,80000003178914 SI 3,23333330949148 EXEMPLO 4.12 Contar Linhas e Coluna de Valores Únicos (Oracle) Resumir a quantidade de oferecimentos e os cursos únicos por ano. SELECT AnoOfer, COUNT(*) AS QtdeOferecimentos, COUNT(DISTINCT NumCurso) AS QtdeCursos FROM Oferecimento GROUP BY AnoOfer uso da função COUNT as funções COUNT(*) e COUNT(column) produzem resultados idênticos, exceto quando a “coluna” contém valores nulos. Ver o Capítulo 9 para obter mais detalhes sobre o efeito dos valores nulos nas funções agregadas. AnoOfer QtdeOferecimentos QtdeCursos 2005 3 2 2006 10 6 04manCap04.qxd 24.10.0713:42 Page 92 EXEMPLO 4.14 Agrupar com Condições de Linha e Grupo Resumir a média geral de notas (GPA) dos estudantes da divisão superior (júnior e sênior) por área de especialização. Listar somente as especializações com média geral maior que 3,1. SELECT Especializacao, AVG(MediaAluno) AS MediaGeral FROM Aluno WHERE Turma IN ('JR', 'SR') GROUP BY Especializacao HAVING AVG(MediaAluno) > 3,1 não têm suporte para a palavra-chave DISTINCT dentro de funções agregadas. O Capítulo 9 apresenta uma formulação alternativa em Access SQL para compensar a impossibilidade de utilizar a palavra-chave DISTINCT dentro da função COUNT. Os exemplos 4.13 e 4.14 contrastam as cláusulas WHERE e HAVING. No Exemplo 4.13, a cláusula WHERE seleciona os estudantes da divisão superior (júniores ou sêniores) antes de agrupá-los por área de especialização. Como a cláusula WHERE elimina os estu- dantes antes de agrupá-los, somente os estudantes da divisão superior são agrupados. No Exemplo 4.14, uma condição HAVING retém os grupos com uma média geral de notas maior que 3,1. A cláusula HAVING é aplicada a grupos de linhas, enquanto a cláusula WHERE é aplicada a linhas individuais. Para utilizar a cláusula HAVING, é necessária a existência de uma cláusula GROUP BY. Capítulo 4 Formulação de Consultas com SQL 93 EXEMPLO 4.13 Agrupar com Condições de Linha Resumir a média geral de notas dos estudantes da divisão superior (júnior e sênior) por área de especialização. SELECT Especializacao, AVG(MediaAluno) AS MediaGeral FROM Aluno WHERE Turma = 'JR' OR Turma = 'SR' GROUP BY Especializacao Especializacao Media Geral CONTB 3,5 FINAN 2,800000031789 SI 3,149999976158 Especializacao MediaGeral CONTB 3,5 SI 3,149999976158 lembrete sobre a cláusula HAVING a cláusula HAVING deve ser precedida da cláusula GROUP BY. WHERE x HAVING utilizar a cláusula WHERE para condições que possam ser testadas em linhas individuais. Utilizar a cláusula HAVING para condições que possam ser testadas somente em grupos. As condições na cláusula HAVING devem envolver funções agregadas, enquanto as condições na cláusula WHERE não podem envolver funções agregadas. Outro destaque sobre os exemplos 4.13 e 4.14 é a utilização do operador OR em com- paração com o operador IN (elemento do conjunto de operador). A condição WHERE nos exemplos 4.13 e 4.14 retém as mesmas linhas. A condição IN é verdadeira se TurmaAluno corresponder a qualquer valor na lista entre parênteses. O Capítulo 9 apresenta mais expli- cações sobre o operador IN para consultas aninhadas. 04manCap04.qxd 24.10.07 13:42 Page 93 Encerra aqui o trecho do livro disponibilizado para esta Unidade de Aprendizagem. Na Biblioteca Virtual da Instituição, você encontra a obra na íntegra.
Compartilhar