create table editora (
cod_editora int not null,
descricao varchar (30) not null,
endereco varchar (30) null,
constraint pk_editora
primary key (cod_editora)
);
create table autor (
cod_autor int not null,
nome varchar (30) not null,
sexo char (1) not null,
data_nascimento date not null,
constraint pk_autor
primary key (cod_autor)
);
create table livro(
cod_livro int not null,
isbn varchar (20) not null,
titulo varchar (45) not null,
num_edicao int not null,
preco float not null,
cod_editora int not null,
constraint pk_primary
primary key (cod_livro),
constraint fk_livro_editora
foreign key (cod_editora)
references editora (cod_editora)
);
create table livro_autor (
cod_livro int not null,
cod_autor int not null,
constraint pk_livro_autor
primary key (cod_livro, cod_autor),
constraint fk_la_livro
foreign key (cod_livro)
references livro (cod_livro),
constraint fk_la_autor
foreign key (cod_autor)
references autor (cod_autor)
);
insert into editora (cod_editora, descricao, endereco)
values (1, 'Campus', 'Rua do Timbó'),
(2, 'Abril', null),
(3, 'Editora Teste', null);
insert into livro
(cod_livro,isbn, titulo, num_edicao, preco, cod_editora)
values (1, '12345','Banco de Dados',3, 70.00, 1),
(2, '35790','SGBD',1, 85.00, 2),
(3, '98765','Redes de Computadores',2, 80.00,2);
insert into autor
(cod_autor, nome, sexo, data_nascimento)
values (1,'João','M','1970/01/01'),
(2, 'Maria', 'F', '1974/05/17'),
(3,'José', 'M', '1977/10/10'),
(4, 'Carla', 'F', '1964/12/08');
insert into livro_autor
(cod_livro, cod_autor)
values (1,1),(1,2),(2,2),(2,4),(3,3);
--1. Atualizar o endereço da Editora Campus para ‘Av. ACM’
update editora
set endereco = 'Av. ACM'
where cod_editora = 1;
select * from editora
--2. Atualizar os preços dos livros em 10%
update livro
set preco = preco*1.1;
--3. Excluir a ‘Editora Teste’
delete from editora
where cod_editora = 3;
--4. Apresentar o nome e data de nascimento de
-- todos os autores
select nome, data_nascimento
from autor;
--5. Apresentar o nome e a data de nascimento
--dos autores por ordem de nome.
select nome, data_nascimento
from autor
order by nome;
select data_nascimento+1 as tchutchuca
from autor
order by tchutchuca
--6. Apresentar o nome e a data de nascimento
--dos autores do sexo feminino ordenados pelo nome.
select nome, data_nascimento
from autor
where sexo = 'F'
order by nome
--7. Apresentar o nome das editoras que não tem o
--endereço cadastrado.
select descricao
from editora
where endereco is null;
select descricao
from editora
where endereco is not null;
--8. Apresentar o título do livro e o nome da sua
--editora
select l.titulo, e.descricao
from editora as e, livro as l
where e.cod_editora = l.cod_editora;
select l.titulo, e.descricao
from editora as e inner join livro as l
on (e.cod_editora = l.cod_editora)
--9. Apresentar o título do livro e o nome da sua
--editora. Caso haja alguma editora sem livro publicado,
--informar os dados da editora com valores nulos para
--os livros.
insert into editora (cod_editora, descricao, endereco)
values (3,'teste',null);
select l.titulo, e.descricao
from editora as e left join livro as l
on (e.cod_editora = l.cod_editora)
select l.titulo, e.descricao
from editora as e left join livro as l
on (e.cod_editora = l.cod_editora)
where l.titulo is null;
select e.descricao
from editora as e
where e.cod_editora not in (select cod_editora from livro)
select e.descricao
from editora as e
where not exists (select * from livro as l
where e.cod_editora = l.cod_editora)
--10. Apresentar o título do livro e o nome dos
--seus autores
select l.titulo, a.nome
from livro as l inner join livro_autor as la
on (l.cod_livro = la.cod_livro)
inner join autor as a
on (la.cod_autor = a.cod_autor)
--11. Apresentar o nome da editora e o nome dos autores
-- que já publicaram algum livro na editora.
select e.descricao, a.nome
from editora as e inner join livro as l
on (e.cod_editora = l.cod_editora)
inner join livro_autor as la
on (l.cod_livro = la.cod_livro)
inner join autor as a
on (la.cod_autor = a.cod_autor)
select distinct a.nome
from editora as e inner join livro as l
on (e.cod_editora = l.cod_editora)
inner join livro_autor as la
on (l.cod_livro = la.cod_livro)
inner join autor as a
on (la.cod_autor = a.cod_autor)
--12. Apresentar o título dos livros que começam
--a string ‘Banco’.
select l.titulo
from livro as l
where l.titulo like 'Banco%'
--13. Apresentar o título dos livros que
--tem a string ‘do’.
select l.titulo
from livro as l
where l.titulo like '%do%'
--14. Apresentar o nome de cada livro e
--seu preço reajustado em 5%
select l.titulo, l.preco*1.05 as preco
from livro as l
--15. Apresentar o nome dos autores que
-- nasceram no mês de outubro
select a.nome
from autor as a
where extract (month from a.data_nascimento) = 10
--16. Apresentar o número de livros do acervo
select count (*) as qtde
from livro as l
select count (distinct cod_editora)
from livro as l
select * from livro
select count (endereco) from editora
select * from livro
--17. Apresentar o número de autores do livro ‘Banco de Dados’
select count (*) as qtde
from livro as l inner join livro_autor as la
on (l.cod_livro = la.cod_livro)
inner join autor as a
on (la.cod_autor = a.cod_autor)
where l.titulo like 'Banco de Dados'
--18. Apresentar o somatório dos preços dos livros do acervo
select sum (preco) as somatorio
from livro as l
--19. Apresentar a média de preços dos livros da editora Campus
select avg (preco) as somatorio
from editora as e inner join livro as l
on (e.cod_editora = l.cod_editora)
where e.descricao = 'Campus'
--20. Apresentar o maior preço dentre todos os livros do acervo.
select max (preco)
from livro as l
select titulo
from livro
where preco = (select max (preco)from livro as l)
--21. Apresentar a data de nascimento do autor mais velho
select min (a.data_nascimento)
from autor as a
select a.nome, a.data_nascimento
from autor as a
where a.data_nascimento in (
select min (a.data_nascimento)
from autor as a)
--22. Apresentar o número de livros por editora
select e.descricao, count (l.cod_livro) as qtde
from editora as e left join livro as l
on (e.cod_editora = l.cod_editora)
group by e.cod_editora
--23. Apresentar o somatório e média de preço dos
--livros por editora
select e.descricao, sum (l.preco) as somatorio,
avg (l.preco) as media
from editora as e left join livro as l
on (e.cod_editora = l.cod_editora)
group by e.cod_editora
--24. Apresentar o número de autores por livro,
--mas apenas dos livros que possuem mais de 1 autor
select l.titulo, count (*) as qtde
from livro as l inner join livro_autor as la
on (l.cod_livro = la.cod_livro)
inner join autor as a
on (la.cod_autor = a.cod_autor)
group by l.cod_livro
select *
from
(select l.titulo, count (*) as qtde
from livro as l inner join livro_autor as la
on (l.cod_livro = la.cod_livro)
inner join autor as a
on (la.cod_autor = a.cod_autor)
group by l.cod_livro) as teste
where qtde > 1
select l.titulo, count (*) as qtde
from livro as l inner join livro_autor as la
on (l.cod_livro = la.cod_livro)
inner join autor as a
on (la.cod_autor = a.cod_autor)
group by l.cod_livro
having count(*) > 1
--25. Apresentar a média de preços geral por editora,
--mas apenas as editoras que possuem média maior que R$ 80,00
select e.descricao, avg (preco)
from editora as e inner join livro as l
on e.cod_editora = l.cod_editora
group by e.cod_editora
having avg (preco) > 80
--26. Apresentar o nome dos autores que não são
--autores do livro Banco de Dados
select nome
from autor
where cod_autor not in (
select la.cod_autor
from livro as l inner join livro_autor as la
on l.cod_livro = la.cod_livro
where l.titulo like 'Banco de Dados')
--27. Apresentar a quantidade de livros da editora Campus e
--Abril em colunas diferentes.
select sum (CAMPUS) as CAMPUS, sum (ABRIL) as ABRIL
from
(select count (*) as CAMPUS, 0 as ABRIL
from editora as e inner join livro as l
on e.cod_editora = l.cod_editora
where e.descricao = 'Campus'
UNION
select 0 as CAMPUS, count (*) as ABRIL
from editora as e inner join livro as l
on e.cod_editora = l.cod_editora
where e.descricao = 'Abril') as teste