/*
 * AULA 01
 * CRIAÇÃO DAS TABELAS
 */
CREATE TABLE TITULO
(
    COD_TIT INT NOT NULL PRIMARY KEY,
    DESCR VARCHAR(50) NOT NULL
);

CREATE TABLE LIVRO
(
    COD_LIVRO INT NOT NULL PRIMARY KEY,
    COD_TIT INT NOT NULL REFERENCES TITULO(COD_TIT),
    LOCALIZ VARCHAR(10)
);

CREATE TABLE LEITOR
(
    COD_LEITOR INT NOT NULL PRIMARY KEY,
    NOME VARCHAR(30) NOT NULL,
    DT_NASC DATE NOT NULL
);

CREATE TABLE RESERVA
(
    COD_RES INT NOT NULL PRIMARY KEY,
    COD_LEITOR INT NOT NULL REFERENCES LEITOR(COD_LEITOR),
    COD_TIT INT NOT NULL REFERENCES TITULO(COD_TIT),
    DT_RES DATE NOT NULL,
    STATUS VARCHAR(1) NOT NULL
);

CREATE TABLE FUNCIONARIO
(
    COD_FUNC INT NOT NULL PRIMARY KEY,
    NOME VARCHAR(30) NOT NULL,
    DT_NASC DATE NOT NULL
);

CREATE TABLE EMPRESTIMO
(
    COD_EMP INT NOT NULL PRIMARY KEY,
    COD_LEITOR INT NOT NULL REFERENCES LEITOR(COD_LEITOR),
    COD_FUNC INT NOT NULL REFERENCES FUNCIONARIO(COD_FUNC),
    COD_LIVRO INT NOT NULL REFERENCES LIVRO(COD_LIVRO),
    DT_EMP DATE NOT NULL,
    DT_DEV DATE
);

/*
 *  POPULAÇÃO DO BANCO DE DADOS
 */

INSERT INTO TITULO VALUES (465749, 'LIVRO DE DANTE ALIGHIERI QUE RETRATA O INFERNO');
INSERT INTO TITULO VALUES (666666, 'BIBLIA');
INSERT INTO TITULO VALUES (3487512, 'ENSINA COMO VENCER NA VIDA MESMO SENDO UM BOSTA');

INSERT INTO LIVRO VALUES (1, 666666, 'BBS10');
INSERT INTO LIVRO VALUES (2, 465749, 'BBS11');
INSERT INTO LIVRO VALUES (3, 3487512, 'EEFS60');

INSERT INTO LEITOR VALUES (40, 'ALBERTO MAURICIO SALES', '16-08-1985');
INSERT INTO LEITOR VALUES (41, 'MARA FILOMENA PEREIRA', '01-01-1997');
INSERT INTO LEITOR VALUES (42, 'HERCULES DA SILVA', '08-08-1966');

INSERT INTO FUNCIONARIO VALUES (90, 'TULIO ABRANTES FACUNDES', '04-12-2000');
INSERT INTO FUNCIONARIO VALUES (91, 'ANDRESSA BORGES COIMBRA', '14-11-1999');
INSERT INTO FUNCIONARIO VALUES (92, 'JULIA ALMEIDA PEREIRA', '20-05-1977');

INSERT INTO RESERVA VALUES (1, 40, 666666, '20-01-2017', 'E');

INSERT INTO EMPRESTIMO VALUES (1, 42, 91, 1, '05-05-2018', NULL);

-- AULA 02
/*
 * OBTER:
 * 1 - CODIGO E NOME DOS LEITORES QUE NASCERAM EM ABRIL DE 1990
 * 2 - DATA DO EMPRESTIMO REALIZADA PELO FUNCIONARIO COD_FUNC 2 OU PELOS LEITORES DE CODIGO MAIOR QUE 5
 * 3 - TODAS AS INFORMAÇÕES DOS LEITORES CUJA TERCEIRA LETRA DO NOME E A LETRA 'I'
 * 4 - CODIGO DOS LIVROS DE TITULO 2 OU 3 OU 4 OU 5 OU 6 OU 7 OU 8 OU 9 OU 10
 * 5 - CODIGO DOS LIVROS DE TITULO 2 OU 5 OU 10 OU 22 OU 41 OU 45
 */

SELECT COD_LEITOR, NOME FROM LEITOR WHERE DT_NASC BETWEEN '1990-04-01' AND '1990-04-30'; -- 1
SELECT DT_EMP FROM EMPRESTIMO WHERE COD_FUNC = 2 OR COD_LEITOR > 5; -- 2
SELECT * FROM LEITOR WHERE NOME ILIKE '__I%'; -- 3
SELECT COD_LIVRO FROM LIVRO WHERE COD_TIT BETWEEN 2 AND 10; -- 4
SELECT COD_LIVRO FROM LIVRO WHERE COD_TIT IN (2, 5, 10, 22, 41, 45); -- 5


-- AULA 03 (SUBQUERIES)

SELECT COD_LIVRO FROM LIVRO WHERE COD_TIT = (SELECT COD_TIT FROM TITULO WHERE DESCR = 'BIBLIA');
SELECT NOME FROM LEITOR WHERE COD_LEITOR IN (SELECT COD_LEITOR FROM EMPRESTIMO WHERE DT_EMP = '2018-04-04');

-- OBTER O NOME DOS FUNCIONARIOS QUE ATENDERAM O JOAO
SELECT NOME FROM FUNCIONARIO WHERE COD_FUNC IN (SELECT COD_FUNC FROM EMPRESTIMO WHERE COD_LEITOR IN (SELECT COD_LEITOR FROM LEITOR WHERE NOME = 'JOAO'));
-- OBTER OS NOMES DOS TITULOS QUE A MARIA TOMOU EMPRESTADO
SELECT DESCR FROM TITULO WHERE COD_TIT IN (SELECT COD_TIT FROM LIVRO WHERE COD_LIVRO IN (SELECT COD_LIVRO FROM EMPRESTIMO WHERE COD_LEITOR IN (SELECT COD_LEITOR FROM LEITOR WHERE NOME = 'MARIA')));
-- OBTER OS NOMES DOS TITULOS EMPRESTADOS NO ANO DE 2018 OU PELO FUNCIONARIO JOAO OU PELO LEITOR JOSE
SELECT DESCR FROM TITULO WHERE COD_TIT IN (SELECT COD_TIT FROM LIVRO WHERE COD_LIVRO IN (SELECT COD_LIVRO FROM EMPRESTIMO WHERE DT_EMP BETWEEN '2018-01-01' AND '2018-12-31' OR COD_FUNC IN (SELECT COD_FUNC FROM FUNCIONARIO WHERE NOME = 'JOAO') OR COD_LEITOR IN (SELECT COD_LEITOR FROM LEITOR WHERE NOME = 'JOSE')));
-- OBTER O NOME DOS FUNCIONARIOS QUE EMPRESTARAM LIVROS PARA O JOAO NO ANO DE 2018
SELECT NOME FROM FUNCIONARIO WHERE COD_FUNC IN (SELECT COD_FUNC FROM EMPRESTIMO WHERE COD_LIVRO IN (SELECT COD_LIVRO FROM EMPRESTIMO WHERE COD_LEITOR IN (SELECT COD_LEITOR FROM LEITOR WHERE NOME = 'JOAO') AND DT_EMP BETWEEN '2018-01-01' AND '2018-12-31'));


-- AULA 04 (FUNÇOES AGREGADAS / ALTERANDO A INSTÂNCIA DO BANCO DE DADOS)

/*
 * MIN()          INSERT
 * MAX()          DELETE
 * AVG()          UPDATE
 * SUM()
 * COUNT()
 */

/* DATA DE NASCIMENTO DO LEITOR MAIS JOVEM */
SELECT MAX(DT_NASC) FROM LEITOR;

/* O NOME DO LEITOR MAIS JOVEM */
SELECT NOME FROM LEITOR WHERE DT_NASC = (SELECT MAX(DT_NASC) FROM LEITOR);

/* O NOME DO LEITOR MAIS VELHO QUE NASCEU NO ANO DE XXXX */
SELECT NOME FROM LEITOR WHERE DT_NASC = (SELECT MIN(DT_NASC) FROM LEITOR WHERE DT_NASC BETWEEN '1997-01-01' AND '1997-12-31');

/* OBTER O NOME DO FUNCIONARIO MAIS JOVEM QUE ATENDEU O LEITOR MAIS VELHO */
/* ta errado SELECT NOME FROM FUNCIONARIO WHERE COD_FUNC IN (SELECT COD_FUNC FROM FUNCIONARIO WHERE DT_NASC IN (SELECT MAX(DT_NASC) FROM FUNCIONARIO)) AND COD_FUNC IN (SELECT COD_FUNC FROM EMPRESTIMO WHERE COD_LEITOR = (SELECT COD_LEITOR FROM LEITOR WHERE DT_NASC = (SELECT MIN(DT_NASC) FROM LEITOR)));*/
SELECT NOME FROM FUNCIONARIO WHERE DT_NASC IN (SELECT MAX(DT_NASC) FROM FUNCIONARIO WHERE COD_FUNC IN (SELECT COD_FUNC FROM EMPRESTIMO WHERE COD_LEITOR IN (SELECT COD_LEITOR FROM LEITOR WHERE DT_NASC IN (SELECT MIN(DT_NASC) FROM LEITOR))));

/* OBTER A QUANTIDADE DE FUNCIONARIOS QUE NAO INFORMARAM O SEU LOCAL DE NASCIMENTO */
SELECT COUNT(COD_FUNC) FROM FUNCIONARIO WHERE COD_FUNC IN (SELECT COD_FUNC FROM FUNCIONARIO WHERE LOCAL_NASC IS NULL);
SELECT COUNT(*) - COUNT(LOCAL_NASC) FROM FUNCIONARIO;

ALTER TABLE FUNCIONARIO ADD COLUMN LOCAL_NASC VARCHAR(40) NULL;

SELECT * FROM FUNCIONARIO ORDER BY COD_FUNC;

UPDATE FUNCIONARIO SET LOCAL_NASC = 'GUARULHOS' WHERE COD_FUNC = 92;

-- AULA 05 (PRODUTO CARTESIANO)

/* CODIGO DOS LIVROS DE TITULO SBD */
SELECT COD_LIVRO FROM LIVRO WHERE COD_TIT IN (SELECT COD_TIT FROM TITULO WHERE DESCR = 'SBD');

SELECT * FROM LIVRO L, TITULO T WHERE L.COD_TIT = T.COD_TIT;

-- OBTER O NOME DOS FUNCIONARIOS QUE ATENDERAM O JOAO USANDO PRODUTO CARTESIANO
SELECT F.NOME FROM FUNCIONARIO F, EMPRESTIMO E, LEITOR L WHERE F.COD_FUNC = E.COD_FUNC AND E.COD_LEITOR = L.COD_LEITOR AND L.NOME = 'JOAO';

-- NOME DOS LEITOR QUE NASCERAM NO MESMO DIA QUE O 'JOAO'
SELECT L2.NOME FROM LEITOR L1, LEITOR L2 WHERE L1.DT_NASC = L2.DT_NASC AND L1.NOME = 'JOAO' AND L2.NOME <> 'JOAO';
SELECT NOME FROM LEITOR WHERE DT_NASC IN (SELECT DT_NASC FROM LEITOR WHERE NOME = 'JOAO') AND NOME <> 'JOAO';

-- OBTER O NOME DOS FUNCIONARIOS QUE ATENDERAM O JOAO
SELECT F.NOME FROM FUNCIONARIO F, EMPRESTIMO E, LEITOR L WHERE F.COD_FUNC = E.COD_FUNC AND E.COD_LEITOR = L.COD_LEITOR AND L.NOME = 'JOAO';
-- OBTER OS NOMES DOS TITULOS QUE A MARIA TOMOU EMPRESTADO
SELECT T.DESCR FROM TITULO T, EMPRESTIMO E, LEITOR L, LIVRO LIV WHERE LIV.COD_TIT = T.COD_TIT AND E.COD_LEITOR = L.COD_LEITOR AND E.COD_LIVRO = LIV.COD_LIVRO AND L.NOME = 'MARIA';
-- OBTER OS NOMES DOS TITULOS EMPRESTADOS NO ANO DE 2018 OU PELO FUNCIONARIO JOAO OU PELO LEITOR JOSE
SELECT T.DESCR FROM EMPRESTIMO E, LIVRO LIV, LEITOR L, FUNCIONARIO F, TITULO T WHERE (E.COD_FUNC = F.COD_FUNC AND E.COD_LEITOR = L.COD_LEITOR AND E.COD_LIVRO = LIV.COD_LIVRO AND LIV.COD_TIT = T.COD_TIT) AND (F.NOME = 'JOAO' OR L.NOME = 'JOSE');
-- OBTER O NOME DOS FUNCIONARIOS QUE EMPRESTARAM LIVROS PARA O JOAO NO ANO DE 2018
SELECT F.NOME FROM EMPRESTIMO E, LEITOR L, FUNCIONARIO F WHERE (E.COD_LEITOR = L.COD_LEITOR AND F.COD_FUNC = E.COD_FUNC) AND DT_EMP BETWEEN '2018-01-01' AND '2018-12-31' AND L.NOME = 'JOAO';

SELECT COD_LIVRO FROM LIVRO L JOIN TITULO T ON L.COD_TIT = T.COD_TIT WHERE T.DESCR = 'SBD';

-- O NOME DOS FUNCIONARIOS QUE ATENDERAM AO JOAO (USANDO INNER JOIN)
SELECT F.NOME FROM FUNCIONARIO F JOIN EMPRESTIMO E ON E.COD_FUNC = F.COD_FUNC JOIN LEITOR L ON E.COD_LEITOR = L.COD_LEITOR WHERE L.NOME = 'JOAO';