SQL
Dados esquemas relaconais das tabelas
abaixo:
•EMPREGADO (numemp, nome, numsec, cargo,
chefe, Salário, Comissão)
•SEÇÃO (numsec, nome, cidade)
Declarar as tabelas acima, usando o DDL do
Oracle e criar as respectivas chaves
primárias e estrangeiras
Resolução: Criação das tabelas
create table seção
(NumSec varchar2(2),
nome Varchar2(25),
cidade varchar2(20));
create table empregado
(numemp varchar2(4),
nome varchar2(20),
numsec varchar2(2),
cargo varchar2(15),
chefe varchar2(4),
Salário number(7,2),
Comissão number (7,2));
Resolução: Declaração das chaves Primárias e
Estrangeiras
Alter table seção add constraint pk_seção
primary key (numsec);
Alter table empregado add constraint
pk_empregado primary key (numemp);
Alter table empregado add constraint
fk_seção_empregado foreign key (numsec)
references seção (numsec);
Inserir os dados abaixo na Tabela Seção
10
20
30
40
50
60
70
Fábrica
Comercial
Marketing
Planejamento
Administração
Informática
Recursos Humanos
Vinhedo
Vinhedo
São Paulo
Jundiaí
Vinhedo
São Paulo
Jundiaí
Resolução: Inserção de Dados na Tabela
SEÇÃO
Insert into seção values (‘10’,’Fábrica’,’Vinhedo’);
Insert into seção values (‘20’,’Comercial’,’Vinhedo’);
Insert into seção values (‘30’,’Marketing’,’São Paulo’);
Insert into seção values (‘40’,’Planejamento’,’Jundiaí’);
Insert into seção values (‘50’,’Administração’,’Vinhedo’);
Insert into seção values (‘60’,’Informática’,’São Paulo’);
Insert into seção values (‘70’,’Recursos Humanos’,’Jundiaí’);
Inserir os dados abaixo na Tabela
Empregado
NumEmp Nome NumSec Cargo
1
2
3
4
5
6
Ana
Paulo
Álvaro
Antonio
Susana
Cláudio
10
70
50
10
20
60
7
Dilminha 50
Chefe Salário Comissão
Programador
Engenheiro
Administrador
Engenheiro
Administrador
Comercial
Presidente
3
1
7
3
4
3000
1500
2500
1450
2750
1000
10
40
0
20
30
50
9000
0
Resolução: Inserção de Dados EMPREGADO
insert into empregado values
( ‘1’,’Ana’,’10’,’Programador’,’3’,3000,10);
Insert into empregado values
(‘2’,’Paulo’,’70’,’Engenheiro’,’1’,1500,40);
Insert into empregado values
(‘3’,’Álvaro’,’50’,’Administrador’,’7’,2500,0);
Insert into empregado values
(‘4’,’Antonio’,’10’,’Engenheiro’,’3’,1450,20);
Insert into empregado values
(‘5’,’Susana’,’20’,’Administrador’,’’,2750,30);
Insert into empregado values
(‘6’,’Cláudio’,’60’,’Comercial’,’4’,1000,50);
Insert into empregado values
(‘7’,’Dilminha’,’50’,’Presidente’,’’,9000,0);
ESQUEMA DAS TABELAS:
EMPREGADO (numemp, nome, numsec, cargo, chefe, salário, comissão)
SEÇÂO (numsec, nome, cidade)
Mostre o nome e a localização
(cidade) da seção 70
SELECT Nome, Cidade FROM
Seção WHERE NumSec=70;
ESQUEMA DAS TABELAS:
EMPREGADO (numemp, nome, numsec, cargo, chefe, salário, comissão)
SEÇÂO (numsec, nome, cidade)
Selecione o cargo, salário e
comissão dos empregados cuja
comissão excedeu o salário.
SELECT Nome, Cargo, Salário,
Comissão FROM Empregado
WHERE Comissão>Salário;
EMPREGADO (numemp, nome, numsec, cargo, chefe, salário, comissão)
SEÇÂO (numsec, nome, cidade)
Mostre os nomes de todas as
seções que ficam em
Vinhedo.
SELECT Nome FROM Secção
WHERE Cidade=’Vinhedo’;
EMPREGADO (numemp, nome, numsec, cargo, chefe, salário, comissão)
SEÇÂO (numsec, nome, cidade)
Selecione todos os cargos de
funcionários (sem repetição).
SELECT DISTINCT Cargo
FROM Empregado;
EMPREGADO (numemp, nome, numsec, cargo, chefe, salário, comissão)
SEÇÂO (numsec, nome, cidade)
Mostre o nome, posto e número
de secção dos empregados que
trabalham nas secções 20, 30 e
40
SELECT Nome, Posto,
Secção FROM Empregado
WHERE Numsec IN (20, 30,
40);
EMPREGADO (numemp, nome, numsec, cargo, chefe, salário, comissão)
SEÇÂO (numsec, nome, cidade)
Calcule e mostre quanto é que a
companhia gasta com engenheiros
e programadores em cada ano.
SELECT
SUM (Salário+Comissão) * 12
FROM Empregado
WHERE Cargo IN (‘Engenheiro’,
‘Programador’);
EMPREGADO (numemp, nome, numsec, cargo, chefe, salário, comissão)
SEÇÂO (numsec, nome, cidade)
Mostre o número, nome e cargo de
todos os empregados, ordenando
pelo número do empregado.
SELECT NumEmp, Nome,
Cargo FROM Empregado
ORDER BY NumEmp;
EMPREGADO (numemp, nome, numsec, cargo, chefe, salário, comissão)
SEÇÂO (numsec, nome, cidade)
Para cada secção, mostre o número de
secção, total de empregados e total dos
salários completos.
SELECT numsec, COUNT(*),
SUM(Salário+Comissão)
FROM Empregado GROUP
BY numsec;
EMPREGADO (numemp, nome, numsec, cargo, chefe, salário, comissão)
SEÇÂO (numsec, nome, cidade)
Mostre o número da seção, cargo,
número de empregados e salário
médio para cada cargo, por seção,
para as seções 10, 20 e 30.
SELECT numsec, Cargo,
COUNT(*), AVG(Salário) FROM
Empregado WHERE numsec IN
(20, 30, 40) GROUP BY numsec,
Cargo;
EMPREGADO (numemp, nome, numsec, cargo, chefe, salário, comissão)
SEÇÂO (numsec, nome, cidade)
Para cada seção, mostre quanto
se paga por ano a Engenheiros.
Mostre também o número da
seção e o total de Engenheiros.
SELECT numsec, COUNT(*),
SUM(Salário+Comissão)*12
FROM Empregado WHERE
Cargo=’Engenheiro’ GROUP BY
numsec;
EMPREGADO (numemp, nome, numsec, cargo, chefe, salário,
comissão)
SEÇÂO (numsec, nome, cidade)
Mostre qual o salário médio de
todos os cargos, exceto o de
presidente. .
SELECT AVG(Salário) FROM
Empregado WHERE
Cargo!=’Presidente’ GROUP
BY Cargo;
EMPREGADO (numemp, nome, numsec, cargo, chefe, salário,
comissão)
SEÇÂO (numsec, nome, cidade)
Mostre o nome e o cargo do
empregado que ganha o maior
salário total (Salário + Comissão)
SELECT Nome, Cargo FROM
Empregado WHERE
Salário+Comissão=(SELECT
MAX(Salário+Comissão) FROM
Empregado);
EMPREGADO (numemp, nome, numsec, cargo, chefe, salário,
comissão)
SEÇÂO (numsec, nome, cidade)
Mostre o número da seção, nome, cargo e salário
total do empregado com o segundo maior salário
total.
SELECT numsec, Nome, Cargo,
Salário+Comissão FROM Empregado WHERE
Salário+Comissão=(SELECT
MAX(Salário+Comissão) FROM Empregado
WHERE NumEmp NOT IN (SELECT NumEmp
FROM Empregado WHERE
Salário+Comissão=(SELECT
MAX(Salário+Comissão) FROM Empregado)));
EMPREGADO (numemp, nome, numsec, cargo, chefe, salário,
comissão)
SEÇÂO (numsec, nome, cidade)
Liste o nome, cargo e salário dos
empregados não pertencentes à seção
10, que têm o mesmo salário e cargo
dos empregados da seção 10.
SELECT sn10.Nome, sn10.Cargo,
sn10.Salário FROM Empregado sn10,
Empregado s10 WHERE
sn10.numsec!=10 AND
sn10.Salário=s10.Salário
AND sn10.Cargo=s10.Cargo
AND s10.numsec=10;
EMPREGADO (numemp, nome, numsec, cargo, chefe, salário,
comissão)
SEÇÂO (numsec, nome, cidade)
•
•
•
•
•
Calcule o número médio de empregados
por secção.
Mostre qual o salário médio de todos os
postos, exceto o de presidente.
Mostre o nome e o posto do empregado
que ganha o maior salário total.
Liste o nome, posto e salário dos
empregados não pertencentes à secção
10, que têm o mesmo salário e posto dos
empregados da secção 10.
Mostre o número da secção, nome,
posto e salário total do empregado com
o segundo maior salário total.
•
SELECT COUNT(*)/(SELECT COUNT(*) FROM
Seccao) FROM Empregado;
•
SELECT AVG(Salario) FROM Empregado WHERE
Posto!=’Presidente’ GROUP BY Posto;
•
SELECT Nome, Posto FROM Empregado WHERE
Salario+Comissao=(SELECT MAX(Salario+Comissao)
FROM Empregado);
•
SELECT sn10.Nome, sn10.Posto, sn10.Salario FROM
Empregado sn10, Empregado s10 WHERE
sn10.Secção!=10 AND sn10.Salario=s10.Salario
AND sn10.Posto=s10.Posto
AND s10.Seccao=10;
•
SELECT Secção, Nome, Posto, Salario+Comissao
FROM Empregado WHERE
Salario+Comissao=(SELECT MAX(Salario+Comissao)
FROM Empregado WHERE NumEmp NOT IN
(SELECT NumEmp FROM Empregado WHERE
Salario+Comissao=(SELECT MAX(Salario+Comissao)
FROM Empregado)));