LBD - PostgreSQL - DML

-- Classes
CREATE TABLE classes (
    id serial primary key,
    classe varchar
);

INSERT INTO classes (classe) VALUES ('Guerreiro'), ('Mago'), ('Paladino'), ('Xamã');

-- Personagens
CREATE TABLE personagens (
    id serial primary key,
    personagem varchar,
    classe_id integer references classes(id),
    hp smallint,
    mp smallint,
    forca smallint,
    defesa smallint
);


INSERT INTO personagens (personagem,classe_id,hp,mp,forca,defesa) VALUES ('Brett',1,79,77,34,38),('Perry',3,94,25,16,11),('Nyssa',1,99,23,37,24),('Ella',1,84,50,4,37),('Bryar',2,88,58,23,10),('Tad',3,46,34,39,26),('Cruz',3,97,49,18,26),('Glenna',1,23,27,9,2),('Deacon',2,67,34,25,21),('Shelly',4,28,68,26,40),('Phillip',3,25,79,18,35),('Phelan',4,31,45,31,38),('Leah',4,48,12,30,4),('Erin',4,76,68,35,25),('Curran',3,94,47,24,2),('Clarke',1,26,45,10,12),('Ebony',1,63,43,16,17),('Maxine',3,23,19,26,40),('Ignatius',2,93,18,13,18),('Alan',3,96,21,33,18);
INSERT INTO personagens (personagem,classe_id,hp,mp,forca,defesa) VALUES ('Dean',1,22,67,34,28),('Perry',4,89,63,39,29),('Reed',3,82,55,11,29),('Lacy',1,68,56,36,8),('Paula',2,28,27,14,11),('Carly',2,53,49,39,17),('Kenneth',1,21,69,18,5),('Athena',2,59,43,39,29),('Latifah',4,86,72,33,3),('Claudia',2,97,30,33,28),('Kermit',2,90,76,19,18),('Deborah',4,24,28,6,3),('Shafira',1,47,16,29,9),('Ivy',4,62,42,35,22),('Maya',1,47,25,4,25),('Hammett',3,20,47,31,37),('Theodore',1,24,11,26,38),('Adele',1,33,28,36,14),('Mercedes',3,97,44,26,15),('Naida',4,94,15,32,2);



-------- JOIN
-- CATEGORIAS
CREATE TABLE categorias (
  id serial primary key,
  nome character varying(200)
);

insert into categorias(nome) values ('Terror'), ('Fantasia'), ('Ficção Cientifica'), ('Infantil');

-- LIVROS

CREATE TABLE livros (
  id serial primary key,
  titulo character varying(200),
  preco money,
  categoria_id integer REFERENCES categorias
);

insert into livros (titulo, preco, categoria_id)
        values ('IT', 70.00, 1), ('Harry Potter e a Câmara Secreta', 50.00, 2),
               ('Senhor dos Anéis', 150.00, 2), ('Código da Vinci', 45.00, 3), ('O Alquimista',  40.00, 3);

insert into livros (titulo, preco)
        values ('O Pequeno Principe', 30.00);


-- ALUNOS
create table alunos (
    id serial primary key,
    nome varchar(200),
    ativo boolean
);

insert into alunos (nome, ativo) values ('Thiago', '1'), ('João', '0'), ('Mario', '1'), ('Flávio', '0');


-- Notas
create table notas (
    aluno_id integer references alunos,
    nota1 numeric(4,2),
    nota2 numeric(4,2),
    nota3 numeric(4,2)
);

insert into notas values (1, 5, 7.5, 9.5), (2, 8.5, 8.5, 6), (3, 10, 3.5, 6);