Curso de Programação C#

William Ivanski

Lista de Exercícios Módulo III Capítulo 5 (Respostas)


1) Instale o PostgreSQL no seu computador ou em uma máquina virtual. Versões recomendadas: 9.4, 9.5 ou 9.6. Sistema operacional recomendado: Linux.


2) Baixe o arquivo compras.db e converta este banco de dados de SQLite para PostgreSQL em um banco novo chamado compras. Neste banco, rode as consultas SQL que são as respostas dos exercícios 1 a 12 da Lista 3.3.


3) Baixe o arquivo escola.db e converta este banco de dados de SQLite para PostgreSQL em um banco novo chamado escola. Neste banco, rode as consultas SQL que são as respostas dos exercícios 13 a 24 da Lista 3.3.


4) Crie um banco de dados chamado employees. Baixe o arquivo employees.dmp e use pg_restore para restaurar nesse banco de dados.


5) Crie um script .sh ou .bat para realizar o backup do banco employees com pg_dump. Agende o backup para ser feito todos os dias às 01:00 hrs, e devem ser mantidas cópias dos últimos 30 dias.


6) Execute a consulta abaixo no banco employees:

select dept_name from departments

Usando nesting, converta os valores retornados em uma única string com os valores separados por vírgula.


select array_to_string(array(select dept_name from departments), ', ')

7) Instale o pg_activity.


8) Instale o pgbench. Crie um banco de dados com 1 milhão de registros na maior tabela. Rode um experimento com 50 conexões simultâneas e 150 transações por conexão.


9) Instale o pgbadger e rode-o. Visualise as consultas do experimento do exercício anterior na página web gerada.


10) Reescreva a consulta abaixo usando CTEs:

select s.from_date,
       s.to_date,
       s.salary
from departments d
inner join dept_emp de
        on de.dept_no = d.dept_no
inner join employees e
        on e.emp_no = de.emp_no
inner join salaries s
        on s.emp_no = e.emp_no
where d.dept_name = 'Finance'
  and e.first_name = 'Georg'
  and e.last_name = 'Matzke'
order by s.from_date

with dep as (
select * from departments where dept_name = 'Finance'
),
emp as (
select * from employees where first_name = 'Georg' and last_name = 'Matzke'
)
select s.from_date,
       s.to_date,
       s.salary
from dep d
inner join dept_emp de
        on de.dept_no = d.dept_no
inner join emp e
        on e.emp_no = de.emp_no
inner join salaries s
        on s.emp_no = e.emp_no
order by s.from_date

11) Dada a consulta do exercício anterior, utilize explain para analisar o plano de execução. Responsa às seguintes perguntas:


12) Crie uma tabela chamada aluno da seguinte maneira:

create table alunos
(
    id serial,
    nome text,
    sexo char,
    nasc timestamp
)

Crie uma função em linguagem SQL chamada fnc_insere_aluno, que recebe como parâmetro o nome, o sexo e a data de nascimento. Essa função deve retornar o código do aluno inserido.


CREATE OR REPLACE FUNCTION fnc_insere_alunoSQL(Text, CHAR, TIMESTAMP)
    returns INTEGER
    language SQL
AS $$
    INSERT INTO alunos (nome, sexo, nasc) VALUES($1, $2, $3);
   SELECT MAX(id) FROM alunos;
$$

13) Altere a função do exercício anterior para ser em linguagem PL/SQL.


create or replace function fnc_insere_aluno(p_Nome text,p_Sexo char,p_DataDeNascimento timestamp)
returns bigint
as $$
declare
    v_saida bigint;
begin
    insert into alunos (nome, sexo, nasc)
    values(p_Nome, p_Sexo, p_DataDeNascimento);
    select max(id) into v_saida from alunos;
    return v_saida;
end;
$$
language plpgsql;

14) Crie um procedimento em linguagem PL/SQL chamado prc_remove_aluno.


create or replace function prc_remove_aluno(p_id integer)
returns void
language plpgsql 
as $$
begin
    delete from alunos
    where alunos.id = p_id;
end;
$$

15) Crie uma função em linguagem PL/SQL chamada fnc_remove_aluno, que recebe como parâmetro o código do aluno. A função deve retornar 0, caso o aluno tenha sido removido, e -1, caso o código passado como parâmetro não exista na tabela de alunos.


create or replace function fnc_remove_aluno(p_id bigint)
returns integer
language plpgsql 
AS $function$
declare
    v_saida integer;
begin
    
    if((select count(*) from alunos where id = p_id) > 0) then
    delete from alunos
    where alunos.id = p_id;
    v_saida := 0;
    else
    v_saida := -1;
    end if;
    
    return v_saida;
end;
$function$

16) Crie uma função em linguagem PL/SQL chamada fnc_altera_aluno, que recebe como parâmetro o código do aluno, o novo nome, o novo sexo e a nova data de nascimento. A função deve retornar 0, caso o aluno tenha sido alterado, e -1, caso o código passado como parâmetro não exista na tabela de alunos.


Create or replace function fnc_altera_aluno(p_Id bigint, p_Nome text, p_Sexo char, p_Data timestamp)
    returns integer
    language plpgsql
as $$
declare
    v_saida int;
begin
    if((select count(*) from alunos where id = p_Id) <> 0) then
        update alunos set nome = p_Nome, 
                          sexo = P_Sexo,
                          nasc = P_Data
        where id = p_Id;
        
        v_saida:= 0;
    else
        v_saida := -1;
    end if;
    return v_saida; 
end;
$$

17) Cria uma função em linguagem PL/SQL chamada fnc_idade_aluno, que recebe como parâmetro o código do aluno. A função deve retornar a idade do aluno (integer), ou -1, caso o código passado como parâmetro não exista na tabela de alunos.


create or replace function fnc_idade_aluno (p_id bigint)
    Returns integer
    language plpgsql
as $$
declare
    v_saida integer;
    v_DataNasc timestamp;
begin
    if((select count(*) from alunos where id = p_id) > 0) then
        v_DataNasc := (select nasc from alunos where id = p_id);
        v_saida := (select extract(year from age(V_DataNasc::timestamp)));
    else
        v_saida := -1;
    end if;
    return v_saida;
end;

18) Conforme vimos em aula, é possível extrair os milisegundos de um timestamp, vide exemplo:

select ((extract(second from '12/11/2016 10:00:30.900150'::timestamp) - floor(extract(second from '12/11/2016 10:00:30.900150'::timestamp))) * 1000) as a

Escreva uma função em linguagem PL/SQL chamada fnc_milisegundos, que recebe como parâmetro uma string representando um timestamp e retorna o valor real em milisegundos.


create or replace function fnc_milisegundos(p_string text)
returns double precision
language plpgsql as
$$
declare
  v_saida double precision;
begin
  v_saida := (select ((extract(second from p_string::timestamp) - floor(extract(second from p_string::timestamp))) * 1000));
  return v_saida;
end;
$$