Aula 07 Banco de Dados - Subconsultas, Operadores de Conjuntos e Dinamica

Banco de Dados Prof Daniel Callegari Aula 07 Aula 07 Subconsultas Como parte de outras consultas Como parte de outros comandos de manipulação de dados Consultas com operadores de conjuntos União Interseção Diferença Dinâmicas O que você vai aprender nessa aula Parte 1 Subconsultas Introdução Parte 2 Subconsultas Parte 3 Atualizações com Subconsultas Parte 4 Operações de Conjuntos Parte 5 Dinâmica Aula 7 Parte 1 Subconsultas Introdução Relembrando o nosso Estudo de Caso Questões motivadoras Aula 7 Parte 2 Subconsultas Subconsultas que retornam um único valor e Subconsultas que retornam um conjunto de valores Exemplos Aula 7 Parte 3 Atualizações com Subconsultas Update Insert Delete Aula 7 Parte 4 Operações de Conjuntos União Interseção Diferença Aula 7 Parte 5 Dinâmica Prática com subconsultas e operadores de conjuntos 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 Subconsultas Introdução Parte 2 Subconsultas Parte 3 Atualizações com Subconsultas Parte 4 Operações de Conjuntos Parte 5 Dinâmica O que você vai precisar para acompanhar essa aula livesqloraclecom Dinâmica Relembrando o nosso Estudo de Caso Médicos Pacientes Fichas de solicitação de Exames Dinâmica Medico crm nome codigo nome cpf data de nascimento sexo email numero data do exame codigo sigla descricao valor prazo de entrega Paciente Especialidade Exame Ficha atende inclui possui 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 Subconsultas Subconsultas são usadas para retornar dados que serão usados na consulta principal como condição para restringir ainda mais os dados a serem recuperados Subconsultas nos permitem realizar consultas mais complexas Subconsultas nos permitem eventualmente substituir JOINs Subconsultas No uso de subconsultas observar que A condição envolve uma operação de comparação entre uma coluna e o resultado que será retornado pela subconsulta A subconsulta pode ser construída de acordo com o problema apontado e incluir condições funções de agrupamento múltiplas colunas etc A subconsulta é executada antes da consulta principal A subconsulta pode ser incluída nas instruções WHERE HAVING ou FROM O operador deve ser adequado ao tipo de retorno da subconsulta uma vez que o retorno pode ser de uma única linha ou de várias linhas PUGA S FRANÇA E GOYA M Banco de Dados implementação em SQL PLSQL e Oracle 11g São Paulo Pearson 2014 332p Resumo do que vimos até agora Revisão do Estudo de Caso Conceito de Subconsultas em SQL Banco de Dados Prof Daniel Callegari Aula 07 Relembrando o conteúdo do vídeo anterior Conceito de Subconsultas em SQL Podem ser utilizadas dentro de outros comandos SQL geralmente dentro de INSERT UPDATE DELETE Bônus Também podem ser usadas dentro de um comando CREATE TABLE O que você vai aprender nessa aula Parte 1 Subconsultas Introdução Parte 2 Subconsultas Parte 3 Atualizações com Subconsultas Parte 4 Operações de Conjuntos Parte 5 Dinâmica O que você vai precisar para acompanhar essa aula livesqloraclecom Subconsultas Na linguagem SQL subconsultas são comandos SELECT aninhados dentro de outros comandos de DML Um SELECT dentro de outro SELECT Um SELECT dentro de um INSERT Um SELECT dentro de um UPDATE ou um SELECT dentro de um DELETE Podemos ter inclusive subconsultas dentro de outras subconsultas A quantidade de níveis permitidos depende do SGBD Subconsultas Exemplos a Quais os exames mais caros do que o exame de Creatinina b Quais as siglas dos exames com prazo de entrega abaixo da média dos exames disponíveis c Quais os nomes dos médicos nas especialidades de Cardiologia e Endocrinologia d Que médicos ainda não pediram exames Subconsultas São comandos SELECT aninhados dentro de outros comandos SELECT INSERT UPDATE ou DELETE Podemos dividir as subconsultas em duas categorias Subconsultas que retornam um único valor e Subconsultas que retornam um conjunto de valores Subconsultas que retornam um único valor Quando uma subconsulta retorna um único valor usamos os operadores básicos de comparação Igualdade Diferença Menor Menor ou igual Maior Maior ou igual Subconsultas que retornam um único valor Exemplo Quais os exames mais caros do que o exame de Creatinina Tabela EXAMES Subconsultas que retornam um único valor Exemplo Quais os exames mais caros do que o exame de Creatinina Passo 1 Quanto custa o exame de Creatinina SELECT valor FROM exames WHERE descricao Creatinina Subconsultas que retornam um único valor Exemplo Quais os exames mais caros do que o exame de Creatinina Passo 1 Quanto custa o exame de Creatinina SELECT valor FROM exames WHERE descricao Creatinina Subconsultas que retornam um único valor Exemplo Quais os exames mais caros do que o exame de Creatinina Passo 1 Quanto custa o exame de Creatinina SELECT valor FROM exames WHERE descricao Creatinina Passo 2 Quais outros exames custam mais do que 3500 SELECT descricao valor FROM exames WHERE valor 35 Subconsultas que retornam um único valor Exemplo Quais os exames mais caros do que o exame de Creatinina Utilizando subconsultas SELECT descricao valor FROM exames WHERE valor SELECT valor FROM exames WHERE descricao Creatinina Observação Repare que a subconsulta é colocada entre parênteses e do lado direito do operador relacional Dinâmica Sobre o Estudo de Caso Quais as siglas dos exames com prazo de entrega abaixo da média dos exames disponíveis Pause o vídeo e tente realiza a consulta Dica Utilize a função de agregação AVG average média Subconsultas que retornam conjuntos Quando uma subconsulta retorna um conjunto de valores não podemos usar um operador relacional comum etc Por que isso ocorre Nesses casos devemos usar operadores especiais IN ANY SOME ALL EXISTS Subconsultas que retornam conjuntos Subconsultas utilizando o operador IN Usamos essa construção para obter as linhas iguais a qualquer linha da subconsulta O operador IN aceita a comparação simultânea de mais de uma coluna A sintaxe geral é SELECT nomedacoluna FROM nomedatabela WHERE condições AND nomedacoluna nomedacoluna NOT IN subconsulta Dinâmica Sobre o Estudo de Caso Quais os nomes dos médicos nas especialidades de Cardiologia e Endocrinologia Pause o vídeo e tente realizar a consulta Dica Utilize o operador IN Dinâmica Sobre o Estudo de Caso Que médicos ainda não pediram exames Pause o vídeo e tente realizar a consulta Dica Utilize o operador IN com o operador lógico NOT Subconsultas que retornam conjuntos Subconsultas utilizando os operadores ANY e SOME Essa construção compara um valor com cada linha da subconsulta utilizando um operador de comparação Observação ANY e SOME são sinônimos usase ou um ou outro A sintaxe geral é SELECT nomedacoluna FROM nomedatabela WHERE condições AND nomedacoluna operadorcomparação ANY SOME subconsulta Subconsultas que retornam conjuntos Subconsultas utilizando os operadores ANY e SOME Exemplo Que pacientes do grupo F possuem mais idade do que algum paciente do grupo M SELECT nome datanasc floormonthsbetweentruncsysdate datanasc12 as idade FROM pacientes WHERE sexo F AND datanasc ANY SELECT datanasc FROM pacientes WHERE sexo M Subconsultas que retornam conjuntos Subconsultas utilizando o operador ALL Essa construção compara um valor com todas as linhas da subconsulta utilizando um operador Exemplo Que pacientes do grupo F possuem mais idade do que algum paciente do grupo M SELECT nome datanasc floormonthsbetweentruncsysdate datanasc12 as idade FROM pacientes WHERE sexo F AND datanasc ALL SELECT datanasc FROM pacientes WHERE sexo M Subconsultas que retornam conjuntos Subconsultas utilizando o operador EXISTS Essa construção retorna verdadeiro se a subconsulta possuir pelo menos uma linha Exemplo Quais médicos solicitaram exame de Hemograma SELECT nome FROM medicos MED WHERE EXISTS SELECT FROM fichas FC JOIN fichasexames FE ON FCnroficha FEnroficha WHERE FCcrm MEDcrm AND FEcodexame 1 Observação Repare essa é uma subsconsulta dita correlacionada pois ela também depende da consulta externa Resumo do que vimos até agora Tipos de subconsultas Operadores especiais Exemplos Banco de Dados Prof Daniel Callegari Aula 07 Relembrando o conteúdo do vídeo anterior Tipos de subconsultas Operadores especiais IN ANY SOME ALL EXISTS Exemplos O que você vai aprender nessa aula Parte 1 Subconsultas Introdução Parte 2 Subconsultas Parte 3 Atualizações com Subconsultas Parte 4 Operações de Conjuntos Parte 5 Dinâmica O que você vai precisar para acompanhar essa aula livesqloraclecom Atualizações com Subconsultas Como já vimos é possível utilizar uma subconsulta dentro dos comandos INSERT UPDATE e DELETE Vamos exemplificar cada caso Atualizações com Subconsultas Exemplo com INSERT 1 Vamos criar uma nova tabela para armazenar dados históricos de valores totais dos exames por data 2 Vamos criar a consulta que recupera tais dados das tabelas originais 3 Em seguida vamos inserir o resultado da consulta como dados para a nova tabela de histórico Atualizações com Subconsultas Exemplo com INSERT Passo 1 CREATE TABLE HistoricoTotais data DATE NOT NULL valorTotExames numeric72 Atualizações com Subconsultas Exemplo com INSERT Passo 2 SELECT data SUMvalor as total FROM fichas join fichasexames on fichasnroficha fichasexamesnroficha join exames on fichasexamescodexame examescodexame GROUP BY data Atualizações com Subconsultas Exemplo com INSERT Passo 3 INSERT INTO HistoricoTotais data valorTotExames SELECT data SUMvalor as total FROM fichas join fichasexames on fichasnroficha fichasexamesnroficha join exames on fichasexamescodexame examescodexame GROUP BY data Atualizações com Subconsultas Exemplo com UPDATE Aplicar um desconto de 10 para os exames que ainda não foram pedidos UPDATE Exames SET valor valor 10100 valor WHERE codexame NOT IN SELECT DISTINCT codexame FROM fichasexames Atualizações com Subconsultas Exemplo com DELETE Excluir os exames que ainda não foram pedidos DELETE FROM Exames WHERE codexame NOT IN SELECT DISTINCT codexame FROM fichasexames Subconsultas Dicas Finais Coloque as subconsultas entre parênteses As subconsultas devem ficar à direita dos operadores relacionais Não use ORDER BY em uma subconsulta Alguns tipos de subconsulta substituem um JOIN O operador IN é equivalente a ANY e o operador NOT IN é equivalente a ALL Resumo do que vimos até agora Subconsultas podem ser utilizadas para consultar dados SELECTs Subconsultas podem ser utilizadas para atualizar dados via INSERTs UPDATEs DELETEs Banco de Dados Prof Daniel Callegari Aula 07 Relembrando o conteúdo do vídeo anterior Subconsultas podem ser utilizadas para consultar dados SELECTs Subconsultas podem ser utilizadas para atualizar dados via INSERTs UPDATEs DELETEs O que você vai aprender nessa aula Parte 1 Subconsultas Introdução Parte 2 Subconsultas Parte 3 Atualizações com Subconsultas Parte 4 Operações de Conjuntos Parte 5 Dinâmica O que você vai precisar para acompanhar essa aula livesqloraclecom Operações de Conjuntos Operadores de Conjunto Esses operadores permitem combinar as linhas retornadas por duas ou mais consultas Atenção o número de colunas e os tipos das colunas retornados pelas consultas devem corresponder embora os nomes de coluna possam ser diferentes Operador Descrição UNION ALL Retorna todas as linhas recuperadas pelas consultas incluindo as duplicadas UNION Retorna todas as linhas não duplicadas recuperadas pelas consultas INTERSECT Retorna as linhas recuperadas pelas duas consultas MINUS Retorna as linhas restantes quando as linhas recuperadas pela segunda consulta são subtraídas das linhas recuperadas pela primeira Conjunto A Conjunto B Dinâmica Operações envolvendo conjuntos Um micro exemplo A 1 2 3 4 B 2 8 9 3 7 create table ta val numeric 1 insert into ta values 1 insert into ta values 2 insert into ta values 3 insert into ta values 4 create table tb val numeric 1 insert into tb values 2 insert into tb values 8 insert into tb values 9 insert into tb values 3 insert into tb values 7 Dinâmica Operações envolvendo conjuntos Utilizando o material do Code Library do Oracle Live SQL em inglês How does union minus and intersect work httpslivesqloraclecomapexlivesqlfilecontentGJG09TMC7UB3B3GEOWQ2SSN1Uhtml Resumo do que vimos até agora Subconsultas Operações de conjuntos Banco de Dados Prof Daniel Callegari Aula 07 Relembrando o conteúdo do vídeo anterior Operações de conjuntos em SQL UNION INTERSECT MINUS O que você vai aprender nessa aula Parte 1 Subconsultas Introdução Parte 2 Subconsultas Parte 3 Atualizações com Subconsultas Parte 4 Operações de Conjuntos Parte 5 Dinâmica O que você vai precisar para acompanhar essa aula livesqloraclecom Dinâmica Subconsultas avançadas Operações envolvendo conjuntos 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 Capítulo 9 Resumo do que vimos até agora Revisão de Conceitos Subconsultas Como parte de outras consultas Como parte de outros comandos de manipulação de dados Consultas com operadores de conjuntos União Interseção Diferença