Student Status

-- (a)
CREATE TABLE DEPT
(
    DEPT_ID NUMBER PRIMARY KEY,
    DEPT_NAME VARCHAR2(20)
);

CREATE TABLE PROG
(
    PROG_ID NUMBER PRIMARY KEY,
    PROG_NAME VARCHAR2(20),
    DEPT_ID NUMBER,
    FOREIGN KEY(DEPT_ID) REFERENCES DEPT
);

CREATE TABLE COURSES
(
    COURSE_ID NUMBER PRIMARY KEY,
    COURSE_NAME VARCHAR2(20),
    CREDIT NUMBER
);

CREATE TABLE STUDENTS
(
    S_ID NUMBER,
    S_NAME NUMBER,
    DEPT_ID NUMBER,
    PROG_ID NUMBER,
    S_DOB DATE,
    S_AY NUMBER,
    S_SEMESTER NUMBER,
    S_GPA NUMBER,
    FOREIGN KEY(DEPT_ID) REFERENCES DEPT,
    FOREIGN KEY(PROG_ID) REFERENCES PROG,
    CHECK (S_GPA BETWEEN 0 AND 4)
);

CREATE TABLE RESULTS
(
    S_ID NUMBER,
    COURSE_ID NUMBER,
    AY NUMBER,
    SEM NUMBER,
    LETTER_GRADE VARCHAR2(5)
);

CREATE TABLE GRADES_EXPLAINED
(
    LETTER VARCHAR2(5) PRIMARY KEY,
    NUM_VALUE NUMBER,
    CHECK (NUM_VALUE BETWEEN 0 AND 4)
);

-- (b)
-- i)
SELECT DEPT_NAME, PROG_NAME, COURSE_NAME, LETTER_GRADE
FROM STUDENTS, DEPT, PROG, COURSES, RESULTS
WHERE STUDENTS.DEPT_ID = DEPT.DEPT_ID AND
STUDENTS.PROG_ID = PROG.PROG_ID AND
STUDENTS.S_ID = RESULTS.S_ID AND
RESULTS.COURSE_ID =  COURSES.COURSE_ID AND
STUDENTS.S_ID = '170042075';

-- ii)
SELECT S_ID, S_NAME
FROM STUDENTS
WHERE S_GPA > (SELECT MIN(AVERAGE)
FROM (SELECT AVG(S_GPA) AS AVERAGE
FROM STUDENTS
GROUP BY DEPT_ID));

-- (c)
CREATE OR REPLACE FUNCTION
FIND_STATUS(STUDENT_ID NUMBER)
RETURN VARCHAR2
AS
STATUS VARCHAR2(20);
CURR_AY NUMBER;
CURR_SEM NUMBER;
FAIL_COUNT NUMBER;
BEGIN
    SELECT MAX(S_AY), MAX(S_SEMESTER) INTO CURR_AY, CURR_SEM
    FROM STUDENTS
    WHERE S_ID = STUDENT_ID;
    SELECT COUNT(*) INTO FAIL_COUNT
    FROM RESULTS
    WHERE AY = CURR_AY AND
    SEM = CURR_SEM AND
    S_ID = STUDENT_ID AND
    LETTER_GRADE = 'F';
    IF FAIL_COUNT > 2 THEN
        STATUS := 'FAILED';
    ELSIF FAIL_COUNT > 0 THEN
        STATUS := 'REFERRED';
    ELSE
        STATUS := 'PASSED';
    END IF;
    RETURN STATUS;
END;
/