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 departmentsUsando 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_datewith 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_date11) 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 aEscreva 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;
$$