script2BIBLIOTECA.sql

-- 1. Crie um DATABASE chamado BIBLIOTECA

-- 2. Crie uma tabela chamada EDITORA, de acordo com os dados abaixo

CREATE TABLE editora(

	IdEditora SERIAL NOT NULL,
	Nome VARCHAR(30) NOT NULL,

	CONSTRAINT pk_edt_IdEditora PRIMARY KEY (IdEditora),
	CONSTRAINT un_edt_Nome UNIQUE (nome)
	
);

-- 3. Insira os dados abaixo na tabela EDITORA.

INSERT INTO editora (nome) VALUES 
('Bookman'),
('Edgard Blusher'),
('Nova Terra'),
('Brasport');

-- 4. Crie uma tabela chamada CATEGORIA, de acordo com os dados abaixo:

CREATE TABLE categoria(

	IdCategoria SERIAL NOT NULL,
	Nome VARCHAR(30) NOT NULL,

	CONSTRAINT pk_ctg_IdCategoria PRIMARY KEY (IdCategoria),
	CONSTRAINT un_ctg_Nome UNIQUE (Nome)

);

-- 5. Insira os dados abaixo na tabela CATEGORIA.

INSERT INTO categoria (nome) VALUES
('Banco de Dados'),
('HTML'),
('Java'),
('PHP');

-- 6. Crie uma tabela chamada AUTOR, de acordo com os dados abaixo:

CREATE TABLE autor(

	IdAutor SERIAL NOT NULL,
	Nome VARCHAR(30) NOT NULL,

	CONSTRAINT pk_atr_IdAutor PRIMARY KEY (IdAutor),
	CONSTRAINT un_atr_Nome UNIQUE (Nome)
	
);

-- 7. Insira os dados abaixo na tabela AUTOR.

INSERT INTO autor (Nome) VALUES
('Waldemar Setzer'),
('Flávio Soares'),
('John Watson'),
('Rui Rossi dos Santos'),
('Antonio Pereira de Resende'),
('Claudiney Calixto Lima'),
('Evandro Carlos Teruel'),
('Ian Graham'),
('Fabrício Xavier'),
('Pablo Dalloglio');

select * from autor

-- 8. Crie uma tabela chamada LIVRO, de acordo com os dados abaixo:

CREATE TABLE livro(

	IdLivro SERIAL NOT NULL,
	IdEditora INTEGER NOT NULL,
	IdCategoria INTEGER NOT NULL,
	Nome VARCHAR(50) NOT NULL,

	CONSTRAINT pk_lvr_IdLivro PRIMARY KEY (IdLivro),
	CONSTRAINT fk_lvr_IdEditora FOREIGN KEY (IdEditora) REFERENCES editora(IdEditora),
	CONSTRAINT fk_lvr_IdCategoria FOREIGN KEY (IdCategoria) REFERENCES categoria(IdCategoria),
	CONSTRAINT un_lvr_Nome UNIQUE (Nome)


);

-- 9. Insira os dados abaixo na tabela LIVRO.

ALTER TABLE livro
ALTER COLUMN Nome TYPE VARCHAR(100);

select * from editora
select * from categoria

INSERT INTO livro (IdEditora, IdCategoria, Nome) VALUES

(2, 1, 'Banco de Dados - 1 Edição'),
(1, 1, 'Oracle DataBase 11G Administração'),
(3, 3, 'Programação de Computadores em Java'),
(4, 3, 'Programação Orientada a Aspectos em Java'),
(4, 2, 'HTML5 – Guia Prático'),
(3, 2, 'XHTML: Guia de Referência para Desenvolvimento na Web'),
(1, 4, 'PHP para Desenvolvimento Profissional'),
(2, 4, 'PHP com Programação Orientada a Objetos');

select * from livro

-- 10. Crie uma tabela chamada LIVRO_AUTOR, de acordo com os dados abaixo:

CREATE TABLE livro_autor(

	IdLivro INTEGER NOT NULL,
	IdAutor INTEGER NOT NULL,

	CONSTRAINT pk_ltr_IdLivroAutor PRIMARY KEY (IdLivro, IdAutor),
	CONSTRAINT fk_ltr_IdLivro FOREIGN KEY (IdLivro) REFERENCES livro (IdLivro),
	CONSTRAINT fk_ltr_IdAutor FOREIGN KEY (IdAutor) REFERENCES autor (IdAutor)

);

-- 11. Insira os dados abaixo na tabela LIVRO_AUTOR.

select * from livro
select * from autor

INSERT INTO livro_autor (IdLivro, IdAutor) VALUES
(1, 1),
(1, 2),
(2, 3),
(3, 4),
(4, 5),
(4, 6),
(5, 7),
(6, 8);

insert into livro_autor (IdLivro, IdAutor) VALUES
(7, 9),
(8, 10);

select * from livro_autor

-- 12. Crie uma tabela chamada ALUNO, de acordo com os dados abaixo:

CREATE TABLE aluno (

	IdAluno SERIAL NOT NULL,
	Nome VARCHAR(50) NOT NULL,

	CONSTRAINT pk_aln_IdAluno PRIMARY KEY (IdAluno),
	CONSTRAINT un_aln_Nome UNIQUE (Nome)

);

-- 13. Insira os dados abaixo na tabela ALUNO.

INSERT INTO aluno (nome) VALUES 

('Mario'),
('João'),
('Paulo'),
('Pedro'),
('Maria');

select * from aluno

-- 14. Crie uma tabela chamada EMPRESTIMO, de acordo com os dados abaixo:

CREATE TABLE emprestimo (

	IdEmprestimo SERIAL NOT NULL,
	IdAluno INTEGER NOT NULL,
	Data_Emprestimo DATE NOT NULL DEFAULT CURRENT_DATE,
	Data_Devolucao DATE NOT NULL,
	Valor FLOAT DEFAULT 0,
	Devolvido CHAR(1) NOT NULL,

	CONSTRAINT pk_emp_IdEmprestimo PRIMARY KEY (IdEmprestimo),
	CONSTRAINT fk_emp_IdAluno FOREIGN KEY (IdAluno) REFERENCES aluno(IdAluno)
	
);

-- 15. Insira os dados abaixo na tabela EMPRESTIMO.

select * from aluno

INSERT INTO emprestimo (IdAluno, Data_Emprestimo, Data_Devolucao, Valor, Devolvido) VALUES
(1, '2012-05-02', '2012-05-12', 10, 'S'),
(1, '2012-04-23', '2012-05-03', 5, 'N'),
(2, '2012-05-10', '2012-05-20', 12, 'N'),
(3, '2012-05-10', '2012-05-20', 8, 'S'),
(4, '2012-05-05', '2012-05-15', 15, 'N'),
(4, '2012-05-07', '2012-05-17', 20, 'S'),
(4, '2012-05-08', '2012-05-18', 5, 'S');

select * from emprestimo


-- 16. Crie uma tabela chamada EMPRESTIMO_LIVRO, de acordo com os dados abaixo:

CREATE TABLE emprestimo_livro (

	IdEmprestimo INTEGER NOT NULL,
	IdLivro INTEGER NOT NULL,

	CONSTRAINT pk_elv_idemprestimolivro PRIMARY KEY (IdEmprestimo, IdLivro),
	CONSTRAINT fk_elv_idemprestimo FOREIGN KEY (IdEmprestimo) REFERENCES emprestimo(IdEmprestimo),
	CONSTRAINT fk_elv_IdLivro FOREIGN KEY (IdLivro) REFERENCES livro(IdLivro)

);

-- 17. Insira os dados abaixo na tabela EMPRESTIMO_LIVRO.

select * from emprestimo
select * from livro
select * from aluno

INSERT INTO emprestimo_livro (IdEmprestimo, IdLivro) VALUES

(1,1), -- mario 1
(2,4), -- mario 2
(2,3), -- mario 2


(3,2), -- joao 1
(3,7), -- joao 1

(4,5), -- paulo

(5,4), -- pedro 1
(6,6), -- pedro 2
(6,1), -- pedro 2
(7,8); -- pedro 3

select * from emprestimo_livro

-- 18. Crie os seguintes índices

CREATE INDEX idx_emp_data_emprestimo ON emprestimo (Data_Emprestimo);
CREATE INDEX idx_emp_data_devolucao ON emprestimo (Data_Devolucao);

-- CONSULTAS SIMPLES

	-- 19. O nome dos autores em ordem alfabética
	select * from autor
	SELECT nome FROM autor ORDER BY nome ASC
	
	-- 20. O nome dos alunos que começam com a letra P.
	SELECT nome FROM aluno WHERE nome LIKE 'P%'

	-- 21. O nome dos livros da categoria Banco de Dados ou Java.
	select * from categoria -- 1 e 3
	SELECT * FROM livro
	SELECT nome FROM livro WHERE idcategoria = 1 OR idcategoria = 3

	-- 22. O nome dos livros da editora Bookman.
	SELECT * FROM editora
	SELECT nome FROM livro WHERE ideditora = 1
	
	-- 23. Os empréstimos realizados entre 05/05/2012 e 10/05/2012.
	SELECT * FROM emprestimo
	SELECT idemprestimo, idaluno, valor FROM emprestimo WHERE data_emprestimo BETWEEN '2012-05-05' AND '2012-05-10'

	-- 24. Os empréstimos que não foram feitos entre 05/05/2012 e 10/05/2012
	SELECT idemprestimo, idaluno, valor FROM emprestimo WHERE data_emprestimo NOT BETWEEN '2012-05-05' AND '2012-05-10'

	-- 25. Os empréstimos que os livros já foram devolvidos.
	SELECT idemprestimo, idaluno, data_devolucao, valor FROM emprestimo WHERE devolvido = 'S'

	

-- CONSULTAS COM AGRUPAMENTO SIMPLES

	-- 26. A quantidade de livros.
	SELECT * FROM LIVRO
	SELECT COUNT(idlivro) FROM livro
	
	-- 27. O somatório do valor dos empréstimos.
	SELECT * FROM emprestimo
	SELECT SUM(valor) FROM emprestimo
	
	-- 28. A média do valor dos empréstimos.
	SELECT AVG(valor) FROM emprestimo
	
	-- 29. O maior valor dos empréstimos
	SELECT MAX(valor) FROM emprestimo
	
	-- 30. O menor valor dos empréstimos.
	SELECT MIN(valor) FROM emprestimo

	-- 31. O somatório do valor do empréstimo que estão entre 05/05/2012 e 10/05/2012.
	SELECT SUM(valor) FROM emprestimo WHERE data_emprestimo BETWEEN '2012-05-05' AND '2012-05-10'
	
	-- 32. A quantidade de empréstimos que estão entre 01/05/2012 e 05/05/2012.
	SELECT COUNT(idemprestimo) FROM emprestimo WHERE data_emprestimo BETWEEN '2012-05-01' AND '2012-05-05'
	

-- CONSULTAS COM JOIN

	-- 33. O nome do livro, a categoria e a editora (LIVRO) – fazer uma view
	
	CREATE VIEW dados_livros AS
	SELECT 
		livro.nome AS Livro,
		categoria.nome AS Categoria,
		editora.nome AS Editora
	FROM 
		livro
	LEFT OUTER JOIN 
		categoria ON livro.idcategoria = categoria.idcategoria
	LEFT OUTER JOIN
		editora ON livro.ideditora = editora.ideditora
	
	-- 34. O nome do livro e o nome do autor (LIVRO_AUTOR) – fazer uma view.

	CREATE VIEW livro_autor_view AS
	SELECT
		livro.nome as livro,
		autor.nome as autor
	FROM
		livro_autor
	LEFT OUTER JOIN
		livro ON livro_autor.idlivro = livro.idlivro
	LEFT OUTER JOIN
		autor ON livro_autor.idautor = autor.idautor

	
	-- 35. O nome dos livros do autor Ian Graham (LIVRO_AUTOR).

	-- 8 ian
	SELECT 
		livro.nome as livro
	FROM
		livro_autor
	LEFT OUTER JOIN
		livro ON livro_autor.idlivro = livro.idlivro
	WHERE
		livro_autor.idautor = 8
		
		
	-- 36. O nome do aluno, a data do empréstimo e a data de devolução (EMPRESTIMO).
	
	SELECT 
		aluno.nome as nome,
		emprestimo.data_emprestimo,
		emprestimo.data_devolucao
	FROM
		emprestimo
	LEFT OUTER JOIN	
		aluno ON emprestimo.idaluno = aluno.idaluno
		
	
	-- 37. O nome de todos os livros que foram emprestados (EMPRESTIMO_LIVRO).

	SELECT
		livro.nome as nome
	FROM
		emprestimo_livro
	LEFT OUTER JOIN
		livro ON emprestimo_livro.idlivro = livro.idlivro

-- CONSULTAS COM AGRUPAMENTO + JOIN

	-- 38. O nome da editora e a quantidade de livros de cada editora (LIVRO).

	SELECT 
		editora.nome as editora,
		COUNT(livro.idlivro) as quantidade
	FROM
		livro
	LEFT OUTER JOIN
		editora on livro.ideditora = editora.ideditora
	GROUP BY
		editora.nome
		
	-- 39.  nome da categoria e a quantidade de livros de cada categoria (LIVRO).

	SELECT
		categoria.nome as categoria,
		COUNT(livro.idlivro) as quantidade
	FROM
		livro	
	LEFT OUTER JOIN
		categoria ON livro.idcategoria = categoria.idcategoria
	GROUP BY
		categoria.nome
	
	-- 40. O nome do autor e a quantidade de livros de cada autor (LIVRO_AUTOR).

	SELECT
		autor.nome as autor,
		COUNT(livro_autor.idlivro) as quantidade
	FROM
		livro_autor
	LEFT OUTER JOIN
		autor ON livro_autor.idautor = autor.idautor
	GROUP BY
		autor.nome
		
	-- 41. O nome do aluno e a quantidade de empréstimo de cada aluno (EMPRESTIMO_LIVRO). -- é so emprestimo

	SELECT
		aluno.nome as aluno,
		COUNT(emprestimo.idemprestimo) as quantidade
	FROM
		emprestimo
	LEFT OUTER JOIN
		aluno ON emprestimo.idaluno = aluno.idaluno
	GROUP BY
		aluno.nome
	
	-- 42. O nome do aluno e o somatório do valor total dos empréstimos de cada aluno (EMPRESTIMO).

	SELECT
		aluno.nome as aluno,
		SUM(emprestimo.valor) as quantidade
	FROM
		emprestimo
	LEFT OUTER JOIN
		aluno ON emprestimo.idaluno = aluno.idaluno
	GROUP BY
		aluno.nome
	
	-- 43. O nome do aluno e o somatório do valor total dos empréstimos de cada aluno somente daqueles que o somatório for maior do 
	-- que 7,00 (EMPRESTIMO). -- 10 eu coloquei pra testar

	SELECT
		aluno.nome as aluno,
		SUM(emprestimo.valor) as quantidade
	FROM
		emprestimo
	LEFT OUTER JOIN
		aluno ON emprestimo.idaluno = aluno.idaluno
	GROUP BY
		aluno.nome
	HAVING -- Aqui é quando vc ja ta fazendo um join
		SUM(emprestimo.valor) > 10

-- CONSULTAS COMANDOS DIVERSOS

	-- 44. O nome de todos os alunos em ordem decrescente e em letra maiúscula.
	SELECT UPPER(nome) FROM aluno ORDER BY nome DESC
	
	-- 45. Os empréstimos que foram feitos no mês 04 de 2012.
	SELECT * FROM emprestimo
	WHERE EXTRACT(YEAR FROM data_emprestimo) = 2012 AND EXTRACT(MONTH FROM data_emprestimo) = 4
	
	-- 46. Todos os campos do empréstimo. Caso já tenha sido devolvido, mostrar a mensagem “Devolução completa”, senão “Em atraso”.
	SELECT 
		*,
		CASE devolvido 
			WHEN 'S' THEN 'Devolução completo'
			WHEN 'N' THEN 'Em atraso'
		END AS status
	FROM
		emprestimo
	
	-- 47. Somente o caractere 5 até o caractere 10 do nome dos autores.
	SELECT SUBSTRING(nome FROM 5 FOR 10) FROM autor 
	
	-- 48. O valor do empréstimo e somente o mês da data de empréstimo. Escreva “Janeiro”, “Fevereiro”, etc
	SELECT
		valor,
		data_emprestimo,
		CASE EXTRACT (MONTH FROM data_emprestimo)
				WHEN 1 THEN 'Janeiro'
				WHEN 2 THEN 'Fevereiro'
				WHEN 3 THEN 'Março'
				WHEN 4 THEN 'Abril'
				WHEN 5 THEN 'Maio'
				WHEN 6 THEN 'Junho'
				WHEN 7 THEN 'Julho'
				WHEN 8 THEN 'Agosto'
				WHEN 9 THEN 'Setembro'
				WHEN 10 THEN 'Outubro'
				WHEN 11 THEN 'Novembro'
				WHEN 12 THEN 'Dezembro'
		END AS Mes

	FROM
		emprestimo
	
-- SUBCONSULTAS

	-- 49. A data do empréstimo e o valor dos empréstimos que o valor seja maior que a média de todos os empréstimos.
	SELECT 
		data_emprestimo,
		valor
	FROM
		emprestimo
	WHERE
		valor > (SELECT AVG(valor) from emprestimo)
		
	-- 50. A data do empréstimo e o valor dos empréstimos que possuem mais de um livro.
	SELECT 
		emprestimo.data_emprestimo,
		emprestimo.valor,
		(SELECT COUNT(emprestimo_livro.idemprestimo) FROM emprestimo_livro WHERE emprestimo_livro.idemprestimo = emprestimo.idemprestimo)
	FROM
		emprestimo
		
	
	-- 51. A data do empréstimo e o valor dos empréstimos que o valor seja menor que a soma de todos os empréstimos.
	SELECT
		data_emprestimo,
		valor
	FROM
		emprestimo
	WHERE
		valor < (SELECT SUM(valor) FROM emprestimo)