Untitled


CREATE DATABASE cod_claro;
\c cod_claro

BEGIN;

CREATE TABLE pessoas(
    id SERIAL,
    email VARCHAR(50),
    apelido VARCHAR(20) UNIQUE,
    cidade VARCHAR(30),
    estado CHAR(2),
    PRIMARY KEY(id)
);

INSERT INTO pessoas(email, apelido, cidade, estado)
VALUES
    ('modike@gmail.com', 'mdk', 'Fortaleza', 'CE'),
    ('prinsjujuba@gmail.com', 'pjuju', 'Nlogonia', 'NP'),
    ('humberto_bsc@hotmail.com', 'humber', 'Juazeiro', 'CE'),
    ('mike@email.com', 'mike332', 'Juazeiro', 'CE'),
    ('haruto@outlook.com.br','haruto248','Sao Paulo','SP'),
    ('kaito@bol.com', 'kaito481', 'Guarapiri', 'SP'),
    ('ren@outlook.com.br','ren1481','Recife','PA'),
    ('eita@outlook.com', 'tata', 'Riachuelo', 'SE'),
    ('doug_nobrega@yahoo.com', 'doug_n', 'Horizonte', 'CE'),
    ('paulo_mirands@ufc.com', 'paulmirs', 'Teresina', 'PI'),
    ('ieslei_bi@ufc.com', 'iesley_pe', 'Juazeiro do Norte', 'CE'),
    ('kams_fre@fufu.com', 'kamz321', 'Quixada', 'CE'),
    ('ikki_fenix@hotmail.com', 'ikkic', 'Bauru', 'SP'),
    ('joezito44@ufpe.com.br', 'joejoe', 'Natal', 'RN'),
    ('mariaguedes@gmail.com', 'maryguedes5', 'Nova Campina', 'SP'),
    ('tourist@ebay.com', 'tourist', 'Macae', 'RJ'),
    ('jonathan_joestar@yahoo.com', 'jojo', 'Mesquita', 'RJ'),
    ('raquels@gmail.com', 'raquel45', 'Fortaleza', 'CE'),
    ('diogsell@outlook.com' , 'diogo_elis', 'Sao Paulo', 'SP'),
    ('letic_sc@gmail.com.br', 'lsaraiva', 'Morada Nova', 'CE'),
    ('yuyuhakusho@gmail.com', 'yuyuh', 'Massape', 'CE'),
    ('triuviratus@uol.com', 'trinity12', 'Bastos', 'SP'),
    ('jojo_kj@globo.com.br', 'joulyne_kujo22', 'Santa Mariana', 'PR'),
    ('mestramagus@gmail.com', 'mestra', 'Nao-Me-Toque', 'RS'),
    ('koichikun@yahoo.com.br', 'koichi', 'Natal', 'RN'),
    ('calos_henr@hotmail.com', 'mtoseverus', 'Maracanau', 'CE'),
    ('basisi@gmail.com', 'basis54', 'Delta', 'MG'),
    ('luscas42@outlook.com', 'luscass', 'Senador Pompeu', 'CE');


CREATE TABLE administradores(
    id_pessoa INT,
    PRIMARY KEY(id_pessoa),
    FOREIGN KEY(id_pessoa) REFERENCES pessoas(id)
);


INSERT INTO administradores
VALUES
    (1),
    (16),
    (23);


CREATE TABLE instituicoes(
    id SERIAL,
    nome VARCHAR(50) UNIQUE,
    PRIMARY KEY(id)
);


INSERT INTO instituicoes(nome)
VALUES
    ('UFC'),
    ('IFCE'),
    ('USP'),
    ('UFRJ'),
    ('UPSP'),
    ('ISP'),
    ('UFPI'),
    ('IFPI'),
    ('UFPR'),
    ('UFD'),
    ('UFMG');


CREATE TABLE estudantes(
    id_pessoa INT,
    id_instituicao INT,
    PRIMARY KEY(id_pessoa),
    FOREIGN KEY(id_pessoa) REFERENCES pessoas(id),
    FOREIGN KEY(id_instituicao) REFERENCES instituicoes(id)
);


INSERT INTO estudantes
VALUES
    (2, 3),
    (3, 5),
    (4, 6),
    (5, 4),
    (6, 3),
    (7, 1),
    (8, 4),
    (9, 2),
    (10, 1),
    (11, 1),
    (12, 2),
    (13, 6),
    (14, 4),
    (15, 3),
    (17, 1),
    (18, 4),
    (19, 5),
    (20, 6),
    (21, 2),
    (22, 2),
    (24, 3),
    (25, 4),
    (26, 3),
    (27, 6),
    (28, 5);


CREATE TABLE problemas(
    id SERIAL,
    titulo VARCHAR(50) UNIQUE,
    descricao VARCHAR(3000),
    entrada VARCHAR(500),
    saida VARCHAR(500),
    pontos INT NOT NULL,
    id_adm INT,
    PRIMARY KEY(id),
    FOREIGN KEY(id_adm) REFERENCES administradores(id_pessoa)
);


INSERT INTO problemas (titulo, descricao, pontos, id_adm)
VALUES
    ('Hello world!', 'Imprima a mensagem hello world.', 10,1),
    ('Fibbo','Calcule fibbonacci de N.',100,1),
    ('Mais Perto','Descubra a cidade mais perto.',250,1),
    ('Maior Subsequencia Comum','Diga qual maior de subsequencia comum.',250,1),
    ('Menor Elemento','Diga qual menor elemento do vetor.',50,16),
    ('Soma de Primos','Calcule o somatorio de todos os primos ate N.',200,1),
    ('Colonia de Formigas', 'Calcule o LCA entre os pares.',200,23),
    ('Josephus', 'Diga o resultado do jogo de josephus.', 100,23),
    ('Busca Binaria', 'Econtre o elemento em logN.',100,1),
    ('Cruzamento de Cubos','Diga se cada cubo se intersecta.',200,1),
    ('Mania de Par','Encontre o menor caminho par ate F.', 250,1),
    ('Diga se é Palindromo','Diga se P é palindromo.', 50 ,16),
    ('Area da Construcao','Calcule a area do poligono P.', 300,1),
    ('Marcando Consulta','Calcule o maximo de consultas para atender.',250,1),
    ('Qual Estrutura', 'Diga se é pilha, fila ou heap.',150,16),
    ('Rota de Caminhao','Calcule menor distancia entre todos pares.',300,1),
    ('Quantos Grupos', 'Diga o minimo de grupos entre elementos.',200,23),
    ('Distancia Entre Pontos', 'Encontre os dois pontos mais proximos.',300,1),
    ('Maiusculador', 'Imprima a string P em maiuscula.',250,23),
    ('MSC', 'Diga o tamanho da maior subsequencia crescente.',100,23),
    ('Seg Tree', 'Diga qual sinal do produto entre intervalos',200,1),
    ('Capoeira', 'Inverta o vetor V dado',50, 16),
    ('Perimetro', 'Calcule o perimetro do circulo', 50,1);

--ALTER SEQUENCE problemas_id_seq RESTART WITH 1;


CREATE TABLE submissoes(
    id SERIAL,
    codigo VARCHAR(5000),
    situacao VARCHAR(20),
    data_submissao DATE,
    tempo INT,
    id_estudante INT,
    id_problema INT,
    PRIMARY KEY(id),
    FOREIGN KEY(id_estudante) REFERENCES estudantes(id_pessoa),
    FOREIGN KEY(id_problema) REFERENCES problemas(id)
);


INSERT INTO submissoes (situacao, codigo, data_submissao, id_estudante, id_problema)
VALUES
    ('accepted', 'printf(hello)', '22-12-2017', 4, 3),
    ('accepted', 'cout<<n-2', '22-12-2017', 2, 7),
    ('accepted', 'solve(x)', '22-12-2017', 20, 15),
    ('accepted', 'printf(hello)', '22-12-2017', 5, 9),
    ('run time error', 'while(x>2) cout << x+3', '15-08-2018', 2, 5),
    ('presentation error', 'console.log(sqtr(u+1))', '14-12-2018', 24, 16),
    ('time limit exceeded', 'printf(hello)', '13-12-2017', 3, 8),
    ('wrong answer', 'printf(hello world)', '21-12-2017', 4, 3),

    ('accepted', 'printf(hello)', '22-12-2017', 15, 22),
    ('accepted', 'cout<< n ** n', '22-12-2017', 19, 1),
    ('accepted', 'print(5y + 7t)', '22-12-2017', 19, 2),
    ('accepted', 'cout << Dijskstra(n) << endl', '22-12-2017', 20, 3),
    ('accepted', 'cout<<n-2', '22-12-2017', 18, 4),
    ('accepted', 'cout<<n-554', '22-12-2017', 17, 5),
    ('accepted', 'cout<<6464', '22-12-2017', 15, 6),
    ('accepted', 'cout<<mnor_ares', '22-12-2017', 17, 7),
    ('accepted', 'cout<<n-66', '22-12-2017', 24, 8),
    ('accepted', 'solve(x)', '22-12-2017', 17, 20),
    ('accepted', 'printf(hello)', '22-12-2017', 18, 21),
    ('run time error', 'while(x>2) cout << x+3', '15-08-2018', 18, 22),
    ('presentation error', 'console.log(sqtr(u+1))', '14-12-2018', 19, 23),
    ('time limit exceeded', 'goto 45', '13-12-2017', 25, 22),
    ('wrong answer', 'printf(hello world)', '21-12-2017', 20, 23),

    ('run time error', 'while(x>2) cout << x+3', '15-08-2018', 4, 3),
    ('presentation error', 'console.log(sqtr(u+1))', '14-12-2018', 8, 1),
    ('time limit exceeded', 'printf(hello)', '13-12-2017', 9, 10),
    ('wrong answer', 'printf(hello world)', '21-12-2017', 10, 11),
    ('accepted', 'printf(hello)', '22-12-2017', 11, 12),
    ('accepted', 'cout<<n-2', '22-12-2017', 12, 13),
    ('accepted', 'solve(x)', '22-12-2017', 14, 15),
    ('accepted', 'printf(hello)', '22-12-2017', 20, 21),
    ('accepted', 'cout<<n-2', '22-12-2017', 21, 22),
    ('accepted', 'solve(x)', '22-12-2017', 22, 23),

    ('run time error', 'while(x>2) cout << x+3', '15-08-2018', 26, 3),
    ('presentation error', 'console.log(sqtr(u+1))', '14-12-2018', 25, 1),
    ('time limit exceeded', 'printf(hello)', '13-12-2017', 24, 10),
    ('wrong answer', 'printf(hello world)', '21-12-2017', 26, 11),
    ('accepted', 'printf(hello)', '24-09-2017', 22, 12),
    ('accepted', 'cout<<n-2', '26-05-2018', 21, 13),
    ('accepted', 'solve(x)', '13-03-2018', 20, 15),
    ('accepted', 'printf(hello)', '05-08-2017', 19, 21),
    ('accepted', 'cout<<n-2', '14-04-2017', 18, 22),
    ('accepted', 'solve(x)', '11-11-2017', 22, 23);


CREATE TABLE categorias(
    id SERIAL PRIMARY KEY,
    descricao VARCHAR(50)
);


INSERT INTO categorias(descricao)
VALUES
    ('iniciante'),
    ('estrutura'),
    ('AD HOC'),
    ('matematica'),
    ('programacao dinamica'),
    ('guloso'),
    ('grafo');


CREATE TABLE problema_categoria(
    id_problema INT,
    id_categoria INT,
    PRIMARY KEY(id_problema, id_categoria),
    FOREIGN KEY(id_problema) REFERENCES problemas(id),
    FOREIGN KEY(id_categoria) REFERENCES categorias(id)
);


INSERT INTO problema_categoria
VALUES
    (1,1),(2,4),(2,5),(3,6),(3,5),(3,7),(4,5),(5,1),(5,2),(6,4),(6,5),(7,7),(7,5),(8,2),(8,1),(9,3),
    (10,4),(10,3),(11,7),(11,5),(11,2),(12,1),(12,3),(13,4),(14,6),(15,2),(16,7),(16,3),(17,2),(18,5),(18,4),(19,1),(19,3),
    (20,2),(20,5),(21,2),(21,5),(22,1),(23,1),(23,4);


ROLLBACK;

--todas questoes da categoria iniciante--
SELECT p.titulo
FROM problemas p, problema_categoria re, categorias cat
WHERE p.id = re.id_problema AND re.id_categoria = cat.id AND cat.descricao = 'iniciante';


--pontuacao de cada usuario--
SELECT pe.apelido AS nick_usuario, SUM(pr.pontos) AS total_de_pontos
FROM pessoas pe RIGHT JOIN estudantes e ON pe.id = e.id_pessoa
     LEFT JOIN submissoes s ON e.id_pessoa = s.id_estudante
     LEFT JOIN problemas pr ON (s.id_problema = pr.id AND s.situacao = 'accepted')
GROUP BY pe.apelido
ORDER BY SUM(pr.pontos) IS NULL, total_de_pontos DESC;


--numero de usuarios por instituicao--
SELECT inst.nome, (SELECT COUNT(*) FROM estudantes est WHERE inst.id = est.id_instituicao) AS qtd_estudantes
FROM instituicoes inst
ORDER BY qtd_estudantes DESC;


--questoes da categoria matematica ordenados pelos pontos--
SELECT prob.titulo, prob.pontos
FROM problemas prob
    INNER JOIN problema_categoria rel ON prob.id = rel.id_problema
    INNER JOIN categorias cat ON rel.id_categoria = cat.id
WHERE cat.descricao = 'matematica'
ORDER BY prob.pontos DESC;


--intituicoes com mais alunos registrados--
SELECT inst.nome
FROM instituicoes inst, estudantes est
WHERE inst.id = est.id_instituicao
GROUP BY inst.nome
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM instituicoes INNER JOIN estudantes ON id = id_instituicao GROUP BY id);


--quais estudantes nao resolveram o problema 1--
SELECT pe.apelido
FROM pessoas pe INNER JOIN estudantes est ON pe.id = est.id_pessoa
WHERE NOT EXISTS (SELECT * FROM submissoes sub WHERE sub.id_estudante = est.id_pessoa AND sub.id_problema = 1);


--quais usuarios sao do Ceara--
SELECT pe.apelido
FROM pessoas pe
WHERE pe.id IN (SELECT id FROM pessoas WHERE pe.estado = 'CE');