lab2_dbd(done)

DROP table STUDENT;
DROP table GRADES;
DROP table COURSE;

CREATE table STUDENT
(
	CODS int primary key,
	name varchar2(20),
	birthdate date
);


CREATE table COURSE
(
	CODC int primary key,
	name varchar2(20),
	professor varchar2(20)
);

CREATE table GRADES
(
	CODS int,
	CODC int,
	CONSTRAINT fk_cods FOREIGN KEY(CODS) REFERENCES STUDENT(CODS),
	CONSTRAINT fk_codc FOREIGN KEY(CODC) REFERENCES COURSE(CODC),
	grade FLOAT,
	prezentare int
);

INSERT INTO STUDENT(CODS, name, birthdate)
VALUES(1,'John Doe', TO_DATE('2003/05/03', 'yyyy/mm/dd'));

INSERT INTO STUDENT(CODS, name, birthdate)
VALUES(2,'Alice Doe', TO_DATE('2004/05/03', 'yyyy/mm/dd'));

INSERT INTO STUDENT(CODS, name, birthdate)
VALUES(3,'Jim Carrey', TO_DATE('1998/05/03', 'yyyy/mm/dd'));


INSERT INTO STUDENT(CODS, name, birthdate)
VALUES(4,'Jimmy Stoya', TO_DATE('1995/06/03', 'yyyy/mm/dd'));

INSERT INTO COURSE(CODC, name, professor)
VALUES(1,'Assembly','Ron Wisley');

INSERT INTO COURSE(CODC, name, professor)
VALUES(2,'Web Development','Johnny Wonka');

INSERT INTO COURSE(CODC, name, professor)
VALUES(3,'Databases','Richard Long');

INSERT INTO GRADES(CODC, CODS, grade, prezentare)
VALUES(1,1,10,1);

INSERT INTO GRADES(CODC, CODS, grade, prezentare)
VALUES(1,2,9,1);

INSERT INTO GRADES(CODC, CODS, grade, prezentare)
VALUES(2,1,9,1);

INSERT INTO GRADES(CODC, CODS, grade, prezentare)
VALUES(2,3,4,3);

INSERT INTO GRADES(CODC, CODS, grade, prezentare)
VALUES(1,4,3,3);

--Extract a table containing the following information:
--STUDENT_NAME, COURSE_NAME, GRADE

DROP VIEW STUDENTS_VIEW;
CREATE VIEW STUDENTS_VIEW(STUDENT, COURSE, GRADE) AS
SELECT DISTINCT S.NAME NUME_STUDENT, C.NAME NUME_CURS, G.GRADE GRADE
FROM GRADES G, STUDENT S, COURSE C
WHERE G.CODC=C.CODC AND G.CODS=S.CODS;
SELECT * FROM STUDENTS_VIEW;


--Extract a table containing all students which failed at certain courses and the
--corresponfing course names.

DROP VIEW FAILED_VIEW;
CREATE VIEW FAILED_VIEW(STUDENT, COURSE, GRADE) AS
SELECT DISTINCT S.NAME NUME_STUDENT, C.NAME NUME_CURS, G.GRADE GRADE
FROM GRADES G, STUDENT S, COURSE C
WHERE G.CODS=S.CODS AND
	G.CODC=C.CODC AND
	GRADE < 5 AND
	G.PREZENTARE >= 3;
SELECT * FROM FAILED_VIEW;


--Count all the students with "failed" courses

SELECT COUNT(*) failed_students FROM FAILED_VIEW;



--Extract a table with all the students signed for (received at least a grade for

--the exam corresponfing to that course in our case) a certain proffesor's course

--(specified through the PROFFESOR field).

CREATE VIEW SIGNED_STUDENTS(STUDENT, COURSE, PROFESSOR, GRADE)
AS SELECT S.NAME, C.NAME, C.PROFESSOR, G.GRADE
FROM GRADES G, STUDENT S, COURSE C
WHERE G.CODS=S.CODS AND G.CODC=C.CODC
GROUP BY C.PROFESSOR, C.NAME, S.NAME, G.GRADE;

SELECT DISTINCT P.STUDENT
FROM SIGNED_STUDENTS P
WHERE P.PROFESSOR='Ron Wisley';


-- Update the GRADES table by promoting all the students not receiving passing marks (grade < 5)
UPDATE GRADES
SET grade = 8
WHERE grade < 5;


--Extract a Student top based on their marks average (all marks, even if a student
--received multiple marks for a certain course).
CREATE VIEW STUDENTS_TOP_MARK
AS SELECT S.NAME, MAX(G.GRADE) HIGHEST_MARK, C.CODC
FROM student S, grades G, course C
WHERE S.CODS=G.CODS and G.CODC=C.CODC GROUP BY S.NAME, C.CODC;
SELECT * FROM STUDENTS_TOP_MARK;

CREATE VIEW STUDENT_AVERAGE(STUDENT, GRADE)
AS SELECT b.NAME STUDENT, AVG(b.HIGHEST_MARK)
FROM STUDENTS_TOP_MARK b
GROUP BY b.NAME ORDER BY  AVG(b.HIGHEST_MARK);
SELECT * FROM STUDENT_AVERAGE WHERE GRADE IS NOT NULL ORDER BY GRADE DESC;

--Similar to h. - but you have to extract only the students without failed exams
--and with an average grade above 8.5
SELECT * FROM STUDENT_AVERAGE
WHERE STUDENT NOT IN
	(SELECT STUDENT FROM FAILED_VIEW)
	AND GRADE >= 8.5;