Apostila - Consultas Avancadas

Prévia do material em texto

<p>1 de 10faculdade.grancursosonline.com.br</p><p>Professor(a): Anderson Nascimento</p><p>Unidade 4 | Aula 4</p><p>Professor(a): anderson nascimento</p><p>Unidade 4 aplicação de consultas em Bancos de dados</p><p>Utilizando Linguagem sQL</p><p>Aula 4 – Consultas Avançadas</p><p>objetivo da aula</p><p>Compreender tópicos avançados em linguagem SQL, tratando de conceitos importantes,</p><p>como subconsultas e consultas baseadas em conjuntos.</p><p>Apresentação</p><p>Nesta aula, abordaremos consultas avançadas em linguagem SQL e outros conceitos</p><p>importantes para resolução de problemas mais específicos e complexos do que os utilizados</p><p>no cotidiano.</p><p>Vamos aprender a trabalhar com subconsultas, que são consultas dentro de outras</p><p>consultas, as quais possibilitam o desenvolvimento de códigos que nos permitem ir além</p><p>na resolução de problemas.</p><p>Também vamos abordar comandos em SQL para trabalhar com a teoria de conjuntos,</p><p>que permitem que seja possível realizar operações como união e interseção.</p><p>Todos esses comandos vão enriquecer bastante o seu conhecimento em SQL. E aí?</p><p>Está pronto?</p><p>1. subconsultas</p><p>As subconsultas são recursos utilizados em linguagem SQL para a elaboração de queries</p><p>mais complexas, que nos permitem solucionar problemas que seriam difíceis de resolver</p><p>com estruturas mais básicas.</p><p>Uma subconsulta é, literalmente, uma consulta dentro de outra consulta. Em sua</p><p>estrutura, temos uma consulta chamada de “externa”, e outra, de “interna”.</p><p>No momento da execução, a consulta interna é executada e retorna o resultado para a</p><p>consulta externa, que utiliza o valor retornado e finaliza a sua execução.</p><p>Temos várias formas de trabalhar com subconsultas, mas basicamente existem dois tipos</p><p>de subconsultas em SQL: subconsultas correlacionadas e subconsultas não correlacionadas.</p><p>Livro Eletrônico</p><p>2 de 10faculdade.grancursosonline.com.br</p><p>Unidade 4 | Aula 4</p><p>Professor(a): Anderson Nascimento</p><p>Uma subconsulta correlacionada é aquela em que a subconsulta depende dos resultados</p><p>da consulta principal. Ou seja, a subconsulta é executada uma vez para cada linha retornada</p><p>pela consulta principal.</p><p>EXEMPLO</p><p>Se você quiser obter todos os clientes que fizeram uma compra maior que a média das</p><p>compras feitas por clientes brasileiros, pode usar uma subconsulta correlacionada para</p><p>calcular a média e comparar com o valor de cada compra.</p><p>Vamos a um exemplo em SQL:</p><p>SELECT nome_cliente, valor_compra</p><p>FROM cliente</p><p>WHERE valor_compra > (</p><p>SELECT AVG(valor_compra)</p><p>FROM cliente</p><p>WHERE pais = ‘Brasil’</p><p>);</p><p>Nesse exemplo, a subconsulta é correlacionada, porque depende dos resultados da</p><p>consulta principal. A subconsulta calcula a média de compra para os clientes brasileiros, e,</p><p>em seguida, a consulta principal seleciona os clientes cuja compra foi maior que essa média.</p><p>Já uma subconsulta não correlacionada é aquela em que a subconsulta não depende</p><p>dos resultados da consulta principal. Essa subconsulta é executada apenas uma vez, e o</p><p>resultado é utilizado na consulta principal.</p><p>EXEMPLO</p><p>Se você quiser obter a lista de todos os produtos que não foram vendidos nos últimos 30</p><p>dias, pode usar uma subconsulta não correlacionada para obter os IDs de todos os produtos</p><p>vendidos nesse período e, em seguida, usar essa lista para filtrar os produtos na consulta</p><p>principal.</p><p>Vamos ver um exemplo de consulta não relacionada em SQL:</p><p>SELECT nome_produto</p><p>FROM produto</p><p>O conteúdo deste livro eletrônico é licenciado para TARCISIO - 03285869356, vedada, por quaisquer meios e a qualquer título, a sua reprodução, cópia, divulgação ou</p><p>distribuição, sujeitando-se aos infratores à responsabilização civil e criminal.</p><p>3 de 10faculdade.grancursosonline.com.br</p><p>Unidade 4 | Aula 4</p><p>Professor(a): Anderson Nascimento</p><p>WHERE id_produto NOT IN (</p><p>SELECT id_produto</p><p>FROM pedidos</p><p>WHERE data_pedido > DATE_SUB(NOW(), INTERVAL 30 DAY)</p><p>);</p><p>Nesse exemplo, a subconsulta é não correlacionada, porque não depende dos resultados</p><p>da consulta principal. A subconsulta seleciona os IDs de todos os produtos que foram vendidos</p><p>nos últimos 30 dias, e a consulta principal seleciona os produtos que não estão nessa lista.</p><p>Observe que usamos o comando NOT IN para filtrar os produtos que não desejamos</p><p>que estejam no resultado final. Cabe, também, ressaltar o uso de uma função que subtrai</p><p>30 dias da data atual a partir da função NOW(). Existe uma infinidade de funções para</p><p>trabalhar com datas em SQL.</p><p>Veja algumas funções para trabalhar com data em sQL server e mysQL neste</p><p>link: https://blog.dbins.com.br/trabalhando-com-datas-no-sql.</p><p>Vamos, agora, resgatar o nosso banco de dados de locadora de veículos e realizar uma</p><p>subconsulta que retorne o nome do acessório mais caro da locadora.</p><p>Aqui está o código da tabela acessório para você treinar no SQLite:</p><p>CREATE TABLE acessorio(</p><p>codacessorio INT PRIMARY KEY,</p><p>nomeacessorio VARCHAR(50) NOT NULL,</p><p>preco DECIMAL(6,2) NOT NULL</p><p>);</p><p>INSERT INTO acessorio (codacessorio, nomeacessorio,preco) VALUES</p><p>(1, ‘Cadeirinha para bebê’, 14.00),</p><p>(2, ‘Bebê conforto’, 15.00),</p><p>(3, ‘Assento para criança’, 17.00),</p><p>(4, ‘Assento de elevação’, 19.00),</p><p>O conteúdo deste livro eletrônico é licenciado para TARCISIO - 03285869356, vedada, por quaisquer meios e a qualquer título, a sua reprodução, cópia, divulgação ou</p><p>distribuição, sujeitando-se aos infratores à responsabilização civil e criminal.</p><p>4 de 10faculdade.grancursosonline.com.br</p><p>Unidade 4 | Aula 4</p><p>Professor(a): Anderson Nascimento</p><p>(5, ‘WiFi’, 20.00),</p><p>(6, ‘Rádio via satélite’, 25.00),</p><p>(7, ‘Tag para estacionamento e pedágios’, 15.00),</p><p>(9, ‘Carregador de celular’,7.00),</p><p>(10, ‘Bagageiro de teto’, 19.00),</p><p>(11, ‘Engate’, 13.00),</p><p>(12, ‘Suporte para bicicleta’, 20.00),</p><p>(13, ‘Carretinha para cargas’, 100.00),</p><p>(14, ‘Tela LCD para vídeos’, 12.00),</p><p>(15, ‘Suporte para prancha’, 18.00);</p><p>Vamos recuperar, via SQL, o nome e o preço do acessório mais caro.</p><p>SELECT nomeacessorio, preco</p><p>FROM acessorio</p><p>WHERE preco =</p><p>(SELECT MAX(preco)</p><p>FROM acessorio</p><p>);</p><p>Figura 1: Aplicando uma subconsulta no SQLite</p><p>Fonte: Elaboração própria.</p><p>O conteúdo deste livro eletrônico é licenciado para TARCISIO - 03285869356, vedada, por quaisquer meios e a qualquer título, a sua reprodução, cópia, divulgação ou</p><p>distribuição, sujeitando-se aos infratores à responsabilização civil e criminal.</p><p>5 de 10faculdade.grancursosonline.com.br</p><p>Unidade 4 | Aula 4</p><p>Professor(a): Anderson Nascimento</p><p>Observe, na solução, que há uma consulta interna que recupera o preço do acessório</p><p>mais caro, utilizando a função MAX(). A consulta externa consome esse valor e compara</p><p>e recupera, na tabela acessório, o produto correspondente a esse valor – nesse caso, a</p><p>carretinha para cargas, que custa 100 reais o aluguel.</p><p>Em resumo, as subconsultas são uma técnica poderosa para realizar consultas complexas</p><p>em SQL, permitindo que você obtenha resultados mais precisos e específicos.</p><p>2. operações com conjuntos</p><p>A teoria de conjuntos está sempre presente quando falamos em operações em bancos</p><p>de dados. Assim como na álgebra relacional, vamos utilizar esses conceitos em linguagem</p><p>SQL. Além disso, todas as operações de conjuntos da álgebra relacional possuem comandos</p><p>correspondentes em linguagem SQL.</p><p>As operações mais comuns são a união e interseção, mas também conseguimos estabelecer</p><p>um paralelo com operações como produto cartesiano e diferença.</p><p>2.1. União</p><p>O comando UNION é usado em SQL para combinar o resultado de duas ou mais consultas</p><p>SELECT, gerando uma única tabela de resultado. Para que a operação seja possível, as</p><p>consultas SELECT que serão combinadas devem ter a mesma estrutura de coluna, ou seja,</p><p>o mesmo número de colunas e tipos de dados correspondentes.</p><p>O comando UNION resulta em uma nova tabela, a partir da combinação de duas ou</p><p>mais consultas SELECT unidas com o comando UNION. É importante destacar que, caso</p><p>haja linhas duplicadas nas tabelas resultantes, o UNION remove essas linhas, a não ser que</p><p>utilizemos o comando UNION seguido da cláusula ALL.</p><p>A sintaxe básica do comando</p><p>UNION é a seguinte:</p><p>SELECT coluna1, coluna2,...</p><p>FROM tabela1</p><p>UNION</p><p>SELECT coluna1, coluna2,...</p><p>FROM tabela2;</p><p>Lembrando que podemos substituir o UNION pelo UNION ALL.</p><p>Vamos a um exemplo.</p><p>O conteúdo deste livro eletrônico é licenciado para TARCISIO - 03285869356, vedada, por quaisquer meios e a qualquer título, a sua reprodução, cópia, divulgação ou</p><p>distribuição, sujeitando-se aos infratores à responsabilização civil e criminal.</p><p>6 de 10faculdade.grancursosonline.com.br</p><p>Unidade 4 | Aula 4</p><p>Professor(a): Anderson Nascimento</p><p>a) Recupere o nome das cidades que possuem clientes e funcionários.</p><p>Para isso, vamos usar a tabela cliente e adicionar uma nova tabela funcionário:</p><p>CREATE TABLE cliente(</p><p>codcliente INTEGER PRIMARY KEY,</p><p>nome VARCHAR(50) NOT NULL,</p><p>cidade VARCHAR(50),</p><p>sexo CHAR(1),</p><p>estado CHAR(2),</p><p>estadocivil CHAR(2)</p><p>);</p><p>INSERT INTO cliente (codcliente, nome, cidade, sexo, estado, estadocivil)</p><p>VALUES</p><p>(1, ‘Ana Silva’, ‘Duque de Caxias’, ‘F’, ‘RJ’, ‘C’),</p><p>(2, ‘Bruna Pereira’, ‘Niterói’, ‘F’, ‘RJ’, ‘C’),</p><p>(3, ‘Túlio Nascimento’, ‘Duque de Caxias’, ‘M’, ‘RJ’, ‘S’),</p><p>(4, ‘Fernando Souza’, ‘Campinas’, ‘M’, ‘SP’, ‘S’),</p><p>(5, ‘Lúcia Andrade’, ‘São Paulo’, ‘F’, ‘SP’, ‘C’);</p><p>CREATE TABLE funcionario(</p><p>codfuncionario INTEGER PRIMARY KEY,</p><p>nomefuncionario VARCHAR(50) NOT NULL,</p><p>cidade VARCHAR(50),</p><p>sexo CHAR(1),</p><p>estado CHAR(2),</p><p>estadocivil CHAR(2)</p><p>);</p><p>INSERT INTO funcionario (codfuncionario, nomefuncionario, cidade, sexo,</p><p>estado, estadocivil)</p><p>O conteúdo deste livro eletrônico é licenciado para TARCISIO - 03285869356, vedada, por quaisquer meios e a qualquer título, a sua reprodução, cópia, divulgação ou</p><p>distribuição, sujeitando-se aos infratores à responsabilização civil e criminal.</p><p>7 de 10faculdade.grancursosonline.com.br</p><p>Unidade 4 | Aula 4</p><p>Professor(a): Anderson Nascimento</p><p>VALUES</p><p>(1, ‘Leonardo Paes’, ‘Duque de Caxias’, ‘M’, ‘RJ’, ‘C’),</p><p>(2, ‘Sergio Arouca’, ‘São Gonçalo’, ‘M’, ‘RJ’, ‘C’),</p><p>(3, ‘Juliana Dias’, ‘São José dos Campos’, ‘F’, ‘SP’, ‘S’);</p><p>Nossa consulta fica da seguinte forma:</p><p>SELECT cidade</p><p>FROM cliente</p><p>UNION</p><p>SELECT cidade</p><p>FROM funcionario;</p><p>Observe, na Figura 2, que o resultado trouxe todas as cidades que possuem funcionários</p><p>ou clientes, eliminando a duplicidade.</p><p>Figura 2: Aplicando uma consulta de união no SQLite</p><p>Fonte: Elaboração própria.</p><p>O conteúdo deste livro eletrônico é licenciado para TARCISIO - 03285869356, vedada, por quaisquer meios e a qualquer título, a sua reprodução, cópia, divulgação ou</p><p>distribuição, sujeitando-se aos infratores à responsabilização civil e criminal.</p><p>8 de 10faculdade.grancursosonline.com.br</p><p>Unidade 4 | Aula 4</p><p>Professor(a): Anderson Nascimento</p><p>Outra forma de executar essa consulta seria por intermédio da cláusula DISTINCT, que</p><p>serve para eliminar linhas com valores duplicados.</p><p>Assim, poderíamos reescrever a consulta da seguinte forma:</p><p>SELECT DISTINCT cidade</p><p>FROM cliente</p><p>UNION</p><p>SELECT DISTINCT cidade</p><p>FROM funcionario;</p><p>Note que o resultado é exatamente igual; a única diferença é a execução, pois cada</p><p>SELECT já elimina as cidades duplicadas antes de juntar os seus resultados com o JOIN.</p><p>2.2. interseção</p><p>Outro comando importante é o de interseção, que serve para encontrar os elementos</p><p>que fazem parte de duas tabelas ao mesmo tempo.</p><p>Em linguagem SQL, para fazer a interseção entre duas tabelas, você deve usar o comando</p><p>INTERSECT. Ele retorna apenas as linhas que existem em ambas as tabelas.</p><p>Sintaxe:</p><p>SELECT coluna1, coluna2,...</p><p>FROM tabelaA</p><p>INTERSECT</p><p>SELECT coluna1, coluna2,...</p><p>FROM tabelaB;</p><p>Vamos a um exemplo de interseção em SQL.</p><p>a) Recupere o nome das cidades que possuem clientes e funcionários.</p><p>SELECT cidade</p><p>FROM cliente</p><p>INTERSECT</p><p>SELECT cidade</p><p>FROM funcionario;</p><p>O conteúdo deste livro eletrônico é licenciado para TARCISIO - 03285869356, vedada, por quaisquer meios e a qualquer título, a sua reprodução, cópia, divulgação ou</p><p>distribuição, sujeitando-se aos infratores à responsabilização civil e criminal.</p><p>9 de 10faculdade.grancursosonline.com.br</p><p>Unidade 4 | Aula 4</p><p>Professor(a): Anderson Nascimento</p><p>Observe, na Figura 3, que o resultado é a cidade de Duque de Caxias, pois ela possui</p><p>clientes e funcionários. A forma de resolução é bastante parecida com o modo como</p><p>resolvemos a consulta com o comando UNION.</p><p>Figura 3: Aplicando uma consulta de união no SQLite</p><p>Fonte: Elaboração própria.</p><p>Por fim, é importante dizer que a linguagem SQL é um universo incrível a ser desvendado,</p><p>e a melhor forma de fazer isso é pesquisando e exercitando sempre!</p><p>considerações finais da aula</p><p>Nesta aula, aprendemos a elaborar consultas avançadas, utilizando linguagem SQL.</p><p>Vimos os conceitos e as aplicações práticas de subconsultas, observando as várias formas</p><p>de se criar consultas desse tipo. Passamos, também, por comandos que nos permitem a</p><p>realização de operações sobre conjuntos, como UNION e INTERSECT.</p><p>Saber trabalhar com consultas avançadas é uma habilidade essencial para profissionais</p><p>de banco de dados e programadores e um conhecimento valioso para qualquer pessoa</p><p>interessada em trabalhar com grandes bases de dados.</p><p>A partir do aprendizado desse conteúdo, você se torna capaz de elaborar consultas</p><p>poderosas, que te permitirão resolver problemas complexos em bancos de dados relacionais.</p><p>O conteúdo deste livro eletrônico é licenciado para TARCISIO - 03285869356, vedada, por quaisquer meios e a qualquer título, a sua reprodução, cópia, divulgação ou</p><p>distribuição, sujeitando-se aos infratores à responsabilização civil e criminal.</p><p>10 de 10faculdade.grancursosonline.com.br</p><p>Unidade 4 | Aula 4</p><p>Professor(a): Anderson Nascimento</p><p>materiais complementares</p><p>7 Conceitos Avançados de SQL que Você Precisa Saber!</p><p>2022, Gian Panacioni.</p><p>Link para acesso: https://blog.dp6.com.br/7-conceitos-avan%C3%A7ados-de-sql-que-</p><p>-voc%C3%AA-precisa-saber-34605e271824. (acesso em 20 abr. 2023.)</p><p>SQL - Operações de Conjunto (UNION/INTERSECT/EXCEPT) - Aula 08/09 - Bancos</p><p>de Dados 2021.2</p><p>2022, André Santanchè.</p><p>Link para acesso: https://www.youtube.com/watch?v=ubTTgpBRdYY (acesso em 20 abr.</p><p>2023.)</p><p>Referências</p><p>CARDOSO, V. Linguagem SQL Fundamentos e Práticas. 1. ed. Rio de Janeiro: Saraiva,</p><p>2013.</p><p>DAMAS, L. SQL - Structured Query Language. 6. ed. Rio de Janeiro: LTC, 2014.</p><p>SILBERSHATZ, A. Sistema de Banco de Dados. 7. ed. Rio de Janeiro: LTC, 2020.</p><p>O conteúdo deste livro eletrônico é licenciado para TARCISIO - 03285869356, vedada, por quaisquer meios e a qualquer título, a sua reprodução, cópia, divulgação ou</p><p>distribuição, sujeitando-se aos infratores à responsabilização civil e criminal.</p><p>Aula 1 – Estrutura Básica de Consultas (DQL)</p><p>Aula 2 – Junções</p><p>Aula 3 – Funções de Agregação de Dados</p><p>Aula 4 – Consultas Avançadas</p><p>Aula 5 – Visões</p>