Alternativamente, poderíamos trazer os funcionários para os quais o atributo não é nulo:
SELECT *
FROM Funcionario
WHERE data_contratacao IS NOT NULL
Pronto! Com isso terminamos a nossa parte da aula a respeito dos operadores lógicos da linguagem SQL. Se você ainda não fez alguma pausa, faça agora pois iremos introduzir um assunto um pouco mais complexo.
Junções (joins)
As consultas que vimos até agora só incluíam uma tabela, a Funcionario. Contudo, para que os bancos de dados sejam instrumentos poderosos para consultas e relacionamentos entre os dados, é necessário dar suporte à extração de dados de múltiplas tabelas de uma vez.
Antes de começarmos...
Para introduzir os comandos JOIN, vamos primeiro trazer uma nova tabela ao nosso modelo. A tabela de Salários.
O comando de criação dela é o seguinte:
CREATE TABLE Salario (
CONSTRAINT PK_Salario PRIMARY KEY (idFuncionario, data_inicio),
CONSTRAINT FK_Salario_Funcionario FOREIGN KEY (idFuncionario) REFERENCES Funcionario (idFuncionario)
)
Opa! Temos algumas novidades aqui. Veja que temos uma nova restrição, ou constraint, cujo comando contém os termos FOREIGN KEY. Veja que se trata de uma chave estrangeira. A sintaxe desse comando é a seguinte:
Observamos, então, que está sendo criada uma chave estrangeira em Salario, mais especificamente no atributo idFuncionario, que referencia o atributo de mesmo nome (idFuncionario) na tabela Funcionario. Ou seja, há um relacionamento 1:N de Funcionario para Salario. Um funcionário da empresa pode ter múltiplos salários ao longo do tempo, cada um delimitado por sua data_inicio e data_fim de vigência.
Por exemplo, se o funcionário de id = 10.001 é contratado no dia 22/06/2001 e um ano depois, em 22/06/2002, recebe um reajuste, ficando com esse salário vigente até o momento em que os dados foram extraídos, veja como seriam seus registros em Salario:
Entendido? Para seguirmos em frente para os joins, gostaria que você observasse também no comando de criação da tabela que a chave primária de Salario é composta pela chave estrangeira (idFuncionario) e por um atributo próprio (data_inicio), indicando tratar-se de uma entidade fraca de Funcionario.
Inner join
As operações de join visam combinar os registros de duas (ou mais) tabelas ao utilizar valores que elas têm em comum. Por exemplo, poderíamos unir Funcionario e Salario pelo atributo idFuncionario. Assim, para um registro de funcionário cujo idFuncionario é igual a x, retornaremos todos os registros de Salário que têm também o atributo idFuncionario igual a x.
A representação gráfica dessa operação é mais ou menos essa:
Para ilustrar melhor, vamos construir um join entre as duas tabelas acima.
Imagine que queremos criar um relatório com nome, sobrenome e data de nascimento de cada funcionário, juntamente com seus salários ao longo do tempo. Só que os atributos nome, sobrenome e data_nascimento estão na tabela Funcionario, enquanto que os valores e datas de seus salários estão na tabela Salario. Precisamos combinar essas duas tabelas para fazer o nosso relatório.
A consulta que utilizaremos para fazer isso será a seguinte:
SELECT
f.idFuncionario ,f.nome
,f.sobrenome ,f.data_nascimento ,s.idFuncionario ,s.salario_anual ,s.data_inicio ,s.data_fim FROM Funcionario f
INNER JOIN Salario s ON f.idFuncionario = s.idFuncionario
Veja que o INNER JOIN, a partir de uma cláusula inserida após o comando ON, junta os registros das duas tabelas que satisfaçam determinadas condições. No nosso caso, a condição apresentada é f.idFuncionario = s.idFuncionario. Ou seja, a consulta vai unir todos os registros de Funcionario e Salario para os quais a igualdade acima seja verdadeira!
Repare que “nomeamos” as tabelas Funcionario de f e Salario de s. Isso ocorre com frequência nas sequências que contêm junções, já que precisamos diferenciar atributos de mesmo nome, mas que vêm de tabelas diferentes.
Também poderíamos utilizar o nome original das tabelas para esse fim: Funcionario.idFuncionario = Salario.idFuncionario.
Assim, o resultado da consulta vai ser do tipo:
Veja que os quatro atributos da esquerda, com o cabeçalho em branco, são da tabela Funcionario. Já os atributos da direita, com o cabeçalho destacado com fundo azul, são originalmente da tabela Salario. Perceba como, para cada registro, o idFuncionario de Funcionario é igual ao valor de idFuncionario em Salario.
A operação é chamada de “inner” join porque ela traz somente os registros do “meio”, da interseção entre as duas tabelas. Veja a representação visual a seguir:
Figura: Diagrama de Venn representando um INNER JOIN (Wikipediai)
Os registros retornados, assim, não são nem de Funcionario, nem de Salario, mas da junção das duas. Repare na imagem que traz os resultados da consulta que, como um mesmo Funcionario tem várias correspondências na tabela Salario, seus atributos, como nome, sobrenome e data_nascimento irão se repetir a cada ocorrência da junção.
Left (outer) join
Como vimos acima, o inner join só retorna os registros que satisfaçam a igualdade. Tudo bem! Agora vamos falar sobre o LEFT JOIN, ou LEFT OUTER JOIN. Essa operação de junção é muito parecida com a primeira, a diferença é que vamos trazer, além da interseção entre as duas tabelas, aqueles registros da tabela da esquerda que não têm correspondência com os da direita.
Figura: Diagrama de Venn representando um LEFT JOIN (Wikipediaii)
Mas o que isso quer dizer? Imagine que existam funcionários sem salário fixo. Ou seja, aqueles que ganham comissão por vendas ou percebem alguma outra forma de remuneração. Eles não terão um salário registrado na tabela Salario, mas estarão normalmente registrados na tabela de Funcionario.
Se o chefe que solicitou o relatório da consulta anterior quiser ver também esses Funcionarios que não possuem um salário correspondente, poderá utilizar o LEFT JOIN, ao contrário do INNER:
SELECT
f.idFuncionario ,f.nome
,f.sobrenome ,f.data_nascimento ,s.idFuncionario ,s.salario_anual ,s.data_inicio ,s.data_fim FROM Funcionario f
LEFT JOIN Salario s ON f.idFuncionario = s.idFuncionario
Essa consulta, como já adiantamos, retornará também aqueles registros da tabela anterior, a da esquerda, que não possuem equivalência com registros da outra tabela. Veja que quase todos os registros do resultado a seguir encontraram correspondência, mas o último registro, o de José da Silva, não possui um registro associado na tabela Salario:
Isso não o impediu de ser retornado! Contudo, veja que, como esse registro não tem correspondência em Salario, os atributos desta tabela “da direita” ficarão nulos no resultado da consulta.
Right (outer) join
De forma análoga, o RIGHT JOIN ou RIGHT OUTER JOIN traz os registros da tabela da direita que não possuem equivalência com os registros da tabela da esquerda:
Figura: Diagrama de Venn representando um RIGHT JOIN (Wikipediaiii)
SELECT
f.idFuncionario ,f.nome
,f.sobrenome ,f.data_nascimento ,s.idFuncionario ,s.salario_anual ,s.data_inicio ,s.data_fim FROM Funcionario f
RIGHT JOIN Salario s ON f.idFuncionario = s.idFuncionario
A consulta acima retornaria a interseção de Funcionario e Salario, mais aqueles registros de Salario que não possuem equivalência em funcionário.
Obs.: Na prática, no modelo de dados que construímos, isso não poderia ocorrer, já que vimos que Salario é entidade fraca de Funcionario, e portanto, há sempre um funcionário associado a um salário. Considere a consulta de exemplo acima apenas para fins didáticos.
Full (outer) join
Também temos o FULL OUTER JOIN, que é um pouco menos comum. O FULL JOIN traz todos os registros das duas tabelas que satisfaçam ou não à cláusula de junção. Por exemplo, considere que adicionamos a tabela Departamento ao nosso modelo já construído durante a aula. Os dados contidos nela são os seguintes:
Considere também que a nossa empresa está em um processo de reestruturação, portanto, alguns departamentos estão temporariamente sem nenhum gerente associado. Assim, observe na tabela acima que três departamentos têm o valor NULL na chave estrangeira idGerente, que referencia a tabela Funcionario.
Assim, vamos construir uma consulta que nos retorne todo um conjunto de funcionários da empresa e todos os departamentos, relacionando os departamentos que possuem gerente com os funcionários que os gerenciam. A nossa consulta vai ser a seguinte:
SELECT f.nome ,f.sobrenome
,d.nome_departamento AS departamento_gerenciado FROM Departamento d
FULL JOIN Funcionario f ON d.idGerente = f.idFuncionario
Veja os primeiros resultados dessa consulta:
Veja que os resultados de números 4, 6 e 8 são de departamentos que não possuem gerente. Já os registros 1, 2, 3, 5, 7 e 9 correspondem a registros em que houve correspondência entre as duas tabelas. Por fim, os demais registros, de 10 a 15, são de funcionários que não são gerentes de nenhum departamento.
Assim, podemos representar o FULL JOIN pelo seguinte diagrama de Venn, em que todas as áreas estão preenchidas. Por isso mesmo chamamos essa junção de FULL, ou seja, cheia ou completa. Veja a relação entre as cores das áreas do diagrama e os registros destacados no parágrafo anterior.
Figura: Diagrama de Venn representando um FULL JOIN (Wikipediaiv)
Você deve ter percebido uma novidade nesse trecho de código acima: o comando ASpermite renomear as colunas exibidas no resultado da consulta. A coluna original na tabela permanece com o mesmo nome, mas o resultado da consulta será exibido de acordo com os nomes que foram aplicados. Esse nome atribuído a cada coluna é conhecido como alias.
Poderíamos utilizar esse AS também no FROM, pois ele já existe, mas está implícito. Por exemplo:
FROM Departamento AS d
FULL JOIN Funcionario AS f ON d.idGerente = f.idFuncionario
Cross join
O cross join é nada mais nada menos que o produto cartesiano entre duas tabelas. Ou seja, ele vai combinar todos os registros da primeira tabela com todos da segunda. Para que você visualize melhor, criei duas tabelas de exemplo bem simples: Tabela_A e Tabela_B. Veja a composição de cada uma a seguir:
Tabela A Tabela B
Agora veja o produto cartesiano, ou CROSS JOIN das duas:
Observe que cada valor de A está associado a todos os valores de B e vice-versa. Como estamos associando todos os valores das duas tabelas, temos que não existe cláusula ON no CROSS JOIN!
Aproveitei essa consulta simples do CROSS JOIN para introduzir mais um conceito para você, o comando ORDER BY . Esse comando, que fica no final da consulta, permite que se ordene os resultados baseados nos valores de algum atributo ou conjunto de atributos.
Observe acima que ordenamos os valores da consulta acima pelo campo valor_A, mas poderíamos também tê-lo ordenado pelo valor_B:
Repare que os valores colocados após o ORDER BY são, por padrão, ordenados de forma crescente. Para ordená-los de forma decrescente, colocamos o termo DESC após o atributo. Podemos ordenar os resultados das consultas por múltiplos atributos, cada um de forma crescente ou decrescente:
Acima, temos o resultado de uma consulta que ordena primeiro pelo valor_A de forma decrescente, e depois pelo valor_B de forma crescente.
Podemos explicitar quando estamos ordenando um atributo de forma crescente colocando o termo ASC. Contudo, como esse é o padrão implícito, não precisamos fazê-lo.
Aqui finalizamos nosso estudo a respeito dos múltiplos tipos de junção existentes. Lembre-se que os mais importantes para a sua prova são o INNER JOIN e o LEFT JOIN.
Algumas considerações sobre o SELECT Operações aritméticas
simplesmente trazê-los em seus valores originais. Por exemplo, é possível manipular o valor do salário de um funcionário de diferentes maneiras. Observe o registro a seguir da tabela Salario.
Suponha que esse funcionário do resultado acima está tendo um ótimo desempenho na empresa. Por isso, seu chefe está considerando recompensar financeiramente o seu esforço. Contudo, a empresa está num momento financeiro difícil e precisa avaliar com bastante cuidado para não dar um aumento que não pode pagar. Assim, o chefe precisa avaliar as seguintes possibilidades:
1) Dar um bônus anual de R$ 2.000 ao funcionário 2) Aumentar o salário do funcionário em 4%
3) Dar dois bônus semestrais de 2,5% do salário
Ao invés de utilizar a calculadora, o chefe pode simplesmente fazer uma simulação na base de dados utilizando operadores aritméticos em uma consulta SQL. Vejamos:
SELECT idFuncionario
,salario_anual AS SalarioAtual ,salario_anual + 2000 AS ComBonus
,salario_anual + (salario_anual * 0.04) AS AumentoPercentual ,salario_anual + 2 * (salario_anual * 0.025) AS BonusSemestral FROM Salario
WHERE idFuncionario = 10002 AND data_inicio = '2001-08-02'
Os resultados dessa consulta são os seguintes:
são realizadas primeiro, antes das somas e subtrações. Assim como nas equações e nos operadores lógicos da própria linguagem SQL, podem ser usados parênteses para alterar a ordem de precedência das operações.
Além dos operadores de soma (+) e multiplicação (*) mostrados acima, podem ser usados os operadores de divisão (/) e subtração (-), bem como o modulo (%). Podemos também realizar operações entre os valores de múltiplos atributos distintos ao invés de valores fixos, algo como:
SELECT atributo1 + atributo2 – atributo3 * atributo4 FROM Tabela
Essas operações são realizadas a nível de linha. Ou seja, a cada linha ou registro do banco de dados, são considerados os valores dos atributos daquele registro. Assim, a consulta acima irá fazer o cálculo atributo1 + atributo2 – atributo3 * atributo4 para cada um dos registros da Tabela e retornar um resultado para cada um deles.
Obs.: Muito cuidado com valores nulos. Uma operação em nível de registro com um valor NULL sempre retornará NULL. Ou seja: 2 + NULL = NULL, 2* NULL = NULL, 2 – NULL = NULL, e assim sucessivamente.
Uma breve curiosidade a respeito de tipos de dados: veja que os resultados nomeados AumentoPercentual e BonusSemestral foram convertidos em números decimais. Isso acontece porque os números inteiros, após serem envolvidos em uma operação matemática com algum decimal, são automaticamente convertidos pelo SGBD.
UNION e UNION ALL
É possível unir os resultados de duas ou mais consultas diferentes através dos comandos UNION e UNION ALL.
Ambos têm a mesma função, com uma pequena diferença. Veja a consulta a seguir, que traz todos os adultos que existem em uma determinada base de dados:
SELECT Nome, Sexo FROM Adultos
Nome Sexo
José da Silva M Joaquim Xavier M Maria da Silva F
Agora veja a consulta sobre a tabela análoga Crianças, que contem as pessoas jovens de um determinado banco de dados:
SELECT Nome, Sexo FROM Crianças
Nome Sexo
Valentina Lopes F Maria da Silva F Enzo Gomes M
Imagine agora que queremos unir os resultados dessas duas consultas em uma só, de modo a obtermos uma grande lista com adultos e crianças que populam o banco de dados. Não faz sentido fazer joins, pois não queremos unir os atributos das duas tabelas, nosso objetivo aqui é realizar uma união entre os registros produzidos pelas duas consultas.
Assim, podemos utilizar o comando UNION ALL entre as duas consultas:
SELECT Nome, Sexo FROM Adultos UNION ALL
SELECT Nome, Sexo FROM Crianças
O resultado iria ser a combinação dos resultados das consultas:
Nome Sexo
José da Silva M Joaquim Xavier M Maria da Silva F Valentina Lopes F Maria da Silva F Enzo Gomes M
Perceba, contudo, que temos uma criança que tem o mesmo nome e sexo de um adulto. Veja os registros destacados em laranja na tabela acima.
Claro que nesse caso faz sentido que se repitam mesmo, pois são pessoas distintas. Contudo, às vezes é interessante que a união dessas duas ou mais consultas não traga resultados repetidos. Para isso, utilizamos o comando UNION, sem o ALL, que remove as tuplas duplicadas do resultado:
SELECT Nome, Sexo FROM Adultos UNION
SELECT Nome, Sexo FROM Crianças
Nome Sexo
José da Silva M Joaquim Xavier M Maria da Silva F Valentina Lopes F Enzo Gomes M
Uma última observação a respeito do UNION e do UNION ALL é que os tipos das colunas das duas consultas devem ser compatíveis para que possamos realizar a união. Por exemplo, assumindo que Idade é um atributo numérico e Nome é um atributo literal, temos que a consulta a seguir certamente produziria um erro devido à mistura dos tipos dos dados:
SELECT Idade, Nome FROM Adultos UNION
SELECT Nome, Idade FROM Crianças
Entretanto, os nomes dos atributos entre as duas consultas podem até ser diferentes, caso em que o resultado irá reter os nomes dos atributos da primeira consulta.
Agrupamentos – A cláusula GROUP BY
Para fechar nossa aula, vou te mostrar como se agrupa dados em consultas. ”Como assim, professor?” É muito simples. Você tem visto que todas as consultas que realizamos até agora trazem registro a registro da tabela selecionada, não é?
Às vezes fizemos filtragens através do WHERE, outras vezes usamos os joins para combinar diferentes tabelas, mas o fato é que sempre retornamos todas as linhas das tabelas resultantes da nossa consulta (após o FROM e o WHERE).
Já as consultas agrupadas através do GROUP BY vão sumarizar os registros, agregando valores.
não nos interessa ver informações no nível de funcionário, vamos dizer que queremos um nível maior de agregação das nossas consultas. Por exemplo, podemos querer saber quantos funcionários de cada sexo temos na empresa.
Para isso, utilizaremos a seguinte consulta:
SELECT f.sexo
,COUNT(f.idFuncionario) AS qtdFuncionarios FROM Funcionario f
GROUP BY f.sexo
Veja que temos duas novidades nessa consulta, o comando GROUP BY e a função COUNT. O primeiro serve justamente para agrupar os funcionários pelo valor de um atributo ou de um conjunto de atributos. Na consulta acima, estamos agrupando os registros da tabela Funcionario por sexo.
Já o COUNT é uma função de agregação, ou seja, é uma função especial que opera em cima de um conjunto de registros, retornando um valor só por registro agregado. Soou estranho? Vai ficar claro quando você vir o resultado da consulta:
O COUNT é uma função de agregação que simplesmente conta os registros da tabela. Então veja que a consulta acima agrupa os registros de Funcionario por sexo através do GROUP BY, enquanto que o comando COUNT agrega os registros desses grupos, contando quantos são do sexo masculino e quantos são do sexo feminino.
Veja que se rodarmos simplesmente um COUNT sem nenhum agrupamento, ele vai nos retornar a quantidade de registros total na tabela que estamos consultando:
Entendido? Seguindo em frente, para apresentar as outras funções de agregação mais comuns, vamos trazer
Opa! Peraí! Veja que o número de funcionários aumentou muito. Você saberia dizer o motivo? Acontece que, quando fazemos o join de Funcionario com Salario, passamos a trabalhar nem com uma tabela nem com a outra, mas com a junção das duas. Lembra?
Ou seja, quando estamos fazendo uma contagem (COUNT) de ocorrências do campo idFuncionario na consulta, o resultado será calculado com base no resultado da junção, não com a tabela original Funcionario. Assim, como o resultado da junção traz várias linhas por funcionário, correspondentes aos vários salários de cada funcionário ao longo do tempo, a nossa contagem de ocorrências também vai aumentar na mesma razão.
Superado este ponto, vamos voltar com o nosso GROUP BY e fazer mais algumas consultas:
1) O salário médio por gênero
2) O maior salário que ocorre nos funcionários, também segregado por gênero 3) Da mesma maneira, vamos calcular o menor salário por gênero
4) A soma de todos os registros de salario dos funcionários de cada gênero Veja a consulta que utilizaremos:
SELECT f.sexo
,AVG(s.salario_anual) AS SalarioMedio ,MAX(s.salario_anual) AS MaiorSalario ,MIN(s.salario_anual) AS MenorSalario ,SUM(s.salario_anual) AS TotalSalarios FROM Funcionario f
INNER JOIN Salario s ON f.idFuncionario = s.idFuncionario GROUP BY f.sexo
Os resultados obtidos são os seguintes:
SUM soma os valores do atributo.
Para finalizar, veja que podemos utilizar mais de um atributo no GROUP BY. Vamos agrupar os salários também por ano em que eles entraram em vigor:
SELECT f.sexo
,YEAR(s.data_inicio) AS Ano
,AVG(s.salario_anual) AS SalarioMedio ,MAX(s.salario_anual) AS MaiorSalario ,MIN(s.salario_anual) AS MenorSalario ,SUM(s.salario_anual) AS TotalSalarios FROM Funcionario f
INNER JOIN Salario s ON f.idFuncionario = s.idFuncionario GROUP BY f.sexo, YEAR(s.data_inicio)
ORDER BY f.sexo, YEAR(s.data_inicio)
A função YEAR extrai o ano de um atributo do tipo data. Não se preocupe muito com ela agora, só veja que podemos colocar vários atributos no GROUP BY.
Veja uma parte do resultado dessa consulta:
atributos no SELECT devem estar em uma de duas situações possíveis - ou dentro da cláusula GROUP BY, ou dentro de uma função de agregação.
Veja, isso é bastante lógico. Não estamos trabalhando mais registro a registro, e sim com conjuntos agregados de dados, certo?. Nessas circunstâncias, se colocarmos um atributo solto, o SGBD simplesmente não saberia como retornar o resultado da consulta.
Vamos voltar ao exemplo da contagem de funcionários para ilustrar nosso argumento. Repare no atributo nome
Vamos voltar ao exemplo da contagem de funcionários para ilustrar nosso argumento. Repare no atributo nome