<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 8
</h1>
<p>O objetivo desse curso é capacitar o aluno a entrar no mercado de trabalho executando as atividades primordiais que são solicitadas no desenvolvimento, manutenção, ou atualização de bancos de dados dos mais diferentes tipos de sistemas.</p>
<p>Focamos na execução, e não em criação de diagramas - atividade essa que é 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 é gratuita e de fácil utilização - entretanto, todo o nosso conteúdo pode ser aplicado em qualquer Gerenciador de Bancos de Dados Relacionais, apenas com algumas mínimas diferenç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 - fernandojnr@gmail.com</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 oitava aula e na próxima, vamos fazer exercícios de <strong>SQL</strong></p>
<p>
Também vamos abordar os seguintes assuntos e comandos:
</p>
<ul>
<li>Alter Table, ADD CONSTRAINT;</li>
<li>initcap, lower, upper;</li>
<li>to_char, to_date;</li>
<li>HAVING;</li>
<li>create unique index;</li>
<li>create user, grant;</li>
<li>grant a sequências para valores de chaves primárias;</li>
<li>revoke;</li>
</ul>
```postgre=
drop database if exists banco40;
create database banco40;
\c banco40
select current_database();
begin;
CREATE TABLE empregado_ex (
codEmp SERIAL primary key,
nome VARCHAR(45),
dataNasc DATE,
endereco VARCHAR(90),
sexo CHAR(1),
salario DEC(10,2),
codSuperv INT,
codDepto INT
);
CREATE TABLE departamento_ex (
codDepto SERIAL primary key,
nome VARCHAR(45),
gerente INT,
dataGerente DATE
);
CREATE TABLE projeto_ex (
codProj SERIAL primary key,
titulo VARCHAR(45),
codDepto INT
);
CREATE TABLE trabalhaEm_ex (
codEmp INT NOT NULL,
codProj INT NOT NULL,
horas INT,
primary key (codEmp,codProj)
);
CREATE TABLE dependente_ex (
codEmp INT NOT NULL,
nome VARCHAR(45),
sexo CHAR(1),
dataNasc DATE,
relacao VARCHAR(20),
primary key (codEmp,nome)
);
ALTER TABLE empregado_ex ADD CONSTRAINT
empregado_depto_ex_fk FOREIGN KEY
(codDepto) REFERENCES departamento_ex(codDepto);
ALTER TABLE projeto_ex ADD CONSTRAINT
projeto_depto_ex_fk FOREIGN KEY
(codDepto) REFERENCES departamento_ex(codDepto);
ALTER TABLE dependente_ex ADD CONSTRAINT
dependente_empregado_ex_fk FOREIGN
KEY (codEmp) REFERENCES empregado_ex(codEmp);
commit;
begin;
INSERT INTO departamento_ex VALUES
(5,'Pesquisa',2,'22/05/1988');
INSERT INTO departamento_ex VALUES
(4,'Administracao',4,'01/01/1985');
INSERT INTO departamento_ex VALUES
(1,'Direcao',7,'19/06/1981');
INSERT INTO empregado_ex VALUES
(1,'Joao da Silva','09/01/1955','Rua dos
Camelos, 23','M',30000,2,5);
INSERT INTO empregado_ex VALUES
(2,'Horacio Bagual','10/10/1970','Av. dos
Tapejaras, 90','M',40000,7,5);
INSERT INTO empregado_ex VALUES
(3,'Ana Bacana','20/06/1980','Av. Atacama,
10 apto 22','F',25000,4,4);
INSERT INTO empregado_ex VALUES
(4,'Antonio Pestana','13/04/1990','Rod.
Imigrantes, 1940','M',45000,7,4);
INSERT INTO empregado_ex VALUES
(5,'Maria Antonia Real','14/11/1982','Rua
Petropolis, 13','F',38000,2,5);
INSERT INTO empregado_ex VALUES
(6,'Ada Maria Lovelace','15/10/1965','Rua
dos Ingleses, 1020','F',25000,2,5);
INSERT INTO empregado_ex VALUES
(8,'Joaquina Pasqualini','17/08/1968','Rua
dos Ingleses, 1010','F',25000,4,4);
INSERT INTO empregado_ex VALUES
(7,'Jaime Bonde','01/01/1958','Rua dos
Ingleses, 1010','M',70000,null,1);
INSERT INTO projeto_ex VALUES
(1,'Transmogrifador',5);
INSERT INTO projeto_ex VALUES
(2,'Cama Hiperbarica',5);
INSERT INTO projeto_ex VALUES
(3,'Emissor de Raios Z',5);
INSERT INTO projeto_ex VALUES
(10,'Gestao dos 80/20',4);
INSERT INTO projeto_ex VALUES
(20,'Business Inteligence',1);
INSERT INTO projeto_ex VALUES
(30,'Bonus para Inventores',4);
INSERT INTO trabalhaEm_ex VALUES (1,1,32.5);
INSERT INTO trabalhaEm_ex VALUES (1,2,7.5);
INSERT INTO trabalhaEm_ex VALUES (5,3,40);
INSERT INTO trabalhaEm_ex VALUES (6,1,20);
INSERT INTO trabalhaEm_ex VALUES (6,2,20);
INSERT INTO trabalhaEm_ex VALUES (2,2,10);
INSERT INTO trabalhaEm_ex VALUES (2,3,10);
INSERT INTO trabalhaEm_ex VALUES (2,10,10);
INSERT INTO trabalhaEm_ex VALUES (2,20,10);
INSERT INTO trabalhaEm_ex VALUES (3,30,30);
INSERT INTO trabalhaEm_ex VALUES (3,10,10);
INSERT INTO trabalhaEm_ex VALUES (8,10,35);
INSERT INTO trabalhaEm_ex VALUES (8,30,5);
INSERT INTO trabalhaEm_ex VALUES (4,30,20);
INSERT INTO trabalhaEm_ex VALUES (4,20,15);
INSERT INTO trabalhaEm_ex VALUES (7,20,0);
INSERT INTO trabalhaEm_ex VALUES (1,3,15);
INSERT INTO dependente_ex VALUES (2,'Joana
Bagual','F','05/05/1986','Filha');
INSERT INTO dependente_ex VALUES (2,'Pedro
Bagual','M','25/10/1983','Filho');
INSERT INTO dependente_ex VALUES (2,'Alice
Bagual','F','03/05/1958','Esposa');
INSERT INTO dependente_ex VALUES (3,'Maximo
Bacana','M','28/02/1942','Esposo');
INSERT INTO dependente_ex VALUES (1,'Jagunco da
Silva','M','01/01/1978','Filho');
INSERT INTO dependente_ex VALUES (1,'Jaqueline da
Silva','F','31/12/1978','Filha');
INSERT INTO dependente_ex VALUES (1,'Joana da
Silva','F','05/05/1957','Esposa');
commit;
-- Alterar o salário do empregado de código 3 para 28000;
select salario from empregado_ex
where codEmp = 3;
banco40=# select salario from empregado_ex
banco40-# where codEmp = 3;
salario
----------
25000.00
(1 row)
update empregado_ex set SALARIO = 28000
where CODEMP = 3;
banco40=# select salario from empregado_ex
banco40-# where codEmp = 3;
salario
----------
28000.00
(1 row)
-- Obter nomes de empregados com salario > 30000;
select NOME, salario
from empregado_ex
where SALARIO > 30000;
nome | salario
--------------------+----------
Horacio Bagual | 40000.00
Antonio Pestana | 45000.00
Maria Antonia Real | 38000.00
Jaime Bonde | 70000.00
-- Obter nomes de empregados que trabalham no projeto
-- 'Transmogrifador'.
select nome
from empregado_ex e,
trabalhaEm_ex t,
projeto_ex p
where e.codemp = t.codemp
and t.codproj = p.codproj
and p.titulo = 'Transmogrifador';
select e.nome
from empregado_ex e
INNER JOIN trabalhaEm_ex t
ON e.codemp = t.codemp
INNER JOIN projeto_ex p
ON t.codproj = p.codproj
where p.titulo = 'Transmogrifador';
nome
--------------------
Joao da Silva
Ada Maria Lovelace
-- Obter nomes e endereços de todos os empregados que trabalham
-- no departamento de 'Pesquisa';
select INITCAP (e.nome), e.endereco, LOWER (d.nome)
from empregado_ex e
INNER JOIN departamento_ex d
ON e.coddepto = d.coddepto
where upper (d.nome) = 'PESQUISA';
initcap | endereco | lower
--------------------+--------------------+----------
Joao Da Silva | Rua dos +| pesquisa
| Camelos, 23 |
Horacio Bagual | Av. dos +| pesquisa
| Tapejaras, 90 |
Maria Antonia Real | Rua +| pesquisa
| Petropolis, 13 |
Ada Maria Lovelace | Rua +| pesquisa
| dos Ingleses, 1020 |
-- Obter nomes de empregados que começam com a letra 'A';
select INITCAP (e.nome)
from empregado_ex e
where upper (e.nome) LIKE 'A%';
initcap
--------------------
Antonio Pestana
Ada Maria Lovelace
Ana Bacana
-- Obter os nomes e datas de nascimento dos empregados que fazem
-- aniversário no mês de outubro;
select nome, dataNasc
from empregado_ex
where to_char(dataNasc, 'MM') = '10';
nome | datanasc
--------------------+------------
Horacio Bagual | 1970-10-10
Ada Maria Lovelace | 1965-10-15
-- Obter os nomes dos empregados nascidos entre as
-- datas 1950-01-01 e 1970-01-01;
select nome, dataNasc
from empregado_ex
where dataNasc between to_date ('01/01/1950', 'dd/mm/yyyy')
and to_date ('01/01/1970', 'dd/mm/yyyy');
nome | datanasc
---------------------+------------
Joao da Silva | 1955-01-09
Ada Maria Lovelace | 1965-10-15
Joaquina Pasqualini | 1968-08-17
Jaime Bonde | 1958-01-01
-- Listar os títulos de projetos em ordem alfabética;
select initcap (titulo) from projeto_ex
order by titulo;
-- order by titulo desc;
initcap
-----------------------
Bonus Para Inventores
Business Inteligence
Cama Hiperbarica
Emissor De Raios Z
Gestao Dos 80/20
Transmogrifador
-- Listar nomes e horas trabalhadas por empregados no projeto
-- de código 3, em ordem decrescente de horas trabalhadas;
select e.nome, t.horas
from empregado_ex e
INNER JOIN trabalhaEm_ex t
ON t.codemp = e.codemp
INNER JOIN projeto_ex p
ON p.codproj = t.codproj
where p.codproj = 3
order by t.horas desc;
select e.nome, t.horas
from empregado_ex e, trabalhaEm_ex t, projeto_ex p
where t.codemp = e.codemp
and p.codproj = t.codproj
and p.codproj = 3
order by t.horas desc;
nome | horas
--------------------+-------
Maria Antonia Real | 40
Joao da Silva | 15
Horacio Bagual | 10
-- Obter códigos de empregados que trabalham mais de 10 horas
-- em algum projeto.
-- O resultado da consulta não deve ter repetições
-- de códigos de empregados;
select distinct e.codemp, e.nome
from empregado_ex e, trabalhaEm_ex t, projeto_ex p
where t.codemp = e.codemp
and p.codproj = t.codproj
and t.horas > 10;
codemp | nome
--------+---------------------
4 | Antonio Pestana
3 | Ana Bacana
5 | Maria Antonia Real
8 | Joaquina Pasqualini
6 | Ada Maria Lovelace
1 | Joao da Silva
(6 rows)
select distinct e.codemp, e.nome,
sum(t.horas) as Total_Horas
from empregado_ex e
INNER JOIN trabalhaEm_ex t
ON t.codemp = e.codemp
INNER JOIN projeto_ex p
ON p.codproj = t.codproj
and t.horas > 10
group by e.codemp
order by Total_Horas desc;
codemp | nome | total_horas
--------+---------------------+-------------
1 | Joao da Silva | 48
5 | Maria Antonia Real | 40
6 | Ada Maria Lovelace | 40
4 | Antonio Pestana | 35
8 | Joaquina Pasqualini | 35
3 | Ana Bacana | 30
(6 rows)
```
<!--
<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 - fernandojnr@gmail.com</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>
Sign in with Wallet
Connect another wallet