Curso de Programação C#

William Ivanski

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

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.

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

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

• a) Qual o custo total previsto para executar a consulta?
• b) Qual é o gargalo?
• c) Faz sentido criar um índice? Em qual(is) coluna(s)?
• d) Se você criou o índice proposto no item c), rode o explain novamente. O custo total previsto reduziu? Para quanto?

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.

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

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

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.

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.

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.

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.