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 (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_LA 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 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
(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 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 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 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 ENDERECO IS NULL;

--8. Apresentar o título do livro e o
--nome da sua editora

INSERT INTO EDITORA (COD_EDITORA, DESCRICAO, ENDERECO)
VALUES (3,'Editora Teste', NULL);

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

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

--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 QUANTIDADE
FROM LIVRO AS L

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

SELECT L.TITULO, COUNT (*) AS QTDE_AUTORES
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 TOTAL
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_PRECO
FROM LIVRO AS L

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

SELECT MIN (DATA_NASCIMENTO) AS DATA_MAIS_VELHO
FROM AUTOR AS A

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

SELECT E.DESCRICAO, COUNT (L.ISBN) 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 (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 (*) AS QTDE_AUTORES
FROM LIVRO AS L INNER JOIN LIVRO_AUTOR AS LA
ON (L.COD_LIVRO = LA.COD_LIVRO)
GROUP BY L.COD_LIVRO
HAVING COUNT (*) > 1;

SELECT *
FROM
(SELECT L.TITULO, COUNT (*) AS QTDE_AUTORES
FROM LIVRO AS L INNER JOIN LIVRO_AUTOR AS LA
ON (L.COD_LIVRO = LA.COD_LIVRO)
GROUP BY L.COD_LIVRO) AS TAB
WHERE TAB.QTDE_AUTORES > 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 (L.PRECO) AS MEDIA
FROM LIVRO AS L INNER JOIN EDITORA AS E
ON (L.COD_EDITORA = E.COD_EDITORA)
GROUP BY E.COD_EDITORA
HAVING AVG (L.PRECO) > 80

-- EXTRA APRESENTE A EDITORA QUE NÃO TEM LIVRO

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.DESCRICAO
FROM EDITORA AS E
WHERE E.COD_EDITORA NOT IN
(SELECT L.COD_EDITORA
FROM LIVRO AS L)

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

SELECT A.NOME
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
FROM
(SELECT COUNT (L.ISBN) 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
GROUP BY E.COD_EDITORA
UNION
SELECT 0 AS CAMPUS, COUNT (L.ISBN) AS ABRIL
FROM EDITORA AS E INNER JOIN LIVRO AS L
ON (E.COD_EDITORA = L.COD_EDITORA)
WHERE E.COD_EDITORA = 2
GROUP BY E.COD_EDITORA) AS TAB

UPDATE LIVRO
SET PRECO = 200
WHERE COD_LIVRO IN
(SELECT L.COD_LIVRO
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 E.DESCRICAO LIKE 'C%' AND A.NOME LIKE 'J%')