-- EXERCICIO 75 QUESTÕES PROFERROR THIADO ELIAS

-- CRIAÇÃO DA TABELA ASSUNTO (1,N)
CREATE TABLE ASSUNTO
(
    COD_ASSUNTO INT NOT NULL,
    DECRICAO VARCHAR(50),
    CONSTRAINT PK_ASSUNTO PRIMARY KEY(COD_ASSUNTO)
);

-- CRIAÇÃO DA TABELA EDITORA (1,N)
CREATE TABLE EDITORA
(
    COD_EDITORA INT NOT NULL,
    CNPJ INT NOT NULL,
    RAZAO_SOCIAL VARCHAR(50),
    CONSTRAINT PK_EDITORA PRIMARY KEY(COD_EDITORA)
);

-- CRIAÇÃO DA TABELA LIVRO (N,N)
CREATE TABLE LIVRO
(
    COD_LIVRO INT NOT NULL,
    ISBN INT NOT NULL,
    TITULO VARCHAR(30),
    PRECO FLOAT NOT NULL,
    DATA_NASCIMENTO DATE,
    COD_ASSUNTO INT NOT NULL REFERENCES ASSUNTO(COD_ASSUNTO),
    COD_EDITORA INT NOT NULL REFERENCES EDITORA(COD_EDITORA),
    CONSTRAINT PK_LIVRO PRIMARY KEY(COD_LIVRO)
);

-- CRIANDO TABELA NACIONALIDADE
CREATE TABLE NACIONALIDADE
(
    COD_NACIONALIDADE INT NOT NULL,
    DESCRICAO VARCHAR(50),
    CONSTRAINT PK_NACIONALIDADE PRIMARY KEY(COD_NACIONALIDADE)
);

-- CRIANDO A TABELA AUTOR
CREATE TABLE AUTOR
(
    COD_AUTOR INT NOT NULL,
    CPF INT NOT NULL,
    NOME VARCHAR(30),
    DATA_NASCIMENTO DATE NOT NULL,
    COD_NACIONALIDADE INT NOT NULL REFERENCES NACIONALIDADE(COD_NACIONALIDADE),
    CONSTRAINT PK_COD_AUTOR PRIMARY KEY(COD_AUTOR)
);

-- CRIANDO A TABELA AUTOR LIVRO (N,N)
CREATE TABLE AUTOR_LIVRO
(
    COD_AUTOR_LIVRO INT NOT NULL,
    COD_AUTOR INT NOT NULL REFERENCES AUTOR(COD_AUTOR),
    COD_LIVRO INT NOT NULL REFERENCES LIVRO(COD_LIVRO),
    CONSTRAINT PK_AUTOR_LIVRO PRIMARY KEY(COD_AUTOR_LIVRO)
);

INSERT INTO ASSUNTO VALUES(1);
INSERT INTO ASSUNTO VALUES(2);
INSERT INTO ASSUNTO VALUES(3);
INSERT INTO ASSUNTO VALUES(4);
INSERT INTO ASSUNTO VALUES(5);
INSERT INTO ASSUNTO VALUES(6);
INSERT INTO ASSUNTO VALUES(7);
INSERT INTO ASSUNTO VALUES(8);
INSERT INTO ASSUNTO VALUES(9);
INSERT INTO ASSUNTO VALUES(10);
INSERT INTO ASSUNTO VALUES(11, 'ESTRUTURA DE DADOS');

INSERT INTO EDITORA VALUES(1, 11111);
INSERT INTO EDITORA VALUES(2, 22222);
INSERT INTO EDITORA VALUES(3, 33333);
INSERT INTO EDITORA VALUES(4, 44444);
INSERT INTO EDITORA VALUES(5, 55555);
INSERT INTO EDITORA VALUES(6, 66666);
INSERT INTO EDITORA VALUES(7, 77777);
INSERT INTO EDITORA VALUES(8, 88888);
INSERT INTO EDITORA VALUES(9, 99999);
INSERT INTO EDITORA VALUES(10, 101010);

INSERT INTO LIVRO VALUES(1, 111111, 'GAME OF TRHONES', 150.00, '2000-01-01', 1, 1);
INSERT INTO LIVRO VALUES(2, 222222, 'NARUTO UZUMAKI', 560.00, '1990-02-01', 2, 2);
INSERT INTO LIVRO VALUES(3, 333333, 'THE LAST KINGDOM', 86.00, '2005-10-01', 3, 3);
INSERT INTO LIVRO VALUES(4, 444444, 'BOKU NO HERO', 1000.00, '2010-06-20', 4, 4);
INSERT INTO LIVRO VALUES(5, 555555, 'ONE PIECE', 780.00, '2012-09-04', 5, 5);
INSERT INTO LIVRO VALUES(6, 666666, 'FAIRY TAIL', 628.00, '2013-03-09', 6, 6);
INSERT INTO LIVRO VALUES(7, 777777, 'DOUTOR HOUSE', 740.00, '2014-03-08', 7, 7);
INSERT INTO LIVRO VALUES(8, 888888, 'LA CASA DE PAPEL', 835.00, '2015-08-15', 8, 8);
INSERT INTO LIVRO VALUES(9, 999999, 'GRAVITY FALLS', 368.00, '2016-04-07', 9, 9);
INSERT INTO LIVRO VALUES(10, 101010, 'SHIGEKY NO KYOJIN', 1120.00, '2017-05-05', 10, 10);
INSERT INTO LIVRO VALUES(11, 1111111, 'BANCO', 220.00, '2015-04-05', 6, 6);
INSERT INTO LIVRO VALUES(12, 2222222, 'BANCO', 320.00, '2011-08-05', 2, 2);
INSERT INTO LIVRO VALUES(13, 3333333, 'BANCO DE DADOS', 215.00, '2018-09-05', 8, 8);
INSERT INTO LIVRO VALUES(14, 4444444, 'BANCO DE DADO', 222.00, '2019-04-05', 3, 3);
INSERT INTO LIVRO VALUES(15, 5555555, 'ESTRUTURA DE DADOS', 880.00, '2018-03-01', 7, 7);
INSERT INTO LIVRO VALUES(16, 6666666, 'ESTRUTURA', 880.00, '2018-03-01', 11, 7);

INSERT INTO NACIONALIDADE VALUES(1, 'BRASIL');
INSERT INTO NACIONALIDADE VALUES(2, 'ALEMANHA');
INSERT INTO NACIONALIDADE VALUES(3, 'PARAGUAI');
INSERT INTO NACIONALIDADE VALUES(4, 'PORTUGAL');
INSERT INTO NACIONALIDADE VALUES(5, 'ARGENTINA');
INSERT INTO NACIONALIDADE VALUES(6, 'ESTADOS UNIDOS');
INSERT INTO NACIONALIDADE VALUES(7, 'EUROPA');
INSERT INTO NACIONALIDADE VALUES(8, 'LONDRES');
INSERT INTO NACIONALIDADE VALUES(9, 'CHELSEA');
INSERT INTO NACIONALIDADE VALUES(10, 'ESPANHA');

INSERT INTO AUTOR VALUES(1, 1111, 'NELSON', '2018-05-10', 1);
INSERT INTO AUTOR VALUES(2, 2222, 'FELIPE', '2017-04-10', 2);
INSERT INTO AUTOR VALUES(3, 3333, 'LUAN', '2016-03-11', 3);
INSERT INTO AUTOR VALUES(4, 4444, 'ANA', '2015-02-18', 4);
INSERT INTO AUTOR VALUES(5, 5555, 'ADRIANA', '2014-09-9', 5);
INSERT INTO AUTOR VALUES(6, 6666, 'GEOVANA', '2013-07-01', 6);
INSERT INTO AUTOR VALUES(7, 7777, 'IGOR', '2012-08-09', 7);
INSERT INTO AUTOR VALUES(8, 8888, 'FERNANDA', '2011-04-07', 8);
INSERT INTO AUTOR VALUES(9, 9999, 'ROGERIO', '2010-06-10', 9);
INSERT INTO AUTOR VALUES(10, 1010, 'CLAUDYA', '2018-01-03', 10);
INSERT INTO AUTOR VALUES(11, 1010, 'CLAUDYA', '2018-01-03', 10);
INSERT INTO AUTOR VALUES(12, 1001, 'RUAN', '2017-01-03', 10);
INSERT INTO AUTOR VALUES(13, 1002, 'GUILHERME', '2017-02-03', 10);

INSERT INTO AUTOR_LIVRO VALUES(1, 1, 1);
INSERT INTO AUTOR_LIVRO VALUES(2, 2, 2);
INSERT INTO AUTOR_LIVRO VALUES(3, 3, 3);
INSERT INTO AUTOR_LIVRO VALUES(4, 4, 4);
INSERT INTO AUTOR_LIVRO VALUES(5, 5, 5);
INSERT INTO AUTOR_LIVRO VALUES(6, 6, 6);
INSERT INTO AUTOR_LIVRO VALUES(7, 7, 7);
INSERT INTO AUTOR_LIVRO VALUES(8, 8, 8);
INSERT INTO AUTOR_LIVRO VALUES(9, 9, 9);
INSERT INTO AUTOR_LIVRO VALUES(10, 10, 10);
INSERT INTO AUTOR_LIVRO VALUES(11, 9, 10);

-- 1º Livros que possuam preços superiores a R$ 50,00
SELECT * FROM LIVRO WHERE PRECO > 50

-- 2º Livros que possuam preços entre R$ 100,00 e R$ 200,00.
SELECT * FROM LIVRO WHERE PRECO >= 100 AND PRECO <= 200

-- 3º Livros cujos títulos possuam a palavra ‘BANCO’.
SELECT * FROM LIVRO WHERE TITULO = 'BANCO'

-- 4º Livros cujos títulos iniciam com a palavra ‘BANCO’.
SELECT * FROM LIVRO WHERE TITULO ILIKE 'BANCO%'

-- 5º Livros cujos títulos terminam com a palavra ‘CO’.
SELECT * FROM LIVRO WHERE TITULO ILIKE '%CO'

-- 6º Livros cujos títulos possuem a expressão ‘Banco de Dados’ ou ‘Banco de Dado’.
SELECT * FROM LIVRO WHERE TITULO = 'BANCO DE DADOS' OR TITULO = 'BANCO DE DADO'

-- 7º Livros que foram lançados há mais de 5 anos.

-- 8º Livros que ainda não foram lançados, ou seja, com a data de lançamento nula.
SELECT * FROM LIVRO WHERE DATA_NASCIMENTO = NULL

-- 9º Livros cujo assunto seja ‘Estrutura de Dados’
SELECT * FROM LIVRO WHERE COD_ASSUNTO IN
(
    SELECT COD_ASSUNTO FROM ASSUNTO WHERE DECRICAO = 'ESTRUTURA DE DADOS'
);

-- 10º Livros cujo assunto tenha código 1, 2 ou 3.
SELECT * FROM LIVRO WHERE COD_ASSUNTO IN (1, 2, 3);

-- 11º Quantidade de livros.
SELECT COUNT(*) FROM LIVRO

-- 12º Quantidade de livros que ainda não foram lançados, ou seja, com a data de lançamento nula.
SELECT COUNT(*) FROM LIVRO WHERE DATA_NASCIMENTO = NULL

-- 13º Somatório dos preços dos livros.
SELECT SUM(PRECO) FROM LIVRO

-- 14º Média de preços dos livros.
SELECT AVG(PRECO) FROM LIVRO

-- 15º Maior preço dos livros.
SELECT MAX(PRECO) FROM LIVRO

-- 16º Menor preço dos livros
SELECT MIN(PRECO) FROM LIVRO

-- 17º O preço médio dos livros para cada assunto.
SELECT DECRICAO, AVG(PRECO) MEDIA_POR_ASSUNTO FROM LIVRO L, ASSUNTO A WHERE
(
    A.COD_ASSUNTO = L.COD_ASSUNTO
)
GROUP BY DECRICAO;

-- 18º Quantidade de livros para cada assunto
SELECT DECRICAO, COUNT(*) QUANTIDADE_POR_ASSUNTO FROM LIVRO L, ASSUNTO A WHERE
(
    L.COD_ASSUNTO = A.COD_ASSUNTO
)
GROUP BY DECRICAO;

-- 19º O preço do livro mais caro de cada assunto, dentre aqueles que já foram lançados.
SELECT DECRICAO, MAX(PRECO) MAIS_CARO_POR_ASSUNTO FROM LIVRO L, ASSUNTO A WHERE
(
    L.COD_ASSUNTO = A.COD_ASSUNTO
)
GROUP BY DECRICAO;

-- 20º Quantidade de livros lançados por editora.
SELECT CNPJ, COUNT(COD_EDITORA) LANCADO_POR_EDITORA FROM EDITORA GROUP BY CNPJ

-- 21º Assuntos cujo preço médio dos livros ultrapassa R$ 50,00.
SELECT * FROM (SELECT DECRICAO, AVG(PRECO) MEDIA_PRECO_POR_ASSUNTO FROM LIVRO L, ASSUNTO A WHERE L.COD_ASSUNTO = A.COD_ASSUNTO GROUP BY DECRICAO)
A1 WHERE MEDIA_PRECO_POR_ASSUNTO > 50

-- 22º Assuntos que possuem pelo menos 2 livros.
SELECT * FROM (SELECT DECRICAO, COUNT(*) QUANTIDADE_LIVRO FROM LIVRO L, ASSUNTO A WHERE L.COD_ASSUNTO = A.COD_ASSUNTO GROUP BY DECRICAO)
A1 WHERE QUANTIDADE_LIVRO < 2

-- 23º Assuntos que possuem pelo menos 2 livros já lançados
SELECT * FROM (SELECT DECRICAO, COUNT(DATA_NASCIMENTO) QUANTIDADE_LIVRO FROM LIVRO L, ASSUNTO A WHERE L.COD_ASSUNTO = A.COD_ASSUNTO GROUP BY DECRICAO)
A1 WHERE QUANTIDADE_LIVRO > 2

-- 24º Quantidade de livros lançados por assunto.
SELECT DECRICAO, COUNT(*) QUANTIDADE_POR_ASSUNTO FROM LIVRO L, ASSUNTO A WHERE
(
    L.COD_ASSUNTO = A.COD_ASSUNTO
)
GROUP BY DECRICAO;

-- 25º Nome e CPF dos autores que possuem a palavra ‘João’ no nome.
SELECT A.NOME, A.CPF FROM AUTOR A WHERE A.NOME IN('JOÃO')

-- 26º Nome e CPF dos autores que nasceram após 1° de janeiro de 1970.
SELECT A.NOME, A.CPF FROM AUTOR A WHERE A.DATA_NASCIMENTO >= '02-01-1970' AND A.DATA_NASCIMENTO <= '31-01-1990'

-- 27º Nome e CPF dos autores que não são brasileiros.
SELECT A.NOME, A.CPF FROM AUTOR A INNER JOIN NACIONALIDADE N ON
A.COD_NACIONALIDADE = N.COD_NACIONALIDADE WHERE N.DESCRICAO NOT IN('BRASIL')
GROUP BY A.NOME, A.CPF

--28º  Quantidade de autores
SELECT COUNT(A.COD_AUTOR) FROM AUTOR A

--29º Quantidade média de autores dos livros.
SELECT AVG(A.COD_AUTOR) MEDIA_AUTOR_LIVRO FROM AUTOR A INNER JOIN AUTOR_LIVRO A_L ON
A.COD_AUTOR = A_L.COD_AUTOR

--30º Livros que possuem ao menos 2 autores.
SELECT L.TITULO LIVROS FROM LIVRO L INNER JOIN AUTOR_LIVRO A_L ON
L.COD_LIVRO = A_L.COD_LIVRO INNER JOIN AUTOR A ON
A_L.COD_AUTOR = A.COD_AUTOR WHERE

SELECT L.TITULO LIVROS FROM LIVRO L INNER JOIN AUTOR_LIVRO A_L ON
L.COD_LIVRO = A_L.COD_LIVRO INNER JOIN AUTOR A ON
A_L.COD_AUTOR = A.COD_AUTOR WHERE A.COD_AUTOR IN (SELECT A.COD_AUTOR FROM AUTOR_LIVRO A_L INNER JOIN AUTOR A ON
A_L.COD_AUTOR = A.COD_AUTOR GROUP BY A.COD_AUTOR HAVING COUNT(A.COD_AUTOR) >= 2)

--31º Preço médio dos livros por editora.
SELECT E.COD_EDITORA, AVG(L.PRECO) PRECO_MEDIO FROM LIVRO L INNER JOIN EDITORA E ON
L.COD_EDITORA = E.COD_EDITORA GROUP BY E.COD_EDITORA

--32º Preço máximo, preço mínimo e o preço médio dos livros cujos códigos do assunto são 1, 2 ou 3, para cada editora.
SELECT E.COD_EDITORA EDITORA, MAX(L.PRECO) PRECO_MAXIMO, MIN(L.PRECO) PRECO_MINIMO, AVG(L.PRECO) PRECO_MEDIO FROM LIVRO L INNER JOIN EDITORA E ON
L.COD_EDITORA = E.COD_EDITORA INNER JOIN ASSUNTO A ON
A.COD_ASSUNTO = E.COD_EDITORA WHERE A.COD_ASSUNTO IN(1, 2, 3) GROUP BY E.COD_EDITORA

--33º Quantidade de autores para cada nacionalidade.
SELECT N.DESCRICAO NACIONALIDADE, COUNT(A.CPF) QUANTIDADE_AUTORES FROM NACIONALIDADE N INNER JOIN AUTOR A ON
A.COD_NACIONALIDADE = N.COD_NACIONALIDADE GROUP BY N.DESCRICAO

--34º Quantidade de autores que nasceram antes de 1°de janeiro de 1920, para cada nacionalidade.
SELECT N.DESCRICAO NACIONALIDADE, COUNT(A.CPF) QUANTIDADE_AUTORES FROM NACIONALIDADE N INNER JOIN AUTOR A ON
N.COD_NACIONALIDADE = A.COD_NACIONALIDADE WHERE A.DATA_NASCIMENTO NOT IN(SELECT A.DATA_NASCIMENTO FROM AUTOR A WHERE A.DATA_NASCIMENTO >= '2018-01-01' AND A.DATA_NASCIMENTO <= '2018-01-31')
GROUP BY N.DESCRICAO

--35º A data de nascimento do autor mais velho.
SELECT MIN(A.DATA_NASCIMENTO) AUTOR_MAIS_VELHO FROM AUTOR A

--36º  A data de nascimento do autor mais novo.
SELECT MAX(A.DATA_NASCIMENTO) AUTOR_MAIS_VELHO FROM AUTOR A

--37º Os novos preços dos livros se os valores fossem reajustados em 10%.
SELECT L.TITULO LIVROS, SUM(L.PRECO*1.1) NOVO_PRECO FROM LIVRO L
GROUP BY L.TITULO

SELECT * FROM NACIONALIDADE N INNER JOIN AUTOR A ON
N.COD_NACIONALIDADE = A.COD_NACIONALIDADE