---
# System prepended metadata

title: Final Seshion | Banco de dados

---

# 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;
   


