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_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.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','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.10;

--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 A.NOME, A.DATA_NASCIMENTO 
FROM AUTOR AS A
ORDER BY A.NOME

--6. Apresentar o nome e a data de nascimento dos autores do sexo feminino 
--ordenados pelo nome. 

SELECT A.NOME, A.DATA_NASCIMENTO
FROM AUTOR AS A
WHERE A.SEXO = 'F'
ORDER BY A.NOME 

--7. Apresentar o nome das editoras que não tem o endereço cadastrado. 

SELECT E.DESCRICAO
FROM EDITORA AS E
WHERE E.ENDERECO IS 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. 

SELECT L.TITULO, E.DESCRICAO
FROM EDITORA AS E LEFT JOIN LIVRO AS L
	 ON (E.COD_EDITORA = L.COD_EDITORA)

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

SELECT E.DESCRICAO
FROM EDITORA AS E
WHERE E.COD_EDITORA NOT IN
	(SELECT L.COD_EDITORA
	FROM LIVRO AS L)
	
SELECT E.DESCRICAO
FROM EDITORA AS E
WHERE NOT EXISTS (  
	SELECT *
	FROM LIVRO AS L
	WHERE L.COD_EDITORA = E.COD_EDITORA)
	
DELETE FROM EDITORA
WHERE DESCRICAO IN
(SELECT E.DESCRICAO
FROM EDITORA AS E
WHERE E.COD_EDITORA NOT IN
	(SELECT L.COD_EDITORA
	FROM LIVRO AS L))
	
UPDATE EDITORA
SET ENDERECO = "BLABLABLA"
WHERE DESCRICAO IN
(SELECT E.DESCRICAO
FROM EDITORA AS E LEFT JOIN LIVRO AS L
	 ON (E.COD_EDITORA = L.COD_EDITORA)
WHERE L.ISBN IS NULL)

--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 DESCRICAO
FROM EDITORA AS E, LIVRO AS L

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

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

--- COMEÇA COM QUALQUER COISA (dois caracteres), 
--tem nc depois e termina com qualquer coisa

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

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

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

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

SELECT *
FROM LIVRO AS L
WHERE PRECO>=70 AND PRECO <=85

SELECT *
FROM LIVRO AS L
WHERE PRECO BETWEEN 70 AND 85

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

SELECT COUNT (*) AS TOTAL
FROM LIVRO AS L

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

SELECT L.TITULO, COUNT (*) AS QTDE_AUTOR
FROM LIVRO AS L INNER JOIN LIVRO_AUTOR AS LA
     ON (L.COD_LIVRO = LA.COD_LIVRO)
WHERE L.COD_LIVRO = 1
GROUP BY L.TITULO

SELECT L.TITULO, COUNT (*) AS QTDE_AUTOR
FROM LIVRO AS L INNER JOIN LIVRO_AUTOR AS LA
     ON (L.COD_LIVRO = LA.COD_LIVRO)
WHERE L.COD_LIVRO = 1
GROUP BY L.COD_LIVRO
--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 MEDIA_CAMPUS
FROM LIVRO AS L
WHERE L.COD_EDITORA = 1
--20. Apresentar o maior preço dentre todos 
--os livros do acervo. 
SELECT MAX (PRECO) AS MAIOR
FROM LIVRO AS L

SELECT L.TITULO
FROM LIVRO AS L
WHERE L.PRECO IN
	(SELECT MAX (PRECO) AS MAIOR
	FROM LIVRO AS L2)

--21. Apresentar a data de nascimento do autor 
--mais velho 

SELECT MIN (DATA_NASCIMENTO) AS DATA
FROM AUTOR AS A
SELECT * FROM EDITORA
--22. Apresentar o número de livros por editora 
SELECT E.DESCRICAO, COUNT (COD_LIVRO) AS QTDE_LIVRO
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 (PRECO) AS SOMATORIO, 
       AVG (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_AUTOR
FROM LIVRO AS L INNER JOIN LIVRO_AUTOR AS LA
     ON (L.COD_LIVRO = LA.COD_LIVRO)
GROUP BY L.TITULO
HAVING COUNT(*) > 1

SELECT *
FROM
(SELECT L.TITULO, COUNT (*) AS QTDE_AUTOR
FROM LIVRO AS L INNER JOIN LIVRO_AUTOR AS LA
     ON (L.COD_LIVRO = LA.COD_LIVRO)
GROUP BY L.TITULO) AS TABELA
WHERE QTDE_AUTOR > 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) AS MEDIA
FROM EDITORA AS E LEFT 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 *
FROM AUTOR AS A
WHERE A.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.COD_LIVRO = 1)

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

SELECT SUM (CAMPUS) AS CAMPUS, SUM (ABRIL) AS ABRIL, SUM (ED_TESTE) AS ED_TESTE
FROM
(SELECT COUNT(L.COD_LIVRO) AS CAMPUS, 0 AS ABRIL, 0 AS ED_TESTE
FROM LIVRO AS L RIGHT JOIN EDITORA AS E
	 ON (L.COD_EDITORA = E.COD_EDITORA)
WHERE E.COD_EDITORA = 1
GROUP BY E.COD_EDITORA
UNION
SELECT 0 AS CAMPUS, COUNT(L.COD_LIVRO) AS ABRIL, 0 AS ED_TESTE
FROM LIVRO AS L RIGHT JOIN EDITORA AS E
	 ON (L.COD_EDITORA = E.COD_EDITORA)
WHERE E.COD_EDITORA = 2
GROUP BY E.COD_EDITORA
UNION 
SELECT 0 AS CAMPUS,  0 AS ABRIL, COUNT(L.COD_LIVRO) AS ED_TESTE
FROM LIVRO AS L RIGHT JOIN EDITORA AS E
	 ON (L.COD_EDITORA = E.COD_EDITORA)
WHERE E.COD_EDITORA = 3
GROUP BY E.COD_EDITORA) AS TABELA