Banco de Dados - SELECT - Exemplos de Consultas Variadas - (LIBRAS)

[Música] Olá eu sou professor Eduard De Santarém a gente vai dar continuidade na nossa disciplina de banco de dados a gente vai falar hoje sobre consultas variadas a gente vai aprofundar né o uso do comando select bom a gente vai trabalhar nesse esquema aqui onde eu tenho três tabelas a tabela de produto a tabela de fornecedor e a tabela de categoria E vocês vão perceber que a gente tem aqui né as chaves estrangeiras para fornecedor e para categoria que ligam com as tabelas de a tabela de produto com as tabelas de fornecedor e categoria E

a gente tem lá na máquina virtual que já a gente já falou na na na semana passada né a gente tem disponível lá para ser usado né uma a um um banco de dados MySQL e eu criei lá um esquema chamado com 300 videoaula 16 que tem esses dados aqui então é esse esquema com esse com esse conjunto de dados que tá aparecendo aqui tá joia é com EG nós vamos executar as nossas consultas então eu sugiro que vocês depois peguem essas mesmas consultas e executem lá que treinem né que experimentem fazer isso bom Vamos

começar com a nossa primeira consulta né Então olha só aqui tem a consulta Ou seja a minha necessidade de consulta Liste nome unidade valor e estoque de todos os produtos que sejam dos fornecedores limpe Sorrindo ou felicidade em pacote e eu quero mostrar isso em ordem alfabética nesse caso aqui né quem são os fornecedores limpes sorrindo e felicidade em pacote eu tenho que olhar aqui para trás Ó são esses dois ó fornecedor três e o fornecedor 4ro então na minha tabela de produtos né eu vou ter que Observar isso aqui a minha consulta vai ser

o conjunto de Campos que eu preciso certo vou fazer uma consulta na tabela de produto e a minha condição aqui ó fornecedor igual a 3 ou fornecedor igual a 4 o que atender minha condição né vai ser mostrado no meu select e aqui eu quero ordenar por nome então o resultado que eu tenho é esse daqui tá então são quatro produtos eu quero mostrar para vocês como que a gente faria isso na máquina virtual Então vamos dar uma olhada lá na máquina virtual aqui no nosso Linux né então a gente vai conectar aqui no My

skywork bch E aí conectando aqui então digita clica aqui vai digitar a senha né que é sempre Univesp a gente vai conectar você pode aparecer em administração aqui eu posso ir para esquema aqui ó já vou tá conectado no esquema aqui com 300 videoaula 16 disponível para vocês e a consulta lá é essa daqui então já trouxe digitado para não precisar digitar agora e eu vou Executar a consulta quando eu executo a consulta aparece os resultados aqui que são os mesmos que eu mostrei no slide Então na verdade o slide é só uma cópia disso

aqui colocado no slide para apresentar para vocês qualquer comando pode ser digitado aqui certo e executado vai aqui no Rainho executa para poder aparecer o resultado mostrar uma coisa para você vocês interessante nas tabelas aqui eu tenho categoria fiz de propósito tá clicar com o botão direito vou mandar selecionar os registros vão ver que tem cinco registros aqui essa aqui a tabela tá um pouco diferente do slide eu quero porque eu queria mostrar para vocês como que eu excluo um registro eu posso vir aqui ó e mandar excluir quando eu mandar excluir depois eu tenho

que vir aqui e aplicar né dar um apply ele vai aplicar E aí sim o registro foi definitivamente excluído tá jo então usem e abusem da máquina virtual vamos voltar pras nossas consultas aqui pessoal a minha necessidade de consulta é list nome unidade valor em estoque E aí valor em dinheiro mesmo estoque de todos os produtos que tenham 100 ou mais unidades em estoque Então essa é a minha condição de consulta então Liste nome unidade E aí eu quero um valor em estoque que que é o valor em estoque é o valor vezes a quantidade

em stoque eu chamei isso de Campo Total feito a tabela de produto e a minha condição aqui ó é a quantidade de estoque maior ou igual a 100 tá eu queria saber os produtos que ten mais do que 100 produtos em estoque E aí eu vou fazer a minha lista eu vou ter aqui o meu resultado tá minha lista de produtos né que eu tenho mais do que 100 em estoque e com o valor total bom Nova consulta Liste apenas os três primeiros produtos cadastrados Liste a tabela em ordem alfabética Então vou lá na tabela

de produtos né vou fazer uma seleção lá os Campos que eu vou selecionar tabela de produtos e aí eu vou ordenar por nome e como eu quero mostrar apenas três registros eu vou pegar e limitar a três registros serem apresentados eu coloco lá o limite três três registros eu vou mostrar aqui somente os três registros não tem condição nenhuma é só mostrar os três primeiros Essa é a condição então eu uso um limite não Air tá limite outra consulta L Liste os produtos em que a informação da última compra é nula de novo uma um

um select básico aqui na tabela de produto só que a minha condição aqui olha só a última compra is nul é nula então aqui olha só ele me trouxe o produto que é a última compra esse é o resultado né só o queijo prato que é a última compra a não tem a data lá de última compra ela tá como nula lá no banco de dados continuando aqui Liste os produtos que tem valor 0,99 499 599 eu estabeleci uma qu quade de valores e é uma lista para eu atender uma condição um valor que tem

que estar dentro de uma lista né select básico a gente tá fazendo só que aqui na minha condição o eu vou ter o qu valor in Esse é um operador in quer dizer dentro dessa lista que eu coloquei aqui entre parenteses 0,99 499 ou 5,99 E aí o meu resultado vai ser esse os produtos percebam que os valores aqui atendem né a condição que eu estabeleci uma próxima consulta Apresente uma lista de produtos mostrando apenas os 10 primeiros caracteres dos do do do nome dos produtos eu quero fazer um corte no nome né quero pegar

só os 10 primeiros caracteres não sei para que uso Talvez para colocar uma etiqueta alguma coisa assim nesse caso no meu select ó substring é uma função substr eu vou pegar o campo nome a partir do primeiro caracter e contar 10 caracteres é isso que ele vai pegar e a partir disso ele vai mostrar percebam que aqui ó ele mostrou os produtos certo e é como se ele tivesse feito uma linha aqui ó para mostrar só os 10 caracteres Tá ok isso aqui que ele vai atender nessa outra consulta a minha condição aqui é que

eu só quero mostrar produtos que ten no máximo 10 caracteres nesse caso que que eu vou fazer eu vou usar a função Zinha lente Então faço uma um select normal e aqui na minha condição a função lente é uma função que pega a quantidade de caracteres no caso aqui que eu vou ter no campo nome e aí se esse valor for menor ou igual a 10 nessa quantidade de caracter menor ou igual a 10 eu vou mostrar essa é a minha condição e ele mostrou aqui ó os produtos são cenoura e limpa tela que a

quantidade de caracteres é menor do que 10 aqui pessoal é muito interessante isso aqui porque é o seguinte é uma consulta aonde a minha a a a minha necessidade é Liste os produtos que foram comprados pela última vez no mês passado tá E aí tem que tomar cuidado porque eu não posso dig tá o mês atual porque eu vou rodar isso aqui né quando eu rodo a consulta eu tenho que saber qual é o mês atual não dá para eu ficar inserindo o mês então eu tenho que me basear no sistema que que eu vou

fazer eu vou usar o Now o Now não me dá a data de hoje né se ele me dá a data de hoje o que que eu tenho que fazer eu tenho que fazer uma consulta com tudo que foi mês passado agora mês passado tem que ser do mesmo ano né então a minha condição de consulta é essa ó eu vou pegar o mês da última compra e ele tem que ser igual ao mês desse mês agora men-1 e também o ano da última compra tem que ser igual ao ano que a gente tá então

mesmo ano mês um a menos eu vou conseguir resolver errado aqui tem um problema e se eu tiver em janeiro em janeiro eu não posso pegar um mês a o mês Janeiro menos um porque o mês anterior é o 12 né e o ano também é o ano anterior Então nesse caso aqui né a gente tem que tomar cuidado por qu eu vou ter que proceder o a compreensão do negócio da seguinte forma eu faço exatamente essa consulta aqui de cima a minha condição Dew é essa de um mês atrás certo mesmo ano um mês

atrás só que eu vou colocar também um h aqui ó certo dizendo o seguinte que também há uma outra condição que é se o meu mês de agora for igual a 1 e o meu mês da última compra for igual a 12 ou seja tô em janeiro o mês da última com compra foi dezembro e também o ano seria um ano menos um do que o ano que eu tenho hoje ou seja o ano da última compra tem que ser o ano de hoje menos um E aí eu vou conseguir estabelecer todas as condições que

eu tenho para tratar com essa data então quando eu tô falando de mês passado o mês passado pode ser do ano anterior se eu tiver em janeiro eu tem que sempre pensar nisso tomem Cuidado com essas siladas de data tá data começo de mês final de mês começo de ano final de ano ela sempre tem tem que est atento para ver como proceder a consulta então só para mostrar para vocês o na aqui né A minha data de hoje seria 10 de janeir 10 de junho né E aí é nessa data que ele estaria se

baseando ou seja teria que pegar todas as últimas compras de Maio seria esse select aqui que eu acabei de mostrar n esse aqui é o select e o resultado seria com esse produto vassoura azul que foi comprado em maio quer dizer aquela primeira consulta que eu tinha mostrado aquela primeira condição ele atend seria legal só que se eu tivesse em janeiro daria er então preciso dessa segunda parte aqui que é essa parte da minha condição aqui do air quando eu trato se for Janeiro tá bom pessoal continuando aqui eu quero listar os os iogurtes cadastrados

na na na tabela de produtos indicando o mês em que foram comprados pela última vez quero ver iogurte e quanto que foi comprado pela última vez nesse caso aqui procedo um select básico tá E aí eu vou colocar aqui ó ó nome unidade última compra tal e eu vou colocar aqui month name month name é uma função para trazer o mês especificamente da última compra e eu chamei a campinho aqui de mês e na minha condição o air né Eu quero pegar o produto em que like seja Iogurte ou seja eu quero os produtos em

que o nome seja iogurte mais alguma coisa na frente por isso tem esse Coringa aí por isso tem esse porcentagem e a listagem vai ser Olha só eu tenho os produtos aqui que são os iogurtes eu trouxe os Campos trouxe a data da última compra 18 de abril de 2021 nos dois casos e aqui eu coloquei o mês ó tá que foi o campinho que eu usei o monf name aqui então usar as funções para apresentar esse tipo de resultado próxima consulta Liste a quantidade total de unidades de estoque por fornecedor então aqui pessoal eu

quero pegar né e juntar somar quantidade de produtos que eu tenho de estoque que eu tenho mas separar por fornecedor então eu fiz uma soma aqui da quantidade de produtos em estoque de todos os produtos e agrupe por fornecedor tô usando o group buy aqui então eu tenho aqui para cada fornecedor a quantidade de produtos em estoque no meu resultado certo Por isso que eu uso o group by lembrando sempre que quando eu vou usar o group Buy né o campo do Group buy tem que est em cima no select depois eu faço as agregações

na frente da maneira que eu que eu precisar vamos para mais um exemplo desse tipo né Liste a quantidade total de valor de estoque por fornecedor valor de estoque é quantidade de produtos que ele tem vezes o valor daquele produto mas Liste somente os fornecedores com valor de estoque acima de r$ 1.000 então o que que a gente vai fazer aqui pego de novo agrupamento por fornecedor grupo e byy fornecedor tá E aqui eu vou fazer uma soma olha da quantidade em estoque vezes o valor e eu chamei esse campinho de Total então o que

que ele vai fazer Vai Multiplicar a quantidade de estoque por valor vai somar esses valores dos produtos e vai separar por fornecedor só que na hora que eu for mostrar eu só quero mostrar quando o total por fornecedor for maior ou igual a 1000 né quando o valor total for maior ou iG 1000 nesse caso os fornecedores que atenderam a essa condição fornecedor um fornecedor TR tá valor aqui ó Total maior do que 1000 tá o valor que eu tenho em estoque para esse desses fornecedores são esses aqui continuando aqui a gente tem uma outra

consulta Liste a quantidade total de unidades de estoque por fornecedor Apresente o nome do fornecedor Opa nesse caso duas tabelas tem que pegar o nome do fornecedor é exatamente igual a consulta anterior né só que aqui ao invés de saber o total eu quero saber a quantidade em estoque então eu quero pegar aqui ó a quantidade em estoque certo vou juntar a quantidade em estoque dos produtos e vou separar por fornecedor só que a minha separação não vou mais usar o campinho fornecedor tabela de produto eu vou usar eu vou fazer aqui um join produto

com fornecedor perceba que eu usei o vermelhinho azulzinho aqui para separar campo de produto e Campo de fornecedor e o meu group buy aqui ó vai ser pela razão social do fornecedor para poder mostrar essa razão mostrar o nome do fornecedor e aqui também vou mostrar como fornecedor aqui ó e aí que que a gente vai ter fornecedor com o nome do fornecedor e a quantidade de produtos que eu tenho em estoque Ou seja a quantidade de produtos que eu tenho daquele fornecedor stock produtos variados tá e ordenados pela pelo nome do fornecedor próxima consulta

eu tenho aqui Liste todos os produtos com seus respectivos fornecedores e categorias Então agora eu vou relacionar três tabelas três tabelas Olha só select P nome né tabela de produto nome como produto razão social da da tabela trans social colocar como fornecedor e a categoria descrição da categoria Vou colocar aqui com categoria faço um left Joint né Por quê Porque eu quero selecionar todos os produtos então eu começo lá pela tabela produto left join com a tabela de fornecedor né e depois um left join de novo com a tabela de categoria então todos os produtos

relacionados com fornecedor relacionados com categoria Trago essa condição de relação e apresento aqui então todos os produtos com o seu fornecedor e com a sua categoria ordenados pelo terceiro Campo que é a categoria como é a minha necessidade de consulta é dessa maneira que eu tô apresentando bom uma outra consulta Apresente a soma de valores dos produtos por categoria somente os que tiveram a última compra no ano de 2021 então aqui pessoal eu faço um uma condição de consulta né onde eu tenho o meu agrupamento por descrição que é da minha categoria e eu vou

somar a quantidade e o valor em estoque então eu faço o mesmo mesmo esquema né valor em estoque quantidade valor em estoque junto tudo isso e separo por categoria não mais por fornecedor quero saber por categoria agora só que uma condição interessante que eu tenho aqui de mostrar a a categoria eu tenho que fazer o meu left join para poder trazer a descrição da categoria E um detalhe da necessidade da minha consulta é que eu só quero os produtos em que a última compra foi feita no ano de 2021 então no meu Air eu tem

aqui Um ear né da última compra igual a 2021 esse aqui é o meu resultado tá Quais são as categorias e com os seus respectivos valores em estoque para cada categoria que eu tenho dentro do meu estoque de produtos bom aqui uma outra consulta Apresente a quantidade de produtos e não a quantidade de unidades tá quantos produtos eu tenho por categoria então a mesma coisa né eu vou ter aqui um select onde eu vou agrupar por categoria E por isso eu precisei fazer um J né um left join E aí eu vou contar a quantidade

de produtos quantos não é a quantidade e e 10 de 1 20 não é quantos produtos eu tenho quantos tipos diferentes de produto eu tenho para cada categoria por isso que eu tô contando e não somando certo e aqui eu tenho ó na categoria de utilidades Eu tenho um produto na categoria de bazar eu tenho mais um produto na categoria de bebidas Eu tenho dois produtos ou seja são dois tipos de produto para categoria bebidas aqui eu monto mostro a partir de um usar o agregador count vamos para mais uma consulta aqui Apresente a quantidade

de unidades de produtos por categoria eu quero saber agora a quantidade de unidades mesmo tá dos produtos por categoria ordene por quantidade de produtos mostre somente quando a quantidade for menor que 500 nesse caso aqui eu vou agrupar pela minha categoria então como eu agrupar pelo nome da categoria eu tenho que ter um left join certo a minha condição aqui meu agregador É somar a quantidade de produtos que eu tenho no geral pego a quantidade de produtos que eu tenho conto lá no meu his quantidade de produtos certo e aí eu vou contar isso e

vou juntar separar por categoria detalhe eu tenho um Raven aqui somente quando a categoria né somente quando aquela categoria tiver uma quantidade menor do que 500 é que eu vou mostrar pode ver aqui que todos T quantidade menor do que 500 Então eu tenho 15 produtos ao todo do Bazar e 118 de utilidades certo e tô ordenando aqui pela categoria que é o meu primeiro campinho e aqui mais uma consulta Apresente a quantidade de produtos e não a quantidade de unidades tá quantidade de de produtos mesmo por categorias e fornecedores Olha só três tabelas então

eu tenho aqui né a gente vai fazer eu tenho descrição da categoria E a minha razão social diferenciador Esse é o meu agrupamento eu tô agrupando por dois Campos e aqui eu tô contando a quantidade em estoque mas eu tô contando aqui certo a quantidade eu tô contando quantos produtos eu tenho certo não a quantidade que eu tenho em estoque mas a unidade é produto um produto dois produto três Então são três produtos é é são os produtos e eu faço aqui o meu left join né faço a o join entre produto categoria E fornecedor

porque eu preciso dos três vou agrupar pelos dois Campos aqui né tanto por categoria quanto pela razão social e vou ordenar pelo primeiro E aí eu vou ter né na descrição aqui ó bazar que é a a o meu a minha categoria do meu e fornecedor limpo e sorrindo quantidade de produtos um E aí vai sucessivamente onde eu vou mostrando categoria razão social e quantidade bom Espero que tenham gostado dessa aula aula é uma aula que eu quero que vocês experimentem na máquina virtual é uma uma aula para mostrar as várias possibilidades que a gente

tem lembrando sempre que a consulta nasce de uma demanda do dia a dia e aí você vai lá e tenta montar essa consulta né usar o máximo de recursos que a gente tem do comando select para poder soltar um relatório que atenda essa necessidade de consulta vejo vocês na próxima [Música] aula n [Música]