lista B SGBD

CREATE TABLE funcionario(
cod NUMBER(5),
nome VARCHAR2(100),
mae VARCHAR2(100),
pai NUMBER(5),
dt_nasc DATE,
rg VARCHAR2(20),
cpf NUMBER(5),
sexo CHAR,
rua VARCHAR2(100),
cidade VARCHAR2(100),

CONSTRAINT pkfunc PRIMARY KEY (cod),
CONSTRAINT ukfuncid UNIQUE (nome,mae,dt_nasc),
CONSTRAINT ukfunccpf UNIQUE (cpf),
CONSTRAINT ukfuncrg UNIQUE (rg),
CONSTRAINT fkfuncpai FOREIGN KEY (pai) REFERENCES funcionario(cod)
);


CREATE TABLE empresa(
cod NUMBER(5),
nome VARCHAR2(100),
rua VARCHAR2(200),
cidade VARCHAR2(100),

CONSTRAINT pkemp PRIMARY KEY(cod)
);

CREATE TABLE trabalha(
codf NUMBER(5),
code NUMBER(5),
salario NUMBER(5),

CONSTRAINT pktrab PRIMARY KEY(codf,code),
CONSTRAINT fktrabcodf FOREIGN KEY(codf) REFERENCES funcionario(cod),
CONSTRAINT fktrabcode FOREIGN KEY(code) REFERENCES empresa(cod)
);

CREATE TABLE gerente(

codf NUMBER(5),
codg NUMBER(5),

CONSTRAINT pkger PRIMARY KEY(codf,codg),
CONSTRAINT fkgercodf FOREIGN KEY(codf) REFERENCES funcionario(cod),
CONSTRAINT fkgercodg FOREIGN KEY(codg) REFERENCES funcionario(cod)
);



INSERT INTO funcionario VALUES (1,'joão','eu',NULL,'12-12-1992','13165',4645,'m','eu','pinhais');
INSERT INTO funcionario VALUES (2,'shhhh','juliana',1,'12-12-4212','5484554',5465,'m','eu','araucária');
INSERT INTO funcionario VALUES (3,'pedro','fabiola',1,'12-12-1252','1245868',47455,'m','eu','araucária');
INSERT INTO funcionario VALUES (4,'pamela','ana',2,'12-12-1262','12775445',57895,'f','eu','curitiba');
INSERT INTO funcionario VALUES (5,'vera','vera',3,'12-12-1217','8784545',455,'f','eu','curitiba');
INSERT INTO funcionario VALUES (6,'rodrigo','vera',3,'12-12-1217','878454',5385,'m','eu','pinhais');



INSERT INTO empresa VALUES (1,'pomarola','empresa do seu joão','araucaria')
INSERT INTO empresa VALUES (2,'renato','empresa do seu joão','renato')
INSERT INTO empresa VALUES (3,'renatinho','empresa do seu joão','pinhais')
INSERT INTO empresa VALUES (4,'azul','empresa do seu joão','pinhais')
INSERT INTO empresa VALUES (5,'tam','do seu joão','curitiba')
INSERT INTO empresa VALUES (6,'volvo','aquiii','araucaria')


INSERT INTO trabalha VALUES (2,1,7552);
INSERT INTO trabalha VALUES (3,1,4555);
INSERT INTO trabalha VALUES (4,2,200);
INSERT INTO trabalha VALUES (1,1,4558);
INSERT INTO trabalha VALUES (1,3,7552);
INSERT INTO trabalha VALUES (4,5,6);
INSERT INTO trabalha VALUES (5,6,230);
INSERT INTO trabalha VALUES (2,5,400);
INSERT INTO trabalha VALUES (3,5,13997);
INSERT INTO trabalha VALUES (2,6,400);
INSERT INTO trabalha VALUES (3,6,13997);



INSERT INTO gerente VALUES (1,2);
INSERT INTO gerente VALUES (1,3);
INSERT INTO gerente VALUES (2,4);

--1
SELECT f.nome, t.salario
FROM funcionario f
INNER JOIN
trabalha t ON(f.cod = t.codf)
WHERE t.salario > (SELECT AVG(salario) FROM trabalha)


--2
SELECT empresa.nome, COUNT(*)
FROM empresa, trabalha
WHERE trabalha.code = empresa.cod
GROUP BY empresa.cod


--3
SELECT empresa.nome, COUNT(*), funcionario.sexo
FROM empresa, trabalha, funcionario
WHERE trabalha.code = empresa.cod AND funcionario.cod = trabalha.codf AND funcionario.cidade IN ('pinhais','curitiba')
GROUP BY empresa.cod , funcionario.sexo

--4
SELECT f.nome, t.salario, t.salario AS atual, (t.salario*1.1) AS novo
FROM empresa e, trabalha t, funcionario f
WHERE e.cod = t.code AND f.cod = t.codf

--5
SELECT funcionario.nome, trabalha.code
FROM funcionario LEFT JOIN trabalha ON (funcionario.cod = trabalha.codf)


--6
SELECT funcionario.nome, trabalha.code
FROM funcionario, trabalha
WHERE funcionario.cod = trabalha.code


--7 Encontre a companhia que tenha a menor folha de pagamento e a que tenha a maior folha pagamento.
SELECT SUM(salario), code
FROM trabalha
GROUP BY code
HAVING SUM(salario) = (SELECT MAX(SUM(salario)) FROM trabalha GROUP BY code)
UNION
SELECT SUM(salario), code
FROM trabalha
GROUP BY code
HAVING SUM(salario) = (SELECT MIN(SUM(salario)) FROM trabalha GROUP BY code)

--8
SELECT AVG(salario), code
FROM trabalha
GROUP BY code
HAVING AVG(salario) = (SELECT MAX(AVG(salario)) FROM trabalha GROUP BY code)

--9
SELECT code, COUNT(codf) AS numero
FROM trabalha
GROUP BY code
HAVING COUNT(salario) = (SELECT MAX(COUNT(codf)) FROM trabalha GROUP BY code)

--10
SELECT code, AVG(salario)
FROM trabalha
GROUP BY code
HAVING AVG(salario) > (SELECT AVG(salario) FROM trabalha, empresa WHERE empresa.cod = trabalha.code AND empresa.nome = 'volvo')

--11
SELECT funcionario.cidade, empresa.cod, empresa.nome
FROM funcionario, empresa , trabalha
WHERE empresa.cod = trabalha.codE AND funcionario.cod = trabalha.codf AND empresa.nome = 'volvo'


--12
SELECT funcionario.cidade, funcionario.nome, empresa.cod, empresa.nome, trabalha.salario
FROM funcionario, empresa , trabalha
WHERE empresa.cod = trabalha.codE AND funcionario.cod = trabalha.codf AND empresa.nome = 'volvo' AND trabalha.salario > 10000 AND trabalha.salario < 20000


--13
SELECT funcionario.cidade, funcionario.nome, empresa.cod, empresa.nome, empresa.cidade
FROM funcionario, empresa, trabalha
WHERE empresa.cod = trabalha.codE AND funcionario.cod = trabalha.codf AND empresa.cidade = funcionario.cidade


-- 14
SELECT f.cidade, f.nome, f.rua, g.cidade, g.nome, g.rua
FROM funcionario f, funcionario g, gerente
WHERE f.cod = gerente.codf AND g.cod = gerente.codg AND f.rua = g.rua AND f.cidade = g.cidade




--15
SELECT funcionario.cidade, funcionario.nome, empresa.cod, empresa.nome
FROM funcionario, empresa, trabalha
WHERE empresa.cod = trabalha.codE AND funcionario.cod = trabalha.codf
MINUS
SELECT funcionario.cidade, funcionario.nome, empresa.cod, empresa.nome
FROM funcionario, empresa, trabalha
WHERE empresa.cod = trabalha.codE AND funcionario.cod = trabalha.codf AND empresa.nome = 'volvo'



--16
SELECT funcionario.cidade, funcionario.nome, empresa.cod, empresa.nome, trabalha.salario
FROM funcionario, empresa, trabalha
WHERE empresa.cod = trabalha.codE AND funcionario.cod = trabalha.codf AND (SELECT AVG(trabalha.salario) FROM trabalha,empresa WHERE trabalha.code = empresa.cod AND empresa.nome = 'volvo') < trabalha.salario



--17
SELECT a.cod, a.nome, a.cidade
FROM empresa a
WHERE a.cidade = (SELECT cidade FROM empresa WHERE nome='volvo')


--18
SELECT f.nome
FROM funcionario f, trabalha
WHERE f.cod = trabalha.codf AND salario > (SELECT AVG(salario) FROM TRABALHA WHERE trabalha.codf = f.cod)


--19
SELECT AVG(salario), code
FROM trabalha
GROUP BY code
HAVING AVG(salario) = (SELECT MAX(AVG(salario)) FROM trabalha GROUP BY code)
UNION
SELECT AVG(salario), code
FROM trabalha
GROUP BY code
HAVING AVG(salario) = (SELECT MIN(AVG(salario)) FROM trabalha GROUP BY code)




--20
SELECT e.cod
FROM empresa e, trabalha t
WHERE e.cod = t.code
GROUP BY e.cod
HAVING COUNT(code) = (SELECT MAX(COUNT(code)) FROM trabalha GROUP BY code)



--21
SELECT SUM(salario), code
FROM trabalha
GROUP BY code
HAVING SUM(salario) = (SELECT MAX(SUM(salario)) FROM trabalha GROUP BY code)
UNION
SELECT SUM(salario), code
FROM trabalha
GROUP BY code
HAVING SUM(salario) = (SELECT MIN(SUM(salario)) FROM trabalha GROUP BY code)



--22
SELECT code, AVG(salario)
FROM trabalha
GROUP BY code
HAVING AVG(salario) > (SELECT AVG(salario) FROM trabalha, empresa WHERE empresa.cod = trabalha.code AND empresa.nome = 'volvo')


--23
UPDATE(SELECT trabalha.salario FROM trabalha,empresa WHERE trabalha.code=empresa.cod AND empresa.nome='volvo') SET salario = salario*1.1

--24
UPDATE(SELECT trabalha.salario FROM trabalha,funcionario WHERE trabalha.codf=funcionario.cod AND funcionario.cidade='pinhais') SET salario = salario*1.1

--25
UPDATE
(SELECT trabalha.salario AS salario
FROM trabalha,empresa
WHERE empresa.cod = trabalha.code AND empresa.nome='volvo')
SET salario =
CASE
    WHEN salario > 9000 THEN salario*1.03
    ELSE salario*1.1
END


--26
CREATE TABLE backup AS
SELECT funcionario.nome AS nome_empregado, funcionario.cod AS codigo, funcionario.cidade AS cidade_emp FROM funcionario,trabalha, empresa WHERE trabalha.codf = funcionario.cod AND trabalha.code = empresa.cod AND empresa.nome = 'renatinho'

--27
SELECT MIN(funcionario.dt_nasc), empresa.cod
FROM empresa,funcionario,trabalha
WHERE funcionario.cod = trabalha.codf AND empresa.cod = trabalha.code
GROUP BY empresa.cod;

--28
SELECT funcionario.nome, funcionario.dt_nasc, FLOOR((SYSDATE - dt_nasc)/365) AS idade
FROM funcionario
WHERE (SYSDATE - dt_nasc)/365 > 24 AND (SYSDATE - dt_nasc)/365 < 31;

--29
INNER JOIN é uma operação que faz uma ligação cartesiana em duas tabelas, e retorna todas linhas cujos atributos retornem verdadeira a condição determinada na sintaxe DO JOIN.
LEFT OUTER JOIN é uma operação que faz o mesmo que INNER JOIN, mas retornando todos os valores da coluna esquerda pelo menos uma vez, independente alguma combinação que os inclua tenha sido permitida pela condição da sintaxe ou não.

--30
SELECT AVG(trabalha.salario), empresa.cod
FROM empresa,trabalha
WHERE empresa.cod = trabalha.code
GROUP BY empresa.cod
HAVING AVG(trabalha.salario) > 1666 AND AVG(trabalha.salario) < 2501