--
-- PostgreSQL database dump
--

CREATE SCHEMA geral;


ALTER SCHEMA geral OWNER TO postgres;



CREATE SCHEMA secretaria;


ALTER SCHEMA secretaria OWNER TO postgres;

SET search_path = geral, pg_catalog;

CREATE TABLE campus (
    id_campus integer NOT NULL,
    nome character varying(30),
    id_cidade integer
);


ALTER TABLE campus OWNER TO postgres;


CREATE SEQUENCE campus_id_campus_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE campus_id_campus_seq OWNER TO postgres;


ALTER SEQUENCE campus_id_campus_seq OWNED BY campus.id_campus;


CREATE TABLE cidade (
    id_cidade integer NOT NULL,
    nome character varying(40)
);


ALTER TABLE cidade OWNER TO postgres;


CREATE TABLE pessoa_fisica (
    id_pessoa_fisica integer NOT NULL,
    cpf character varying(11) NOT NULL,
    nome character varying(90)
);


ALTER TABLE pessoa_fisica OWNER TO postgres;



CREATE SEQUENCE pessoa_fisica_id_pessoa_fisica_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE pessoa_fisica_id_pessoa_fisica_seq OWNER TO postgres;



ALTER SEQUENCE pessoa_fisica_id_pessoa_fisica_seq OWNED BY pessoa_fisica.id_pessoa_fisica;


SET search_path = secretaria, pg_catalog;



CREATE TABLE aluno (
    id_aluno integer NOT NULL,
    id_curso integer NOT NULL,
    id_pessoa_fisica integer NOT NULL,
    registro_matricula character varying(9)
);


ALTER TABLE aluno OWNER TO postgres;


CREATE SEQUENCE aluno_id_aluno_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE aluno_id_aluno_seq OWNER TO postgres;



ALTER SEQUENCE aluno_id_aluno_seq OWNED BY aluno.id_aluno;




CREATE TABLE curso (
    id_curso integer NOT NULL,
	codigo character varying(11),
    nome character varying(50) NOT NULL,
    id_nivel integer NOT NULL,
    id_campus integer
);


ALTER TABLE curso OWNER TO postgres;



CREATE SEQUENCE curso_id_curso_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE curso_id_curso_seq OWNER TO postgres;



ALTER SEQUENCE curso_id_curso_seq OWNED BY curso.id_curso;




CREATE TABLE nivel (
    id_nivel integer NOT NULL,
    nome character varying(30)
);


ALTER TABLE nivel OWNER TO postgres;

SET search_path = geral, pg_catalog;



ALTER TABLE ONLY campus ALTER COLUMN id_campus SET DEFAULT nextval('campus_id_campus_seq'::regclass);




ALTER TABLE ONLY pessoa_fisica ALTER COLUMN id_pessoa_fisica SET DEFAULT nextval('pessoa_fisica_id_pessoa_fisica_seq'::regclass);


SET search_path = secretaria, pg_catalog;



ALTER TABLE ONLY aluno ALTER COLUMN id_aluno SET DEFAULT nextval('aluno_id_aluno_seq'::regclass);




ALTER TABLE ONLY curso ALTER COLUMN id_curso SET DEFAULT nextval('curso_id_curso_seq'::regclass);


SET search_path = geral, pg_catalog;


INSERT INTO campus (id_campus, nome, id_cidade) VALUES(1,'José Ribeiro Filho',1);
INSERT INTO campus (id_campus, nome, id_cidade) VALUES(2,'Guajará-Mirim',2);
INSERT INTO campus (id_campus, nome, id_cidade) VALUES(3,'Vilhena',4);



INSERT INTO cidade (id_cidade, nome) VALUES(1,	'Porto Velho');

INSERT INTO cidade (id_cidade, nome) VALUES(2,	'Guajará-Mirim');

INSERT INTO cidade (id_cidade, nome) VALUES(3,	'Ariquemes');

INSERT INTO cidade (id_cidade, nome) VALUES(4,	'Cacoal');
INSERT INTO cidade (id_cidade, nome) VALUES(5,	'Vilhena');



SELECT pg_catalog.setval('campus_id_campus_seq', 1, false);






INSERT INTO  pessoa_fisica ( cpf, nome) VALUES ('12345678910',	'André Aguiar dos Santos');
INSERT INTO  pessoa_fisica ( cpf, nome) VALUES ('12345678901',	'Bruno da Silva');
INSERT INTO  pessoa_fisica ( cpf, nome) VALUES ('12345678109',	'Carlos dos Santos');
INSERT INTO  pessoa_fisica ( cpf, nome) VALUES ('12345678019',	'Diego Gonçalves');
INSERT INTO  pessoa_fisica ( cpf, nome) VALUES ('12345678107',	'Carlos Henrique');
INSERT INTO  pessoa_fisica ( cpf, nome) VALUES ('12345678015',	'Diego Lates');






SELECT pg_catalog.setval('pessoa_fisica_id_pessoa_fisica_seq', 1, false);


SET search_path = secretaria, pg_catalog;


INSERT INTO aluno ( id_curso, id_pessoa_fisica, registro_matricula) VALUES (1,1,'201501018');
INSERT INTO aluno ( id_curso, id_pessoa_fisica, registro_matricula) VALUES (2,	2,'201502001');
INSERT INTO aluno ( id_curso, id_pessoa_fisica, registro_matricula) VALUES (3,	3,'201601012');
INSERT INTO aluno ( id_curso, id_pessoa_fisica, registro_matricula) VALUES (4,	4,'201701022');
INSERT INTO aluno ( id_curso, id_pessoa_fisica, registro_matricula) VALUES (5,	5,'201601017');
INSERT INTO aluno ( id_curso, id_pessoa_fisica, registro_matricula) VALUES (6,	6,'201701025');





SELECT pg_catalog.setval('aluno_id_aluno_seq', 1, false);




INSERT INTO curso ( codigo, nome, id_nivel, id_campus) VALUES(19,	'Informática',	1,	1);

INSERT INTO curso ( codigo, nome, id_nivel, id_campus) VALUES(59,	'Bacharelado em Ciência da Computação',	1,	1);

INSERT INTO curso ( codigo, nome, id_nivel, id_campus) VALUES(60,	'Licenciatura em Computação',	1,	1);

INSERT INTO curso ( codigo, nome, id_nivel, id_campus) VALUES(4,	'Direito',	1,	1);



SELECT pg_catalog.setval('curso_id_curso_seq', 1, false);




INSERT INTO nivel (id_nivel, nome) VALUES (1,'Graduação');
INSERT INTO nivel (id_nivel, nome) VALUES (2,'Pós-Graduação');


SET search_path = geral, pg_catalog;



ALTER TABLE ONLY campus
    ADD CONSTRAINT campus_pkey PRIMARY KEY (id_campus);




ALTER TABLE ONLY cidade
    ADD CONSTRAINT cidade_pkey PRIMARY KEY (id_cidade);




ALTER TABLE ONLY pessoa_fisica
    ADD CONSTRAINT pessoa_fisica_pkey PRIMARY KEY (id_pessoa_fisica);


SET search_path = secretaria, pg_catalog;



ALTER TABLE ONLY aluno
    ADD CONSTRAINT aluno_pkey PRIMARY KEY (id_aluno);




ALTER TABLE ONLY curso
    ADD CONSTRAINT curso_pkey PRIMARY KEY (id_curso);




ALTER TABLE ONLY nivel
    ADD CONSTRAINT nivel_pkey PRIMARY KEY (id_nivel);




ALTER TABLE ONLY curso
    ADD CONSTRAINT "Foreign_key01" FOREIGN KEY (id_campus) REFERENCES geral.campus(id_campus);




ALTER TABLE ONLY curso
    ADD CONSTRAINT "Foreign_key02" FOREIGN KEY (id_nivel) REFERENCES nivel(id_nivel);

CREATE OR REPLACE FUNCTION secretaria.func_monitora_dml_aluno() 
RETURNS TRIGGER AS $tr_monitora_dml_aluno$
BEGIN
        -- 
        -- Cria uma linha na tabela secretaria.log para refletir a operação 
        -- realizada na tabela emp. Utiliza a variável especial TG_OP 
        -- para descobrir a operação sendo realizada. 
        -- 
        IF (TG_OP = 'UPDATE') THEN 
            INSERT INTO secretaria.log SELECT 'Atualizar', user, now(), NEW.registro_matricula; 
            RETURN NEW; 
        ELSIF (TG_OP = 'INSERT') THEN 
            INSERT INTO secretaria.log SELECT 'Inserir', user, now(), NEW.id_aluno||', '||NEW.registro_matricula||', '|| NEW.id_pessoa_fisica; 
            RETURN NEW; 
        END IF;  
        RETURN NULL; -- o resultado é ignorado uma vez que este é um gatilho AFTER 
    END; 
$tr_monitora_dml_aluno$ language plpgsql; 



CREATE TRIGGER tr_monitora_dml_aluno 
AFTER UPDATE OR INSERT ON secretaria.aluno 
    FOR EACH ROW EXECUTE PROCEDURE secretaria.func_monitora_dml_aluno(); 


	
SELECT a.codigo,
       count(b.id_aluno) as alunos
FROM secretaria.curso a
     JOIN secretaria.aluno b ON a.id_curso=b.id_curso    
Group by 1
having count(b.id_aluno) =(SELECT distinct count(b.id_aluno)
                           FROM secretaria.aluno b
                           group by b.id_curso
                           order by count (b.id_aluno) desc
                           limit 1)