GABARITO - Problema de modelagem 2

Modele no TOAD e resolva as seguintes consultas
Foi criado um sistema de compra de ingressos em teatros. São armazenados o nome, valor do
ingresso e a data em que o espetaculo acontecerá em determinado teatro. Um cliente que queira
comprar um ingresso, deve fornecer o seu nome e telefone de contato. O ingresso está associado a
uma determinada cadeira do teatro, válida apenas para aquele espetáculo. Ao comprar o ingresso, a
cadeira deve ser marcada como ocupada.
Realizar as seguintes consultas.
•
Quais as cadeiras vazias para os espetáculos que começam com a letra 'A',
•
Qual cliente comprou mais ingressos.
•
Que cliente comprou ingressos para cadeiras pares ?
•
Que espetáculos possuem mais cadeiras ocupadas?
/*
Created
Modified
Project
Model
Company
Author
Version
Database
*/
13/10/2016
13/10/2016
PostgreSQL 8.1
/* Create Tables */
Create table cliente
(
cod_cliente Char(20) NOT NULL,
nome_cliente Varchar(50),
telefone_cliente Varchar(10),
primary key (cod_cliente)
) Without Oids;
Create table espetaculo
(
cod_espetaculo Integer NOT NULL,
nome_espetaculo Varchar(50),
valor_espetaculo Double precision,
data_espetaculo Date,
primary key (cod_espetaculo)
) Without Oids;
Create table cadeira
(
cod_cadeira Integer NOT NULL,
fila_cadeira Char(1),
num_cadeira Integer,
primary key (cod_cadeira)
) Without Oids;
Create table ingresso
(
cod_espetaculo Integer NOT NULL,
cod_cliente Char(20),
cod_cadeira Integer NOT NULL,
ocupado Boolean,
primary key (cod_espetaculo,cod_cadeira)
) Without Oids;
/* Create Foreign Keys */
Alter table ingresso add foreign key (cod_cliente) references cliente (cod_cliente) on update
restrict on delete restrict;
Alter table ingresso add foreign key (cod_espetaculo) references espetaculo (cod_espetaculo)
on update restrict on delete restrict;
Alter table ingresso add foreign key (cod_cadeira) references cadeira (cod_cadeira) on update
restrict on delete restrict;
insert into cliente (cod_cliente, nome_cliente,
telefone_cliente) values
(1, 'Grinaldo','3350-8787'),
(2,'Emanuel','2345-1213'),
(3, 'Isis', '7654-6565');
insert into espetaculo (cod_espetaculo,
nome_espetaculo, valor_espetaculo,
data_espetaculo)
values
(1,'Peppa', 40.00, '2016/10/01'),
(2,'Patati Patata', 60.00, '2016/10/06'),
(3,'Galinha Pintadinha', 100.00, '2016/10/10');
insert into cadeira (cod_cadeira, fila_cadeira,
num_cadeira)
values
(1,'A',1),
(2,'B',1),
(3,'C',1);
insert into ingresso
select cod_espetaculo, null as cod_cliente, cod_cadeira, 'false'
from espetaculo, cadeira
update ingresso
set cod_cliente = 3, ocupado = 'true'
where cod_espetaculo = 3 and cod_cadeira=3;
select s.cod_cadeira
from espetaculo as e inner join ingresso as s
on (e.cod_espetaculo = s.cod_espetaculo)
where s.ocupado = 'true'
and e.nome_espetaculo like 'G%'
select c.nome_cliente, count (*) as qtde
from cliente as c inner join ingresso as i
on (c.cod_cliente = i.cod_cliente)
group by c.cod_cliente
having count (*) =
(select max (qtde)
from
(select c.nome_cliente, count (*) as qtde
from cliente as c inner join ingresso as i
on (c.cod_cliente = i.cod_cliente)
group by c.cod_cliente
order by qtde desc) as tabela)
select nome_espetaculo, count (*) as qtde
from espetaculo as e inner join ingresso as i
on (e.cod_espetaculo = i.cod_espetaculo)
where i.ocupado = 'true'
group by e.cod_espetaculo
select c.nome_cliente, d.num_cadeira
from cliente as c inner join ingresso as i
on (c.cod_cliente = i.cod_cliente)
inner join cadeira as d
on (i.cod_cadeira = d.cod_cadeira)
where d.num_cadeira % 2 = 1;
select nome_espetaculo, count (*) as qtde
from espetaculo as e inner join ingresso as i
on (e.cod_espetaculo = i.cod_espetaculo)
where i.ocupado = 'true'
group by e.cod_espetaculo
having count (*) =
(select max (qtde)
from
(select nome_espetaculo, count (*) as qtde
from espetaculo as e inner join ingresso as i
on (e.cod_espetaculo = i.cod_espetaculo)
where i.ocupado = 'true'
group by e.cod_espetaculo) as tabela)