/*drop table LIVROAUTOR;
drop table AUTOR;
drop table LIVRO;
*/
--1
CREATE TABLE AUTOR (
IDAUTOR SERIAL not NULL,
NOME VARCHAR(200) NOT null,
constraint PK_AUTOR primary key (IDAUTOR));
CREATE TABLE LIVRO (
IDLIVRO serial not NULL,
TITULO VARCHAR(200) NOT NULL,
RESUMO VARCHAR(4000),
DATALANCTO DATE not null,
DATAULTIMOEMPRESTIMO DATE,
constraint PK_LIVRO PRIMARY KEY (IDLIVRO)
);
CREATE TABLE LIVROAUTOR (
IDLIVROAUTOR SERIAL not null,
IDLIVRO INT not NULL,
IDAUTOR INT not NULL,
constraint PK_LIVROAUTOR PRIMARY KEY (IDLIVROAUTOR),
constraint FK_LIVAUT_LIVRO FOREIGN KEY (IDLIVRO) REFERENCES LIVRO(IDLIVRO),
constraint FK_LIVAUT_AUTOR FOREIGN KEY (IDAUTOR) REFERENCES AUTOR(IDAUTOR)
);
--2
insert into AUTOR (NOME)
values ('Paula Pimenta'),
('Yuval Noah Harari'),
('J.K ROWLING');
insert into LIVRO (TITULO, RESUMO,
DATALANCTO,
DATAULTIMOEMPRESTIMO)
values ('Harry Potter e a Pedra Filosofal',
'Em Harry Potter e a Pedra Filosofal,
um jovem órfão descobre ser um bruxo',
'26/06/1997', '01/10/2024');
insert into LIVRO (TITULO,
DATALANCTO,
DATAULTIMOEMPRESTIMO)
values ('Sapiens - Uma breve história da humanidade',
'01/01/2011', '09/05/2024');
insert into LIVRO (TITULO, RESUMO,
DATALANCTO)
values ('Harry Potter e a ordem da fenix',
'Em Harry Potter e a Ordem da Fênix,
Harry enfrenta a descrença do mundo bruxo.',
'21/06/2003');
insert into livroautor (idlivro, idautor)
values (1,3),
(3,3),
(2,2);
--3
alter table autor add DATANASCIMENTO date;
alter table livro add valor float default 0 not null;
alter table livro add SITUACAO varchar(50);
alter table livro add constraint
ck_sitlivro check (situacao in ('ATIVO', 'INATIVO'));
--4
--a
update autor
set datanascimento = '02/06/1975'
where idautor = 1;
--b
update livro
set valor = 100
where idlivro = 1;
--c
update livro
set valor = 34.65
where idlivro = 2;
--d
update livro
set valor = 67.98
where idlivro = 3;
--e
update livro
set situacao = 'ATIVO';
--f
update livro
set situacao = 'INATIVO'
where idlivro = 2;
--5
--a
select * from autor;
--b
select count(1) from livro;
--c
select [Link] as "Título livro",
[Link] as "Cod livro",
[Link] as "Nome autor",
[Link] as "Lancto livro"
from livro l, autor a, livroautor la
where [Link] = [Link]
and [Link] = [Link]
order by [Link];
--d
select [Link] as "Cod. autor",
[Link] as "Nome autor",
(select count(1)
from livroautor la
where [Link] = [Link]) as "Quantidade livros"
from autor a;
--e
select [Link] as "Título livro",
[Link] as "Cod. autor",
[Link] as "Nome autor",
[Link] as "Lançamento"
from livroautor la
join livro l on ([Link] = [Link])
join autor a on ([Link] = [Link])
where [Link] between '01/02/2003'
and '03/06/2011';
--f
select sum([Link])
from livro;
--g
select coalesce(sum([Link]),0) as "Valor livros", [Link], [Link]
from autor a
left join livroautor la on ([Link] = [Link])
left join livro l on ([Link] = [Link])
group by [Link], [Link];
--h
select *
from autor a
where (select count(1)
from livroautor l
where [Link] = [Link])<=0;
--i
select *
from livro l
where [Link] is null
or [Link] <= '15/05/2024';
--j
select * from livro where titulo like '%de%';
--k
select *
from livro
where titulo like 'Harry%'
and datalancto <= '01/01/2000';