<p><img src="https://i.imgur.com/D01Fpky.png" alt="" title="Entre no nosso site e veja nossos cursos"></p>
<h1>
<img class="emoji" alt="" src="https://image.flaticon.com/icons/png/128/2906/2906274.png"> BD Essencial - Aula 9
</h1>

<p>O objetivo desse curso &eacute; capacitar o aluno a entrar no mercado de trabalho executando as atividades primordiais que s&atilde;o solicitadas no desenvolvimento, manuten&ccedil;&atilde;o, ou atualiza&ccedil;&atilde;o de bancos de dados dos mais diferentes tipos de sistemas.</p>
<p>Focamos na execu&ccedil;&atilde;o, e n&atilde;o em cria&ccedil;&atilde;o de diagramas - atividade essa que &eacute; desenvolvida por profissionais mais experientes.</p>
<p>Nas nossas prieiras aulas, utilizamos o Gerenciador de Bancos de Dados Relacionais <strong>MySQL</strong> nesse nosso curso de Banco de Dados Essencial, porque essa ferramenta &eacute; gratuita e de f&aacute;cil utiliza&ccedil;&atilde;o - entretanto, todo o nosso conte&uacute;do pode ser aplicado em qualquer Gerenciador de Bancos de Dados Relacionais, apenas com algumas m&iacute;nimas diferen&ccedil;as.</p>
<p>Na segunda parte do curso, utilizamos o Gerenciador de Bancos de Dados Relacionais <strong>PostgreSQL</strong>.</p>

<blockquote style="border-left-color: red;">
<p><span class="color" data-color="red"></span><span> </span><small><i class="fa fa-user"></i> Prof Fernando Gomes - [e-mail]</small></p></blockquote>

<h2 id="-Tema-da-aula" data-id="-Tema-da-aula"><a class="anchor hidden-xs" href="#-Tema-da-aula" title="-Tema-da-aula"><span class="octicon octicon-link"></span></a><img class="emoji" alt=":book:" src="https://cdn.jsdelivr.net/npm/@hackmd/emojify.js@2.1.0/dist/images/basic/book.png">
<span> Tema da aula:</span></h2><span>
<p>Nessa nona aula, damos sequência aos exercícios de <strong>SQL</strong></p>

-- Obter a quantidade de empregados pertencentes 
-- ao departamento 4;
select count(*) from departamento_ex
where codDepto = 4;

 count
-------
     1
(1 row)

-- Obter, a partir da tabela trabalhaEm, os números mínimo, 
-- máximo e médio de horas trabalhadas por empregados  
-- em cada projeto. O resultado deve possuir 4 colunas nomeadas: 
-- projeto, minimo, maximo e media;
select p.codproj, p.titulo projeto, min(t.horas) minimo, 
        max(t.horas) maximo, avg(t.horas) media 
  from trabalhaEm_ex t
  INNER JOIN projeto_ex p
  ON p.codproj = t.codproj
  group by p.codProj
  order by projeto;

 codproj |        projeto        | minimo | maximo 
---------+-----------------------+--------+--------
      30 | Bonus para Inventores |      5 |     30 
      20 | Business Inteligence  |      0 |     15 
       2 | Cama Hiperbarica      |      8 |     20 
       3 | Emissor de Raios Z    |     10 |     40 
      10 | Gestao dos 80/20      |     10 |     35 
       1 | Transmogrifador       |     20 |     33 


|        media
+---------------------
| 18.3333333333333333
|  8.3333333333333333
| 12.6666666666666667
| 21.6666666666666667
| 18.3333333333333333
| 26.5000000000000000
(6 rows)
 
-- Obter os códigos de projetos cuja média de horas 
-- trabalhadas seja maior que 20;
 select p.codproj codigo, p.titulo projeto, 
        avg(t.horas) media 
  from trabalhaEm_ex t
  inner join projeto_ex p
  on p.codproj = t.codproj
  group by p.codproj
  having avg(t.horas) > 20;
 codigo |      projeto       |        media
--------+--------------------+---------------------
      1 | Transmogrifador    | 26.5000000000000000
      3 | Emissor de Raios Z | 21.6666666666666667
(2 rows)  

-- Obter os nomes de projetos correspondentes à consulta anterior. 
-- Usar a consulta anterior como uma consulta 
-- aninhada à nova consulta;
select titulo from projeto_ex
where codProj in (
    select p.codproj
        from trabalhaEm_ex t
        inner join projeto_ex p
        on p.codproj = t.codproj
        group by p.codproj
        having avg(t.horas) > 20
);

       titulo
--------------------
 Transmogrifador
 Emissor de Raios Z
(2 rows)

-- Criar um índice único de data do nascimento e nome 
-- para a tabela empregado_ex;

/* Criar índice evita que haja repetição de funcionário
e data de nascimento, ou seja, mesmo nome 
e nascidos na mesma data. 
Índice agiliza muito o acesso aos dados, mas faz com
que alterações [updates] demorem um pouco mais. 
Devemos criar índices em campos que haja uma boa
chance de se fazer uma busca por eles. */

create unique index idx_nascto_nome 
      on empregado_ex (dataNasc, nome);

banco40=# \d empregado_ex
   Table "public.empregado_ex"
  Column   |         Type          |
-----------+-----------------------+
 codemp    | integer               |
 nome      | character varying(45) |
 datanasc  | date                  |
 endereco  | character varying(90) |
 sexo      | character(1)          |
 salario   | numeric(10,2)         |
 codsuperv | integer               |
 coddepto  | integer               |

|                           Modifiers
+---------------------------------------------------------------
| not null default nextval('empregado_ex_codemp_seq'::regclass)

Indexes:
    "empregado_ex_pkey" PRIMARY KEY, btree (codemp)
    "idx_nascto_nome" UNIQUE, btree (datanasc, nome)
Foreign-key constraints:
    "empregado_depto_ex_fk" FOREIGN KEY (coddepto) 
		REFERENCES departamento_ex(coddepto)
Referenced by:
 TABLE "dependente_ex" CONSTRAINT "dependente_empregado_ex_fk" 
  FOREIGN KEY (codemp) REFERENCES empregado_ex(codemp)


-- Criar um índice único do gerente e código do departamento 
-- a tabela departamento_ex;
create unique index idx_gerente_depto
      on departamento_ex (gerente, coddepto);

-- Criar uma visão para os atributos codEmp, nome e salario 
-- da tabela empregado_ex;
 create view view_empregado as
  select codemp, nome, salario
  from empregado_ex;

 select * from view_empregado;

banco40=# select * from view_empregado;
 codemp |        nome         | salario
--------+---------------------+----------
      1 | Joao da Silva       | 30000.00
      2 | Horacio Bagual      | 40000.00
      4 | Antonio Pestana     | 45000.00
      5 | Maria Antonia Real  | 38000.00
      6 | Ada Maria Lovelace  | 25000.00
      8 | Joaquina Pasqualini | 25000.00
      7 | Jaime Bonde         | 70000.00
      3 | Ana Bacana          | 28000.00
(8 rows)

-- Criar uma visão para os atributos codEmp e nome da tabela 
-- empregado_ex e os atributos nome, 
-- sexo e relacao da tabela dependente_ex;
create view view_empregado_dependente as
  select e.codemp, e.nome nome_empregado, 
      d.nome nome_dependente, d.sexo, d.relacao
  from empregado_ex e INNER JOIN
       dependente_ex d
        ON e.codemp = d.codemp;

select * from view_empregado_dependente;

banco40=# select * from view_empregado_dependente;
 codemp | nome_empregado | nome_dependente | sexo | relacao
--------+----------------+-----------------+------+---------
      2 | Horacio Bagual | Joana          +| F    | Filha
        |                | Bagual          |      |
      2 | Horacio Bagual | Pedro          +| M    | Filho
        |                | Bagual          |      |
      2 | Horacio Bagual | Alice          +| F    | Esposa
        |                | Bagual          |      |
      3 | Ana Bacana     | Maximo         +| M    | Esposo
        |                | Bacana          |      |
      1 | Joao da Silva  | Jagunco da     +| M    | Filho
        |                | Silva           |      |
      1 | Joao da Silva  | Jaqueline da   +| F    | Filha
        |                | Silva           |      |
      1 | Joao da Silva  | Joana da       +| F    | Esposa
        |                | Silva           |      |
(7 rows)

-- Criar uma visão para os atributos codEmp e nome da tabela 
-- empregado_ex, o atributo horas da tabela trabalhaEm_ex 
-- e os atributos titulo e codDepto da tabela projeto_ex;
create view view_empregado_projeto as
  select e.codemp, e.nome nome_empregado, t.horas, 
            p.titulo, p.coddepto
  from empregado_ex e INNER JOIN
       trabalhaEm_ex t ON 
       e.codemp = t.codemp
      INNER JOIN projeto_ex p
      ON p.codproj = t.codproj
      order by nome_empregado;

select * from view_empregado_projeto;

banco40=# select * from view_empregado_projeto;
 codemp |   nome_empregado    | horas |        titulo         | coddepto
--------+---------------------+-------+-----------------------+----------
      6 | Ada Maria Lovelace  |    20 | Cama Hiperbarica      |        5
      6 | Ada Maria Lovelace  |    20 | Transmogrifador       |        5
      3 | Ana Bacana          |    30 | Bonus para Inventores |        4
      3 | Ana Bacana          |    10 | Gestao dos 80/20      |        4
      4 | Antonio Pestana     |    15 | Business Inteligence  |        1
      4 | Antonio Pestana     |    20 | Bonus para Inventores |        4
      2 | Horacio Bagual      |    10 | Emissor de Raios Z    |        5
      2 | Horacio Bagual      |    10 | Gestao dos 80/20      |        4
      2 | Horacio Bagual      |    10 | Business Inteligence  |        1
      2 | Horacio Bagual      |    10 | Cama Hiperbarica      |        5
      7 | Jaime Bonde         |     0 | Business Inteligence  |        1
      1 | Joao da Silva       |    15 | Emissor de Raios Z    |        5
      1 | Joao da Silva       |     8 | Cama Hiperbarica      |        5
      1 | Joao da Silva       |    33 | Transmogrifador       |        5
      8 | Joaquina Pasqualini |    35 | Gestao dos 80/20      |        4
      8 | Joaquina Pasqualini |     5 | Bonus para Inventores |        4
      5 | Maria Antonia Real  |    40 | Emissor de Raios Z    |        5
(17 rows)

-- Crie os usuários adm1, adm2 e adm3;
SELECT rolname, rolsuper FROM pg_roles;
-- listando os usuarios e vendo quais sao superusuarios
  rolname  | rolsuper
-----------+----------
 postgres  | t
 estagio   | f
 estudante | f
 aluno     | f
 luciana   | f
(5 rows)
-- estagio não é usuário, é um role
-- nas versões mais recentes do PostgreSQL, role e usuário
-- são vistos de forma muito semelhante

drop user if exists adm1, adm2, adm3;

create user adm1;
 alter user adm1 with password '123';

create user adm2;
 alter user adm2 with password '123';

create user adm3;
 alter user adm3 with password '123'; 


-- Crie a tabela aluno com 5 atributos na sessão do 
-- usuário adm1 (não use not null);

\c postgres postgres
-- desconectando-se de banco40
\c banco40 adm1

postgres=# \c banco40 adm1
Password for user adm1:
You are now connected to database "banco40" as user "adm1".

CREATE TABLE ALUNO(
  ID_ALUNO SERIAL primary key,
  NOME VARCHAR(50),
  TELEFONE VARCHAR(20),
  SEXO VARCHAR (1) check (SEXO in('m','f')),
  EMAIL VARCHAR(30) unique);

banco40=> CREATE TABLE ALUNO(
banco40(>   ID_ALUNO SERIAL primary key,
banco40(>   NOME VARCHAR(50),
banco40(>   TELEFONE VARCHAR(20),
banco40(>   SEXO VARCHAR (1) check (SEXO in('m','f')),
banco40(>   EMAIL VARCHAR(30) unique);
CREATE TABLE

-- Permita que o usuário adm2 consulte e o usuário 
-- adm3 insira e atualize;
GRANT SELECT ON ALUNO TO adm2;
GRANT INSERT, UPDATE ON ALUNO TO adm3;

banco40=> GRANT SELECT ON ALUNO TO adm2;
GRANT
banco40=> GRANT INSERT, UPDATE ON ALUNO TO adm3;
GRANT

-- antes de concluir, precisamos dar acesso também
-- à sequencia da chave primária de ID_ALUNO
-- senão, o postgresql não permite que novos registros 
-- sejam criados pelos usuários que tiverem acesso 
-- apenas à tabela e não à sequência

banco40=> \ds
                      List of relations
 Schema |             Name             |   Type   |  Owner
--------+------------------------------+----------+----------
 public | aluno_id_aluno_seq           | sequence | adm1

GRANT USAGE, SELECT ON SEQUENCE aluno_id_aluno_seq TO adm3;


-- No usuário adm3 insira 3 registros;


INSERT INTO ALUNO (NOME,TELEFONE,SEXO,EMAIL) values
('ALUNO01','21-98956-0101','m','[e-mail]'),
('ALUNA01','11-95678-1010','f','[e-mail]'),
('ALUNA02','31-91234-2222','f','[e-mail]');



-- No usuário adm2 consulte os registros;
banco40=> select * from aluno;
 id_aluno |  nome   |   telefone    | sexo |       email
----------+---------+---------------+------+-------------------
        1 | ALUNO01 | 21-98956-0101 | m    | [e-mail]
        2 | ALUNA01 | 11-95678-1010 | f    | [e-mail]
        3 | ALUNA02 | 31-91234-2222 | f    | [e-mail]
(3 rows)

-- No usuário adm1 crie uma visão sobre 3 atributos;
CREATE VIEW VIEW_ALUNOS AS
  SELECT ID_ALUNO, NOME, EMAIL
  FROM ALUNO;

banco40=> select * from VIEW_ALUNOS;
 id_aluno |  nome   |       email
----------+---------+-------------------
        1 | ALUNO01 | [e-mail]
        2 | ALUNA01 | [e-mail]
        3 | ALUNA02 | [e-mail]
(3 rows)

-- Permita consulta da visão ao usuário adm3;
GRANT SELECT ON VIEW_ALUNOS TO adm3;

-- Permita inserção e atualização da visão ao 
-- usuário adm2;
GRANT INSERT, UPDATE ON VIEW_ALUNOS TO adm2;
GRANT USAGE, SELECT ON SEQUENCE aluno_id_aluno_seq TO adm2;

-- No usuário adm2 insira 3 registros na visão;
INSERT INTO VIEW_ALUNOS (NOME,EMAIL) values
('ALUNO10','[e-mail]'),
('ALUNA10','[e-mail]'),
('ALUNA20','[e-mail]');


-- No usuário adm3 consulte os registros da visão;
select * from view_alunos;

 id_aluno |  nome   |       email
----------+---------+-------------------
        1 | ALUNO01 | [e-mail]
        2 | ALUNA01 | [e-mail]
        3 | ALUNA02 | [e-mail]
        4 | ALUNO10 | [e-mail]
        5 | ALUNA10 | [e-mail]
        6 | ALUNA20 | [e-mail]
(6 rows)

-- Retire as permissões do usuário 
-- adm3 para a tabela aluno;
banco40=> REVOKE ALL ON ALUNO FROM adm3;
REVOKE

<!--
<h3>
Tipos de Dados Numéricos do MySQL
</h3>

<p>
Na tabela abaixo vemos os tipos da dados numéricos aceitos no MySQL. Utilizamos, BIT ou TINYINT(1) para armazenar um campo booleano. Além dessa situação, os mais utilizados são TINYINT, INT, e DOUBLE. Por padrão, os campos admitem números com sinal, portanto, o padrão [default] é SIGNED.
</p>

-->

<blockquote style="border-left-color: red;">
<p><span class="color" data-color="red"></span><span> </span><small><i class="fa fa-user"></i> Prof Fernando Gomes - [e-mail]</small></p></blockquote>

<br>
<div class="alert alert-info">
<blockquote style="border-left-color: blue;">
<p><span class="color" data-color="blue"></span><span> </span><small><i class="fa fa-user"></i> E.B. Cursos https://www.cursoseb.com.br/</small><br>
<span class="color" data-color="blue"></span><span> </span><small><i class="fa fa-user"></i> E.B. Cursos EAD https://edsonbelemtreinamento.com.br/ead/</small></p>
</blockquote>
</div>