SQL Queries and Database Triggers Examples

Posted on Jan 4, 2025 in Computers

SQL Queries Examples

Page 22 Exercises

  1. Exercise 5

    Buscar o nome e a idade em meses dos pacientes:

    SELECT nome, (idade*12) as "Idade em meses" FROM Pacientes;

  2. Exercise 7

    Qual o menor e o maior salário dos funcionários de Florianópolis?

    SELECT MIN(salario), MAX(salario) FROM Funcionarios WHERE cidade='Florianopolis';

  3. Exercise 11

    Qual a média de idade dos médicos e o total de ambulatórios atendidos por eles?

    SELECT AVG(idade), count(distinct nroa) FROM Medicos;

  4. Exercise 12

    Buscar o código, o nome e o salário líquidos dos funcionários. O salário líquido é obtido pela diferença entre o salário cadastrado menos 20% deste mesmo salário:

    SELECT codf, nome, (salario*0.8) AS "Salário Líquido" FROM Funcionarios;

  5. Exercise 15

    Buscar o nome e CPF dos funcionários que não possuam a sequência “00000” em seus CPFs:

    SELECT nome, cpf FROM Funcionarios WHERE cpf NOT LIKE '%00000%';

  6. Exercise 16

    Buscar os códigos e nomes dos pacientes com mais de 25 anos que estão com tendinite, fratura, gripe e sarampo.

    SELECT codp, nome FROM Pacientes WHERE idade>25 AND doenca IN('tendinite', 'fratura', 'gripe', 'sarampo');

Page 31 Exercises

  1. Exercise 1

    Buscar nome e cpf dos médicos que também são pacientes do hospital:

    SELECT m.nome, m.cpf FROM Medicos m JOIN Pacientes p ON m.cpf=p.cpf;

    OU

    SELECT m.nome, m.cpf FROM Medicos m, Pacientes p WHERE m.cpf=p.cpf;

Database Views, Permissions and Triggers

  1. Permissions

    1.1) GRANT INSERT, UPDATE, DELETE, SELECT ON Pacientes TO médico;

    1.2) CREATE VIEW vEstagiario AS SELECT m.nome, p.nome, Data FROM Medicos m JOIN Consultas c ON m.codm=c.codm JOIN Pacientes p ON c.codp = p.codp;

    GRANT SELECT ON vEstagiario TO estagiário;

    1.3) CREATE VIEW vMedicos4 AS SELECT m.* FROM Medicos m JOIN Ambulatorios a ON a.nroa = m.nroa WHERE a.andar = 4;

    GRANT INSERT, UPDATE, DELETE, SELECT ON vMedicos4 TO secretária;

    1.4) CREATE ROLE QUERY;

    GRANT SELECT ON Pacientes, Medicos TO QUERY;

    GRANT QUERY TO U1, U2;

  2. Triggers

    2.1) CREATE TRIGGER fReduçãoSalario BEFORE UPDATE OF salario ON Funcionarios FOR EACH ROW EXECUTE PROCEDURE verificaSalario();

    CREATE FUNCTION verificaSalario() RETURNS trigger as $$
  BEGIN
    IF new.salario

    2.2) CREATE TRIGGER cpfDuplicado BEFORE INSERT ON Pacientes FOR EACH ROW EXECUTE PROCEDURE verificaCPF();

    CREATE FUNCTION verificaCPF() RETURNS trigger as $$
DECLARE
  linhas_afetadas integer DEFAULT 0;
BEGIN
  EXECUTE SELECT * FROM Medicos WHERE cpf = new.cpf;
  GET DIAGNOSTICS linhas_afetadas = ROW_COUNT;
  IF linhas_afetadas > 0 THEN
    RAISE NOTICE 'Inclusão não autorizada! O CPF já está sendo usado como CPF de um médico!';
  END IF;
END;
$$ LANGUAGE plpgsql;

    2.3) CREATE TRIGGER mResponsavel BEFORE INSERT ON Medicos FOR EACH ROW EXECUTE PROCEDURE ambulatorioResp();

    CREATE FUNCTION ambulatorioResp() RETURNS trigger as $$
DECLARE
  linhas_afetadas integer DEFAULT 0;
BEGIN
  EXECUTE SELECT * FROM Medicos WHERE nroa = new.nroa;
  GET DIAGNOSTICS linhas_afetadas = ROW_COUNT;
  IF linhas_afetadas > 0 THEN
    RAISE NOTICE 'Inserção não autorizada! Este ambulatório já possui um responsável!';
  END IF;
END;
$$ LANGUAGE plpgsql;

    2.4) CREATE TRIGGER medicosemAmb BEFORE UPDATE OF nroa ON Medicos FOR EACH ROW EXECUTE PROCEDURE removerMedico();

    CREATE FUNCTION removerMedico() RETURNS trigger as $$
BEGIN
  IF new.nroa = NULL THEN
    EXECUTE DELETE FROM Medicos WHERE new.codm = codm;
  END IF;
END;
$$ LANGUAGE plpgsql;

    2.5) CREATE TRIGGER rvAmbulatorio BEFORE DELETE ON Ambulatorios FOR EACH ROW EXECUTE PROCEDURE realocarCapacidade();

    CREATE FUNCTION realocarCapacidade() RETURNS trigger as $$
DECLARE row RECORD;
BEGIN
  FOR row IN SELECT * FROM ambulatorios WHERE andar = old.andar LOOP
    EXECUTE UPDATE ambulatorios SET capacidade = capacidade + old.capacidade WHERE nroa = row.nroa;
    EXIT;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Advanced SQL Queries

  1. a) Buscar o nome dos atletas que participaram (tem aproveitamento) em TODOS os times patrocinados pelo patrocinador cujo nome é ‘Rainha Malhas’

    SELECT nome FROM Atletas a WHERE
  NOT EXISTS(Select * From Times t Where codidoPatrocinador=(Select codigoPatrocinador From Patrocinadores Where nome='Rainha Malhas' And NOT EXISTS(Select * From Aproveitamento ap Where ap.codigoAtleta=a.codigoAtleta And ap.codigoTime=t.codigoTime));

  2. b) Alterar a data para ‘20016-10-10’ dos aproveitamentos que se referem aos times que possuem o maior score dentre todos os times

    UPDATE Aproveitamento SET data='2016-10-10' WHERE
  codigoTime IN (Select codigoTime From Times where score=(Select MAX(score) From Times));

  3. c) Buscar o nome dos atletas e dos times que possuem aproveitamento na partida mais recente. A partida mais recente corresponde a maior data registrada na relação Aproveitamento, Se houver empate, mostrar todos os dados requisitados referentes a essa maior data.

    SELECT a.nome, t.nome FROM Aproveitamento ap JOIN Atletas a ON ap.codigoAtleta=a.codigoAtleta
  JOIN Times t ON ap.codigoTime = t.codigoTime
  WHERE data = (SELECT MAX(data) From Aproveitamento);

  4. d) Buscar o nome dos patrocinadores que patrocinam APENAS times da modalidade com nome ‘Tênis de Mesa’.

    SELECT p.nome FROm Patrocinadores p JOIN Times t ON p.codigoPatrocinador=t.codigoPatrocinador AND
  p.codigoPatrocinador NOT IN(Select codigoPatrocinador From Times Where
    codigoModalidade IN (Select codigoModalidade From Modalidades Where nome != 'Tênis de Mesa'));

  5. e) Com frequência o sistema precisa saber quais são os times que possuem score inferior a 3 e quais seus times reserva, caso existir. Formule uma visão capaz de fornecer o código e nome de TODOS os times com score inferior a 3. Caso o time possua um time reserva, apresentar também o nome e score do time que se refere ao time reserva dele.

    CREATE VIEW vScore3 AS
  SELECT t1.codigoTime, t1.nome, t2.nome, t2.score FROM Times t1 LEFT JOIN Times t2 ON t1.timeReserva=t2.codigoTime
  WHERE t1.score < 3;