DROP PROCEDURE IF EXISTS all_students;
DROP PROCEDURE IF EXISTS id_of;
DROP PROCEDURE IF EXISTS class_codes_of;
DROP PROCEDURE IF EXISTS classmates_of;

DELIMITER $$

CREATE PROCEDURE all_students()
BEGIN
    SELECT *
    FROM student;
END$$

CREATE PROCEDURE id_of(IN first_name VARCHAR(32), 
                       IN last_name VARCHAR(32),
                       OUT sid VARCHAR(5))
BEGIN
    SELECT id INTO sid
    FROM student
    WHERE first = first_name
    AND   last  = last_name;
END$$

CREATE PROCEDURE class_codes_of(IN first_name VARCHAR(32), 
                                IN last_name VARCHAR(32))
BEGIN
    CALL id_of(first_name, last_name, @sid);
    
    SELECT code
    FROM student
    JOIN takes
      ON takes.student_id = student.id
    JOIN class
      ON class.code = takes.class_code
    WHERE student.id = @sid;
END$$

CREATE PROCEDURE classmates_of(IN target_first_name VARCHAR(32), 
                               IN target_last_name VARCHAR(32))
BEGIN
    CALL id_of(target_first_name, target_last_name, @target_id);
    
    SELECT s.first, s.last, c.code, c.subject
    FROM student s
    JOIN takes t
      ON t.student_id = s.id
    JOIN class c
      ON c.code = t.class_code
    WHERE c.code IN (
        SELECT c.code 
        FROM class c
        JOIN takes t
          ON t.class_code = c.code
        JOIN student s
          ON s.id = t.student_id
        WHERE s.id = @target_id
    )
    AND s.id != @target_id;
END$$

DELIMITER ;
