Notes

CREATE VIEW vAltoEscalao AS
SELECT rg, nome, salario
FROM funcionario
WHERE salario > 1000;

SELECT * FROM vAltoEscalao;

-- 1
CREATE VIEW vMedicoPacienteConsulta AS
SELECT p.nome AS pnome, m.nome AS mnome, c.data
FROM medico m JOIN consulta c ON m.crm = c.crm
JOIN paciente p ON p.rg = c.rg;

SELECT * FROM vMedicoPacienteConsulta;


-- 2
CREATE VIEW vMedicoSemConsulta AS
SELECT m.nome
FROM medico m
WHERE m.numeroA NOT IN (SELECT numeroA FROM ambulatorio);

SELECT * FROM vMedicoSemConsulta;


-- 3
CREATE VIEW vFuncionarioNuncaConsulta AS
SELECT f.* FROM funcionario f
WHERE f.rg NOT IN (SELECT rg FROM consulta);

SELECT * FROM vFuncionarioNuncaConsulta;


--
CREATE USER dba_zarabatana PASSWORD 'zarabatana';
CREATE USER medico_zarabatana PASSWORD 'zarabatana';
CREATE USER estagiario_zarabatana PASSWORD 'zarabatana';
CREATE USER secretaria_zarabatana PASSWORD 'zarabatana';


-- 4
GRANT SELECT ON vMedicoPacienteConsulta TO estagiario_zarabatana;


-- 5
GRANT INSERT ON funcionario TO estagiario_zarabatana;


-- 6
GRANT INSERT, DELETE, UPDATE ON paciente TO medico_zarabatana;


-- 7
GRANT SELECT (salario), UPDATE (salario) ON funcionario TO dba_zarabatana;


-- 8
CREATE VIEW vMedicoQuartoAndar AS