Upload
internet
View
132
Download
6
Embed Size (px)
Citation preview
REVISÃOComandos SQL - DML SELECT * FROM ?
• 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)
Comandos SQL - DML
Comandos SQL - DML
• Operações de manipulação:
- SELECT
- INSERT
- UPDATE
- DELETE
Comandos SQL - DML
• O comando SELECT: forma básicaSELECT <lista atributos>
FROM <lista tabelas>
WHERE <condição>
• O resultado de qualquer comando SELECT é uma tabela- O campo FROM realiza produto cartesiano das
tabelas relacionadas- WHERE deve trazer as condições de junção
Comandos SQL - DML
• Q1. Obtenha todos os atributos de todos os empregados
• Q2. Selecione as matrículas dos empregados
Comandos SQL – DML - Desafio
• Q1. Obtenha todos os atributos de todos os empregadosSELECT *
FROM Empregado
• Q2. Selecione as matrículas dos empregadosSELECT matricula FROM empregado
Comandos SQL – DML - Desafio
• Q3. Obtenha o salário do empregado Eduardo
• Q4. Selecione todos os atributos de todos os empregados do departamento d5
Comandos SQL – DML - Desafio
• Q3. Obtenha o salário do empregado EduardoSELECT salario FROM Empregado WHERE nome = ‘Eduardo’
• Q4. Selecione todos os atributos de todos os empregados do departamento código 4SELECT *FROM Empregado WHERE depto = 4
Comandos SQL – DML - Desafio
10
• Q5. Faça o produto cartesiano, seguido de projeção de Empregados X Departamento retornando a matrícula do empregado e o nome do departamento
SELECT empregado.matricula,
departamento.nome
FROM empregado,
departamento;
A cláusula FROM realiza o produto cartesiano- Departamento.nome é especificado para não haver confusão
com o atributo nome de empregado
- Empregados são relacionados com outros departamentos além daquele em que trabalham
Comandos SQL – DML - Desafio
11
• Q5. Faça o produto cartesiano, seguido de projeção de Empregados X Departamento retornando a matrícula do empregado e o nome do departamento
SELECT empregado.matricula,
departamento.nome
FROM empregado,
departamento
WHERE empregado.codigoDepertamento = departamento.codigoDepartamento;
A cláusula FROM realiza o produto cartesiano- Departamento.nome é especificado para não haver confusão
com o atributo nome de empregado- Empregados são relacionados com outros departamentos
além daquele em que trabalham (problema resolvido no WHERE)
Comandos SQL – DML - Desafio
• Q6. Selecione o nome e o endereço de todos os empregados que trabalham no departamento 'DDG - Departamento de Desenvolvimento de Games'
Comandos SQL – DML - Desafio
• Q6. Selecione o nome e o endereço de todos os empregados que trabalham no departamento 'DDG - Departamento de Desenvolvimento de Games'
SELECT e.nome, e.endereco
FROM empregado e, departamento d
WHERE d.coddep = e.deptoAND d.nome = 'DDG - Departamento de
Desenvolvimento de Games';
Comandos SQL – DML - Desafio
• Q7. Para cada projeto no ‘Fragata’, liste o código do projeto, o departamento que controla o projeto e o nome do gerente com endereço e salário
Comandos SQL – DML - Exemplos
• Q7. Para cada projeto em ‘Fragata’, liste o código do projeto, o departamento que controla o projeto e o nome do gerente com endereço e salário
SELECT p.codproj, d.nome, e.nome, e.endereco, e.salarioFROM projeto p, departamento d, empregado eWHERE p.depart = d.coddep AND d.gerente = e.matricula AND p.local = ‘Fragata’
Comandos SQL – DML - Exemplos
• Q8. Para cada empregado, recupere seu nome e o nome do seu supervisor
Comandos SQL – DML - Exemplos
• Q8. Para cada empregado, recupere seu nome e o nome do seu supervisor
SELECT e.nome, s.nomeFROM empregado e, empregado sWHERE s.matricula = e.supervisor
Comandos SQL – DML - Exemplos
- Q9. Selecione todos os atributos de todos os empregados do 'DTRD - Departamento de Teste de Redes para Dormir'
- Q10. Recupere os salários dos empregados
Comandos SQL – DML - Exemplos
- Q9. Selecione todos os atributos de todos os empregados do departamento 'DTRD - Departamento de Teste de Redes para Dormir'
SELECT *
FROM empregado e, departamento d
WHERE d.coddep = e.depto
AND d.nome = 'DTRD - Departamento de
Teste de Redes para Dormir'
- Q10. Recupere os salários dos empregados
SELECT salario FROM empregado
Comandos SQL – DML - Exemplos
• Algumas vezes surgem duplicatas como resposta a uma query. Podemos eliminá-las usando o comando DISTINCT na cláusula SELECT
- Q11. Selecione os diferentes salários pagos pela empresa aos empregados
Comandos SQL – DML - Exemplos
• Algumas vezes surgem duplicatas como resposta a uma query. Podemos eliminá-las usando o comando DISTINCT na cláusula SELECT
- Q11. Selecione os diferentes salários pagos pela empresa aos empregados
SELECT DISTINCT salario
FROM empregado
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 ‘Eduardo’ como trabalhador ou como gerente do departamento que controla o projeto
Comandos SQL – DML - Exemplos
- Q12 (parcial). Liste todos os nomes de projetos que envolvem o empregado ‘Eduardo’ 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 = 'Eduardo';
Comandos SQL – DML - Exemplos
- Q12 (parcial). Liste todos os nomes de projetos que envolvem o empregado ‘Eduardo’ 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 = 'Eduardo‘;
Comandos SQL – DML - Exemplos
- Q12.
(SELECT p.nomeFROM PROJETO p, ALOCACAO a, EMPREGADO eWHERE p.codproj = a.codigopAND e.matricula = a.matricAND e.nome = 'Eduardo')
UNION(SELECT p.nome FROM PROJETO p, DEPARTAMENTO d, EMPREGADO e WHERE d.coddep = p.departAND d.gerente = e.matricula AND e.nome = 'Eduardo');
Comandos SQL – DML - Exemplos
26
Comandos SQL - DMLSOCORRO!
MATÉRIA NOVA!
• 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)
Comandos SQL - DML
Comandos SQL - DML
• 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:
Comandos SQL – DML - Exemplos
Comandos SQL – DML - Exemplos- Q13.
SELECT DISTINCT nome FROM PROJETO WHERE codproj IN (SELECT p.codproj FROM PROJETO p, DEPARTAMENTO d, EMPREGADO e WHERE p.depart = d.coddep AND d.gerente = e.matricula AND e.nome = 'Eduardo') OR codproj IN (SELECT a.codigop FROM ALOCAÇÃO a, EMPREGADO e WHERE a.matric = e.matricula AND e.nome = 'Eduardo')
• Q14. Recupere o nome de cada empregado que tem um dependente com o mesmo nome e mesmo sexo
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
Comandos SQL – DML - Exemplos
-Q15. Re-escrevendo a Q14 sem usar aninhamento
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 and e.nome = d.nome and e.sexo = d.sexo
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.nomeFROM EMPREGADO eWHERE EXISTS (SELECT *
FROM DEPENDENTE WHERE e.matricula = matricula and e.nome = nome and e.sexo = sexo)
Comandos SQL – DML - Exemplos
• Podemos usar o NOT EXISTS(Q)- Q16. Recupere os nomes dos empregados que não
têm dependentes
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)
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
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)
Comandos SQL – DML - Exemplos
• Podemos verificar valores nulos através de IS NULL e IS NOT NULL
- Q18. Selecione os nomes de todos os empregados que não têm supervisores
Comandos SQL – DML - Exemplos
• Podemos verificar valores nulos através de IS NULL e IS NOT NULL
- Q18. Selecione os nomes de todos os empregados que não têm supervisores
SELECT nome
FROM EMPREGADO
WHERE supervisor IS NULL
Comandos SQL – DML - Exemplos
• 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)
Comandos SQL – DML - Funções
-Q19. Encontre o total de salários, o maior e o menor salário e a média salarial da relação empregados
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
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
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
Comandos SQL – DML - Exemplos
- Q.22 Obter o número de salários distintos do departamento de Contabilidade
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 = 'DDG -
Departamento de Desenvolvimento de Games');
-O que aconteceria se escrevêssemos COUNT(salario) ao invés de COUNT(DISTINCT salario)?
Comandos SQL – DML - Exemplos
- Q23. Obter o nome dos empregados que tenham 2 ou mais dependentes
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
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
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
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
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
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
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
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
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 nomeFROM EMPREGADOWHERE salario BETWEEN 3000 AND 8000
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
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 EMPREGADOWHERE endereco LIKE '%Oliveira%';
Comandos SQL – DML - Exemplos
- Q29. Queremos ver o efeito de dar aos empregados que trabalham no ProdutoX um aumento de 10%
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
and a.codigop = p.codproj
and p.nome = 'Pop Center 2';
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
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;
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
Comandos SQL – DML - Exemplos
• 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')
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
Inserção de Dados
CREATE TABLE DEPTO_INFO
(nome character(15), numemp integer, 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;
Inserçã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
Exclusã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
Modificação de Dados
• O comando UPDATE
- Ex. Modifique o nome do Departamento de Computação para Departamento de Informática
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 = 'DDG - Departamento de Desenvolvimento de Games'
WHERE nome = 'DDG';
Modificação de Dados
• O comando UPDATE
- Ex. Dê um aumento de 10% a todos os empregados do departamento de Pesquisa
Modificação de Dados
• O comando UPDATE
- Ex. Dê um aumento de 10% a todos os empregados do departamento de Pesquisa
UPDATE EMPREGADO
SET salario = salario * 1.1
WHERE depto in (SELECT coddep
FROM DEPARTAMENTO
WHERE nome = 'DDG');
Modificação de Dados