CREATE TYPE exam_type AS
  ENUM('exam','dm','oral','projet');

CREATE TABLE exam (
 id SERIAL PRIMARY KEY,
 date DATE NOT NULL,
 title TEXT,
 max_grade DECIMAL(10,0) NOT NULL,
 type exam_type,
 coefficient INT NOT NULL
);


CREATE TABLE student (
 id SERIAL PRIMARY KEY,
 first TEXT,
 last TEXT
);

CREATE TABLE grade (
 student INT NOT NULL REFERENCES student(id),
 exam INT NOT NULL REFERENCES exam(id),
 grade decimal(5,2) NOT NULL,
 PRIMARY KEY (student,exam)
);

INSERT INTO exam(date,title,max_grade,type,coefficient) VALUES
  ('2014-03-04','Devoir Python',20,'dm',1),
  ('2014-03-04','Interrogation Python',10,'exam',2);
INSERT INTO student(first,last) VALUES ('John','Doe'),('Jane','Doe');
INSERT INTO grade VALUES (1,1,15),(1,2,8),(2,1,17.25),(2,2,4.5);

SELECT * FROM exam;

SELECT * FROM exam WHERE id=2;

SELECT student,first,last,grade
FROM grade,student 
WHERE grade.exam=1 AND grade.student=student.id;

SELECT student,first,last,
       ROUND(SUM(grade/max_grade*coefficient)*20/SUM(coefficient),2) AS grade
FROM grade,exam,student
WHERE grade.exam=exam.id AND grade.student=student.id
GROUP BY student,first,last;
