tee 2025_10_20_sql_log.txt
-- A very simple stored procedure.
-- Redefine the delimiter to end the procedure.
DELIMITER $$

CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), OUT numFaculty INT)
BEGIN
   --  Display some information.
   SELECT d.deptName, d.SchoolCode, t1.n_majors, t2.n_minors
   FROM department AS d INNER JOIN
      (SELECT COUNT(stuId) AS n_majors
      FROM student
      WHERE major = dCode) AS t1 INNER JOIN
      (SELECT COUNT(stuId) AS n_minors
      FROM student
      WHERE minor = dCode) AS t2
   WHERE d.deptCode = dCode;

   -- MySQL does not directly sending output to console.
   -- It is necessary to use a SQL statement.
   SELECT 'Debuggin comment can be put here.';
   SELECT CONCAT('Faculty in the department: ', dCode) AS faculty;

   SELECT *
   FROM faculty AS f
   WHERE f.deptCode = dCode;

   SELECT COUNT(f.facId) INTO numFaculty
   FROM faculty AS f
   WHERE f.deptCode = dCode;
END $$

DELIMITER ;

SHOW CREATE PROCEDURE deptInfo;

SET @numFaculty = 0;
SET @dCode = 'CSCI';
CALL deptInfo(@dCode, @numFaculty);
SELECT @dCode, @numFaculty;

SET @dCode = 'ITEC';
CALL deptInfo(@dCode, @numFaculty);
SELECT @dCode, @numFaculty;

CALL deptInfo('CINF', @numFaculty);
SELECT 'CINF', @numFaculty;

CALL deptInfo('CINF', 4);
SELECT 'CINF', 4;

CALL deptInfo(CONCAT('CI', 'NF'), @numFaculty);

DELIMITER //
CREATE OR REPLACE PROCEDURE AddNewCourse(
    IN course_id INT,
    IN rubric CHAR(4),
    IN course_number CHAR(4),
    IN course_title VARCHAR(80),
    IN credits TINYINT
)
BEGIN
    INSERT INTO Course (courseId, rubric, number, title, credits)
    VALUES (course_id, rubric, course_number, course_title, credits);
    -- add audit logic

END //
DELIMITER ;
CALL AddNewCourse(3009, 'CSCI', '4436', 'Systems Administration', 3);

-- A simple function
DELIMITER //

CREATE OR REPLACE FUNCTION n_major(dCode varchar(4)) RETURNS INT
READS SQL DATA  -- OUT and IN OUT are not allowed in functions
BEGIN
   DECLARE count INT DEFAULT 0;

   SELECT COUNT(*) -- INTO count
   FROM student
   WHERE major = dCode;

   RETURN count;
END //

DELIMITER ;


SELECT ROUND(SUM(g.gradePoint * co.credits) / SUM(co.credits), 2) INTO @gpa
    FROM Enroll e
    JOIN Class c ON e.classId = c.classId
    JOIN Course co ON c.courseId = co.courseId
    JOIN Grade g ON e.grade = g.grade
    WHERE e.stuId = 100000;

-- primary key of all tables in toyu