Funções de Grupo

Prévia do material em texto

Funções de Grupo
Operam sobre conjunto de linhas.
Retornam resultados baseados no agrupamentos das linhas.
As linhas são agrupadas em menores grupos. 
 Ex: select nome_coluna
 From nome_tabela
 Where condição
 group by expressão -> colunas 
AVG(x)
Retorna o valor médio da coluna “X”.
Ignora os valores nulos.
 Ex: AVG(SALARIO).
MAX(X)
Retorna o valor máximo da coluna “X”.
Ignora os valores nulos.
 Ex: MAX(SALARIO)
MIN(X)
Retorna o valor mínimo da coluna “X”.
Ignora os valores nulos.
 Ex: MIN(SALARIO)
COUNT (X)
Retorna o número de valores não nulos da coluna X.
Ex: COUNT (SALARIO)
COUNT (*)
Retorna o número de linhas de uma tabela. 
Considera os valores nulos.
CLAUSULA HAVING
Função semelhante a clausula where, porém a mesma atua em funções de grupo.
Exemplo: 
SELECT UF, SUM(SALARIO)
FROM EMPREGADO
GROUP BY UF
HAVING SUM (SALARIO)> 100000;
Junções de Tabela
Utilizamos quando desejamos obter os dados de duas mais tabelas (JOIN).
Para tanto, as tabelas necessitam ter colunas em comum que são as responsáveis por realizar a junção.
INNER JOIN
Retorna todas as linhas que possuem nas duas tabelas (intersecção).
 Sintaxe:
 SELECT COLUNAS 
 FROM TABELAS_A A
 INNER JOIN TABELA_B B 
 ON A.ID_JUNCAO = B.ID_JUNCAO;
LEFT JOIN 
Retorna todas as linhas da tabela a esquerda e as linhas de intersecção com a direita. 
RIGHT JOIN
Retorna todas as linhas da tabela a esquerda e as linhas de intersecção com a esquerda. 
FULL JOIN
Retorna todas as linhas que possuem em alguma das tabelas. 
AUTO JUNÇÃO
Junção da tabela com ela mesma.
	EMPREGADO
IDEMP INT PK
SEXO CHAR (1)
IDCHEFE INT FK
	DEPENDENTE
ID DEP INT PK
NOME VARCHAR (255)
IDEMP INT FK 
 
 
EXERCICIOS
SELECIONE O NOME DO EMPREGADO E O NOME DO CHEFE
R=(SELECT E.NOME, C.NOME 
INNER JOIN EMPREGADO C 
ON E.IDEMP = C.IDCHEFE;)
SELECIONE TODOS OS EMPREGADOS DO SEXO FEMININO E DEPENDENTES DO SEXO FIMININ.
R=(SELECT E.NOME, D NOME 
FROM EMPREGADO E 
INNER JOIN DEPENDENTE D 
ON E.IDEMP = D.IDEMP
WHERE E.SOXO = ‘F’ AND D.SEXO = ‘F’;)
UNION E UNION ALL 
UNION NÃO REPETE OS REGISTROS.
UNION ALL REPETE OS REGISTROS.
NUMERO E ORDEM DAS COLUNAS DEVEM SER IDÊNTICO BEM COMO OS TIPOS DE DADOS DAS COLUNAS.
SITAXE:
SELECT....
UNION|UNION ALL 
SELECT.....
INTERSECT 
SOMENTE OS REGISTROS COMUNS NAS DUAS TABELAS.
EXCEPT 
RETORNA AS LINHAS DA CONSULTA
A ESQUERDA QUE NÃO POSSUEM REGISTRO NA DIREITA 
CONT. EXERCICIO 
	PRODUTO
IDPRODUTO INT PK
NOME VARCHAR(255)
VALOR INT
	VENDA_PRODUTO
IDVENDA INT PK
IDPRODUTO INT FK
USANDO UNION, UNION ALL, INSERT OU EXCEPT
SELECIONE OS PRODUTOS QUE NÃO POSSUEM	
VENDA (IDPRODUTO, NOME E VALOR)
R1- (SELECT IDPRODUTO FROM PRODUTO
 EXCEPT
 SELECT IDPRODUTO FROM VENDA_PRODUTO;)
R2- (SELECT IDPRODUTO FROM VENDA_PRODUTO P 
 LEFT JOIN VENDA_PRODUTO VP
 ON VP.IDPRODUTO = P.IDPRODUTO
 WHERW VP.IDVENDA IS NULL;
 ID FROM EM)
SELECIONE OS PRUDUTOS QUE POSSUEM VENDA. 
 R-(SELECT IDPRODUTO FROM PRODUTO
 INTERSECT
 SELECT IDPRODUTO FROM VENDA_PRODUTO;)
SUB CONSUTA 
CONSULTA DENTRO DE UM OUTRO COMANDO SQL.
OS COMANDO PODEM SER:
CREAT TABLE 
CREAT VIEW
SELECT
INSERT
DELETE
UPDATE
REGRAS PARA CONSTRUÇÃO 
CONSULTA INTERNA DEVE ESTAR ENTRE PARENTESES E A MAIS INTERNA SEMPRE É EXECULTADA PRIMEIRO.
ADMITE ANINHAMENTO DE N CONSUTAS AND, OR.
RETORNA UMA OU VÁRIAS COLUNAS.
USUALMENTE É ULTILIZADA NA CLAUSULA WHERE DOS COMANDOS SELECR, UPDATE E DELETE.
NÃO PODE CONTER A CLAUSULA ORDER BY.
PODE REFERENCIAR COLUNAS DA CONSULTA MAIS EXTERNA.
SUB CONSULTA SIMPLES 
É AQUELA QUE RETORNA NO MAXIMO UMA LINHA, PODEM SER TRATADAS PELOS SEGUINTES OPERADORES. =, <>, <, >, <=, >=
CONT. EXERCICIO 
SELECIONE OS FUNCIONARIOS QUE TEM O CARGO GERENTE NO SETOR TI.
SELECT E.NOME FROM EMPREGADO E 
WHERE E.IDSETOR =
 (SELECT IDSETOR FROM SETOR S
WHERE S.NOME = TI)
AND 
E.IDCARGO = 
(
SELECT IDCARGO FROM CARGO C 
WHERE C.NOME = ’GERENTE’
);
SUB CONSULTAS MULTILINHAS
RETORNA MAIS QUE UMA LINHA A CONSULTA PRINCIPAL.
OPERADORES (IN, NOT IN). 
EXERCICIOS.
SELECIONE AS APLICAÇÕES QUE EXECUTAM EM SERVIDORES COM SISTEMA OPERACIONAL QUE POSSUE PARTE DO NOME LINUX. 
SELECT A.NOME APLICACAO
FROM APLICACAO A 
WHERE A.IDSERVIDOR IN
(
SELECT S.IDSERVIDOR 
FROM SERVIDOR S 
WHERE S.IDSO IN
(
SELECT SO.IDSO
FROM SO
WHERE SO.NOME LIKE
‘%LINUX%’
)
);
SELECIONE O NOME E A VERSÃO DO FRAMEWORK DA APLICAÇÃO QUE POSSUI O NOME SISTEMA TESTE.
SELECT F.NOME, F.VERSAO 
FROM FRAMEWORK F
WHERE F.IDFRAMEWORK IN
(
SELECT APF.IDFRAMEWORK
FROM APLICACAO_FRAMEWORK APF
WHERE APF.IDAPLICACAO IN
(
SELECT A.EDAPLICACAO
FROM APLICACAO A 
WHEREE A.NOMEAPLICACAO = ‘SISTEMA TESTE’
)
)
SELECIONE O NOME DO SERVIDOR, O NOME DA APLICAÇÃO E O NOME DO SISTEMA OPERACIONAL.
SELECIONE AS APLICASÕES QUE POSSUEM SIGLA INICIANDO EM ‘SI’, OI NOME DO SERVIDOR DOS SISTEMAS QUE SÃO PRODUZIDOS NO SISTEMAS OPERACIONAS COM O NOME ‘WINDOWS’.
SELECIONE AS APLICAÇÕES QUE POSSUEM CUSTO MAIOR QUE A MÉDIS DO CUSTO DAS APLICAÇÕES