Baixe o app para aproveitar ainda mais
Prévia do material em texto
Banco de Dados Structured Query Language (SQL) 3 Livros Texto : Introdução a Sistemas de Banco de Dados - Date Sistemas de Banco de Dados – Korth & Silberschatz Sistemas de Banco de Dados Fundamentos e Aplicações – Elmasri & Navathe Structured Query Language - Slide ‹nº› 1 SQL Manipulação de Dados SELECT/ORDER BY Classifica o resultado da consulta ( em ordem ascendente, caso nada seja especificado) Itens especificados no ORDER BY não precisam aparecer na lista da seleção Com ORDER BY nulos são listados primeiros. select [distinct] lista from tabela [where condicao] [order by {coluna | expressao} [asc|desc]] Select matricula, nome from empregado order by nome Select nome, salario from empregado order by salario desc Structured Query Language - Slide ‹nº› 2 SQL Manipulação de Dados FUNÇÃO DE AGREGAÇÃO e AGRUPAMENTO Funções comuns aplicadas a coleções de valores numérico, incluem SUM(soma), AVG(média), MAX (máximo) e MIN (mínimo); Função COUNT é utilizada para contagem de tuplas Função SUM e AVG só funcionam com valores numéricos; Utilizadas no SELECT e na cláusula HAVING; Structured Query Language - Slide ‹nº› 3 SQL Manipulação de Dados FUNÇÃO DE AGREGAÇÃO Não podem ser utilizadas num comando WHERE; Podem ser aplicados a todas as linhas em uma tabela ou num grupo de linhas de uma tabela. select funcao_agregada ([distinct] expressao) from tabela [where condicoes] Structured Query Language - Slide ‹nº› 4 SQL Manipulação de Dados FUNÇÃO DE AGREGAÇÃO - COUNT Soma o número de linhas resultante de uma condição estabelecida. Determinar o número de linhas da tabela select count(*) from empregado Número de valores não nulos de um atributo select count(atributo) from empregado Structured Query Language - Slide ‹nº› 5 SQL Manipulação de Dados FUNÇÃO DE AGREGAÇÃO – MAX/ MIN Determinar o maior valor select max(salario) from empregado Determina o menor valor select min(salario) from empregado Structured Query Language - Slide ‹nº› 6 SQL Manipulação de Dados FUNÇÃO DE AGREGAÇÃO – SUM/AVG Determinar o somatório dos valores da coluna especificada select sum(salario) from empregado where coddep = 4 Determina a média dos valores da coluna especificada select avg(salario) from empregado where funcao = ‘analista’ Structured Query Language - Slide ‹nº› 7 SQL Manipulação de Dados FUNÇÃO DE AGREGAÇÃO – DISTINCT Opcional com sum, avg e count; Não permitida com min, max e count (*); Permitida com count (atributo); Usado apenas com atributos, não com expressões aritméticas Média dos salarios distintos select avg(distinct salario) from empregado Conta as funções distintas Select count (distinct funcao) from empregado Structured Query Language - Slide ‹nº› 8 SQL Manipulação de Dados FUNÇÃO DE AGREGAÇÃO Funções de agregação, com exceção do COUNT(*), ignoram valores nulos; SUM e AVG só funcionam com valores numéricos; Retorna apenas uma linha (sem GROUP BY ); Structured Query Language - Slide ‹nº› 9 SQL Manipulação de Dados FUNÇÃO DE AGRUPAMENTO - SELECT/GROUP BY Divide os dados em grupos; Normalmente utilizada com uma função de agregação na lista de seleção; Os valores nulos na coluna do group by são tratados como um grupo; select [distinct] lista from tabela group by expressao Select codproj, avg(horas) from Trabalhaem group by codproj Structured Query Language - Slide ‹nº› 10 SQL Manipulação de Dados SELECT/GROUP BY/HAVING HAVING estabelece condições para a sentença GROUP BY; select [distinct] lista from tabela group by expressao having condicoes Select codproj, avg(horas) from Trabalhaem group by codproj having avg(horas) > 30 Structured Query Language - Slide ‹nº› 11 SQL Manipulação de Dados Exercícios 1. Qual é a média de salários dos empregados: a) por departamento? b) por funcao? 2. Qual é o salario mais baixo e mais alto de cada departamento? 3. Quantos horas cada empregado trabalhou em projetos ? 4. Para os empregado que trabalharam menos de 10 horas por projeto, calcule a média de horas trabalhadas em projetos destes funcionários. 5. Quantos empregados existem por funcao ? Structured Query Language - Slide ‹nº› 12 SQL Manipulação de Dados Exercícios 6. Qual o número de empregados por departamento? 7. Quantas horas foram gastas por projeto? 8. Quantos horas cada empregado trabalhou em projetos? 9. Quantos salarios distintos existem na tabela de empregados? 10. Por codigo do projeto recupere quantos funcionários trabalharam nele e qual a valor minimo e máximo do número de horas trabalhadas em cada projeto. Structured Query Language - Slide ‹nº› 13 SQL Manipulação de Dados Exercícios 11. Sem utilizar AVG calcule a média de sálario dos empregados por departamento. 12. Listar o codigo do departamento e nome de todos os empregados classificados por codigo do departamento e nome. 13. Qual o número de dependentes filhos de sexo feminino? 14. Selecione as matriculas dos empregados que trabalham em mais de 2 projetos. 15. Selecione os projetos onde trabalham mais de 3 empregados. Structured Query Language - Slide ‹nº› 14 SQL Manipulação de Dados Junções Pode-se utilizar tabelas do mesmo banco ou de banco diferentes; Atributos comparados devem ter valores similares; Valores nulos não participam da operação de junção; Os atributos na condição de junção não precisam estar na lista de seleção Select e. nome, d.nome from empregado e, departamento d where codepart = cod Structured Query Language - Slide ‹nº› 15 SQL Manipulação de Dados Junções Recupera dados de duas ou mais tabelas; Combina tabelas através da correspondência de valores de linhas em cada tabela; Qualificar os atributos que têm o mesmo nome, usando o nome do atributo precedido pelo nome da tabela; Ex: tabela.atributo Structured Query Language - Slide ‹nº› 16 SQL Manipulação de Dados Junções Idênticas Os atributos de junção são do mesmo tipo e fazem parte de um relacionamento entre as tabelas. (integridade referencial, chaves primária e estrangeira). Não idênticas Os atributos de junção podem não ser do mesmo tipo e nem fazer parte de um relacionamento entre as tabelas. Muito utilizada para associar o resultado de uma função de agregação com outras tabelas. Structured Query Language - Slide ‹nº› 17 SQL Manipulação de Dados Exercícios 1. Mostre nome do departamento e todos os seus empregados classificado por nome do departamento e nome empregado; 2.Recupere os nome departamento, nome empregado e nome do projeto ordenados por departamento e nome; Structured Query Language - Slide ‹nº› 18 SQL Manipulação de Dados Junções – Expressão JOIN SQL2 - Possibilidade de colocar na cláusula FROM a especificação da junção [CROSS, NATURAL, INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER] JOIN Cross Join – Produto cartesiano Natural Join – Junção Natural - Junta tabelas sem precisar declarar a condição seleção. Para isso as relações devem possuir atributos com mesmo nome. Será executada uma igualdade para cada par de atributos com o mesmo nome. Structured Query Language - Slide ‹nº› 19 SQL Manipulação de Dados Junções – Expressão JOIN Inner Join – Junção Interna - Padrão para join. Junta tabelas a partir de uma condição especificada. Uma tupla será incluída no resultado somente se existir uma tupla que combine na outra relação. Outer Join – Junção Externa - Junta tabelas a partir de uma condição especificada. Uma tupla será incluída no resultado se existir uma tupla que combine na outrarelação, caso não combine, a tupla será incluída para a relação sinalizada (esquerda ou direita) e os atributos da outra relação serão preenchidos com nulo. LEFT OUTER JOIN – Junção externa à direita RIGHT OUTER JOIN – Junção externa à esquerda FULL OUTER JOIN – Junção externa completa Structured Query Language - Slide ‹nº› 20 SQL Manipulação de Dados Junções – Expressão JOIN Produto cartesiano SELECT * FROM departamento, empregado; pode ser : SELECT * FROM departamento CROSS JOIN empregado; Structured Query Language - Slide ‹nº› 21 SQL Manipulação de Dados Junções interna – Expressão INNER JOIN Selecione o nome e função dos empregados que trabalham no departamento de Pesquisa SELECT empregado. nome, empregado.funcao FROM (empregado INNER JOIN departamento ON empregado.codepart = departamento.cod ) WHERE departamento.nome = ‘Pesquisa’; semelhante a : SELECT empregado. nome, empregado.funcao FROM empregado, departamento WHERE empregado.codepart = departamento.cod AND departamento.nome = ‘Pesquisa’; Structured Query Language - Slide ‹nº› 22 SQL Manipulação de Dados Junções interna – Expressão INNER JOIN Para os projetos concluídos, liste o número do projeto, departamento controlador e o nome do gerente do departamento. SELECT projeto.cod, departamento.nome, empregado. nome FROM ((projeto JOIN departamento ON projeto.coddepar = departamento.cod ) JOIN empregado on departamento.matgerente=empregado.mat) WHERE projeto.situacao=‘Concluido’; semelhante a : SELECT projeto.cod, departamento.nome, empregado. nome FROM projeto, departamento, empregado WHERE projeto.coddepar = departamento.cod AND departamento.matgerente=empregado.mat AND projeto.situacao=‘Concluido’; Structured Query Language - Slide ‹nº› 23 SQL Manipulação de Dados Na junção interna (inner join) uma tupla é incluída no resultado somente se as tuplas das duas relações combinarem. SELECT empregado.nome, dependente. nome FROM (empregado JOIN dependente ON empregado.mat = dependente.mat) Neste caso os empregados sem dependentes não aparecerão Structured Query Language - Slide ‹nº› 24 SQL Manipulação de Dados Junções externas – Expressão OUTER JOIN No junção externa (outer join) as tuplas que não atendem a condição aparecerão com os atributos da relação indicada (ou right ou left ou full) e com os atributos da outra relação nulos. SELECT empregado.nome, dependente. nome FROM (empregado LEFT OUTER JOIN dependente ON empregado.mat = dependente.mat) Neste caso todos os empregados aparecerão, nos empregados sem dependentes o nome do dependente ficará nulo. Structured Query Language - Slide ‹nº› 25 SQL Manipulação de Dados Exercícios 1. Listar para cada empregado, o nome de todos os seus dependentes e sexo dos dependentes, ordenados por sexo do dependente; 2.Mostre para cada nome de departamento o total de salário de seus empregados, ordenado por total de salário; 3.Recupere o matrícula, nome e salário total (salário + comissão) de todos os empregados que ganham mais que seu gerente; Structured Query Language - Slide ‹nº› 26 SQL Manipulação de Dados Exercícios 4. Selecione os nomes e funções dos empregados que trabalham no projeto Sistema de Pagamento e são do departamento Informática; 5. Mostre para cada nome de departamento, o nome de seu gerente e salário, ordenado por salário em ordem descendente; 6. Mostre o nome, horas trabalhadas e situação dos projetos que Ana Santos trabalhou. 7. Calcule o salário mais comissão de todos os funcionarios do departamento Informática que têm funcao Analista; Structured Query Language - Slide ‹nº› 27 SQL Manipulação de Dados Exemplos 8. Selecionar todos os projetos em andamento com seus empregados em ordem de projetos e empregados. 9. Selecionar os empregados e quantidade de dependentes para empregados que têm mais de 2 dependentes. 10. Selecionar todos os empregados com seus dependentes. 11. Selecione o nome de todos os empregados e dos projetos em que estão envolvidos. Structured Query Language - Slide ‹nº› 28
Compartilhar