Oracle Agrupamento de Dados

SQL Agrupamento de Dados

AVG – Média
Count – contar
max
min
stddev
sum
variance

Grupo de Dados

GROUP BY
SELECT AVG(salary) FROM employees GROUP BY departament_id

Agrupamento por mais de uma coluna:
SELECT department_id, job_id, SUM (salary) FROM employees GROUP BY deparment_id, job_id

Consultas Ilegais
Qualquer função na lista SELECT que não for uma função agregada precisa estar no GROUP BY
ORA-00937: not a single select

Não pode usar clausula WHRE para restringir grupos
Deve usar a clausula HAVING para restringir grupos
Código Erro: ORA-00934

Restrigindo resultado de grupo com HAVING
Select departement_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>1000

SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE ‘%REP%’
GROUP BY job_id
HAVING SUM (salary)>13000
WHERE

Funções de agrupamento Aninhadas

Relatório para encontrar o maios, o menor, a soma e a édia dos sálarios de todos os empregados. Chame as colunas de máximo, minimo, total e média, respectivamente. Arredonde o salário máximo.
SELECT ROUND(MAX(salary),0) AS Maximo, MIN(salary) Minimo, COUNT(salary) Total, AVG(salary) Media FROM employees

Agrupando por job_id
SELECT job_id, ROUND(MAX(salary),0) AS Maximo, MIN(salary) Minimo, COUNT(salary) Total, ROUND(AVG(salary),2) Media FROM employees GROUP BY job_id

Exibir quantidade de empregados em cada cargo (job_id)
SELECT COUNT(NVL(job_id,0)), job_id, ROUND(MAX(salary),0) AS Maximo, MIN(salary) Minimo, COUNT(salary) Total, ROUND(AVG(salary),2) Media FROM employees GROUP BY job_id

Diferença salaria entre o maior salario e o menos salário na base de dados dos empregados.
SELECT MAX(salary) – MIN(salary) AS diferenca FROM employees

Relatório para mostrar o id do gerente, para cada gerente, exiba o salário do empregado sobordinado a ele com o menor salário. Excluir emrpegados que não possuem gerente. Excluir os grupos cujos salarios for menos de $6000 ou menos ordene por ordem decrescente:

SELECT manager_id, MIN(salary) minimo FROM employees WHERE manager_id is not null GROUP BY manager_id HAVING MAX(salary) >6000 ORDER BY MINIMO DESC

Cirar uma consulta com matriz:
SELECT COUNT(*) total,
SUM(DECODE (TO_CHAR(hire_date, ‘YYYY’),1995,1,0))”1995″,
SUM(DECODE (TO_CHAR(hire_date, ‘YYYY’),1996,1,0))”1996″,
SUM(DECODE (TO_CHAR(hire_date, ‘YYYY’),1997,1,0))”1997″,
SUM(DECODE (TO_CHAR(hire_date, ‘YYYY’),1998,1,0))”1998″
FROM employees

Crie uma consulta que mostre a função, a soma dos salários dos funcionários que exerecem essa função por departamento para os departamentos 20,50,80,90, e mostra o total dos salários de quem exerce a função, independentemente do departamento.

SELECT job_id,
SUM(DECODE (department_id,20,salary,0)) “20″,
SUM(DECODE (department_id,50,salary,0)) “50″,
SUM(DECODE (department_id,80,salary,0)) “80″,
SUM(DECODE (department_id,90,salary,0)) “90″,
SUM(salary) Total
FROM employees GROUP BY job_id

Exibindo dados em Múltiplas tabelas

Cross Join
Produz o produto cruzado ou cartesiano de duas tabelas.

Natural Joins
Baseada em todas as colunas de duas tabelas que possuem os mesmos nomes.
Seleciona linhas de duas tabelas que tem valores iguais nas colunas em comum.
Se as colunas que possuem mesmo nome forem de tipos diferentes um erro será exibidos.
Exemplo:
SELECT * FROM departments NATURAL JOIN locations WHERE departement_id IN (50,60)

Gerar consulta que gere todos os endereços de todos os departamentos.
SELECT location_id, street_address, city, state_province, country_name FROM locations NATURAL JOIN countries

Cláusula USING
Se muitas colunas tems os mesmos nomes mas tipos de dados diferentes, o natural join pode ser modificado com o USING, para selecionar apenas as colunas desejadas.
Utilize a clausula USING para comparar apenas uma coluna ainda que mais de uma tenha o mesmo nome.
Exemplo:
SELECT l.city, d.departement_name FROM location l JOIN departements d USING (location_id) WHERE location_id = 1400;
Obs: no WHERE não colocar qo l.location_id o qualificador, o banco deve verificar automáticamente.
ERRO: ORA-25154

Full Outer Join
Um join entre duas tabela que retorna os resultados do inner join além dos registros não comuns à tabela da esquerda ou direita.
Quando não tem resultado associado.
left outer join, right outer join, ou full outer join

INNER JOINS
O join de duas tabelas retornando apenas linhas comuns é chamado de inner Joins.
Envolvem chave primária e chave estrangeira, colunas relacionadas podem ser de nomes diferentes, equijoins ou simplejoin são a mesma coisa que INNER JOIN.

Condições arbitrárias para Outer Joins

SELF-JOIN
SELECT e.last_name emp, m.last_name mgr FROM employees e JOIN employees m ON (e.manager_id = m.employee_id)

SELECT e.last_name emp, m.last_name mgr FROM employees e JOIN employees m ON (e.manager_id = m.employee_id) AND e.manager_id =149

SELECT e.last_name Empregado, e.manager_id Emp#, em.last_name, em.manager_id Ger# FROM employees e JOIN employees em ON em.manager_id = e.employee_id

NonequalJoins
A condição do join é um operador diferente de igual.
SELECT e.last_name emp, e.salary, j.grade_level FROM employees e JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal

Relatório de empregados de Toronto, mostrar sobrenome, cargo, numero do departamento e nome do departamento de todos os empregados de Toronto: (SELF-JOIN)
SELECT e.last_name, e.job_id, e.department_id, d.department_name FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
JOIN locations l
ON (d.location_id = l.location_id)
WHERE LOWER(l.city) = ‘toronto’

Gerar relatório dos colegas de trabalho dos empregados. (SELF-JOIN)
SELECT e.department_id department, e.last_name employee, c.last_name colega
FROM employees e JOIN employees c ON (e.department_id = c.department_id)
WHERE e.employee_id <> c.employee_id
ORDER BY e.department_id, e.last_name, c.last_name

SELECT e.last_name, e.job_id, e.salary, d.department_name, j.grade_level FROM employees e
JOIN departments d ON d.department_id = e.department_id
JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal