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 (45) 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 (25) NOT NULL,
	TITULO VARCHAR (45) NOT NULL,
	NUM_EDICAO INT NOT NULL,
	PRECO FLOAT NOT NULL,
	COD_EDITORA INT NOT NULL,
	CONSTRAINT PK_LIVRO 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.0, 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','01/01/1970'),
			(2, 'Maria', 'F', '17/05/1974'),
			(3, 'José', 'M', '10/10/1977'),
			(4, 'Carla', 'F', '08/12/1964');
			
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;

--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. 

-- ASCENDENTE POR NOME
SELECT NOME, DATA_NASCIMENTO
FROM AUTOR
ORDER BY NOME;

-- DESCEDENTE POR NOME
SELECT NOME, DATA_NASCIMENTO
FROM AUTOR
ORDER BY NOME DESC;

--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; 

-- O INVERSO, POSSUI ENDEREÇO
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 LIVRO AS L, EDITORA AS E
WHERE L.COD_EDITORA = E.COD_EDITORA

SELECT E.DESCRICAO, L.TITULO 
FROM LIVRO AS L INNER JOIN EDITORA AS E
     ON (L.COD_EDITORA = E.COD_EDITORA)
	 
INSERT INTO EDITORA (COD_EDITORA, DESCRICAO)
VALUES (3, 'EDITORA TESTE');
-- RECUPERANDO EDITORA QUE NAO TEM LIVRO
SELECT E.DESCRICAO, L.TITULO 
FROM LIVRO AS L RIGHT JOIN EDITORA AS E
     ON (L.COD_EDITORA = E.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. 

SELECT E.DESCRICAO, L.TITULO 
FROM LIVRO AS L RIGHT JOIN EDITORA AS E
     ON (L.COD_EDITORA = E.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)

INSERT INTO LIVRO_AUTOR VALUES (3,4);

SELECT DISTINCT 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 E.DESCRICAO, A.NOME
FROM EDITORA AS E, LIVRO AS L, LIVRO_AUTOR AS LA, AUTOR AS A
WHERE E.COD_EDITORA = L.COD_EDITORA AND
      L.COD_LIVRO = LA.COD_LIVRO AND
	  LA.COD_AUTOR = A.COD_AUTOR
ORDER BY A.DATA_NASCIMENTO

--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%'

SELECT L.TITULO
FROM LIVRO AS L
WHERE L.TITULO LIKE '__D%'

SELECT L.TITULO
FROM LIVRO AS L
WHERE UPPER(L.TITULO) LIKE '%O_'

--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, A.DATA_NASCIMENTO
FROM AUTOR A
WHERE EXTRACT ('MONTH' FROM A.DATA_NASCIMENTO) = 10;

--16. Apresentar o número de livros do acervo 

SELECT COUNT (*) AS TOTAL_LIVROS
FROM LIVRO AS L

SELECT * FROM EDITORA
SELECT COUNT (*) FROM EDITORA
SELECT COUNT (ENDERECO) FROM EDITORA

--17. Apresentar o número de autores do livro ‘Banco de Dados’ 

SELECT COUNT (COD_AUTOR) AS QTDE_AUTOR
FROM LIVRO AS L INNER JOIN LIVRO_AUTOR AS LA
     ON (L.COD_LIVRO = LA.COD_LIVRO)
WHERE L.TITULO = 'BANCO DE DADOS'

SELECT * FROM LIVRO_AUTOR
SELECT COUNT (DISTINCT COD_AUTOR) FROM LIVRO_AUTOR

SELECT COUNT (DISTINCT A.NOME) AS QTDE_AUTORES
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)
WHERE L.TITULO LIKE 'B%' OR L.TITULO LIKE 'S%'  

--18. Apresentar o somatório dos preços dos livros do acervo 

SELECT SUM (L.PRECO) AS SOMA
FROM LIVRO AS L

SELECT L.TITULO
FROM LIVRO AS L
WHERE L.PRECO = (SELECT MAX (L.PRECO) AS MAIOR_PRECO
FROM LIVRO AS L)

19. Apresentar a média de preços dos livros da editora Campus 

SELECT AVG (L.PRECO) AS MEDIA
FROM LIVRO AS L

20. Apresentar o maior preço dentre todos os livros do acervo. 

SELECT * FROM LIVRO

SELECT MAX (L.PRECO) AS MAIOR_PRECO
FROM LIVRO AS L


SELECT L.TITULO, L.PRECO
FROM LIVRO AS L
WHERE L.PRECO = (SELECT MAX (L.PRECO) AS MAIOR_PRECO
FROM LIVRO AS L)

21. Apresentar a data de nascimento do autor mais velho 

SELECT MIN (A.DATA_NASCIMENTO) AS MENOR_DATA
FROM AUTOR AS A

SELECT A.NOME
FROM AUTOR AS A
WHERE A.DATA_NASCIMENTO = (SELECT MIN (A.DATA_NASCIMENTO) AS MENOR_DATA
FROM AUTOR AS A)

22. Apresentar o número de livros por editora 

SELECT E.DESCRICAO, COUNT (DISTINCT L.TITULO) AS TOTAL_LIVROS
FROM EDITORA AS E LEFT JOIN LIVRO AS L
     ON (E.COD_EDITORA = L.COD_EDITORA) 
GROUP BY E.COD_EDITORA

SELECT *
FROM EDITORA AS E LEFT JOIN LIVRO AS L
     ON (E.COD_EDITORA = L.COD_EDITORA) 

*Apresentar a editora que não tem livro publicado

SELECT E.DESCRICAO
FROM EDITORA AS E LEFT JOIN LIVRO AS L
     ON (E.COD_EDITORA = L.COD_EDITORA)
WHERE L.COD_LIVRO IS NULL	

SELECT E.COD_EDITORA, E.DESCRICAO
FROM EDITORA AS E
WHERE E.COD_EDITORA NOT IN 
      (SELECT L.COD_EDITORA
	  FROM LIVRO AS L)

SELECT E.COD_EDITORA, E.DESCRICAO 
FROM EDITORA AS E
WHERE NOT EXISTS (
SELECT *
FROM LIVRO AS L
WHERE E.COD_EDITORA = L.COD_EDITORA)

--23. Apresentar o somatório e média de preço dos livros por editora 

SELECT E.DESCRICAO, SUM (L.PRECO) AS SOMA, 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 (DISTINCT A.NOME) AS QTD_AUTOR
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 (DISTINCT A.NOME) AS QTD_AUTOR
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 T
WHERE T.QTD_AUTOR > 1

SELECT L.TITULO, COUNT (DISTINCT A.NOME) AS QTD_AUTOR
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 (DISTINCT A.NOME) > 1
ORDER BY QTD_AUTOR, L.TITULO DESC

--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 (L.PRECO) AS MEDIA
FROM EDITORA AS E INNER JOIN LIVRO AS L
     ON (E.COD_EDITORA = L.COD_EDITORA) 
GROUP BY E.COD_EDITORA
HAVING AVG (L.PRECO) > 80

--26. Apresentar o nome dos autores que não são autores do 
--livro Banco de Dados 

SELECT AT.NOME
FROM AUTOR AS AT
WHERE AT.NOME NOT IN (
SELECT 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)
WHERE L.TITULO ='BANCO DE DADOS')

27. Apresentar a quantidade de livros da editora Campus e Abril em colunas 
diferentes.

SELECT E.DESCRICAO, COUNT (DISTINCT L.TITULO) AS TOTAL_LIVROS
FROM EDITORA AS E INNER JOIN LIVRO AS L
     ON (E.COD_EDITORA = L.COD_EDITORA) 
GROUP BY E.COD_EDITORA


SELECT SUM (CAMPUS) AS CAMPUS, SUM (ABRIL) AS ABRIL 
FROM
(SELECT COUNT (DISTINCT L.TITULO) AS CAMPUS, 0 AS ABRIL
FROM EDITORA AS E INNER JOIN LIVRO AS L
     ON (E.COD_EDITORA = L.COD_EDITORA) 
WHERE E.COD_EDITORA = 1
UNION ALL
SELECT 0 AS CAMPUS,  COUNT (DISTINCT L.TITULO) AS ABRIL
FROM EDITORA AS E INNER JOIN LIVRO AS L
     ON (E.COD_EDITORA = L.COD_EDITORA) 
WHERE E.COD_EDITORA = 2) AS T