Created
June 12, 2018 14:39
-
-
Save tuliopaim/817a397583fb3a6672bedb338f907220 to your computer and use it in GitHub Desktop.
ex-17 banco de dados
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| drop database locadora; | |
| create database locadora; | |
| use locadora | |
| create table locadora( | |
| cgc int not null, | |
| nome varchar(30) not null | |
| ); | |
| create table cliente( | |
| preNome varchar(20) not null, | |
| sobreNome varchar(20) not null, | |
| telefone varchar(11) not null, | |
| endereco varchar(50) not null, | |
| numero int not null, | |
| primary key(numero) | |
| ); | |
| create table categoria( | |
| cod int not null, | |
| nome varchar(20), | |
| primary key (cod) | |
| ); | |
| create table filme( | |
| id int not null, | |
| titulo varchar(20), | |
| cod_categoria int not null, | |
| primary key(id), | |
| foreign key(cod_categoria) references categoria(cod) | |
| ); | |
| create table dvd( | |
| numero int not null, | |
| rolo varchar(20) not null, | |
| id_filme int not null, | |
| num_cliente int not null, | |
| primary key (numero), | |
| foreign key (id_filme) references filme(id), | |
| foreign key (num_cliente) references cliente(numero) | |
| ); | |
| create table ator( | |
| dataNasc date, | |
| nomePopular varchar(30) not null, | |
| nomeArtistico varchar(30) not null, | |
| cod int not null, | |
| primary key(cod) | |
| ); | |
| create table estrela( | |
| id_filme int not null, | |
| cod_ator int not null, | |
| primary key(id_filme, cod_ator), | |
| foreign key(id_filme) references filme(id), | |
| foreign key(cod_ator) references ator(cod) | |
| ); | |
| /* | |
| CONSULTAS | |
| a) | |
| SELECT nomeArtistico, COUNT(*) | |
| FROM ATOR JOIN ESTRELA | |
| ON ator.cod_ator = estrela.cod_ator | |
| GROUP BY cod_ator, nomeArtistico | |
| SELECT nomeArtistico, COUNT(*) | |
| FROM ATOR NATURAL JOIN ESTRELA | |
| GROUP BY cod_ator, nomeArtistico | |
| SELECT A.nomeArtistico, | |
| (SELECT COUNT(*) FROM ESTRELA E WHERE A.cod_ator = E.cod_ator) | |
| FROM ATOR A | |
| */ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment