# Final Seshion | Banco de dados
**autor:** Josemir Fernandes
**turma:** Info C
**numero:** 25
# Modelagem 1 : App Catalago Globo Play
> Create script
```sql=
create table tb_app (
id_app int primary key auto_increment,
nm_nome varchar(100),
ds_genero varchar(100),
bt_disponivel_ios bool,
bt_disponivel_android bool,
dt_atualizacao date,
ds_tamanho varchar(100),
nm_criador varchar(100),
ds_avaliacao decimal(10,2),
ds_download varchar(100)
);
```
> Insert script
```sql=
insert into tb_app (nm_nome, ds_genero, bt_disponivel_ios, bt_disponivel_android, dt_atualizacao, ds_tamanho, nm_criador, ds_avaliacao, ds_download)
values ("facebook", "todos", true, true, "2021-04-11", "328 mb", "Mark Zuckerberg", "2.8", "2,6 bilhões"),
("tiktok", "todos", true, true, "2021-04-16", "225,9 mb", "Zhang Yiming", "4.2", "800 milhões"),
("snapchat", "todos", true, true, "2021-04-16", "207,2 mb", "Evan Spiegel", "3.9", "1,3 bilhões")
```
> Select script
```sql=
select *
from tb_app
where nm_nome = "tiktok"
and bt_disponivel_ios = true;
select *
from tb_app
where nm_nome = "facebook"
```
# Modelagem 2 : Paises
> Create script
```sql=
create table tb_paises (
id int primary key auto_increment,
nm_nome varchar(100),
ds_sigla varchar(100),
ds_populacao varchar(100),
ds_area varchar(100),
dt_aniversario date,
ds_pib varchar(100),
ds_continente varchar(100),
ds_capital varchar(100),
ds_idioma varchar(100)
);
```
> Insert script
```sql=
insert into tb_paises ( nm_nome, ds_sigla, ds_populacao, ds_area, dt_aniversario, ds_pib, ds_continente, ds_capital, ds_idioma)
values ( "alemanha", "DE", "83,02 milhões", "357.386 km²", "2021-10-03", "3.861 trilhões", "europa", "berlim", "alemao"),
( "franca", "FR", "67,06 milhões", "643.801 km²", "2021-11-28", "2,716 trilhões ", "europa", "paris", "frances"),
( "brasil", "br", "211 milhões", "8.516.000 km²", "2021-04-22", "1,84 trilhão", "america do sul", "brasilia", "portugues")
```
> Select script
```sql=
select *
from tb_paises
where ds_pib = "3.861 trilhões";
select *
from tb_paises
where dt_aniversario >= "2021- 11-28"
order
by "dt_dt_aniversario"
```
# Modelagem : 3 Clube
> Create script
```sql=
create table tb_clube (
id int primary key auto_increment,
nm_nome varchar(100),
ds_sigla varchar(100),
ds_pais varchar(100),
ds_cidade varchar(100),
dt_fundacao date,
vl_titulos int(100),
nm_presidente varchar(100),
vl_estadio int(100),
nm_estadio varchar(100)
);
```
> Insert script
```sql=
insert into tb_clube ( nm_nome, ds_sigla, ds_pais, ds_cidade, dt_fundacao, vl_titulos, nm_presidente, vl_estadio, nm_estadio)
values ( "sao paulo", "spfc", "brasil", "sao paulo", " 1930-01-25", "42", "julio cazares", "66.795", "cicero pompeu de toledo"),
( "santos", "sfc", "brasil", "sao paulo", " 1914-04-14", "47", "andres rueda", "16.068", "vila belmiro"),
( "internacional", "sci", "brasil", "rio garnde do sul", " 1909-04-25", "88", "alessandro barcelos", "66.795", "beira rio")
```
> Select script
```sql=
select *
from tb_clube
where nm_nome = "sao paulo";
select *
from tb_clube
where nm_estadio = "vila belmiro"
```
# Modelagem 4: Musica
> Create script
```sql=
create table tb_musica (
id int primary key auto_increment,
nm_nome varchar(100),
nm_artista varchar(100),
ds_album varchar(100),
ds_genero varchar(100),
dt_lancamento date,
nr_viws decimal(15,5),
nr_likes decimal(15,5),
ds_duracao decimal(15,5)
);
```
> Insert script
```sql=
insert into tb_musica ( nm_nome, nm_artista, ds_album, ds_genero , dt_lancamento, nr_viws, nr_likes, ds_duracao)
values ("alo bebe", "henrique e juliano", "ao vivo no ibirabuera", "sertanejo", "2020-02-21", "106.031", "335.000", "2.49"),
("espetinho", "gustavo lima", "embaixador", "sertanejo", "2020-11-20", "112.016", "555 ", "2.57"),
("relaxa o coracao", "henrique e juliano", "ao vivo no ibirabuera", "sertanejo", "2020-02-07", "18.619", "161.000", "2.36")
```
> Select script
```sql=
select *
from tb_musica
where dt_lancamento >= "2020-02-21"
order
by dt_lancamento desc;
select *
from tb_musica
where nr_likes = "161.000
```
# modelagem 5 : Concessionaria
> Create script
```sql=
create table tb_carro (
Id_carro int primary key auto_increment,
ds_modelo varchar(100),
ds_marca varchar (100),
dt_ano_fabricacao date,
dt_ano_modelo date,
bt_direcao_hidraulica bool,
bt_Arcondicionado bool,
vl_kilometragem decimal(10,3),
vl_preco decimal(10,3),
ds_cor varchar(100)
);
```
> Insert script
```sql=
insert into tb_carro
(ds_modelo,ds_marca,dt_ano_fabricacao,dt_ano_modelo,bt_direcao_hidraulica,bt_Arcondici
onado,vl_kilometragem, vl_preco,ds_cor)
values
('honda fit','honda','2015-01-01','2015-01-01',1,1,54.000,52.990,'prata'),
('tucson','hyundai','2014-01-01','2014-01-01',1,1,120.000,35.800,'prata'),
('hyndai i30 cw','hyndai','2012-01-01','2013-01-01',0,1,11.000,37.900,'prata'),
('new civic','honda','2007-01-01','2007-01-01',1,1,200.000,18.000,'prata'),
('dodgre ram 25000 laramie','dogde ram','2019-01-01','2020-01- 01',1,1,10.000,380.000,'preta'),
('celta','chevroplet','1980-01-01','1980-01-01',0,0,93.000,3.990,'prata'),
('gol g2','volkswagen','1998-01-01','1997-01-01',0,0,123.544,12.990,'marrom')
('gol 97','volkswagen','1997-01-01','1997-01-01',0,0,158.000,16.000,'branca'),
('palio','fiat','1998-01-01','1998-01-01',0,1,256.000,15.000,'cinza'),
('new tucson','hyundai','2020-01-01','2021-01-01',1,1,0.000,100.000,'preta');
```
> Select script
```sql=
Select *
From tb_carro
Where vl_preco >= 40.000
And bt_direcao_hidraulica = true;
```
# Modelagem 6: Computador
> Create script
```sql=
create table tb_computador (
id int primary key auto_increment,
ds_marca varchar(100),
ds_processador varchar(100),
ds_memoria_ram varchar(100),
ds_amarzenamento varchar(100),
ds_sistema varchar(100),
bt_office bool,
nr_tamanho decimal(15,5),
vl_preco decimal(10.5)
);
```
> Insert script
```sql=
insert into tb_computador ( ds_marca, ds_processador, ds_memoria_ram, ds_amarzenamento, ds_sistema, bt_office, nr_tamanho, vl_preco)
values ("dell", "intel- i5", "4 gb", "ssd 128 gb", "windows", false, "15.6", "3.499"),
("positivo", "intel-13", "4 gb", "ssd 128 gb", "linux", false, "14.0", "1.699"),
("dell", "intel core-i5", "8 gb", "ssd 258 gb", "windows", false, "15.6", "3.999")
```
> Select script
```sql=
select *
from tb_computador
where ds_marca = "dell";
select *
from tb_computador
where ds_memoria_ram not like ds_amarzenamento;
Sign in with Wallet
Connect another wallet