Banco de Dados - Linguagem SQL - Consultas com funções - SELECT

E aí [Música] em vários o conselho do ar de Santarém o nome da quantidade a segunda de banco de dados não falar continuar falando sobre É o comando select que é um comando de consulta e nós vamos hoje tratar um pouquinho das funções e agregação vamos dar uma olhada longe vai continuar tratando essa estrutura para fazer nossas consultas nossos exemplos né que essa estrutura aqui de contexto didático de uma empresa bom e que que são as funções da linguagem a linguagem SQL pessoal implementa uma variedade de funções que podem ser utilizadas mais variadas formas né e é importante a gente saber que algumas dessas funções são implementadas por apenas alguns produtos e podem ter nomes diferentes em produtos diferentes Então a gente vai apresentar daqui para frente funções do mais Kelly que é o banco que Nós escolhemos para tratar nessa disciplina Então se por acaso você pode trazer que tava um comando desse né que a gente vai ver aqui no banco de dados Oracle SQL server post entre outros e perceber que a função não funciona É porque ela é uma função específica do mais querem mas eu fazer outra coisa para vocês Essas funções que vão apresentar todas elas elas têm também nos outros bancos de dados o que pode acontecer é que às vezes ela tem uma sintáxi diferente um nome diferente vai ter que procurar né na documentação do banco para saber qual é a não são como que ela está escrita nesse outro banco de dados para saber que que ela faz a mesma operação são muitas funções Eu sugiro a vocês sempre que em tem um documentação dos bancos de dados porque lá tem muitas funções matemáticas né as funções inteira suas funções de data tem muitas muitas muitas frentes por aí usar e poder fazer um bom uso dela nossas consultas bom eu vou mostrar para vocês aqui né É na aula passada eu falei sobre a gente fazer comandos é que não tem uma tabela associada são select a Quero mostrar pra vocês algumas consultas e não vou estar usando esse numa tabela tá usando só especificamente o comando para mostrar então por exemplo a função troncatti que essa daqui ó tem dois parâmetros x e o de o primeiro parâmetro é o valor aqui nesse caso não seja trabalhando com uma tabela você vai usar o nome do Campo aqui né e o segundo valor é o que é como que ele tem que quantas casas decimais Aí tem que fazer outro quente há Como troquei tem semente importa é você olhar lá e e a camisa então você perceber aqui ó se o valor 1223 é um colocar uma casa desse mais desse mal desculpa 1. 223 né com uma casa decimal aqui não é mil não é um ponto 22 ele vai estar como resultado um ponto dois ou seja ele foi lá e cortou aonde tinha uma casa decimal cortou aqui ó ficou só Essa parte a mesma coisa aqui em baixo uma casa decimal 1. 999 pega uma casa decimal quando ele não tem casa decimal 0 e vai mostrar só o número um então não tem nada de aproximação É só cortar e um outro exemplo é quando eu tenho por exemplo negativo ele vai cortando trás para frente aqui vai apresentar os valores Eu não casei substituir por zero aqui pensando que ele corta aqui esses dois mas na hora de mostrar e vai mostrar 10 Então são recursos que a gente pode ter para o dia a dia para estar usando a função é que eu tenho a função round né ela pode ser apresentada dessa forma ou dessa é a função round né a maneira que ela é apresentada é para você aproximar para você arredondar o valor então por exemplo quando eu coloco só o round sozinho aqui ó menos 1.

23 né eu digo só o áudio não tá em casas decimais não tem o segundo parâmetro ele vai me dizer é menos um aqui menos uns 58 já aproximar pelo menos dois porque ele tá fazendo um arredondamento sem em casa decimais aqui embaixo eu disse que é uma casa decimal só então o que que ele faz ele pega esse 1. 2 98 ele pega uma casa decimal e o 298 aquele aproxima para três então a um arredondamento é assim que funciona Essas funções numéricas aqui né funções valores numéricos aí tem uma função stringhi por exemplo né que é a com Kate com Kátia para concatenar juntar Então você tem nome sobrenome não sei que você quer juntar esses campos você e são com Kate percebeu que a função com Kate aqui ó eu coloquei uma stringhi aqui na stringhi aqui e uma stringhi aqui é que ele fez aí juntou tudo no meu resultado juntou todo mundo se eu tenho por acaso entre essas Strings tem valor no Gu e vai me dar o resultado como no tão específica isso aqui para gente saber e eu tenho uma outra que a com Kate WS com caso da Grécia que eu tenho um separador tá então eu tenho muitos Campos eu tenho que juntar tudo como separador você quer juntar tudo numa strengh só e colocar e separadores Então olha só aqui meu separador é a, tá aqui ó depois eu tenho first Name seco name last name então o que que ele fez juntou tudo aqui no meu na minha resposta tá separados por, aí foi colocando se aparecer para você paradorzinho no começo ele vai ser parando vai colocando tudo no mustang só fazendo a separação por, é muito interessante quando está usando no dia a dia no mercado Tá trabalhando tá você que é muito legal a internação com esse separador que eu tenho dois comandinhos bem básicos lower lower é para pegar qualquer texto tá pegando aqui o parâmetro ó um texto e transformando em minúsculo e hooper pegando um texto e transformando tudo em maiúsculo tá então são funções de string gás a gente diz para mostrar o resultado para mostrar em busca mostrar mais mas o banco não tá desse jeito e a gente consegue mostrar o resultado desse jeito você pode ser aplicado para qualquer canto tá ter sol nesse caso que canta o tipo stringhi coloco aqui a gente tem outros exemplos de funções de string ring também substring' que é uma função muito usado ela tem várias maneiras de escrever a primeira delas que é essa daqui ó eu tenho dois parâmetros que é o stream EA posição da minha estranho Inicial e a partir de qual posição que eu quero pegar então a minha estranho Inicial é essa daqui ó eles eu quero pegar a partir da posição sim que eu vou contar cinco caracteres e o resultado vai ser a partir dessa olha só a partido e bactérias quem vai ter como resultado essa outra aqui né Eu já faço a mesma a mesma estrutura só que ele coloca a palavrinha from aqui e a mesma coisa e vai pegar a partir do caracter quatro é exatamente igual acima só com uma palavrinha from Isso é uma característica mais que a gente que acontece Às vezes pessoal banco vai evoluindo eles vão colocando novas funções que substitui o anterior né eles mantêm anterior então você vai encontrar várias variações aí né de uma mesma função de como escrever ela aqui eu tenho por exemplo strengh posição e quantidade ou seja as trend que eu vou ter a partir de qual posição e Quantos caracteres eu quero pegar esse aqui ó é um exemplo bom então é a string que eu tenho a partir da posição 5 e eu quero pegar seis caracteres e pode encontrar também negativo o que é começar de trás para frente contar de trás para frente que ele vai pegar a posição menos cinco aí vai pegar o contrário contando de trás para frente vai pegar três caracteres é isso que acontece aqui então essa é a minha função Zinha substring eu tenho left também tá onde eu pego os caracteres a partir da esquerda então por exemplo aqui eu tenho a função left eu pego o texto e pega os 5 caracteres da esquerda da esquerda para direita tá então e conta a quantidade né o tamanho pegando da esquerda para direita 5 caracteres e outra muito legal é o replay esse usar muitos aqui tá então aqui eu tenho a string original a origem ou seja o que que eu quero procurar E pelo o que que eu quero substituir no caso aqui ó eu tenho uma stringhi né é procurar todo o caracter w minúsculo e substituir por um w maiúsculo e minúsculo Olha como ficou o resultado onde ele achou o w minúsculo ele substituiu por um w maiúsculo w minúsculo e a replay Esse é muito utilizado vai pegar um dado do banco de dados vai querer alterar a coisas para poder mostrar o juntar coisas e alterar é muito importante conhecer Essas funções de string que função de data função não é para buscar a hora do sistema tá então você coloca lá e vai te trazer a hora exata do sistema aqui eu tenho uma função de para trabalhar com 11 meses então eu tenho uma data da então se eu coloco lá a função malte por exemplo e coloco a data e já me dizer qual que é o mês estou no caso aqui essa data que é três verão 2008 e vai dizer que o mês é dois no caso aqui malte name é o nome do mês fêmur EA de Fevereiro tentar buscar aqui dessa maneira não é buscar o mês eu queria trazer ele também muito comum que a gente precisa usar aqui eu tenho funções de dia né então daí nem a gente tem Day of movie Day of Week Day Of His nesse caso aqui ó e vai fazer vai pegar essa data né três de fevereiro am7 Rose oca que dia Dame que é né Qual que é o nome do dia ele vai me dar é certo ele é sábado a mesma coisa que eu queria saber qual é o dia da semana só que não texto ele vai comer mostrar como numérico então no caso aqui é o dia sete dele conta um domingo 7 sábado aqui Day of morf na então ele quer saber que dia do mês que é no caso aqui é meu meu dia três Então traz o dia aqui e aqui dia do ano né então contando aqui três de fevereiro de2007 então é o trigésimo quarto dia do ano para fazer consultas com pedido data de pedir isso é muito importante quartil muitas coisas vai usar Essas funções aqui para fazer consulta no banco de dados aqui tem outras funções de data aqui no caso deixe disse é uma data menos a outra data data um menos a data dois ele é melhor resultado em dias então eu tenho uma data que a 31 12/2007 e 30 2007 vai me Bom dia e aqui e vai me dizer que é menos 31 dias por quê porque aquele tá fazendo a conta com datas né quando eu faço o dente de fiquei com data é a menor menos a maior que ele vai fazer como Dias negativos outra coisa outra função Zinha essa tio desses então eu tenho um select aqui com o tio 10 e aí eu tenho nesse caso aqui ó ele vai fazer quantos dias eu tenho Desde o ano zero até essa data e vai contar né um para cada dia até chegar nesse dia estabelecido aqui na minha consulta uma outra quer subir date vou mostrar aquela como o dente subir como subir dentes são variações que a gente tem mais Kelly também né então normalmente é o que eu tenho uma data e eu quero subtrair alguns dias então aqui nesse caso eu apresento uma data coloca a palavrinha interval 31 dias ou seja eu quero essa data - 31 dias então eu tinha aqui do Rio de Janeiro de D o mesmo 31 dias o resultado aqui ó né dois de Dezembro 2007 31 dias antes Aqui é uma outra variação que se apresenta da mesma forma bom o exemplo pessoal da violação de funções tem muitos que a gente vai usar né então é só selecione nome e sobrenome data de nascimento de todos os funcionários que fazem aniversário no mês de agosto selecione nome sobrenome data de nascimento from funcionar e como que eu sei que o funcionário nasce em agosto Olha só se o mês de nascimento dele então resultado dessa função for igual a oito vai satisfazer a condição vou listar os funcionários hoje mesmo Nascimento é oito Tá ok bom é aqui o exemplo Então a gente tem aqui a data de nascimento né tem os funcionários que tem um mês oito aqui ó que são o Caio e a Cláudia tá na minha consulta e retornaria nome sobrenome datas nas se procedia à consulta né para quem nasceu no mês de agosto esse seria tabela que resultaria é a consulta como que ela traria como resultado da minha consulta aqui esse nosso exemplo agora quero falar para vocês de agregações as funções de agregação pessoal são funções que permitem executar operações matemáticas né nos valores das colunas em todos esses de uma tabela então eu consigo né consolidar valores olhando para mim a tabela olhando para os registros ele consegue consolidar os valores desses registros Essas funções são AVG e calcula a média dos valores Counter que conta esses registros são os some né que é a somatória desses valores Max que ele vai pegar o maior valor entre os meus registros e tumim que eu vai achar o menor valor entre os meus registros ah e também eu tenho é a função de agrupar o group by you have Então as funções de agrupamento né É ela funciona muito bem com as o funcionamento do agrupamento com agregação funciona muito bem porque normalmente eu vou querer fazer o quê Vou querer agrupar para poder consolidar valores dentro de grupos eu vou mostrar para vocês como fazer isso aqui as funções de agregação vão utilizar essa cláusula group by né Para a gente poder separar esses valores separar por categorias específicas e eu tenho também o uso da cláusula a heavy que é quando eu aplico um filtro não é como se fosse o essa que eu aplico um filtro depois esses valores consolidados pelo group by então o Hulk by agrupa né Eu apresento outros consolidade e depois eu consigo fazer um filtro em cima disso e pessoal Note que tanto é né que filtra os dados antes da agregação e o rev e os dados depois de agregação são filtros Mas eles são feitos em momentos diferentes quando eu procedo um group by seu apresenta seu colocar um Air ele já vai ser já vai pegar os dados vai filtrar antes aí depois ele faz o agrupamento agrupe esses dados e depois eu posso aplicar o é o Have No meu resultado no resultado que eu já tenho é assim que funciona o air e deve navegação Ball aqui eu tenho a metade aí eu vou continuar usando ela né então a tabela funcionários e a gente vai mostrar alguns resultados percebo que é que eu coloquei alguns detalhes né como por exemplo esses esses todos os registros aqui a gente vai explorar um pouco então vendo esse aqui aqui ó o apps a Claudia quer esse registro aqui não tem um supervisor tá como nulo e o Antônio ainda não foi atribuído para ele qual departamento Ele trabalha então as informações importantes que mostrar para vocês então como Campos como nulos aqui tá joia bom o AVG né que é para calcular média Então olha só eu quero fazer um select da média do salário para todos os funcionários então meu resultado vai ser isso aqui ó se eu pegar Teu Olhar todos os registros e vai pegar e juntar todos os salários que eu tenho lá e vai dar uma média salarial né de todos funcionários cadastrados de 2. 759.

Alguma coisa então aqui eu trago a média de todos os salários aqui embaixo eu fiz a mesma coisa select AVG salário de uma média do campo salário só que eu fiz um filtro eu quero saber a média do salário de quem trabalha no departamento quatro então percebo que aqui ó já uma média é diferente daqui de cima é porque aqui em cima eu tinha a média de todos os na hora da empresa e aqui embaixo que eu tenho a média só de quem trabalha no departamento quatro bom Aqui é a soma né então o sanque É somar os salários a mesma coisa soma tanto o salário de todos os funcionários tá aqui ó somou e agora soma todos os salários funcionários trabalham de apartamento quatro e aí somou e eu tenho a soma dos salários trabalho departamento 4 a gente for olhar para nossa tabela original só não vê que exatamente isso que eu quero que vocês façam isso que vocês pegam essa tabela fiquei olhando executa essas consultas e realmente que bate esses valores tá joia importante que vocês façam e refaçam isso muitas vezes não é para conseguir consolidar essa informação aí na cabeça de vocês bom aqui eu tenho uma outra função de ligação que é o Max e o meio eu vou fazer aqui Max salário ou seja qual o maior salário e qual é o menor salário qual que é a maior data de nascimento e qual que é menor data de nascimento de todos os funcionários são o maior salário a é o 5. 558 menor salário é o 1550 e aqui a minha data minha maior data de nascimento tá em 86 e a minha menor data de nascimento tá em 74 ele selecionou e todos os registros que trouxe esses dados maior salário menor salário maior data de nascimento maior data de nascimento aqui é a mesma coisa Quais são os maiores salários dos menores salários na tabela de funcionário certo só que eu quero saber para o departamento número 4 somente quem trabalha no departamento quatro então ele vai trazer lá o maior salário departamento quatro e o menor salário departamento quatro então uma condição que eu estou estabelecendo aqui aqui é a minha função count para contar então eu vou contar os registros então count asteriscos conta quantas vezes eu tenho naquela tabela se eu faço um cão de salário vai pegar todos os salários vai lá na no campo salário e contar quantos eu tenho E no caso aqui no Counter quando eu faço é que é o ep Idêntica Idêntica de Educação do do do supervisor lembra que um dos registros era nulo então por isso que meu resultado aqui deu cinco porque ele contou e ele não conta os números tá E aqui dos outros entendeu resultados eu já tenho seis vezes na 16 com salário e somente cinco tem o supervisor EA que acontece a mesma coisa quando eu vou fazer né O carro te* e o country para o meu supervisor do meu departamento número um e o meu departamento número um todos os funcionários têm supervisor então aparece aqui o Counter como dois e o Counter o supervisor também como dois bom group by aqui quando eu uso a cláusula group by eu quero agrupar os dados nesse caso aqui olha só eu quero mostrar para vocês que eu tenho que ter esse campo aqui ó que é o campo que vai estabelecer o meu agrupamento como que eu vou separar os registros Então eu disse aqui que eu vou separar por número de apartamento tão que que ele vai fazer esse campinho que aparece inicialmente tem que aparecer aqui como definição do meu grupo e vai e ele vai aparecer aqui também ó eu percebo que ele não vai se repetir então ele vai dizer o seguinte eu tô pegando a média salarial EA soma dos salários o ouro departamento então no meu departamento um a média salarial em 1550 e a soma dos salários 3100 aqui embaixo o departamento 4 a soma a média do salário treino 19 EA soma 7758 então percebo que eu quando eu faço o grupo e pai eu a grupo categorizo não é para poder dar tanto para tratar a agregação tanto de salário quanto de tanto de média de salário conta de soma de salário aqui embaixo eu faço a mesma coisa só que que eu fiz antes Aqui ó a gente tá usando um Air Então antes de estabelecer a estrutura eu vou lá trago somente os registros que são número do departamento maior do que um já exclui quem é departamento um lá e aí eu vou proceder a mesma consulta né então de novo group by por número de apartamento Então tá agrupado no departamento que não tem mais um que eu já eliminei um antes E aí eu vou contar quanto funcionários eu tenho e qual que é o maior salário dentro de cada departamento Então o que eu fiz aqui ó contar quantos quantos funcionários eu tenho por departamento e ver qual que é o maior salário que eu tenho a que a Claúdia have ela faz um filtro certo esse filtro pessoal é um filtro depois que eu já pro sede né A minha agregação Então é só eu vou aqui ordenar agrupar por pelo número departamento tá aqui ó eu tenho o número de apartamentos aqui eu quero pegar a média do salário e a soma dos salários médios do salário é a soma dos salários da tabela de funcionário só que eu só aí vou agrupar para o nome departamento só que eu só quero trazer quando a soma do salário for menor que cinco mil Então eu tô pegando um filtro aqui ó e trazendo somente quando a soma do salário for menor do que 5 mil naquele de parte então quero olhar para o departamento que tem que a soma dos salários é menor do que o valor de cinco mil é assim que eu tô procedendo aqui embaixo a gente faz a mesma coisa só que aqui eu tô fazendo o quê Tá agrupando para o departamento tô contando quantos funcionários quero ver o maior salário tá um ano antes no from eu tenho uma condição aqui do número de apartamentos e marca Então somente o departamento são maior do que um e eu vou fazer um Heaven aqui ó que o salário máximo tem que ser 5.