BANCO DE DADOS - AULA 14

Prévia do material em texto

AULA Nº 14
BANCOS DE DADOS
Linguagem de consulta –
SQL Parte II
SQL
Esquema para o modelo Relacional do contexto didático: EMPRESA.
FUNCIONARIO(ident, nome, sobrenome, endereco, dtnasc, salario, sexo, 
 supident, dnumero)
DEPENDENTE(fident, nome, dt_nasc, sexo, relacionamento)
DEPARTAMENTO(numero, nome, gident, dtinicio)
LOCALIZACOES(dnumero, localizacao)
PROJETO(numero, nome, localizacao, dnumero)
TRABALHA_EM(pnumero, fident, horas)
Algumas simplificações nos nomes de variáveis foram realizadas para facilitar o uso delas nas consultas.
A chave primária, em cada relação, está sublinhada.
SQL
Estrutura básica de um consulta para extração de informação em SQL
SELECT < lista de atributos >
FROM < lista de relações/tabelas >
WHERE <condição>
em que:
• < lista de atributos >: é uma lista de nomes de atributos cujos valores serão 
recuperados pela consulta.
• < lista de relações/tabelas >: é uma lista de nomes de relações necessárias para 
encontrar a informação desejada na consulta.
• < condição >: é uma expressão condicional (booleana) que identifica quais tuplas 
das relações mencionadas devem ser consideradas na consulta.
SQL
SELECT dtnasc, endereco
FROM FUNCIONARIO
WHERE sobrenome = ’Brotas’;
Consulta 18: Recupere as datas de nascimento e os 
endereços dos funcionários que possuem o 
sobrenome ’Brotas’. 
SQL
SELECT ident
FROM FUNCIONARIO;
Consulta 19: Selecione os identificadores de todos os 
funcionários. 
SQL
SELECT FUNCIONARIO.nome, endereco
FROM FUNCIONARIO, DEPARTAMENTO
WHERE DEPARTAMENTO.nome = ’Engenharia’ AND
 numero = dnumero;
Consulta 20: Recupere os nomes e os endereços de 
todos os funcionários que trabalham para o 
departamento de engenharia. 
FROM FUNCIONARIO, DEPARTAMENTO
FUNCIONARIO
numer
o
nome ...
1 Informática ...
3 Engenharia ...
4 Recursos 
Humanos
...
DEPARTAMENTO
iden
t
nome ... enderec
o
... dnumer
o
116
3
Claudi
a
... Rua A ... 1
116
4
Jorge ... Rua B ... 3
116
5
Moaci
r
... Rua C ... 4
116
7
Caio ... Rua D ... 3
iden
t
FUNCIONARIO.
nome
..
.
endere
co
... dnumero numero DEPARTAMENTO.
nome
...
1163 Claudia ..
.
Rua A ... 1 1 Informática ...
1163 Claudia ..
.
Rua A ... 1 3 Engenharia ...
1163 Claudia ..
.
Rua A ... 1 4 Recursos Humanos ...
1164 Jorge ..
.
Rua B ... 3 1 Informática ...
1164 Jorge ..
.
RuaB ... 3 3 Engenharia ...
... ... ..
.
... ... ... ... ... ...
1167 Caio ..
.
Rua D ... 3 4 Recursos Humanos ...
WHERE DEPARTAMENTO.nome = ’Engenharia’ AND numero = dnumero;
SELECT FUNCIONARIO.nome, endereco
nome enderec
o
Jorge Rua B
Caio Rua D
iden
t
FUNCIONARIO.
nome
..
.
endere
co
... dnumero numero DEPARTAMENTO.
nome
...
1163 Claudia ..
.
Rua A ... 1 1 Informática ...
1163 Claudia ..
.
Rua A ... 1 3 Engenharia ...
1163 Claudia ..
.
Rua A ... 1 4 Recursos Humanos ...
1164 Jorge ..
.
Rua B ... 3 1 Informática ...
1164 Jorge ..
.
RuaB ... 3 3 Engenharia ...
... ... ..
.
... ... ... ... ... ...
1167 Caio ..
.
Rua D ... 3 4 Recursos Humanos ...
SQL
SELECT *
FROM FUNCIONARIO, DEPARTAMENTO
WHERE DEPARTAMENTO.nome = ’Engenharia’ AND
 numero = dnumero;
Consulta 21: Recupere todos os atributos de 
FUNCIONARIO e de DEPARTAMENTO, considerando 
que o funcionário trabalha para o departamento 
’Engenharia’. 
SQL
SELECT PROJETO.numero, PROJETO.dnumero, 
 FUNCIONARIO.nome, endereco, dtnasc
FROM PROJETO, DEPARTAMENTO, FUNCIONARIO
WHERE PROJETO.dnumero = DEPARTAMENTO.numero AND
 gident = ident AND PROJETO.localizacao = ’São Paulo’;
Consulta 22: Para cada projeto localizado em ’São 
Paulo’, liste o número do projeto, o número do 
departamento que controla o projeto, e o nome, 
endereço e data de nascimento do gerente deste 
departamento.
SQL
SELECT F.nome, S.nome
FROM FUNCIONARIO AS F, FUNCIONARIO AS S
WHERE F.supident = S.ident;
Consulta 23: Para cada funcionário, recupere seu 
nome e o nome do seu supervisor imediato. 
FUNCIONARIO
iden
t
nome ... supide
nt
dnumer
o
116
3
Claudi
a
... NULL 1
116
4
Jorge ... 1163 3
116
5
Moaci
r
... 1164 4
116
7
Caio ... 1163 3FUNCIONARIO AS F
iden
t
nome ... supide
nt
dnumer
o
116
3
Claudi
a
... NULL 1
116
4
Jorge ... 1163 3
116
5
Moaci
r
... 1164 4
116
7
Caio ... 1163 3
FUNCIONARIO AS S
iden
t
nome ... supide
nt
dnumer
o
116
3
Claudi
a
... NULL 1
116
4
Jorge ... 1163 3
116
5
Moaci
r
... 1164 4
116
7
Caio ... 1163 3
Papel de supervisionado Papel de supervisor
F
iden
t
nome ... supide
nt
dnumer
o
116
3
Claudi
a
... NULL 1
116
4
Jorge ... 1163 3
116
5
Moaci
r
... 1164 4
116
7
Caio ... 1163 3
S
iden
t
nome ... supide
nt
dnumer
o
116
3
Claudi
a
... NULL 1
116
4
Jorge ... 1163 3
116
5
Moaci
r
... 1164 4
116
7
Caio ... 1163 3
FROM FUNCIONARIO AS F, FUNCIONARIO AS S
F.ide
nt
F.nom
e
... F.supid
ent
F.dume
ro
S.ident S.nome ... S.supide
nt
S.dnume
ro
1163 Claudia ... NULL 1 1163 Claudia ... NULL 1
... ... ... ... ... ... ... ... ... ...
1167 Caio ... 1163 3 1163 Claudia ... NULL 1
1167 Caio ... 1163 3 1164 Jorge ... 1163 3
1167 Caio ... 1163 3 1165 Moacir ... 1164 4
1167 Caio ... 1163 3 1167 Caio ... 1163 3
F.ide
nt
F.nom
e
... F.supid
ent
F.dume
ro
S.ident S.nome ... S.supide
nt
S.dnume
ro
1163 Claudia ... NULL 1 1163 Claudia ... NULL 1
... ... ... ... ... ... ... ... ... ...
1167 Caio ... 1163 3 1163 Claudia ... NULL 1
1167 Caio ... 1163 3 1164 Jorge ... 1163 3
1167 Caio ... 1163 3 1165 Moacir ... 1164 4
1167 Caio ... 1163 3 1167 Caio ... 1163 3
WHERE F.supident = S.ident
SELECT F.nome, S.nome
F.nom
e
S.nome
Jorge Claudia
Moacir Jorge
Caio Claudia
SQL
SELECT ALL salario
FROM FUNCIONARIO;
Consulta 24: Recupere os salários de todos os funcionários.
SELECT DISTINCT salario
FROM FUNCIONARIO;
Consulta 25: Recupere os valores distintos de salários 
praticados na empresa.
SQL
Consulta 26: Faça uma lista de números de projetos 
que envolvem um funcionário cujo sobrenome é 
’Silva’. O envolvimento do funcionário pode ser como 
trabalhador ou como gerente do departamento que 
controla o projeto. 
( SELECT DISTINCT PROJETO.numero
FROM PROJETO, DEPARTAMENTO, FUNCIONARIO
WHERE PROJETO.dnumero = DEPARTAMENTO.numero AND 
 gident = ident AND sobrenome = ’Silva’)
UNION 
( SELECT DISTINCT PROJETO.numero
FROM PROJETO, TRABALHA_EM, FUNCIONARIO
WHERE PROJETO.dnumero = TRABALHA_EM.pnumero AND
 TRABALHA_EM.fident = ident AND sobrenome = ’Silva’);
Consulta 26: Faça uma lista de números de projetos que envolvem um 
funcionário cujo sobrenome é ’Silva’. O envolvimento do funcionário pode 
ser como trabalhador ou como gerente do departamento que controla o 
projeto. 
( SELECT DISTINCT PROJETO.numero
FROM PROJETO, DEPARTAMENTO, FUNCIONARIO
WHERE PROJETO.dnumero = DEPARTAMENTO.numero AND 
 gident = ident AND sobrenome = ’Silva’)
UNION 
( SELECT DISTINCT PROJETO.numero
FROM PROJETO, TRABALHA_EM, FUNCIONARIO
WHERE PROJETO.dnumero = TRABALHA_EM.pnumero AND
 TRABALHA_EM.fident = ident AND sobrenome = ’Silva’);
Tuplas em duplicidade serão eliminadas do resultado. 
Use [UNION | EXCEPT | INTERSECT ] ALL se for 
necessário manter as duplicatas.
SQL
SELECT nome, sobrenome
FROM FUNCIONARIO 
WHERE endereco LIKE ’%SP%’;
Consulta 27: Recupere todos os funcionários cujos 
endereços são em SP. 
SQL
SELECT nome, sobrenome
FROM FUNCIONARIO 
WHERE dtnasc LIKE ’_ _ / _ _ / 195 _’;
Consulta 28: Recupere todos os funcionários que 
nasceram nos anos de 1950. 
SQL
SELECT D.nome, F.nome, P.numero
FROM DEPARTAMENTO AS D, FUNCIONARIO AS F,
 TRABALHA_EM AS T, PROJETO AS P
WHERE D.numero = F.dnumero AND F.ident = T.fident 
 AND T.pnumero = P.numero
ORDER BY D.nome ASC, F.nome ASC;
Consulta 29: Recupere a lista de funcionários e de 
projetos nos quais eles trabalham, ordenada por 
departamento. Dentro de departamento, ordene por 
nome do funcionário. 
SQL
SELECT nome
FROM FUNCIONARIOWHERE supident IS NULL;
Consulta 30: Recupere os nomes de todos os 
funcionarios que não possuem supervisores
SQL
Exemplos de uso de JOIN
SELECT F.nome, endereco
FROM (FUNCIONARIO JOIN DEPARTAMENTO ON dnumero = numero)
WHERE DEPARTAMENTO.nome = ’Engenharia’;
SELECT F.nome, S.nome
FROM (FUNCIONARIO AS F JOIN FUNCIONARIO AS S ON 
 F.ident = S.supident );
SQL
Exemplos de funções agregadas
SELECT SUM(salario), MAX(salario), MIN(salario), AVG(salario)
FROM FUNCIONARIO;
SELECT nome,
FROM FUNCIONARIO 
WHERE ( SELECT COUNT(*)
 FROM DEPENDENTE
 WHERE ident = fident ) >= 2;
Subconsulta – próxima aula !!!
SQL
Exemplo: GROUP BY e HAVING 
SELECT Dnome, COUNT(*)
FROM DEPARTAMENTO AS D, FUNCIONARIO AS F 
WHERE D.numero = F.dnumero AND salario > 40.000 
GROUP BY Dnome
HAVING COUNT(*) > 5;
Linguagem de consulta –
SQL Parte II
Estes slides estão baseados na bibliografia:
•Elmasri, Ramez; Navathe, Shamkant B. Sistemas 
de Banco de Dados. Pearson, 6a edição, 808p., 
2011.
FIM!
	Slide 1
	SQL
	SQL
	SQL
	SQL
	SQL
	Slide 7
	Slide 8
	SQL
	SQL
	SQL
	Slide 12
	Slide 13
	Slide 14
	SQL
	SQL
	Slide 17
	Slide 18
	SQL
	SQL
	SQL
	SQL
	SQL
	SQL
	SQL
	Linguagem de consulta – SQL Parte II