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