Banco de Dados - Aula 06 - Manipulacao Avancada e Agrupamento de Dados

Banco de Dados Prof Daniel Callegari Aula 06 Aula 06 Boasvindas à segunda parte da disciplina Revisão dos conteúdos da primeira parte Continuação do conteúdo Manipulação Avançada de Dados Funções Seleção com agrupamentos de dados Dinâmica Aula 06 Apresentação Daniel Antonio Callegari é Doutor em Ciência da Computação pela PUCRS Possui Especialização em Gestão Empresarial Sebrae ANFE Itália e Certificações Microsoft e IBM Associado da Sociedade Brasileira de Computação SBC Foi sócio diretor de empresas de tecnologia e sempre equilibrou a atuação acadêmica com o mundo empresarial Tem mais de 20 anos de experiência na área da Computação Atualmente é professor de disciplinas de engenharia de software banco de dados e programação além de atuar como coordenador de times de desenvolvimento de software em cooperação com a Dell Computadores do Brasil O que você vai aprender nessa aula Parte 1 Revisão de Conteúdos Parte 2 Manipulação Avançada de Dados Parte 3 Agrupamento de Dados Parte 4 Agrupamento de Dados Parte 5 Dinâmica Aula 6 Parte 1 Revisão de Conteúdos Dados Arquitetura de um SGBD Processo de Modelagem de Dados Integridade de Dados Linguagem SQL Aula 6 Parte 2 Manipulação Avançada de Dados Manipulação Avançada de Dados Funções Aula 6 Parte 3 Agrupamento de Dados Cláusula GROUP BY Aula 6 Parte 4 Agrupamento de Dados Cláusula GROUP BY com HAVING Aula 6 Parte 5 Dinâmica Prática com agrupamento de dados O que você vai precisar para acompanhar essa aula livesqloraclecom brModelo Web httpswwwbrmodelowebcomlangptbrindexhtml OU Download brModelo 32 httpssourceforgenetprojectsbrmodelo O que você vai aprender nessa aula Parte 1 Revisão de Conteúdos Parte 2 Manipulação Avançada de Dados Parte 3 Agrupamento de Dados Parte 4 Agrupamento de Dados Parte 5 Dinâmica Revisão 1 Dados 2 Arquitetura de um SGBD 3 Processo de Modelagem de Dados 4 Integridade de Dados 5 Linguagem SQL Revisão 1 Dados 2 Arquitetura de um SGBD 3 Processo de Modelagem 4 Integridade de Dados 5 Linguagem SQL Dado Unidade de informação Banco de dados Coleção de dados armazenados e organizados de modo a atender às necessidades dos seus usuários SGBD Sistema Gerenciador de Banco de Dados Softwares que incorporam as funções de definição recuperação e alteração de dados em um banco de dados Heuser 2009 Puga 2014 Revisão 1 Dados 2 Arquitetura de um SGBD 3 Processo de Modelagem 4 Integridade de Dados 5 Linguagem SQL Revisão 1 Dados 2 Arquitetura de um SGBD 3 Processo de Modelagem 4 Integridade de Dados 5 Linguagem SQL Revisão 1 Dados 2 Arquitetura de um SGBD 3 Processo de Modelagem 4 Integridade de Dados 5 Linguagem SQL Integridade de Identidade Chaves candidatas Chave primária PK Chaves alternativas UNIQUE Integridade de Domínio Cláusulas de verificação CHECK Integridade Referencial Chaves estrangeiras FOREIGN KEY Revisão 1 Dados 2 Arquitetura de um SGBD 3 Processo de Modelagem 4 Integridade de Dados 5 Linguagem SQL Recuperação de dados SELECT Linguagem de definição de dados DDL Data Definition Language CREATE ALTER DROP RENAME e TRUNCATE Linguagem de manipulação de dados DML Data Manipulation Language Inserções INSERT atualizações UPDATE e exclusões DELETE Linguagem para controle de transações COMMIT ROLLBACK e SAVEPOINT Linguagem para controle de acesso a dados GRANT e REVOKE Resumo do que vimos até agora Revisão de Conceitos Relembrando o conteúdo do vídeo anterior Revisão dos principais conceitos 1 Dados 2 Arquitetura de um SGBD 3 Processo de Modelagem 4 Integridade de Dados 5 Linguagem SQL O que você vai aprender nessa aula Parte 1 Revisão de Conteúdos Parte 2 Manipulação Avançada de Dados Parte 3 Agrupamento de Dados Parte 4 Agrupamento de Dados Parte 5 Dinâmica O que você vai precisar para acompanhar essa aula livesqloraclecom brModelo Web httpswwwbrmodelowebcomlangptbrindexhtml OU Download brModelo 32 httpssourceforgenetprojectsbrmodelo Aula 6 Manipulação Avançada de Dados Funções Agrupamentos categorias segmentações regiões cidades etc Funções Em SQL podemos aplicar dois tipos de funções sobre linhas de uma tabela Funções sobre linhas as quais operam sobre cada linha do resultado individualmente e Funções sobre conjuntos de linhas que operam sobre diversas linhas calculando valores sobre todo o conjunto para determinar totais médias o maior valor entre outras possibilidades Funções As funções numéricas mais comuns são ABSn ACOSn ASINn ATANn ATAN2n CEILn COSn COSHn EXPn FLOORn LNn LOGn MODnm POWERnm ROUNDnm SIGNn SINn SINHn SQRTn TANn TANHn TRUNCnm ou similares dependendo do SGBD Funções As funções mais comuns sobre caracteres são LOWERs UPPERs INITCAPs LTRIMs1s2 RTRIMs1s2 CONCATs1s2 LPADs1ns2 RPADs1ns2 LENGTHs SUBSTRsnm REPLACEs1s2s3 CHRn SOUNDEXs TRANSLATEs1s2s3 ou similares dependendo do SGBD Funções Exemplos SELECT UPPERtitulo ROUND11 preco 2 FROM produtos SELECT SUBSTRplaca 1 3 FROM veiculos SELECT INITCAPnome FROM pacientes Funções Exemplos SELECT UPPERtitulo ROUND11 preco 2 FROM produtos INTRODUÇÃO A SQL 4688 PROGRAMANDO EM JAVA 8914 QUALIDADE DE SOFTWARE 9264 Funções Exemplos SELECT SUBSTRplaca 1 3 FROM veiculos IJK ABC RSV IMM Funções Exemplos SELECT INITCAPnome FROM pacientes Josiane Oliveira Carlos Santos Maria Eduarda Nunes Pedro Souza Neto Funções de Agregação Uma função de agregação ou função agregada é uma função que opera sobre um conjunto de linhas As funções de agregação permitem calcular Valores totais para toda uma tabela e Subtotais para toda uma tabela agrupando o resultado por determinado atributo e apresentandoo como uma nova coluna Funções de Agregação A sintaxe geral de uma função de agregação é SELECT funçãoagregada FROM nomedatabela Funções de Agregação Já aprendemos sobre uma das funções de agregação anteriormente lembrese de COUNT As funções agregadas mais comuns são COUNT COUNT ALLDISTINCT nomedacoluna SUM ALLDISTINCT nomedacoluna AVG ALLDISTINCT nomedacoluna MAX ALLDISTINCT nomedacoluna MIN ALLDISTINCT nomedacoluna STDDEV ALLDISTINCT nomedacoluna VARIANCE ALLDISTINCT nomedacoluna Funções de Agregação Alguns exemplos SELECT AVGpreco MEDIA FROM PRODUTOS SELECT MAXpreco FROM PRODUTOS SELECT COUNT AS NUMCLIENTES FROM CLIENTES SELECT COUNTemail FROM CLIENTES Dinâmica Usaremos um banco de dados de filmes CREATE TABLE Filmes titulo VARCHAR250 ano NUMBER4 diretor VARCHAR100 genero VARCHAR80 atoresPrincipais VARCHAR1000 duracao NUMBER4em minutos valorIngresso NUMBER52 Resumo do que vimos até agora Revisão de Conceitos Introdução à Manipulação Avançada de Dados Funções Relembrando o conteúdo do vídeo anterior Introdução à Manipulação Avançada de Dados Funções O que você vai aprender nessa aula Parte 1 Revisão de Conteúdos Parte 2 Manipulação Avançada de Dados Parte 3 Agrupamento de Dados Parte 4 Agrupamento de Dados Parte 5 Dinâmica O que você vai precisar para acompanhar essa aula livesqloraclecom brModelo Web httpswwwbrmodelowebcomlangptbrindexhtml OU Download brModelo 32 httpssourceforgenetprojectsbrmodelo Funções de Agregação Uma função de agregação ou função agregada é uma função que opera sobre um conjunto de linhas As funções de agregação permitem calcular Valores totais para toda uma tabela e Subtotais para toda uma tabela agrupando o resultado por determinado atributo e apresentandoo como uma nova coluna Funções de Agregação A sintaxe geral de uma função de agregação é SELECT funçãoagregada FROM nomedatabela Funções de Agregação Já aprendemos sobre uma das funções de agregação anteriormente lembrese de COUNT As funções agregadas mais comuns são COUNT COUNT ALLDISTINCT nomedacoluna SUM ALLDISTINCT nomedacoluna AVG ALLDISTINCT nomedacoluna MAX ALLDISTINCT nomedacoluna MIN ALLDISTINCT nomedacoluna STDDEV ALLDISTINCT nomedacoluna VARIANCE ALLDISTINCT nomedacoluna Funções de Agregação Alguns exemplos SELECT AVGpreco MEDIA FROM PRODUTOS SELECT MAXpreco FROM PRODUTOS SELECT COUNT AS NUMCLIENTES FROM CLIENTES SELECT COUNTemail FROM CLIENTES A cláusula GROUP BY Em SQL a cláusula GROUP BY pode ser utilizada em um comando SELECT para agrupar os resultados de uma consulta gerando subtotais por grupos em novas colunas A forma geral do comando é SELECT nomedacoluna funçãoagregada FROM nomedatabela GROUP BY ALL nomedacoluna A cláusula GROUP BY Observações sobre a cláusula GROUP BY O operador ALL inclui no resultado todos os grupos incluindo aqueles que não atendem às condições de busca As colunas contidas na cláusula SELECT devem estar todas obrigatoriamente na cláusula GROUP BY As colunas da cláusula GROUP BY não precisam estar na cláusula SELECT Podese agrupar também por mais de uma coluna Uma cláusula adicional ORDER BY é bastante comum nesses casos porque organiza o resultado da consulta Dinâmica Vamos criar uma tabela para armazenar dados de produtos classificados em Suprimentos Componentes e Periféricos CREATE TABLE PRODS codigo NUMERIC3 NOT NULL nome VARCHAR50 NOT NULL preco NUMERIC52 NOT NULL tipo CHAR1 NULL Suprimento Componente Periférico CONSTRAINT PK1 PRIMARY KEY codigo Dinâmica INSERT INTO PRODS VALUES 10 HD 200 C INSERT INTO PRODS VALUES 11 Memoria 250 C INSERT INTO PRODS VALUES 12 Impressora 680P INSERT INTO PRODS VALUES 13 Processador 600 C INSERT INTO PRODS VALUES 14 Tinta 38 S INSERT INTO PRODS VALUES 15 Papel A4 19 S INSERT INTO PRODS VALUES 16 Scanner 199 P Dinâmica Escrevendo comandos SELECT para as seguintes consultas a Quantos produtos existem na tabela PRODS b Quantos tipos de produtos existem na tabela PRODS c Quantos produtos existem de cada tipo d Qual a média de preço de todos os produtos e Qual a média de preço dos suprimentos tipo S f Qual a média de preço dos produtos de cada tipo Dinâmica Siga as instruções do professor para executar os seguintes comandos adicionais ALTER TABLE PRODS ADD usuario NUMBER1 NULL UPDATE PRODS SET usuario 1 WHERE codigo IN 10121314 UPDATE PRODS SET usuario 2 WHERE usuario IS NULL SELECT tipo usuario AVGpreco FROM PRODS GROUP BY tipo usuario ORDER BY tipo usuario UPDATE PRODS SET usuario 2 WHERE codigo 14 UPDATE PRODS SET usuario NULL WHERE codigo 13 Resumo do que vimos até agora Revisão de Conceitos Introdução à Manipulação Avançada de Dados Funções Agregações Cláusula GROUP BY Relembrando o conteúdo do vídeo anterior Introdução à Manipulação Avançada de Dados Funções Agrupamentos Cláusula GROUP BY O que você vai aprender nessa aula Parte 1 Revisão de Conteúdos Parte 2 Manipulação Avançada de Dados Parte 3 Agrupamento de Dados Parte 4 Agrupamento de Dados Parte 5 Dinâmica O que você vai precisar para acompanhar essa aula livesqloraclecom brModelo Web httpswwwbrmodelowebcomlangptbrindexhtml OU Download brModelo 32 httpssourceforgenetprojectsbrmodelo Aula 6 Manipulação Avançada de Dados Agrupamentos e seleção de grupos A cláusula GROUP BY HAVING A cláusula HAVING é usada em conjunto com a cláusula GROUP BY Ela determina as condições sobre as quais será realizada a composição dos grupos Em outras palavras a cláusula HAVING serve para decidir quais dos grupos gerados farão parte do resultado final Os grupos que não satisfizerem as condições da cláusula HAVING são descartados A cláusula GROUP BY HAVING A forma geral do comando é SELECT nomedacoluna funçãoagregada FROM nomedatabela GROUP BY ALL nomedacoluna HAVING condições ORDER BY colunas Dica As novas colunas geradas pelo cálculo das funções agregadas podem ser referidas na cláusula HAVING Dinâmica Exercício CREATE TABLE Funcionarios cod NUMBER2 PRIMARY KEY nome VARCHAR30 NOT NULL modalidade CHAR1 depto VARCHAR15 salario NUMBER102 Dinâmica Exercício INSERT INTO Funcionarios Cod Nome modalidade Depto Salario VALUES 10Joao PCompras 100000 INSERT INTO Funcionarios Cod Nome modalidade Depto Salario VALUES 12Maria PVendas 97000 INSERT INTO Funcionarios Cod Nome modalidade Depto Salario VALUES 35Pedro PExpedicao 80000 INSERT INTO Funcionarios Cod Nome modalidade Depto Salario VALUES 08Ana PExpedicao 79000 INSERT INTO Funcionarios Cod Nome modalidade Depto Salario VALUES 37Carlos RVendas 209000 INSERT INTO Funcionarios Cod Nome modalidade Depto Salario VALUES 47Marco RCompras 197000 INSERT INTO Funcionarios Cod Nome modalidade Depto Salario VALUES 18Tiago RExpedicao 70000 INSERT INTO Funcionarios Cod Nome modalidade Depto Salario VALUES 32Lucas RVendas 482000 INSERT INTO Funcionarios Cod Nome modalidade Depto Salario VALUES 09ClaudiaRVendas 222000 INSERT INTO Funcionarios Cod Nome modalidade Depto Salario VALUES 15Joana PCompras 77000 Dinâmica Exercício a Quantidade de funcionários por departamento b Que departamentos têm média salarial maior que R 1000 c Média salarial dos funcionários por modalidade do setor de Compras d O maior salário do departamento que possui pelo menos duas pessoas e Número de pessoas por modalidade por departamento f Qual o total da folha de pagamento de cada departamento g Soma dos salários dos Departamentos com mais de x funcionários Para Saber Mais Consultar PUGA S FRANÇA E GOYA M Banco de Dados implementação em SQL PLSQL e Oracle 11g São Paulo Pearson 2014 332p Seção 95 Resumo do que vimos até agora Revisão de Conceitos Introdução à Manipulação Avançada de Dados Há vários tipos de funções que podem ser aplicados aos dados em SQL Há funções que operam sobre valores individuais e funções que operam sobre conjuntos de valores As cláusulas GROUP BY e HAVING complementam o comando SELECT para computar valores para grupos de registros Os agrupamentos podem ser também aplicados a dados provenientes de várias tabelas Uma forma de lembrar o propósito da cláusula HAVING é pensála como sendo o WHERE do GROUP BY ou seja um filtro sobre grupos de registros Relembrando o conteúdo do vídeo anterior Introdução à Manipulação Avançada de Dados Funções Agrupamentos Cláusula GROUP BY HAVING O que você vai aprender nessa aula Parte 1 Revisão de Conteúdos Parte 2 Manipulação Avançada de Dados Parte 3 Agrupamento de Dados Parte 4 Agrupamento de Dados Parte 5 Dinâmica O que você vai precisar para acompanhar essa aula livesqloraclecom brModelo Web httpswwwbrmodelowebcomlangptbrindexhtml OU Download brModelo 32 httpssourceforgenetprojectsbrmodelo Dinâmica Estudo de Caso Médicos Pacientes Fichas de solicitação de Exames Dinâmica Medico crm nome Medico possui Especialidade codigo nome Paciente codigo nome cpf data de nascimento sexo email Ficha atende numero data do exame Exame codigo sigla descricao valor prazo de entrega inclui Dinâmica Medico crm nome FKEspecialidadecodigo Especialidade codigo nome Paciente codigo nome cpf datadenascimento sexo email Fichas numero datadoexame FKMedicocrm FKPacientecodigo Exames codigo sigla descricao valor prazodeentrega FichasExames FKFichaatendenumerc FKExamecodigo Dinâmica Tabela MEDICOS Tabela ESPECIALIDADES Dinâmica Tabela PACIENTES CODPACIENTE NOME CPF DATANASC SEXO EMAIL 1 Juliana Pires 49281225506 04OCT62 F jupiresemailcom 2 Maria Souza 49638826479 16NOV52 F mariaemailcom 3 Jaques Lutti 37843461388 09AUG70 M jaquesemailcom 4 Carla do Santos 70719177871 13SEP84 F csantosemailcom 5 Raquel Gomes 25513392275 21JAN37 F raqgomesemailcom 6 Katia Fernandes 52616855089 21JUL38 F katia22emailcom 7 Marcelo Cunha 64271911445 19MAR75 M mcemailcom 8 Guilherme Alves 83773468423 27DEC98 M guial89emailcom 9 Marcio Perez 71317480210 03JUL94 M marciopemailcom 10 Lucas Florian 58795952268 30DEC85 M florianemailcom Tabela EXAMES CODEXAME SIGLAEXAME DESCRICAO VALOR PRAZODEENTREGA 1 HEMO Hemograma 50 2 2 COHDL Colesterol HDL 25 2 3 COLDL Colesterol LDL 25 2 4 TRIGL Triglicerídios 20 3 5 ACDUR Ácido Úrico 30 4 6 CREAT Creatinina 35 1 7 TGO Transaminase Oxalacética 27 4 8 TGP Transaminase Pirúvica 36 3 9 TSH Tireoestimulante 40 2 10 PSA Antigeno Prostático Especifico 43 2 Dinâmica Tabela FICHAS Tabela FICHASEXAMES select from PACIENTES CODPACIENTE NOME CPF DATANASC SEXO EMAIL 1 Juliana Pires 49281225506 04OCT62 F jupiresemailcom 2 Maria Souza 49638826479 16NOV52 F mariaemailcom 3 Jaques Lutti 37843461388 09AUG70 M jaquesemailcom 4 Carla do Santos 70719177871 13SEP84 F csantosemailcom 5 Raquel Gomes 25513392275 21JAN37 F raqgomesemailcom 6 Katia Fernandes 52616855089 21JUL38 F katia22emailcom 7 Marcelo Cunha 64271911445 19MAR75 M mcemailcom 8 Guilherme Alves 83773468423 27DEC98 M guial89emailcom 9 Marcio Perez 71317480210 03JUL94 M marciopemailcom 10 Lucas Florian 58795952268 30DEC85 M florianemailcom Download CSV 10 rows selected Para Saber Mais Consultar PUGA S FRANÇA E GOYA M Banco de Dados implementação em SQL PLSQL e Oracle 11g São Paulo Pearson 2014 332p Seção 95 Resumo do que vimos até agora Revisão de Conceitos Introdução à Manipulação Avançada de Dados Há vários tipos de funções que podem ser aplicados aos dados em SQL Há funções que operam sobre valores individuais e funções que operam sobre conjuntos de valores As cláusulas GROUP BY e HAVING complementam o comando SELECT para computar valores para grupos de registros Os agrupamentos podem ser também aplicados a dados provenientes de várias tabelas Uma forma de lembrar o propósito da cláusula HAVING é pensála como sendo o WHERE do GROUP BY ou seja um filtro sobre grupos de registros