MariaDB [toyu]> CREATE OR REPLACE VIEW school_summary(
    ->    schoolCode, schoolName, n_departments) AS
    -> SELECT s.schoolCode, s.schoolName,
    -> COUNT(d.deptCode) AS n_departments
    -> FROM school AS s LEFT JOIN department AS d ON (s.schoolCode = d.schoolCode)
    -> GROUP BY s.schoolCode, s.schoolName;
Query OK, 0 rows affected (0.005 sec)

MariaDB [toyu]> -- A very simple stored procedure.
MariaDB [toyu]> -- Redefine the delimiter to end the procedure.
MariaDB [toyu]> DELIMITER //
MariaDB [toyu]> 
MariaDB [toyu]> 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 direct 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 //
Query OK, 0 rows affected (0.003 sec)

MariaDB [toyu]> 
MariaDB [toyu]> DELIMITER ;
MariaDB [toyu]> 
MariaDB [toyu]> SHOW CREATE PROCEDURE deptInfo;
+-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                            | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| deptInfo  | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`yue`@`localhost` PROCEDURE `deptInfo`(IN dCode VARCHAR(4), OUT numFaculty INT)
BEGIN
   
   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;

   
   
   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 | cp850                | cp850_general_ci     | utf8mb4_general_ci |
+-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.001 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SET @numFaculty = 0;
Query OK, 0 rows affected (0.000 sec)

MariaDB [toyu]> SET @dCode = 'CSCI';
Query OK, 0 rows affected (0.000 sec)

MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty);
+------------------+------------+----------+----------+
| deptName         | SchoolCode | n_majors | n_minors |
+------------------+------------+----------+----------+
| Computer Science | CSE        |        3 |        1 |
+------------------+------------+----------+----------+
1 row in set (0.002 sec)

+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
1 row in set (0.004 sec)

+---------------------------------+
| faculty                         |
+---------------------------------+
| Faculty in the department: CSCI |
+---------------------------------+
1 row in set (0.005 sec)

+-------+--------+--------+----------+---------------------+
| facId | fname  | lname  | deptCode | rank                |
+-------+--------+--------+----------+---------------------+
|  1011 | Paul   | Smith  | CSCI     | Professor           |
|  1012 | Mary   | Tran   | CSCI     | Associate Professor |
|  1013 | David  | Love   | CSCI     | NULL                |
|  1014 | Sharon | Mannes | CSCI     | Assistant Professor |
+-------+--------+--------+----------+---------------------+
4 rows in set (0.007 sec)

Query OK, 1 row affected (0.013 sec)

MariaDB [toyu]> SELECT @dCode, @numFaculty;
+--------+-------------+
| @dCode | @numFaculty |
+--------+-------------+
| CSCI   |           4 |
+--------+-------------+
1 row in set (0.000 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SET @dCode = 'ITEC';
Query OK, 0 rows affected (0.000 sec)

MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty);
+------------------------+------------+----------+----------+
| deptName               | SchoolCode | n_majors | n_minors |
+------------------------+------------+----------+----------+
| Information Technology | CSE        |        2 |        2 |
+------------------------+------------+----------+----------+
1 row in set (0.000 sec)

+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
1 row in set (0.002 sec)

+---------------------------------+
| faculty                         |
+---------------------------------+
| Faculty in the department: ITEC |
+---------------------------------+
1 row in set (0.003 sec)

+-------+----------+-------+----------+-----------+
| facId | fname    | lname | deptCode | rank      |
+-------+----------+-------+----------+-----------+
|  1017 | Deborah  | Gump  | ITEC     | Professor |
|  1019 | Benjamin | Yu    | ITEC     | Lecturer  |
+-------+----------+-------+----------+-----------+
2 rows in set (0.005 sec)

Query OK, 1 row affected (0.007 sec)

MariaDB [toyu]> SELECT @dCode, @numFaculty;
+--------+-------------+
| @dCode | @numFaculty |
+--------+-------------+
| ITEC   |           2 |
+--------+-------------+
1 row in set (0.000 sec)

MariaDB [toyu]> 
MariaDB [toyu]> DELIMITER $$
MariaDB [toyu]> 
MariaDB [toyu]> 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 direct 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 $$
Query OK, 0 rows affected (0.012 sec)

MariaDB [toyu]> 
MariaDB [toyu]> DELIMITER ;
MariaDB [toyu]> 
MariaDB [toyu]> SHOW CREATE PROCEDURE deptInfo;
+-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                            | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| deptInfo  | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`yue`@`localhost` PROCEDURE `deptInfo`(IN dCode VARCHAR(4), OUT numFaculty INT)
BEGIN
   
   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;

   
   
   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 | cp850                | cp850_general_ci     | utf8mb4_general_ci |
+-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.000 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SET @numFaculty = 0;
Query OK, 0 rows affected (0.000 sec)

MariaDB [toyu]> SET @dCode = 'CSCI';
Query OK, 0 rows affected (0.000 sec)

MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty);
+------------------+------------+----------+----------+
| deptName         | SchoolCode | n_majors | n_minors |
+------------------+------------+----------+----------+
| Computer Science | CSE        |        3 |        1 |
+------------------+------------+----------+----------+
1 row in set (0.001 sec)

+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
1 row in set (0.003 sec)

+---------------------------------+
| faculty                         |
+---------------------------------+
| Faculty in the department: CSCI |
+---------------------------------+
1 row in set (0.004 sec)

+-------+--------+--------+----------+---------------------+
| facId | fname  | lname  | deptCode | rank                |
+-------+--------+--------+----------+---------------------+
|  1011 | Paul   | Smith  | CSCI     | Professor           |
|  1012 | Mary   | Tran   | CSCI     | Associate Professor |
|  1013 | David  | Love   | CSCI     | NULL                |
|  1014 | Sharon | Mannes | CSCI     | Assistant Professor |
+-------+--------+--------+----------+---------------------+
4 rows in set (0.005 sec)

Query OK, 1 row affected (0.009 sec)

MariaDB [toyu]> SELECT @dCode, @numFaculty;
+--------+-------------+
| @dCode | @numFaculty |
+--------+-------------+
| CSCI   |           4 |
+--------+-------------+
1 row in set (0.000 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SET @dCode = 'ITEC';
Query OK, 0 rows affected (0.000 sec)

MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty);
+------------------------+------------+----------+----------+
| deptName               | SchoolCode | n_majors | n_minors |
+------------------------+------------+----------+----------+
| Information Technology | CSE        |        2 |        2 |
+------------------------+------------+----------+----------+
1 row in set (0.000 sec)

+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
1 row in set (0.002 sec)

+---------------------------------+
| faculty                         |
+---------------------------------+
| Faculty in the department: ITEC |
+---------------------------------+
1 row in set (0.003 sec)

+-------+----------+-------+----------+-----------+
| facId | fname    | lname | deptCode | rank      |
+-------+----------+-------+----------+-----------+
|  1017 | Deborah  | Gump  | ITEC     | Professor |
|  1019 | Benjamin | Yu    | ITEC     | Lecturer  |
+-------+----------+-------+----------+-----------+
2 rows in set (0.005 sec)

Query OK, 1 row affected (0.007 sec)

MariaDB [toyu]> SELECT @dCode, @numFaculty;
+--------+-------------+
| @dCode | @numFaculty |
+--------+-------------+
| ITEC   |           2 |
+--------+-------------+
1 row in set (0.000 sec)

MariaDB [toyu]> CALL deptInfo('CINF', @numFaculty);
+------------------------------+------------+----------+----------+
| deptName                     | SchoolCode | n_majors | n_minors |
+------------------------------+------------+----------+----------+
| Computer Information Systems | CSE        |        2 |        3 |
+------------------------------+------------+----------+----------+
1 row in set (0.001 sec)

+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
1 row in set (0.003 sec)

+---------------------------------+
| faculty                         |
+---------------------------------+
| Faculty in the department: CINF |
+---------------------------------+
1 row in set (0.004 sec)

+-------+--------+-------+----------+---------------------+
| facId | fname  | lname | deptCode | rank                |
+-------+--------+-------+----------+---------------------+
|  1015 | Daniel | Kim   | CINF     | Professor           |
|  1016 | Andrew | Byre  | CINF     | Associate Professor |
+-------+--------+-------+----------+---------------------+
2 rows in set (0.005 sec)

Query OK, 1 row affected (0.008 sec)

MariaDB [toyu]> SELECT 'CINF', @numFaculty;
+------+-------------+
| CINF | @numFaculty |
+------+-------------+
| CINF |           2 |
+------+-------------+
1 row in set (0.000 sec)

MariaDB [toyu]> CALL deptInfo('CINF', 4);
ERROR 1414 (42000): OUT or INOUT argument 2 for routine toyu.deptInfo is not a variable or NEW pseudo-variable in BEFORE trigger
MariaDB [toyu]> SELECT 'CINF', 4;
+------+---+
| CINF | 4 |
+------+---+
| CINF | 4 |
+------+---+
1 row in set (0.000 sec)

MariaDB [toyu]> CALL deptInfo(CONCAT('CI', 'NF'), @numFaculty);
+------------------------------+------------+----------+----------+
| deptName                     | SchoolCode | n_majors | n_minors |
+------------------------------+------------+----------+----------+
| Computer Information Systems | CSE        |        2 |        3 |
+------------------------------+------------+----------+----------+
1 row in set (0.001 sec)

+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
1 row in set (0.005 sec)

+---------------------------------+
| faculty                         |
+---------------------------------+
| Faculty in the department: CINF |
+---------------------------------+
1 row in set (0.006 sec)

+-------+--------+-------+----------+---------------------+
| facId | fname  | lname | deptCode | rank                |
+-------+--------+-------+----------+---------------------+
|  1015 | Daniel | Kim   | CINF     | Professor           |
|  1016 | Andrew | Byre  | CINF     | Associate Professor |
+-------+--------+-------+----------+---------------------+
2 rows in set (0.008 sec)

Query OK, 1 row affected (0.014 sec)

MariaDB [toyu]> CALL AddNewCourse(3009, 'CSCI', '4436', 'Systems Administration', 3);
Query OK, 1 row affected (0.005 sec)

MariaDB [toyu]> DELIMITER //
MariaDB [toyu]> 
MariaDB [toyu]> CREATE OR REPLACE FUNCTION n_major(IN 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 //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IN dCode varchar(4)) RETURNS INT
READS SQL DATA  
BEGIN
   DECLARE count INT ...' at line 1
MariaDB [toyu]> 
MariaDB [toyu]> DELIMITER ;
MariaDB [toyu]> 
MariaDB [toyu]> -- A simple function
MariaDB [toyu]> DELIMITER //
MariaDB [toyu]> 
MariaDB [toyu]> 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 //
ERROR 1415 (0A000): Not allowed to return a result set from a function
MariaDB [toyu]> 
MariaDB [toyu]> DELIMITER ;
MariaDB [toyu]> 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;
Query OK, 1 row affected (0.002 sec)

MariaDB [toyu]> SELECT @gpa;
+------+
| @gpa |
+------+
| 3.00 |
+------+
1 row in set (0.000 sec)

MariaDB [toyu]> notee