Banco de Dados I
SQL – DML
Frederico D. Bortoloti
[email protected]
SQL - DML
• O
Os exemplos
l d
da aula
l serão
ã elaborados
l b d
para o esquema de dados a seguir:
Empregado(matricula, nome,
Empregado(matricula
nome endereco
endereco, salario
salario,
supervisor, depto)
Departamento(coddep nome,
Departamento(coddep,
nome gerente
gerente, dataini)
Projeto(codproj, nome, local, depart)
Al
Alocacao(matric,codigop,
( ti
di
h
horas)
)
Dependente(coddepend, matricula, nome,
sexo)
SQL - DML
• As operações de manipulação sem cursor
são
– SELECT
– INSERT
– UPDATE
– DELETE
SQL - DML
• O comando SELECT: forma básica
SELECT <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
SQL - DML: Exemplos
• Q1
Q1. Obt
Obtenha
h todos
t d os atributos
t ib t de
d ttodos
d os
empregados
Select *
From Empregado
p g
• Q2
Q2. Selecione as matrículas dos empregados
Select matricula
F
From
empregado
d
SQL - DML: Exemplos
• Q3
Q3. Obtenha
Obt h o salário
lá i d
do empregado
d JJosé
é
Select salario
From Empregado
Where nome = ‘José’
• Q4. Selecione todos os atributos de todos os
empregados do departamento d5
Select *
From Empregado
Where depto = ‘d5’
d5
SQL - DML: Exemplos
• 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 matricula, Departamento.nome
From Empregado,
Empregado Departamento
– A cláusula FROM já 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
d
departamentos
t
t além
lé d
daquele
l em que ttrabalham
b lh
SQL - DML: Exemplos
• Q6
Q6. Selecione
S l i
o nome e o endereço
d
d
de ttodos
d os
empregados que trabalham no departamento de
produção
Select e.nome, e.endereco
F
From
empregado
d e, d
departamento
t
t d
Where d.nome = ‘Produção’ and d.coddep =
e.depto
d t
SQL - DML: Exemplos
• Q7
Q7. Para
P
cada
d projeto
j t em ‘F
‘Fortaleza’,
t l
’ liliste
t 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,
p codproj d
d.nome,
nome e
e.nome,
nome
e.endereco, e.salario
From Projeto p
p, Departamento d
d,
Empregado e
Where p
p.depart
depart = d
d.coddep
coddep and d
d.gerente
gerente =
e.matricula and p.local = ‘Fortaleza’
SQL - DML: Exemplos
• Q8
Q8. P
Para cada
d empregado,
d recupere
seu nome e o nome do seu supervisor
Select e.nome, s.nome
From Empregado e s
Where s.matricula = e.supervisor
SQL - DML: Exemplos
– Q9. Selecione todos os atributos de todos os
empregados do departamento pessoal
Select *
From Empregado e, Departamento d
Where d.nome = ‘Pessoal’ and
d coddep = e
d.coddep
e.depto
depto
– Q10. Recupere os salários dos empregados
Select salario
From empregado
SQL - DML: Exemplos
• Algumas vezes surgem duplicatas como
p
a uma q
query.
y Podemos elimináresposta
las usando o comando DISTINCT na
cláusula SELECT
– Q11.
Q11 Selecione
S l i
os diferentes
dif
salários
lá i pagos
pela empresa aos empregados
Select distinct salario
p g
From empregado
SQL - DML: Exemplos
• SQL implementa a operação UNIÃO
à da
g
relacional. É requerido
q
q
que as
álgebra
relações sejam compatíveis de união
– Q12. Liste todos os nomes de projetos que
envolvem
l
o empregado
d ‘Sil
‘Silva’’ como
trabalhador ou como gerente do
d
departamento
t
t que controla
t l o projeto
j t
SQL - DML: Exemplos
(SELECT p.nome
FROM Projeto
j
P,, Departamento
p
d,, Empregado
p g
e
WHERE d.coddep
d coddep = p
p.depart
depart and d.gerente
d gerente =
e.matricula and e.nome = ‘Silva’)
UNION
((SELECT p
p.nome
FROM Projeto p, Alocação a, Empregado e
WHERE p.codproj
d j = a.codproj
d j and
d e.matricula
ti l
= a.matricula and e.nome = ‘Silva’)
SQL - DML: Exemplos
• Além da operação UNIÃO,
à o SQL
p
as operações
p ç
INTERSEÇÃO
Ç
implementa
e DIFERENÇA ( ∩ e - )
– INTERSECT
– EXCEPT
• A sintaxe para os dois comandos é a
mesma da UNIÃO
SQL - DML: Exemplos
• Consultas Aninhadas: consultas que
possuem consultas completas
p
p
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:
SQL - DML: Exemplos
SELECT DISTINCT nome
FROM Projeto
WHERE codproj in
(SELECT p.codproj
FROM Projeto p,
p Departamento d
d, Empregado e
WHERE p.depart = d.coddep and d.gerente =
e.matricula and e.nome = ‘Silva’))
or
codproj in
(SELECT a.codigop
FROM Alocação a, Empregado e
WHERE a.matric
a matric = e.matricula
e matricula and e
e.nome
nome =
‘Silva’)
SQL - DML: Exemplos
• Q14
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
d t
WHERE matricula = e.matricula
and e
e.nome
nome = nome
and e.sexo = sexo)
Obs.: Veja que e.matricula, e.nome e e.sexo são
atributos de empregado da consulta externa
SQL - DML: Exemplos
– Q15.
Q15 Re
Re-escrevendo
escrevendo a Q14 sem usar
aninhamento
SELECT e.nome
FROM empregado e
e, dependente d
WHERE e.matricula = d.matricula and
e.nome = d.nome and e.sexo = d.sexo
SQL - DML: Exemplos
• A função EXISTS
– É usada para verificar se o resultado de uma
consulta aninhada é vazio ou não
não. É sempre
usado em conjunto com um query aninhada
– A consulta Q14 poderia ser:
SELECT e.nome
FROM empregado
d e
WHERE EXISTS (SELECT *
FROM dependente
WHERE e.matricula = matricula and
e.nome = nome and e.sexo
= sexo)
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)
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)
(10 20 30)
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
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
l
– MAX: retorna o maior valor de uma coluna
– MIN: identifica o menor valor de uma coluna
• Estas funções
ç
só p
podem ser usadas numa
cláusula select ou numa cláusula HAVING (a ser
vista depois)
SQL - DML: Exemplos
– Q19.
Q19 Encontre o total de salários
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
E
d
SQL - DML: Exemplos
– Q20.
Q20 Encontre o maior e menor salário do
departamento de Produção
SELECT MAX(
MAX(salario),
l i ) MIN(salario)
MIN( l i )
FROM Empregado e, Departamento d
WHERE e.depto = d.coddep and
d.nome = ‘Produção’
Produção
– Q.21 Obtenha o número de empregados da
empresa
SELECT COUNT(*)
FROM empregado
SQL - DML: Exemplos
– Q.22
Q 22 Obter o número de salários distintos do
departamento de Contabilidade
SELECT COUNT(DISTINCT salario)
l i )
FROM empregado e, departamento d
WHERE (e.depto = d.coddep and
d.nome = ‘Contabilidade’)
Contabilidade )
– O que aconteceria se escrevêssemos
COUNT(salario) ao invés de
COUNT(DISTINCT salario)?
SQL - DML: Exemplos
– Q23.
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)
d.matricula)
>= 2
SQL - DML: Exemplos
• Cláusulas GROUP BY e HAVING
– Usadas para lidar com grupos
– Q24.
Q24 Para
P
cada
d departamento,
d
t
t obter
bt o código
ódi
do departamento, o número de empregados e
a média salarial
SELECT depto, COUNT(*), AVG(salario)
FROM Empregado
E
d
GROUP BY depto
ÖAs tuplas de empregados são separadas em
grupos (departamento) e as funções COUNT
e AVG são
ã aplicadas
li d a cada
d grupo
separadamente
SQL - DML: Exemplos
• Q25
Q25. Para cada projeto
projeto, obter o código do
projeto, seu nome e o número de
empregados que trabalham nele
SELECT codigop, nome, COUNT(
COUNT(*))
FROM Projeto p, Alocacao a
WHERE p.codproj = a.codigop
GROUP BY p
p.codproj,
codproj p
p.nome
nome
– O agrupamento e as funções são aplicadas
após a junção
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
trabalhando, obter o
código do projeto, nome do projeto e
número de empregados que trabalha
neste projeto
SQL - DML: Exemplos
SELECT p
p.codproj,
codproj p
p.nome,
nome COUNT(*)
FROM Projeto p, Alocacao a
WHERE p
p.codproj
codproj = a
a.codigop
codigop
GROUP BY p.codproj, p.nome
HAVING COUNT(*) > 2
SQL - DML: Exemplos
• Operadores
p
de comparação
p ç e aritméticos
• BETWEEN
– Sintaxe: expressão [NOT] BETWEEN
expressão AND expressão
– Ex.:
Ex : y BETWEEN x AND z equivale a x <= y
<= z
– Q27.
Q27 Selecione os nomes dos empregados
que ganham mais de 1000 e menos de 2000
reais
SELECT nome
FROM Empregado
WHERE salario BETWEEN 1000 AND 2000
SQL - DML: Exemplos
• LIKE
– Permite comparações
p ç
de substrings.
g Usa dois
caracteres reservados ‘%’ (substitui um
número arbitrário de caracteres)) e ‘_‘
(substitui um único caracter)
– Q.28
Q 28 Obter os nomes de empregados cujos
endereços estão em Natal, RN
SELECT nome
FROM empregado
WHERE endereco LIKE ‘%Natal,RN%’
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
e matricula = a
a.matricula
matricula and
a.codigop
= p.codproj and p.nome =
‘ProdutoX’
ProdutoX
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
projetos, listando
ordenado pelo nome do departamento
SELECT d
d.nome,
nome e
e.nome,
nome p
p.nome
nome
FROM departamento d, empregado e, projeto p
WHERE d
d.coddep
dd = e.depto
d t and
d e.matricula
ti l =
a.matricula and a.codigop =
p codproj
p.codproj
ORDER BY d.nome, e.nome
SQL - DML: Exemplos
• Ordenação
– A ordem default é ascendente (ASC) caso
queiramos ordem decrescente usamos DESC
ORDER BY d.nome
d nome DESC
DESC, e
e.nome
nome ASC
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’)
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
DEPTO_INFO
INFO
(nome character(15),
numemp integer,
)
totsal real);
INSERT INTO
DEPTO INFO(nome, numemp, totsal)
DEPTO_INFO(nome,
SELECT d.nome, COUNT(*), SUM(salario)
FROM Departamento d
d, Empregado e
WHERE d.coddep = e.depto
GROUP BY d.nome
d nome
Remoção de Dados
• O comando
d 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
p
ç vazia
Modificação de Dados
• O comando
d UPDATE
– Modifica o valor de atributos de uma ou mais
tuplas.
– Sintaxe
UPDATE tabela
ç
de valores
SET lista_atributos com atribuições
[
[WHERE
condição]
ç ]
– Obs.: omitir a cláusula WHERE implica que o
UPDATE deve ser aplicado
p
a todas as tuplas
p
da relação
Modificação de Dados
• O comando UPDATE
– Ex. Modifique
q o nome do Departamento
p
de
Computação para Departamento de
Informática
UPDATE Departamento
SET nome = ‘Informática’
WHERE nome = ‘Computação’
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.1
1
WHERE depto in (SELECT coddep
FROM Departamento
q
)
WHERE nome =‘Pequisa’)