Questão 1

Create Table Pessoa(
pID int primary key, 
pNome varchar(30) not null, 
rua varchar(50), 
pCidade varchar (20));

Create Table Empresa(
eID int primary key, 
eNome varchar(20) not null unique, 
eCidade varchar(20));

Create Table Trabalha(
pID int references Pessoa(pID) on delete cascade, 
eID int references Empresa(eID), 
dataInicio date, 
salario numeric(7,2),
primary key(pID,eID),
check (dataInicio < '2018-01-01' or salario < 15000)
);


Questão 2

-- a) Listar o nome e o salário dos trabalhadores que possuem Gates no sobrenome e que ganham um salário maior que R$15.000,00 em algum de seus trabalhos.

select pNome, salario from Pessoa natural join Trabalha where pNome like '%  Gates%' and salario > 15000;

--ou

select pNome, salario from Pessoa P, Trabalha T where P.pID = T.pID and pNome like '%  Gates%' and salario > 15000;

-- b) Listar os identificadores das empresas que possuem trabalhadores que moram em Osasco e que também possuem trabalhadores que moram em Guarulhos.

(select eID from Pessoa natural join Trabalha where pCidade = 'Osasco')
intersect
(select eID from Pessoa natural join Trabalha where pCidade = 'Guarulhos')

--ou

select T1.eID from Pessoa P1, Trabalha T1, Pessoa P2, Trabalha T2 
where P1.pID = T1.pID and P2.pID = T2.pID and T1.eID = T2.eID and
      P1.pCidade = 'Osasco' and P2.pCidade = 'Guarulhos';

-- c) Liste o nome de cada pessoa que trabalha em todas as empresas cadastradas no BD.

select pNome from Pessoa p where not exists 
(select eID from Empresa where eID not in 
  (select eID from Trabalha where pID = p.pID));

-- d) Obter o nome e a quantidade de trabalhadores de cada empresa que possui pelo menos 30 trabalhadores.

select eNome, quantidade
from Empresa natural join 
  (select eID, count(pID) as quantidade from Trabalha
   group by eID having count(pID) >= 30) as T; 

-- Obs.: O "as T" é necessário porque o PostgreSQL obriga que subconsultas que apareçam na cláusula FROM sejam apelidadas.

-- e) Para cada pessoa cadastrada no BD, exiba o nome e o seu maior salário (obtido entre os salários que ela ganha em todas as empresas em que trabalha). Uma pessoa tem que aparecer nessa listagem mesmo se ela não trabalhar em empresa alguma (nesse caso, seu maior salário deve ser mostrado como nulo).

select pNome, maiorSalario 
from Pessoa natural left outer join
 (select pID, max(salario) as maiorSalario 
   from Trabalha group by pID) as T;

-- Obs.: O "as T" é necessário porque o PostgreSQL obriga que subconsultas que apareçam na cláusula FROM sejam apelidadas. 

-- f) Liste as triplas (pNome1, pNome2, eID) tais que pNome1 e pNome2 correspondem a nomes de pessoas que começaram a trabalhar na empresa identificada por eID em uma mesma data, mas que a pessoa pNome1 ganha menos que a pessoa pNome2 nessa empresa.

select p1.pNome, p2.pNome, t1.eID 
from Pessoa p1, Pessoa p2, Trabalha t1, Trabalha t2
where p1.pID = t1.pID and p2.pID = t2.pID and 
      t1.eID = t2.eID and t1.dataInicio = t2.dataInicio and
      t1.salario < t2.salario;

-- g) A empresa IMEstat demitiu todos os seus funcionários contratados a partir de 15/08/2019. Remova de Trabalha as tuplas correspondentes.

delete from Trabalha
where dataInicio >= '2019-08-15' and
      eID in (select eID from Empresa where eNome = 'IMEstat');

-- h) Aumentar em 5% o salário daqueles que recebem um salário menor que a média de salários de sua empresa.

update Trabalha T set salario = salario * 1.05 
where salario < (select AVG(salario) from Trabalha where eID = T.eID) 


Questão 3

-- a) Usando o esquema relacional do exercício 1, escreva um comando em SQL para criar uma visão chamada  GanhaBemEmSP que contenha o ID da pessoa, o ID da empresa e o salário das pessoas que trabalham em empresas da cidade de São Paulo e que ganham mais do que R$10.000,00. Defina a visão de forma que ela seja atualizável, ou seja, possa sofrer inserções, alterações e remoções de tuplas. 

create view GanhaBemEmSP(pID, eID, salario) as (
	select pID, eID, salario from Trabalha 
	where eID in (select eID from Empresa where eCidade = 'São Paulo')
	      and salario > 10000
); 


b) Dê um exemplo de uma inserção e de uma alteração "com anomalias" na visão GanhaBemEmSP - ou seja, um comando INSERT e um comando UPDATE que sejam aceitos como válidos, causem uma modificação no BD, mas cuja a modificação realizada por eles não seja refletida na visão em questão. Justifique a sua resposta.

-- Comando INSERT
-- Assumindo que 123 e 321 são identificadores válidos de pessoa e empresa, respectivamente: 

insert into GanhaBemEmSP(pID, eID, salario) values (123, 321, 5000);

-- O comando acima resultará na inclusão de uma tupla na relação Trabalha, mas essa tupla não aparecerá na visão GanhaBemEmSP, já que o valor para salario nela é inferior a 10.000,00.

Um outro exemplo:

-- Comando UPDATE

update GanhaBemEmSP set salario = 5000;

-- Esse comando fará com que todas as tuplas na relação Trabalha que se refiram a trabalhos em empresas da cidade de São Paulo e com salário maior que R$10.000,00 tenham o valor do atributo salario reduzido para 5.000. Com isso, a visão GanhaBemEmSP ficará vazia. Portanto, da perspectiva de GanhaBemEmSP, o comando update acima terá o efeito de um delete.