dm_connection.sql

CREATE TABLE teachers (
    teacher_id VARCHAR2(10) PRIMARY KEY,  -- will be like T1000
   password VARCHAR2(100) NOT NULL,
    email VARCHAR2(100) UNIQUE NOT NULL,
    created_at DATE DEFAULT SYSDATE
);

insert into teachers( email , password)VALUES('admin@gmail.com', '1234');

CREATE SEQUENCE seq_teacher_id
START WITH 1000  -- starting number
INCREMENT BY 1
;


CREATE OR REPLACE TRIGGER trg_teacher_id
BEFORE INSERT ON teachers
FOR EACH ROW
BEGIN
    IF :NEW.teacher_id IS NULL THEN
        :NEW.teacher_id := 'T' || TO_CHAR(seq_teacher_id.NEXTVAL);
    END IF;
END;





CREATE TABLE classes (
    class_id VARCHAR2(10) PRIMARY KEY,  -- C1000 onwards
    name VARCHAR2(50) NOT NULL,
    created_at DATE DEFAULT SYSDATE
);






CREATE TABLE students (
    student_id VARCHAR2(10) PRIMARY KEY,  -- ST1000 onwards
    name VARCHAR2(100) NOT NULL,
    age NUMBER,
    email VARCHAR2(100) UNIQUE NOT NULL,
    created_at DATE DEFAULT SYSDATE
);







CREATE TABLE student_classes (
    student_id VARCHAR2(10) REFERENCES students(student_id),
    class_id VARCHAR2(10) REFERENCES classes(class_id),
    PRIMARY KEY (student_id, class_id)
);



SELECT * FROM student_classes;





CREATE TABLE subjects (
    subject_id VARCHAR2(10) PRIMARY KEY,  -- S1000 onwards
    teacher_id VARCHAR2(10) REFERENCES teachers(teacher_id),
    name VARCHAR2(100) NOT NULL,
    duration NUMBER,   -- e.g., weeks
    created_at DATE DEFAULT SYSDATE
);


CREATE TABLE student_subjects (
    student_id VARCHAR2(10) REFERENCES students(student_id),
    subject_id VARCHAR2(10) REFERENCES subjects(subject_id),
    PRIMARY KEY (student_id, subject_id)
);



CREATE TABLE grades (
    grade_id VARCHAR2(10) PRIMARY KEY,  -- G1000 onwards
    student_id VARCHAR2(10) REFERENCES students(student_id),
    subject_id VARCHAR2(10) REFERENCES subjects(subject_id),
    mark NUMBER(5,2),
    gpa NUMBER(3,2),
    created_at DATE DEFAULT SYSDATE
);

CREATE SEQUENCE seq_grade_id START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE OR REPLACE TRIGGER trg_grade_id
BEFORE INSERT ON grades
FOR EACH ROW
BEGIN
    IF :NEW.grade_id IS NULL THEN
        :NEW.grade_id := 'G' || TO_CHAR(seq_grade_id.NEXTVAL);
    END IF;
END;







--func and procedures to solve things like high/lw gpa--




CREATE OR REPLACE FUNCTION get_class_stats(p_class_id VARCHAR2)
RETURN VARCHAR2
IS
    v_num_students NUMBER;
    v_highest_gpa NUMBER;
    v_lowest_gpa NUMBER;
    v_avg_gpa NUMBER;
    v_range NUMBER;
    v_result VARCHAR2(500);
BEGIN
    SELECT COUNT(DISTINCT s.student_id),
           MAX(g.gpa),
           MIN(g.gpa),
           AVG(g.gpa),
           MAX(g.gpa)-MIN(g.gpa)
    INTO v_num_students, v_highest_gpa, v_lowest_gpa, v_avg_gpa, v_range
    FROM students s
    JOIN student_classes sc ON s.student_id = sc.student_id
    JOIN grades g ON s.student_id = g.student_id
    WHERE sc.class_id = p_class_id;

    v_result := 'Number of Students: ' || v_num_students ||
                ', Highest GPA: ' || v_highest_gpa ||
                ', Lowest GPA: ' || v_lowest_gpa ||
                ', Average GPA: ' || ROUND(v_avg_gpa,2) ||
                ', GPA Range: ' || ROUND(v_range,2);
    RETURN v_result;
END;


















CREATE TABLE student_gpa (
    student_id VARCHAR2(10) PRIMARY KEY
        REFERENCES students(student_id) ON DELETE CASCADE,
    gpa NUMBER(4,2)
);


CREATE OR REPLACE TRIGGER trg_update_gpa
AFTER INSERT OR UPDATE ON grades
FOR EACH ROW
DECLARE
    v_new_gpa NUMBER;
BEGIN
    v_new_gpa := calc_gpa(:NEW.student_id);

    MERGE INTO student_gpa g
    USING (SELECT :NEW.student_id AS sid FROM dual) d
    ON (g.student_id = d.sid)
    WHEN MATCHED THEN
        UPDATE SET g.gpa = v_new_gpa
    WHEN NOT MATCHED THEN
        INSERT (student_id, gpa)
        VALUES (:NEW.student_id, v_new_gpa);
END;



SELECT * FROM students;

INSERT INTO classes (name)
VALUES ('Class A');

COMMIT;


SELECT trigger_name, status
FROM user_triggers
WHERE trigger_name = 'TRG_CLASS_ID';

ALTER TRIGGER trg_class_id COMPILE;
SHOW ERRORS TRIGGER trg_class_id;






DROP TRIGGER trg_class_id;
DROP SEQUENCE seq_class_id;

CREATE SEQUENCE seq_class_id
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;


CREATE SEQUENCE STUDENT_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE MARK_SEQ START WITH 1 INCREMENT BY 1;
















SELECT * FROM students;
CREATE OR REPLACE PROCEDURE upsert_grade(
    p_student_id VARCHAR2,
    p_subject_id VARCHAR2,
    p_mark NUMBER
)
AS
BEGIN
    MERGE INTO grades g
    USING (SELECT p_student_id AS student_id, p_subject_id AS subject_id FROM dual) src
    ON (g.student_id = src.student_id AND g.subject_id = src.subject_id)
    WHEN MATCHED THEN
        UPDATE SET mark = p_mark, created_at = SYSDATE
    WHEN NOT MATCHED THEN
        INSERT (grade_id, student_id, subject_id, mark, created_at)
        VALUES ('G' || grades_seq.NEXTVAL, p_student_id, p_subject_id, p_mark, SYSDATE);
END;/


SELECT object_name, status 
FROM all_objects 
WHERE object_type = 'PROCEDURE' 
  AND object_name = 'UPSERT_STUDENT';

ALTER PROCEDURE UPSERT_STUDENT COMPILE;
SHOW ERRORS PROCEDURE SYSTEM.UPSERT_STUDENT;
DESC UPSERT_STUDENT;

SELECT owner, type_name, typecode
FROM all_types
WHERE type_name = 'P_CLASS_IDS_TYPE';


CREATE OR REPLACE FUNCTION get_gpa(p_student_id VARCHAR2) RETURN NUMBER IS
    v_gpa NUMBER;
BEGIN
    SELECT AVG(mark)/20 INTO v_gpa
    FROM grades
    WHERE student_id = p_student_id;
    RETURN v_gpa;
END;


DESCRIBE GET_GPA;






SELECT object_name
FROM user_objects
WHERE object_type = 'PROCEDURE';

-- List all functions
SELECT object_name
FROM user_objects
WHERE object_type = 'FUNCTION';


















-- Total students
CREATE OR REPLACE FUNCTION GET_TOTAL_STUDENTS RETURN NUMBER IS
  total NUMBER;
BEGIN
  SELECT COUNT(*) INTO total FROM STUDENTS;
  RETURN total;
END;
/

-- Total classes
CREATE OR REPLACE FUNCTION GET_TOTAL_CLASSES RETURN NUMBER IS
  total NUMBER;
BEGIN
  SELECT COUNT(*) INTO total FROM CLASSES;
  RETURN total;
END;
/

-- Average GPA
CREATE OR REPLACE FUNCTION GET_AVG_GPA RETURN NUMBER IS
  avg_gpa NUMBER;
BEGIN
  SELECT AVG(MARK/20) INTO avg_gpa FROM GRADES; -- Assuming GPA = MARK / 20
  RETURN avg_gpa;
END;
/

-- Highest GPA
CREATE OR REPLACE FUNCTION GET_HIGHEST_GPA RETURN NUMBER IS
  max_gpa NUMBER;
BEGIN
  SELECT MAX(MARK/20) INTO max_gpa FROM GRADES;
  RETURN max_gpa;
END;
/

-- Lowest GPA
CREATE OR REPLACE FUNCTION GET_LOWEST_GPA RETURN NUMBER IS
  min_gpa NUMBER;
BEGIN
  SELECT MIN(MARK/20) INTO min_gpa FROM GRADES;
  RETURN min_gpa;
END;
/

-- Count of failing students (below threshold)
CREATE OR REPLACE FUNCTION GET_FAILING_COUNT(threshold NUMBER) RETURN NUMBER IS
  fail_count NUMBER;
BEGIN
  SELECT COUNT(DISTINCT STUDENT_ID) INTO fail_count
  FROM GRADES
  WHERE MARK/20 < threshold;
  RETURN fail_count;
END;
/




-- Average GPA per class
CREATE OR REPLACE PROCEDURE GET_AVG_GPA_BY_CLASS(p_cursor OUT SYS_REFCURSOR) IS
BEGIN
  OPEN p_cursor FOR
    SELECT sc.CLASS_ID, c.NAME AS CLASS_NAME, AVG(g.MARK/20) AS AVG_GPA
    FROM STUDENT_CLASSES sc
    JOIN CLASSES c ON sc.CLASS_ID = c.CLASS_ID
    JOIN GRADES g ON sc.STUDENT_ID = g.STUDENT_ID
    GROUP BY sc.CLASS_ID, c.NAME;
END;
/

-- Top performing students
CREATE OR REPLACE PROCEDURE GET_TOP_STUDENTS(p_cursor OUT SYS_REFCURSOR, p_limit IN NUMBER) IS
BEGIN
  OPEN p_cursor FOR
    SELECT s.STUDENT_ID, s.NAME, AVG(g.MARK/20) AS GPA
    FROM STUDENTS s
    JOIN GRADES g ON s.STUDENT_ID = g.STUDENT_ID
    GROUP BY s.STUDENT_ID, s.NAME
    ORDER BY GPA DESC
    FETCH FIRST p_limit ROWS ONLY;
END;
/

-- Class ranking
CREATE OR REPLACE PROCEDURE GET_CLASS_RANKING(p_cursor OUT SYS_REFCURSOR) IS
BEGIN
  OPEN p_cursor FOR
    SELECT CLASS_ID, NAME, AVG_GPA,
           RANK() OVER (ORDER BY AVG_GPA DESC) AS RANKING
    FROM (
      SELECT sc.CLASS_ID, c.NAME, AVG(g.MARK/20) AS AVG_GPA
      FROM STUDENT_CLASSES sc
      JOIN CLASSES c ON sc.CLASS_ID = c.CLASS_ID
      JOIN GRADES g ON sc.STUDENT_ID = g.STUDENT_ID
      GROUP BY sc.CLASS_ID, c.NAME
    );
END;
/



CREATE OR REPLACE PROCEDURE GET_GRADE_DISTRIBUTION(p_cursor OUT SYS_REFCURSOR) IS
BEGIN
  OPEN p_cursor FOR
    SELECT 
      CASE 
        WHEN g.MARK/20 >= 3.5 THEN 'A'
        WHEN g.MARK/20 >= 3.0 THEN 'B'
        WHEN g.MARK/20 >= 2.0 THEN 'C'
        ELSE 'F'
      END AS GRADE,
      COUNT(*) AS STUDENT_COUNT
    FROM GRADES g
    GROUP BY 
      CASE 
        WHEN g.MARK/20 >= 3.5 THEN 'A'
        WHEN g.MARK/20 >= 3.0 THEN 'B'
        WHEN g.MARK/20 >= 2.0 THEN 'C'
        ELSE 'F'
      END
    ORDER BY GRADE;
END;
/


SHOW ERRORS PROCEDURE GET_GRADE_DISTRIBUTION;

CREATE SEQUENCE GRADE_SEQ START WITH 1 INCREMENT BY 1;


















CREATE OR REPLACE FUNCTION calc_gpa(p_student_id VARCHAR2) RETURN NUMBER
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_gpa NUMBER;
BEGIN
    SELECT AVG(mark)/20
    INTO v_gpa
    FROM grades
    WHERE student_id = p_student_id;

    RETURN v_gpa;
END;
/

CREATE OR REPLACE TRIGGER trg_update_gpa
AFTER INSERT OR UPDATE ON grades
FOR EACH ROW
DECLARE
    v_new_gpa NUMBER;
BEGIN
    v_new_gpa := calc_gpa(:NEW.student_id);

    MERGE INTO student_gpa g
    USING (SELECT :NEW.student_id AS sid FROM dual) d
    ON (g.student_id = d.sid)
    WHEN MATCHED THEN
        UPDATE SET g.gpa = v_new_gpa
    WHEN NOT MATCHED THEN
        INSERT (student_id, gpa)
        VALUES (:NEW.student_id, v_new_gpa);

END;
/

DESC grades;



ALTER TABLE student_classes
ADD CONSTRAINT student_classes_fk_class
FOREIGN KEY (class_id)
REFERENCES classes(class_id)
ON DELETE CASCADE;

-- Sequence for Class IDs
CREATE SEQUENCE seq_class_id
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;






CREATE TABLE teachers (
    teacher_id VARCHAR2(10) PRIMARY KEY,  -- will be like T1000
   password VARCHAR2(100) NOT NULL,
    email VARCHAR2(100) UNIQUE NOT NULL,
    created_at DATE DEFAULT SYSDATE
);
create or replace NONEDITIONABLE PROCEDURE assign_class_to_student(
    p_student_id IN VARCHAR2,
    p_class_input IN VARCHAR2 -- can be class_id or class name
) AS
    v_class_id VARCHAR2(50);
    v_count NUMBER;
BEGIN
    -- Check if student exists
    SELECT COUNT(*) INTO v_count
    FROM students
    WHERE student_id = p_student_id
      AND is_deleted = 'N';

    IF v_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Student ID does not exist: ' || p_student_id);
    END IF;

    -- Find class by ID or name
    SELECT class_id INTO v_class_id
    FROM classes
    WHERE (class_id = p_class_input OR name = p_class_input)
      AND is_deleted = 'N';

    -- Remove old class assignments for this student
    DELETE FROM student_classes
    WHERE student_id = p_student_id;

    -- Assign the new class
    INSERT INTO student_classes(student_id, class_id)
    VALUES (p_student_id, v_class_id);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20002, 'Class does not exist: ' || p_class_input);
    WHEN OTHERS THEN
        RAISE;
END; 

create or replace NONEDITIONABLE PROCEDURE ASSIGN_SUBJECT_TO_STUDENT(
    p_student_id IN VARCHAR2,
    p_subject_id IN VARCHAR2
)
AS
    v_count NUMBER;
BEGIN
    -- Check if the student already has this subject
    SELECT COUNT(*) 
    INTO v_count
    FROM student_subjects
    WHERE student_id = p_student_id
      AND subject_id = p_subject_id;

    -- If not assigned, insert
    IF v_count = 0 THEN
        INSERT INTO student_subjects (student_id, subject_id)
        VALUES (p_student_id, p_subject_id);
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END ASSIGN_SUBJECT_TO_STUDENT; 

create or replace NONEDITIONABLE PROCEDURE get_marks_by_student (
    p_student_id IN VARCHAR2,
    p_cursor     OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT
            subject_id,
            mark
        FROM grades
        WHERE student_id = p_student_id;
END; 

create or replace NONEDITIONABLE PROCEDURE get_class_name(
    p_class_id IN VARCHAR2,
    p_class_name OUT VARCHAR2
)
AS
BEGIN
    SELECT name INTO p_class_name
    FROM classes
    WHERE class_id = p_class_id
      AND is_deleted = 'N';
END; create or replace NONEDITIONABLE PROCEDURE get_classes_by_student(
    p_student_id IN VARCHAR2,
    p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT c.class_id, c.name, c.created_at
        FROM classes c
        JOIN student_classes sc ON c.class_id = sc.class_id
        WHERE sc.student_id = p_student_id
          AND c.is_deleted = 'N';
END;


insert into teachers( email , password)VALUES('admin@gmail.com', '1234');

CREATE SEQUENCE seq_teacher_id
START WITH 1000  -- starting number
INCREMENT BY 1
;


CREATE OR REPLACE TRIGGER trg_teacher_id
BEFORE INSERT ON teachers
FOR EACH ROW
BEGIN
    IF :NEW.teacher_id IS NULL THEN
        :NEW.teacher_id := 'T' || TO_CHAR(seq_teacher_id.NEXTVAL);
    END IF;
END;





CREATE TABLE classes (
    class_id VARCHAR2(10) PRIMARY KEY,  -- C1000 onwards
    name VARCHAR2(50) NOT NULL,
    created_at DATE DEFAULT SYSDATE
);






CREATE TABLE students (
    student_id VARCHAR2(10) PRIMARY KEY,  -- ST1000 onwards
    name VARCHAR2(100) NOT NULL,
    age NUMBER,
    email VARCHAR2(100) UNIQUE NOT NULL,
    created_at DATE DEFAULT SYSDATE
);







CREATE TABLE student_classes (
    student_id VARCHAR2(10) REFERENCES students(student_id),
    class_id VARCHAR2(10) REFERENCES classes(class_id),
    PRIMARY KEY (student_id, class_id)
);



SELECT * FROM student_classes;





CREATE TABLE subjects (
    subject_id VARCHAR2(10) PRIMARY KEY,  -- S1000 onwards
    teacher_id VARCHAR2(10) REFERENCES teachers(teacher_id),
    name VARCHAR2(100) NOT NULL,
    duration NUMBER,   -- e.g., weeks
    created_at DATE DEFAULT SYSDATE
);


CREATE TABLE student_subjects (
    student_id VARCHAR2(10) REFERENCES students(student_id),
    subject_id VARCHAR2(10) REFERENCES subjects(subject_id),
    PRIMARY KEY (student_id, subject_id)
);



CREATE TABLE grades (
    grade_id VARCHAR2(10) PRIMARY KEY,  -- G1000 onwards
    student_id VARCHAR2(10) REFERENCES students(student_id),
    subject_id VARCHAR2(10) REFERENCES subjects(subject_id),
    mark NUMBER(5,2),
    gpa NUMBER(3,2),
    created_at DATE DEFAULT SYSDATE
);

CREATE SEQUENCE seq_grade_id START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE OR REPLACE TRIGGER trg_grade_id
BEFORE INSERT ON grades
FOR EACH ROW
BEGIN
    IF :NEW.grade_id IS NULL THEN
        :NEW.grade_id := 'G' || TO_CHAR(seq_grade_id.NEXTVAL);
    END IF;
END;







--func and procedures to solve things like high/lw gpa--




CREATE OR REPLACE FUNCTION get_class_stats(p_class_id VARCHAR2)
RETURN VARCHAR2
IS
    v_num_students NUMBER;
    v_highest_gpa NUMBER;
    v_lowest_gpa NUMBER;
    v_avg_gpa NUMBER;
    v_range NUMBER;
    v_result VARCHAR2(500);
BEGIN
    SELECT COUNT(DISTINCT s.student_id),
           MAX(g.gpa),
           MIN(g.gpa),
           AVG(g.gpa),
           MAX(g.gpa)-MIN(g.gpa)
    INTO v_num_students, v_highest_gpa, v_lowest_gpa, v_avg_gpa, v_range
    FROM students s
    JOIN student_classes sc ON s.student_id = sc.student_id
    JOIN grades g ON s.student_id = g.student_id
    WHERE sc.class_id = p_class_id;

    v_result := 'Number of Students: ' || v_num_students ||
                ', Highest GPA: ' || v_highest_gpa ||
                ', Lowest GPA: ' || v_lowest_gpa ||
                ', Average GPA: ' || ROUND(v_avg_gpa,2) ||
                ', GPA Range: ' || ROUND(v_range,2);
    RETURN v_result;
END;


















CREATE TABLE student_gpa (
    student_id VARCHAR2(10) PRIMARY KEY
        REFERENCES students(student_id) ON DELETE CASCADE,
    gpa NUMBER(4,2)
);


CREATE OR REPLACE TRIGGER trg_update_gpa
AFTER INSERT OR UPDATE ON grades
FOR EACH ROW
DECLARE
    v_new_gpa NUMBER;
BEGIN
    v_new_gpa := calc_gpa(:NEW.student_id);

    MERGE INTO student_gpa g
    USING (SELECT :NEW.student_id AS sid FROM dual) d
    ON (g.student_id = d.sid)
    WHEN MATCHED THEN
        UPDATE SET g.gpa = v_new_gpa
    WHEN NOT MATCHED THEN
        INSERT (student_id, gpa)
        VALUES (:NEW.student_id, v_new_gpa);
END;



SELECT * FROM students;

INSERT INTO classes (name)
VALUES ('Class A');

COMMIT;


SELECT trigger_name, status
FROM user_triggers
WHERE trigger_name = 'TRG_CLASS_ID';

ALTER TRIGGER trg_class_id COMPILE;
SHOW ERRORS TRIGGER trg_class_id;






DROP TRIGGER trg_class_id;
DROP SEQUENCE seq_class_id;

CREATE SEQUENCE seq_class_id
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;


CREATE SEQUENCE STUDENT_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE MARK_SEQ START WITH 1 INCREMENT BY 1;
















SELECT * FROM students;
CREATE OR REPLACE PROCEDURE upsert_grade(
    p_student_id VARCHAR2,
    p_subject_id VARCHAR2,
    p_mark NUMBER
)
AS
BEGIN
    MERGE INTO grades g
    USING (SELECT p_student_id AS student_id, p_subject_id AS subject_id FROM dual) src
    ON (g.student_id = src.student_id AND g.subject_id = src.subject_id)
    WHEN MATCHED THEN
        UPDATE SET mark = p_mark, created_at = SYSDATE
    WHEN NOT MATCHED THEN
        INSERT (grade_id, student_id, subject_id, mark, created_at)
        VALUES ('G' || grades_seq.NEXTVAL, p_student_id, p_subject_id, p_mark, SYSDATE);
END;/


SELECT object_name, status 
FROM all_objects 
WHERE object_type = 'PROCEDURE' 
  AND object_name = 'UPSERT_STUDENT';

ALTER PROCEDURE UPSERT_STUDENT COMPILE;
SHOW ERRORS PROCEDURE SYSTEM.UPSERT_STUDENT;
DESC UPSERT_STUDENT;

SELECT owner, type_name, typecode
FROM all_types
WHERE type_name = 'P_CLASS_IDS_TYPE';


CREATE OR REPLACE FUNCTION get_gpa(p_student_id VARCHAR2) RETURN NUMBER IS
    v_gpa NUMBER;
BEGIN
    SELECT AVG(mark)/20 INTO v_gpa
    FROM grades
    WHERE student_id = p_student_id;
    RETURN v_gpa;
END;


DESCRIBE GET_GPA;






SELECT object_name
FROM user_objects
WHERE object_type = 'PROCEDURE';

-- List all functions
SELECT object_name
FROM user_objects
WHERE object_type = 'FUNCTION';


















-- Total students
CREATE OR REPLACE FUNCTION GET_TOTAL_STUDENTS RETURN NUMBER IS
  total NUMBER;
BEGIN
  SELECT COUNT(*) INTO total FROM STUDENTS;
  RETURN total;
END;
/

-- Total classes
CREATE OR REPLACE FUNCTION GET_TOTAL_CLASSES RETURN NUMBER IS
  total NUMBER;
BEGIN
  SELECT COUNT(*) INTO total FROM CLASSES;
  RETURN total;
END;
/

-- Average GPA
CREATE OR REPLACE FUNCTION GET_AVG_GPA RETURN NUMBER IS
  avg_gpa NUMBER;
BEGIN
  SELECT AVG(MARK/20) INTO avg_gpa FROM GRADES; -- Assuming GPA = MARK / 20
  RETURN avg_gpa;
END;
/

-- Highest GPA
CREATE OR REPLACE FUNCTION GET_HIGHEST_GPA RETURN NUMBER IS
  max_gpa NUMBER;
BEGIN
  SELECT MAX(MARK/20) INTO max_gpa FROM GRADES;
  RETURN max_gpa;
END;
/

-- Lowest GPA
CREATE OR REPLACE FUNCTION GET_LOWEST_GPA RETURN NUMBER IS
  min_gpa NUMBER;
BEGIN
  SELECT MIN(MARK/20) INTO min_gpa FROM GRADES;
  RETURN min_gpa;
END;
/

-- Count of failing students (below threshold)
CREATE OR REPLACE FUNCTION GET_FAILING_COUNT(threshold NUMBER) RETURN NUMBER IS
  fail_count NUMBER;
BEGIN
  SELECT COUNT(DISTINCT STUDENT_ID) INTO fail_count
  FROM GRADES
  WHERE MARK/20 < threshold;
  RETURN fail_count;
END;
/




-- Average GPA per class
CREATE OR REPLACE PROCEDURE GET_AVG_GPA_BY_CLASS(p_cursor OUT SYS_REFCURSOR) IS
BEGIN
  OPEN p_cursor FOR
    SELECT sc.CLASS_ID, c.NAME AS CLASS_NAME, AVG(g.MARK/20) AS AVG_GPA
    FROM STUDENT_CLASSES sc
    JOIN CLASSES c ON sc.CLASS_ID = c.CLASS_ID
    JOIN GRADES g ON sc.STUDENT_ID = g.STUDENT_ID
    GROUP BY sc.CLASS_ID, c.NAME;
END;
/

-- Top performing students
CREATE OR REPLACE PROCEDURE GET_TOP_STUDENTS(p_cursor OUT SYS_REFCURSOR, p_limit IN NUMBER) IS
BEGIN
  OPEN p_cursor FOR
    SELECT s.STUDENT_ID, s.NAME, AVG(g.MARK/20) AS GPA
    FROM STUDENTS s
    JOIN GRADES g ON s.STUDENT_ID = g.STUDENT_ID
    GROUP BY s.STUDENT_ID, s.NAME
    ORDER BY GPA DESC
    FETCH FIRST p_limit ROWS ONLY;
END;
/

-- Class ranking
CREATE OR REPLACE PROCEDURE GET_CLASS_RANKING(p_cursor OUT SYS_REFCURSOR) IS
BEGIN
  OPEN p_cursor FOR
    SELECT CLASS_ID, NAME, AVG_GPA,
           RANK() OVER (ORDER BY AVG_GPA DESC) AS RANKING
    FROM (
      SELECT sc.CLASS_ID, c.NAME, AVG(g.MARK/20) AS AVG_GPA
      FROM STUDENT_CLASSES sc
      JOIN CLASSES c ON sc.CLASS_ID = c.CLASS_ID
      JOIN GRADES g ON sc.STUDENT_ID = g.STUDENT_ID
      GROUP BY sc.CLASS_ID, c.NAME
    );
END;
/



CREATE OR REPLACE PROCEDURE GET_GRADE_DISTRIBUTION(p_cursor OUT SYS_REFCURSOR) IS
BEGIN
  OPEN p_cursor FOR
    SELECT 
      CASE 
        WHEN g.MARK/20 >= 3.5 THEN 'A'
        WHEN g.MARK/20 >= 3.0 THEN 'B'
        WHEN g.MARK/20 >= 2.0 THEN 'C'
        ELSE 'F'
      END AS GRADE,
      COUNT(*) AS STUDENT_COUNT
    FROM GRADES g
    GROUP BY 
      CASE 
        WHEN g.MARK/20 >= 3.5 THEN 'A'
        WHEN g.MARK/20 >= 3.0 THEN 'B'
        WHEN g.MARK/20 >= 2.0 THEN 'C'
        ELSE 'F'
      END
    ORDER BY GRADE;
END;
/


SHOW ERRORS PROCEDURE GET_GRADE_DISTRIBUTION;

CREATE SEQUENCE GRADE_SEQ START WITH 1 INCREMENT BY 1;


















CREATE OR REPLACE FUNCTION calc_gpa(p_student_id VARCHAR2) RETURN NUMBER
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_gpa NUMBER;
BEGIN
    SELECT AVG(mark)/20
    INTO v_gpa
    FROM grades
    WHERE student_id = p_student_id;

    RETURN v_gpa;
END;
/

CREATE OR REPLACE TRIGGER trg_update_gpa
AFTER INSERT OR UPDATE ON grades
FOR EACH ROW
DECLARE
    v_new_gpa NUMBER;
BEGIN
    v_new_gpa := calc_gpa(:NEW.student_id);

    MERGE INTO student_gpa g
    USING (SELECT :NEW.student_id AS sid FROM dual) d
    ON (g.student_id = d.sid)
    WHEN MATCHED THEN
        UPDATE SET g.gpa = v_new_gpa
    WHEN NOT MATCHED THEN
        INSERT (student_id, gpa)
        VALUES (:NEW.student_id, v_new_gpa);

END;
/

DESC grades;



ALTER TABLE student_classes
ADD CONSTRAINT student_classes_fk_class
FOREIGN KEY (class_id)
REFERENCES classes(class_id)
ON DELETE CASCADE;
-- Sequence for Class IDs
CREATE SEQUENCE seq_class_id
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;





SELECT object_name, status
FROM user_objects
WHERE object_type = 'PROCEDURE'
  AND object_name = 'UPSERT_CLASS';

SHOW ERRORS PROCEDURE upsert_class;

ALTER TABLE classes ADD is_deleted CHAR(1) DEFAULT 'N';

CREATE OR REPLACE PROCEDURE upsert_class(
    p_action    IN VARCHAR2,       -- 'ADD', 'UPDATE', 'DELETE'
    p_class_id  IN OUT VARCHAR2,
    p_name      IN VARCHAR2 DEFAULT NULL
) AS
    v_count NUMBER;
BEGIN
    IF p_action = 'ADD' THEN
        -- Check if class name already exists (non-deleted)
        SELECT COUNT(*) INTO v_count
        FROM classes
        WHERE LOWER(name) = LOWER(p_name)
          AND is_deleted = 'N';

        IF v_count > 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Class name already exists: ' || p_name);
        END IF;

        -- Generate new class ID
        SELECT 'C' || TO_CHAR(NVL(MAX(TO_NUMBER(SUBSTR(class_id,2))),999)+1)
        INTO p_class_id
        FROM classes;

        INSERT INTO classes(class_id, name, is_deleted)
        VALUES (p_class_id, p_name, 'N');

    ELSIF p_action = 'UPDATE' THEN
        -- Check if name already exists in another non-deleted class
        SELECT COUNT(*) INTO v_count
        FROM classes
        WHERE LOWER(name) = LOWER(p_name)
          AND class_id != p_class_id
          AND is_deleted = 'N';

        IF v_count > 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Class name already exists: ' || p_name);
        END IF;

        UPDATE classes
        SET name = p_name
        WHERE class_id = p_class_id;

    ELSIF p_action = 'DELETE' THEN
        -- Check if students exist
        SELECT COUNT(*) INTO v_count
        FROM student_classes
        WHERE class_id = p_class_id;

        IF v_count > 0 THEN
            -- Soft delete if students exist
            UPDATE classes
            SET is_deleted = 'Y'
            WHERE class_id = p_class_id;
        ELSE
            -- Fully delete if no students
            DELETE FROM classes
            WHERE class_id = p_class_id;
        END IF;

    ELSE
        RAISE_APPLICATION_ERROR(-20003, 'Invalid action: ' || p_action);
    END IF;
END;
/
//wanted to hide the classes with students instead of deleting them
ALTER TABLE classes ADD (is_deleted CHAR(1) DEFAULT 'N');

ALTER TABLE classes ADD is_deleted CHAR(1) DEFAULT 'N';


select * from student_classes;


ALTER TABLE subjects
ADD is_deleted VARCHAR2(1) DEFAULT 'N' NOT NULL;



CREATE OR REPLACE PROCEDURE upsert_subject(
    p_action      IN VARCHAR2,       -- 'ADD', 'UPDATE', 'DELETE', 'GET'
    p_subject_id  IN OUT VARCHAR2,
    p_name        IN VARCHAR2 DEFAULT NULL,
    p_duration    IN NUMBER DEFAULT NULL
) AS
    v_count NUMBER;
BEGIN
    IF p_action = 'ADD' THEN
        SELECT COUNT(*) INTO v_count
        FROM subjects
        WHERE LOWER(name) = LOWER(p_name)
          AND is_deleted = 'N';

        IF v_count > 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Subject name already exists: ' || p_name);
        END IF;

        -- Generate new ID
        SELECT 'S' || TO_CHAR(NVL(MAX(TO_NUMBER(SUBSTR(subject_id,2))),999)+1)
        INTO p_subject_id
        FROM subjects;

        INSERT INTO subjects(subject_id, name, duration, is_deleted)
        VALUES (p_subject_id, p_name, p_duration, 'N');

    ELSIF p_action = 'UPDATE' THEN
        SELECT COUNT(*) INTO v_count
        FROM subjects
        WHERE LOWER(name) = LOWER(p_name)
          AND subject_id != p_subject_id
          AND is_deleted = 'N';

        IF v_count > 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Subject name already exists: ' || p_name);
        END IF;

        UPDATE subjects
        SET name = p_name, duration = p_duration
        WHERE subject_id = p_subject_id;

    ELSIF p_action = 'DELETE' THEN
        SELECT COUNT(*) INTO v_count
        FROM student_subjects
        WHERE subject_id = p_subject_id;

        IF v_count > 0 THEN
            UPDATE subjects SET is_deleted = 'Y' WHERE subject_id = p_subject_id;
        ELSE
            DELETE FROM subjects WHERE subject_id = p_subject_id;
        END IF;

    ELSIF p_action = 'GET' THEN
        NULL; -- We'll fetch using a SELECT in backend
    ELSE
        RAISE_APPLICATION_ERROR(-20003, 'Invalid action: ' || p_action);
    END IF;
END;
/

ALTER TABLE students ADD is_deleted CHAR(1) DEFAULT 'N';

CREATE OR REPLACE PROCEDURE upsert_student(
    p_action     IN VARCHAR2,       -- 'ADD', 'UPDATE', 'DELETE'
    p_student_id IN OUT VARCHAR2,
    p_name       IN VARCHAR2,
    p_age        IN NUMBER,
    p_email      IN VARCHAR2
) AS
    v_count NUMBER;
BEGIN
    IF p_action = 'ADD' THEN
        -- Check if email already exists (non-deleted)
        SELECT COUNT(*) INTO v_count
        FROM students
        WHERE LOWER(email) = LOWER(p_email)
          AND is_deleted = 'N';

        IF v_count > 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Email already exists: ' || p_email);
        END IF;

        -- Generate new student ID
        SELECT 'ST' || TO_CHAR(NVL(MAX(TO_NUMBER(SUBSTR(student_id,3))),999)+1)
        INTO p_student_id
        FROM students;

        INSERT INTO students(student_id, name, age, email, is_deleted)
        VALUES (p_student_id, p_name, p_age, p_email, 'N');

    ELSIF p_action = 'UPDATE' THEN
        -- Check if email exists in another student
        SELECT COUNT(*) INTO v_count
        FROM students
        WHERE LOWER(email) = LOWER(p_email)
          AND student_id != p_student_id
          AND is_deleted = 'N';

        IF v_count > 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Email already exists: ' || p_email);
        END IF;

        UPDATE students
        SET name = p_name, age = p_age, email = p_email
        WHERE student_id = p_student_id;

    ELSIF p_action = 'DELETE' THEN
        -- Check if student has linked classes or subjects
        SELECT COUNT(*) INTO v_count
        FROM student_classes
        WHERE student_id = p_student_id;

        IF v_count > 0 THEN
            -- Soft delete
            UPDATE students
            SET is_deleted = 'Y'
            WHERE student_id = p_student_id;
        ELSE
            -- Fully delete if no classes
            DELETE FROM students
            WHERE student_id = p_student_id;
        END IF;

    ELSE
        RAISE_APPLICATION_ERROR(-20003, 'Invalid action: ' || p_action);
    END IF;
END;
/


CREATE OR REPLACE PROCEDURE get_students_by_class(
    p_class_id IN VARCHAR2,
    p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT s.student_id, s.name, s.age, s.email
        FROM students s
        JOIN student_classes sc ON s.student_id = sc.student_id
        WHERE sc.class_id = p_class_id
          AND s.is_deleted = 'N';
END;
/

CREATE OR REPLACE PROCEDURE get_subjects_by_student(
    p_student_id IN VARCHAR2,
    p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT sub.subject_id, sub.name, sub.duration
        FROM subjects sub
        JOIN student_subjects ss ON sub.subject_id = ss.subject_id
        WHERE ss.student_id = p_student_id
          AND sub.is_deleted = 'N';
END;
/

CREATE OR REPLACE PROCEDURE get_all_students(
    p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT student_id, name, age, email
        FROM students
        WHERE is_deleted = 'N';
END;
/

CREATE OR REPLACE PROCEDURE get_all_grades(
    p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT grade_id, student_id, subject_id, mark, gpa, created_at
        FROM grades;
END;
/

CREATE OR REPLACE PROCEDURE get_grades_by_student_proc(
    p_student_id IN VARCHAR2,
    p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT grade_id, student_id, subject_id, mark, gpa, created_at
        FROM grades
        WHERE student_id = p_student_id;
END;
/