1 / 52

• Os exemplos serão elaborados para o esquema de dados a seguir:

Comandos SQL - DML. • Os exemplos serão elaborados para o esquema de dados a seguir: EMPREGADO ( matricula , nome, sexo, endereco , salario, supervisor , depto ) DEPARTAMENTO ( coddep , nome, gerente , dataini ) PROJETO ( codproj , nome, local, depart )

aracely-trey
Download Presentation

• Os exemplos serão elaborados para o esquema de dados a seguir:

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Comandos SQL - DML • Os exemplos serão elaborados para o esquema de dados a seguir: EMPREGADO(matricula, nome, sexo, endereco, salario, supervisor, depto) DEPARTAMENTO(coddep, nome, gerente, dataini) PROJETO(codproj, nome, local, depart) ALOCACAO(matric, codigop, horas) DEPENDENTE(coddepend, matricula, nome, sexo)

  2. Comandos SQL - DML

  3. Comandos SQL – DML - Exemplos • SQL implementa a operação UNIAO da álgebra relacional. É requerido que as relações sejam compatíveis de união - Q12. Liste todos os nomes de projetos que envolvem o empregado ‘Suzana’ como trabalhador ou como gerente do departamento que controla o projeto

  4. Comandos SQL – DML - Exemplos • Q12 (parcial). Liste todos os nomes de projetos que envolvem o empregado ‘Suzana’ como trabalhador • SELECT p.nome • FROM PROJETO p, ALOCACAO a, EMPREGADO e • WHERE p.codproj = a.codigop • AND e.matricula = a.matric • AND e.nome = ‘Suzana'

  5. Comandos SQL – DML - Exemplos • Q12 (parcial). Liste todos os nomes de projetos que envolvem o empregado ‘Suzana’ como gerente do departamento que controla o projeto • SELECT p.nome FROM PROJETO p, DEPARTAMENTO d, EMPREGADO e • WHERE d.coddep = p.depart • AND d.gerente = e.matricula • AND e.nome = ‘Suzana'

  6. Comandos SQL – DML - Exemplos - Q12. (SELECT p.nome FROM PROJETO p, ALOCACAO a, EMPREGADO e WHERE p.codproj = a.codigop AND e.matricula = a.matric AND e.nome = ‘Suzana') UNION (SELECT p.nome FROM PROJETO p, DEPARTAMENTO d, EMPREGADO e WHERE d.coddep = p.depart AND d.gerente = e.matricula AND e.nome = ‘Suzana');

  7. Comandos SQL – DML - Exemplos • Consultas Aninhadas: consultas que possuem consultas completas dentro de sua cláusula WHERE - Motivação: Algumas queries requerem que valores do BD sejam buscados e então usados numa condição - Q13. A consulta Q12 poderia ser reescrita da seguinte forma:

  8. Comandos SQL – DML - Exemplos - Q13. SELECT DISTINCT nome FROM PROJETO WHERE codprojIN (SELECT p.codproj FROM PROJETO p, DEPARTAMENTO d, EMPREGADO e WHERE p.depart = d.coddep AND d.gerente= e.matricula AND e.nome= ‘Suzana’) OR codprojIN (SELECT a.codigop FROM ALOCAÇÃO a, EMPREGADO e WHERE a.matric = e.matricula AND e.nome= ‘Suzana’)

  9. Comandos SQL – DML - Exemplos • Q14. Recupere o nome de cada empregado que tem um dependente com o mesmo nome e mesmo sexo

  10. Comandos SQL – DML - Exemplos • Q14. Recupere o nome de cada empregado que tem um dependente com o mesmo nome e mesmo sexo SELECT e.nome FROM EMPREGADO e WHERE e.matricula IN (SELECT matricula FROM DEPENDENTE d WHERE d.matricula= e.matricula AND e.nome = d.nome AND e.sexo = d.sexo); Obs.: Veja que e.matricula, e.nome e e.sexo são atributos de empregado da consulta externa

  11. Comandos SQL – DML - Exemplos -Q15. Re-escrevendo a Q14 sem usar aninhamento

  12. Comandos SQL – DML - Exemplos -Q15. Re-escrevendo a Q14 sem usar aninhamento SELECT e.nome FROM EMPREGADO e, DEPENDENTE d WHERE e.matricula = d.matricula ande.nome = d.nome ande.sexo = d.sexo

  13. Comandos SQL – DML - Exemplos • • A função EXISTS • É usada para verificar se o resultado de uma consulta aninhada é vazio ou não. É sempre usado em conjunto com um query aninhada • -A consulta Q14 poderia ser: • SELECT e.nome • FROM EMPREGADO e • WHERE EXISTS (SELECT * • FROM DEPENDENTE • WHERE e.matricula = matricula • ande.nome = nome • ande.sexo = sexo)

  14. Comandos SQL – DML - Exemplos • Podemos usar o NOT EXISTS(Q) - Q16. Recupere os nomes dos empregados que não têm dependentes

  15. Comandos SQL – DML - Exemplos • Podemos usar o NOT EXISTS(Q) - Q16. Recupere os nomes dos empregados que não têm dependentes SELECT e.nome FROM EMPREGADO e WHERE NOT EXISTS (SELECT * FROM DEPENDENTE WHERE e.matricula = matricula)

  16. Comandos SQL – DML - Exemplos • Podemos usar um conjunto de valores explícitos - Q17. Selecione a matricula de todos os empregados que trabalham nos projetos 10, 20 ou 30

  17. Comandos SQL – DML - Exemplos • Podemos usar um conjunto de valores explícitos - Q17. Selecione a matricula de todos os empregados que trabalham nos projetos 10, 20 ou 30 SELECT DISTINCT matric FROM ALOCACAO WHERE codigop IN (10,20,30)

  18. Comandos SQL – DML - Exemplos • Podemos verificar valores nulos através de IS NULL e IS NOTNULL - Q18. Selecione os nomes de todos os empregados que não têm supervisores

  19. Comandos SQL – DML - Exemplos • Podemos verificar valores nulos através de IS NULL e IS NOTNULL - Q18. Selecione os nomes de todos os empregados que não têm supervisores SELECT nome FROM EMPREGADO WHERE supervisor IS NULL

  20. Comandos SQL – DML - Funções • SQL fornece 5 funções embutidas - COUNT: retorna o número de tuplas ou valores especificados numa query - SUM: retorna a soma os valores de uma coluna - AVG: retorna a média dos valores de uma coluna - MAX: retorna o maior valor de uma coluna - MIN: identifica o menor valor de uma coluna • Estas funções só podem ser usadas numa cláusula SELECT ou numa cláusula HAVING (a ser vista depois)

  21. Comandos SQL – DML - Exemplos -Q19. Encontre o total de salários, o maior e o menor salário e a média salarial da relação empregados

  22. Comandos SQL – DML - Exemplos -Q19. Encontre o total de salários, o maior e o menor salário e a média salarial da relação empregados SELECT SUM(salario), MAX(salario), MIN(salario), AVG(salario) FROM EMPREGADO

  23. Comandos SQL – DML - Exemplos - Q20. Encontre o maior e menor salário do departamento de Produção - Q.21 Obtenha o número de empregados da empresa

  24. Comandos SQL – DML - Exemplos - Q20. Encontre o maior e menor salário do departamento de Produção SELECT MAX(salario), MIN(salario) FROM EMPREGADO e, DEPARTAMENTO d WHERE e.depto = d.coddep AND d.nome = 'Produção'; - Q.21 Obtenha o número de empregados da empresa SELECT COUNT(*) FROM EMPREGADO

  25. Comandos SQL – DML - Exemplos • Q.22 Obter o número de salários distintos do departamento de Contabilidade

  26. Comandos SQL – DML - Exemplos • Q.22 Obter o número de salários distintos do departamento de Contabilidade • SELECT COUNT(DISTINCT salario) • FROM empregado e, departamento d • WHERE (e.depto = d.coddep AND d.nome = 'Contabilidade'); • -O que aconteceria se escrevêssemos COUNT(salario) ao invés de COUNT(DISTINCT salario)?

  27. Comandos SQL – DML - Exemplos - Q23. Obter o nome dos empregados que tenham 2 ou mais dependentes

  28. Comandos SQL – DML - Exemplos - Q23. Obter o nome dos empregados que tenham 2 ou mais dependentes SELECT e.nome FROM EMPREGADO e WHERE (SELECT COUNT(*) FROM DEPENDENTE d WHERE e.matricula = d.matricula) >= 2

  29. Comandos SQL – DML - Exemplos • • Cláusulas GROUP BY e HAVING • Usadas para lidar com grupos • Q24. Para cada departamento, obter o código do departamento, o número de empregados e a média salarial

  30. Comandos SQL – DML - Exemplos • • Cláusulas GROUP BY e HAVING • Usadas para lidar com grupos • Q24. Para cada departamento, obter o código do departamento, o número de empregados e a média salarial • SELECT depto, COUNT(*), AVG(salario) FROM EMPREGADO GROUP BY depto • - As tuplas de empregados são separadas em grupos (departamento) e as funções COUNT e AVG são aplicadas a cada grupo separadamente

  31. Comandos SQL – DML - Exemplos • Q25. Para cada projeto, obter o código do projeto, seu nome e o número de empregados que trabalham nele

  32. Comandos SQL – DML - Exemplos • Q25. Para cada projeto, obter o código do projeto, seu nome e o número de empregados que trabalham nele SELECT codigop, nome, COUNT(*) FROM PROJETO p, ALOCACAO a WHERE p.codproj = a.codigop GROUP BY p.codproj, p.nome - O agrupamento e as funções são aplicadas após a junção

  33. Comandos SQL – DML - Exemplos • • HAVING • Cláusula usada em conjunto com GROUP BY para permitir a inclusão de condições nos grupos • • Q26. Para cada projeto que possui mais de 2 empregados trabalhando, obter o código do projeto, nome do projeto e número de empregados que trabalha neste projeto

  34. Comandos SQL – DML - Exemplos • Q26. SELECT p.codproj, p.nome, COUNT(*) FROM PROJETO p, ALOCACAO a WHERE p.codproj = a.codigop GROUP BY p.codproj, p.nome HAVING COUNT(*) > 2

  35. Comandos SQL – DML - Exemplos • • Operadores de comparação e aritméticos • • BETWEEN • Sintaxe: expressão [NOT] BETWEEN expressão AND expressão • Ex.: y BETWEEN x AND z equivale a x <= y <= z • - Q27. Selecione os nomes dos empregados que ganham mais de 1000 e menos de 2000 reais

  36. Comandos SQL – DML - Exemplos • • Operadores de comparação e aritméticos • • BETWEEN • Sintaxe: expressão [NOT] BETWEEN expressão AND expressão • Ex.: y BETWEEN x AND z equivale a x <= y <= z • - Q27. Selecione os nomes dos empregados que ganham mais de 1000 e menos de 2000 reais • SELECT nome • FROM EMPREGADO • WHERE salario BETWEEN 3000 AND 8000

  37. Comandos SQL – DML - Exemplos • • LIKE • Permite comparações de substrings. Usa dois caracteres reservados ‘%’ (substitui um número arbitrário de caracteres) e ‘_‘ (substitui um único caracter) • - Q.28 Obter os nomes de empregados cujos endereços estão em Espírito Santo

  38. Comandos SQL – DML - Exemplos • • LIKE • Permite comparações de substrings. Usa dois caracteres reservados ‘%’ (substitui um número arbitrário de caracteres) e ‘_‘ (substitui um único caracter) • - Q.28 Obter os nomes de empregados cujos endereços estão em Espírito Santo • SELECT nome • FROM EMPREGADO • WHERE endereco LIKE ‘%Santo%’

  39. Comandos SQL – DML - Exemplos - Q29. Queremos ver o efeito de dar aos empregados que trabalham no ProdutoX um aumento de 10%

  40. Comandos SQL – DML - Exemplos - Q29. Queremos ver o efeito de dar aos empregados que trabalham no ProdutoX um aumento de 10% SELECT e.nome, 1.1*salario FROM EMPREGADO e, ALOCACAO a, PROJETO p WHERE e.matricula = a.matric anda.codigop = p.codproj andp.nome = 'Pop Center 2‘;

  41. Comandos SQL – DML - Exemplos • Ordenação - O operador ORDER BY permite ordenar o resultado de uma query por um ou mais atributos - Q.30 Obter uma lista de empregados e seus respectivos departamentos e projetos, listando ordenado pelo nome do departamento

  42. Comandos SQL – DML - Exemplos • Ordenação - O operador ORDER BY permite ordenar o resultado de uma query por um ou mais atributos - Q.30 Obter uma lista de empregados e seus respectivos departamentos e projetos, listando ordenado pelo nome do departamento SELECT d.nome, e.nome, p.nome FROM DEPARTAMENTO d, EMPREGADO e, PROJETO p, ALOCACAO a WHERE d.codDep = e.depto AND e.matricula = a.matric AND a.codigop = p.codproj ORDER BY d.nome, e.nome;

  43. Comandos SQL – DML - Exemplos • Ordenação -A ordem default é ascendente (ASC) caso queiramos ordem decrescente usamos DESC ORDER BY d.nome DESC, e.nome ASC

  44. Inserção de Dados • • O comando INSERT • - Usado para adicionar uma tupla a uma relação • - Sintaxe: INSERT INTO tabela [ (lista colunas)] fonte • - Onde fonte pode ser uma especificação de pesquisa (SELECT) ou uma cláusula VALUES da forma • • VALUES (lista de valores atômicos) • Obs.: Se o comando INSERT incluir a cláusula VALUES então uma única tupla é inserida na relação • Ex.: • INSERT INTO EMPREGADO (matricula, nome) VALUES (9491,’Ana’)

  45. Inserção de Dados • Obs.: A inserção será rejeitada se tentarmos omitir um atributo que não permite valores nulos (NOT NULL) • Ex.: • INSERT INTO EMPREGADO (nome, salario) VALUES(‘Flávia’, 960) • - Podemos inserir várias tuplas numa relação através de uma query

  46. Inserção de Dados • CREATE TABLE DEPTO_INFO • (nome character(15), numempinteger, totsal real); • INSERT INTO DEPTO_INFO(nome, numemp, totsal) • SELECT d.nome, COUNT(*), SUM(salario) • FROM DEPARTAMENTO d, EMPREGADO e • WHERE d.coddep = e.depto • GROUP BY d.nome

  47. Exclusão de Dados • • O comando DELETE • - Remove tuplas de uma relação • Sintaxe DELETE FROM tabela [WHERE condição] • - Obs.: Se omitirmos a cláusula WHERE, então o DELETE deve ser aplicado a todas as tuplas da relação. Porém, a relação permanece no BD como uma relação vazia

  48. Modificação de Dados • O comando UPDATE - Modifica o valor de atributos de uma ou mais tuplas. - Sintaxe UPDATE tabela SET lista_atributos com atribuições de valores [WHERE condição] - Obs.: omitir a cláusula WHERE implica que o UPDATE deve ser aplicado a todas as tuplas da relação

  49. Modificação de Dados • O comando UPDATE - Ex. Modifique o nome do Departamento de Computação para Departamento de Informática

  50. Modificação de Dados • O comando UPDATE - Ex. Modifique o nome do Departamento de Computação para Departamento de Informática UPDATE DEPARTAMENTO SET nome = ‘Informática’ WHERE nome = ‘Computação’

More Related