Exercicios Bando Dados

create database ex1;

use ex1;

create table tbl_discos(

cod_disco int not null,

titulo_disco varchar(100),

copias_vendidas int,

data_gravacao date,

grupo_interprete varchar(100),

primary key(cod_disco)

);

mysql> describe tbl_discos;

+------------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------------+--------------+------+-----+---------+-------+

| cod_disco | int(11) | NO | PRI | NULL | |

| titulo_disco | varchar(100) | YES | | NULL | |

| copias_vendidas | int(11) | YES | | NULL | |

| data_gravacao | date | YES | | NULL | |

| grupo_interprete | varchar(100) | YES | | NULL | |

+------------------+--------------+------+-----+---------+-------+

5 rows in set (0.01 sec)

create table tbl_musicas(

cod_musica int not null,

titulo_musica varchar(100),

autor_musica varchar(100),

primary key(cod_musica)

);

mysql> describe tbl_musicas;

+---------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------------+--------------+------+-----+---------+-------+

| cod_musica | int(11) | NO | PRI | NULL | |

| titulo_musica | varchar(100) | YES | | NULL | |

| autor_musica | varchar(100) | YES | | NULL | |

+---------------+--------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

create table tbl_discos_musicas(

cod_disco int not null,

cod_musica int not null,

data_selecao date,

constraint fk_CodDisco foreign key (cod_disco) references tbl_discos(cod_disco),

constraint fk_CodMusica foreign key (cod_musica) references tbl_musicas(cod_musica)

);

mysql> describe tbl_discos_musicas;

+--------------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------+---------+------+-----+---------+-------+

| cod_disco | int(11) | NO | MUL | NULL | |

| cod_musica | int(11) | NO | MUL | NULL | |

| data_selecao | date | YES | | NULL | |

+--------------+---------+------+-----+---------+-------+

3 rows in set (0.01 sec)

insert into tbl_musicas values(0,"November Rain","Guns n Roses");

insert into tbl_musicas values(1,"Patience","Guns n Roses");

insert into tbl_musicas values(2,"Estranged","Guns n Roses");

insert into tbl_musicas values(3,"Basket Case","Greenday");

insert into tbl_musicas values(4,"Time of your life","Greenday");

insert into tbl_musicas values(5,"Always","Blink 182");

insert into tbl_musicas values(6,"What's my age again","Blink 182");

insert into tbl_musicas values(7,"Californication","Red Hot Chili Peppers");

insert into tbl_musicas values(8,"Scar Tissue","Red Hot Chili Peppers");

insert into tbl_musicas values(9,"Under the bridge","Red Hot Chili Peppers");

mysql> select * from tbl_musicas;

+------------+---------------------+-----------------------+

| cod_musica | titulo_musica | autor_musica |

+------------+---------------------+-----------------------+

| 0 | November Rain | Guns n Roses |

| 1 | Patience | Guns n Roses |

| 2 | Estranged | Guns n Roses |

| 3 | Basket Case | Greenday |

| 4 | Time of your life | Greenday |

| 5 | Always | Blink 182 |

| 6 | What's my age again | Blink 182 |

| 7 | Californication | Red Hot Chili Peppers |

| 8 |