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),
CONSTRAINT CK_SEXO CHECK (SEXO='M' OR SEXO = 'F' )
);

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 

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

--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 A.DESCRICAO
FROM EDITORA AS A
WHERE 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 INNER JOIN LIVRO AS L
     ON (E.COD_EDITORA = L.COD_EDITORA)

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

-- 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.PRECO 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)
	 
--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
ORDER BY PRECO DESC

--15. Apresentar o nome dos autores 
--que nasceram no mês de outubro 

SELECT A.NOME, A.DATA_NASCIMENTO
FROM AUTOR AS A
WHERE EXTRACT(MONTH FROM A.DATA_NASCIMENTO) = 10

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

SELECT COUNT (*) AS NUMERO_LIVROS
FROM LIVRO AS L

SELECT COUNT (L.TITULO) AS NUMERO_LIVROS
FROM LIVRO AS L

SELECT COUNT (DISTINCT L.COD_EDITORA) AS NUMERO_LIVROS
FROM LIVRO AS L

SELECT COUNT (E.ENDERECO)
FROM EDITORA AS E

--17. Apresentar o número de autores do 
--livro ‘Banco de Dados’ 
-- PODERIA SER COUNT (*) OU COUNT (LA.COD_AUTOR)
SELECT COUNT (DISTINCT LA.COD_AUTOR) AS QTDE_AUTORES
FROM LIVRO AS L INNER JOIN LIVRO_AUTOR AS LA
     ON (L.COD_LIVRO = LA.COD_LIVRO)
WHERE L.TITULO = 'Banco de Dados'

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

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

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

SELECT AVG (L.PRECO) AS MEDIA_CAMPUS
FROM EDITORA AS E INNER JOIN LIVRO AS L
     ON (E.COD_EDITORA = L.COD_EDITORA)
WHERE E.COD_EDITORA = 1

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

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

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


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

SELECT MIN (A.DATA_NASCIMENTO)
FROM AUTOR AS A

--22. Apresentar o número de livros por editora 

SELECT E.DESCRICAO, COUNT (L.TITULO) 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 
24. Apresentar o número de autores por livro, mas apenas dos livros que 
possuem mais de 1 autor 
25. Apresentar a média de preços geral por editora, mas apenas as editoras que 
possuem média maior que R$ 80,00 
26. Apresentar o nome dos autores que não são autores do livro Banco de 
Dados 
27. Apresentar a quantidade de livros da editora Campus e Abril em colunas 
diferentes.