MariaDB [(none)]> use toyu Database changed MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> WITH temp AS -> (SELECT DISTINCT sc.schoolName AS college, d.deptName AS department, -> COUNT(s.stuId) As deptMajor -> FROM school AS sc INNER JOIN department AS d ON (sc.schoolCode = d.schoolCode) -> LEFT JOIN student AS s ON (s.major = d.deptCode) -> GROUP BY college, department) -> SELECT temp.college, temp.department, -> temp.deptMajor AS `major in department`, -> SUM(deptMajor) OVER(PARTITION BY college) AS `major in college`, -> SUM(deptMajor) OVER() AS `major in university` -> FROM temp; +-------------------------------+------------------------------+---------------------+------------------+---------------------+ | college | department | major in department | major in college | major in university | +-------------------------------+------------------------------+---------------------+------------------+---------------------+ | Business | Accounting | 0 | 0 | 9 | | Human Sciences and Humanities | Arts | 2 | 2 | 9 | | Human Sciences and Humanities | English | 0 | 2 | 9 | | Science and Engineering | Information Technology | 2 | 7 | 9 | | Science and Engineering | Mathematics | 0 | 7 | 9 | | Science and Engineering | Computer Information Systems | 2 | 7 | 9 | | Science and Engineering | Computer Science | 3 | 7 | 9 | +-------------------------------+------------------------------+---------------------+------------------+---------------------+ 7 rows in set (0.002 sec) MariaDB [toyu]> -- A very simple stored procedure. MariaDB [toyu]> -- Redefine the delimiter to end the procedure. MariaDB [toyu]> DELIMITER // MariaDB [toyu]> MariaDB [toyu]> -- IN: pass by value; OUT: copy out hen exiting MariaDB [toyu]> CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), OUT numFaculty INT) -> BEGIN -> -- Display some information. -> -- e.g. dCode: `CSCI'; numFaculty: null -> 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.014 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.003 sec) +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ 1 row in set (0.062 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: CSCI | +---------------------------------+ 1 row in set (0.093 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.214 sec) Query OK, 1 row affected (0.384 sec) MariaDB [toyu]> SELECT @dCode, @numFaculty; +--------+-------------+ | @dCode | @numFaculty | +--------+-------------+ | CSCI | 4 | +--------+-------------+ 1 row in set (0.001 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.001 sec) +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ 1 row in set (0.085 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: ITEC | +---------------------------------+ 1 row in set (0.218 sec) +-------+----------+-------+----------+-----------+ | facId | fname | lname | deptCode | rank | +-------+----------+-------+----------+-----------+ | 1017 | Deborah | Gump | ITEC | Professor | | 1019 | Benjamin | Yu | ITEC | Lecturer | +-------+----------+-------+----------+-----------+ 2 rows in set (0.325 sec) Query OK, 1 row affected (0.462 sec) MariaDB [toyu]> CALL deptInfo('CSCI', @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.039 sec) MariaDB [toyu]> SELECT 'CSCI', @numFaculty; +------+-------------+ | CSCI | @numFaculty | +------+-------------+ | CSCI | 4 | +------+-------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> CALL deptInfo('CSCI', @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.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.006 sec) Query OK, 1 row affected (0.024 sec) MariaDB [toyu]> SELECT 'CSCI', @numFaculty; +------+-------------+ | CSCI | @numFaculty | +------+-------------+ | CSCI | 4 | +------+-------------+ 1 row in set (0.000 sec) MariaDB [toyu]> CALL deptInfo('CSCI', 3); 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]> MariaDB [toyu]> DELIMITER // MariaDB [toyu]> MariaDB [toyu]> -- IN: pass by value; OUT: copy out hen exiting MariaDB [toyu]> CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), IN OUT numFaculty INT) -> BEGIN -> SELECT dCode, numFaculty; -> -- Display some information. -> -- e.g. dCode: `CSCI'; numFaculty: null -> 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 // 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 'OUT numFaculty INT) BEGIN SELECT dCode, numFaculty; SELECT d.d...' at line 1 MariaDB [toyu]> MariaDB [toyu]> DELIMITER ; 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.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: ITEC | +---------------------------------+ 1 row in set (0.023 sec) +-------+----------+-------+----------+-----------+ | facId | fname | lname | deptCode | rank | +-------+----------+-------+----------+-----------+ | 1017 | Deborah | Gump | ITEC | Professor | | 1019 | Benjamin | Yu | ITEC | Lecturer | +-------+----------+-------+----------+-----------+ 2 rows in set (0.129 sec) Query OK, 1 row affected (0.253 sec) MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> DELIMITER // MariaDB [toyu]> MariaDB [toyu]> -- IN: pass by value; OUT: copy out hen exiting MariaDB [toyu]> CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), INOUT numFaculty INT) -> BEGIN -> SELECT dCode, numFaculty; -> -- Display some information. -> -- e.g. dCode: `CSCI'; numFaculty: null -> 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.017 sec) MariaDB [toyu]> MariaDB [toyu]> DELIMITER ; MariaDB [toyu]> MariaDB [toyu]> SET @dCode = 'ITEC'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty); +-------+------------+ | dCode | numFaculty | +-------+------------+ | ITEC | 2 | +-------+------------+ 1 row in set (0.001 sec) +------------------------+------------+----------+----------+ | deptName | SchoolCode | n_majors | n_minors | +------------------------+------------+----------+----------+ | Information Technology | CSE | 2 | 2 | +------------------------+------------+----------+----------+ 1 row in set (0.175 sec) +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ 1 row in set (0.215 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: ITEC | +---------------------------------+ 1 row in set (0.285 sec) +-------+----------+-------+----------+-----------+ | facId | fname | lname | deptCode | rank | +-------+----------+-------+----------+-----------+ | 1017 | Deborah | Gump | ITEC | Professor | | 1019 | Benjamin | Yu | ITEC | Lecturer | +-------+----------+-------+----------+-----------+ 2 rows in set (0.422 sec) Query OK, 1 row affected (0.561 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT @dCode, @numFaculty; +--------+-------------+ | @dCode | @numFaculty | +--------+-------------+ | ITEC | 2 | +--------+-------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> SET @dCode = 'CSCI'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty); +-------+------------+ | dCode | numFaculty | +-------+------------+ | CSCI | 2 | +-------+------------+ 1 row in set (0.001 sec) +------------------+------------+----------+----------+ | deptName | SchoolCode | n_majors | n_minors | +------------------+------------+----------+----------+ | Computer Science | CSE | 3 | 1 | +------------------+------------+----------+----------+ 1 row in set (0.008 sec) +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ 1 row in set (0.097 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: CSCI | +---------------------------------+ 1 row in set (0.201 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.329 sec) Query OK, 1 row affected (0.484 sec) MariaDB [toyu]> MariaDB [toyu]> SET @dCode = 'CSCI'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty); +-------+------------+ | dCode | numFaculty | +-------+------------+ | CSCI | 4 | +-------+------------+ 1 row in set (0.001 sec) +------------------+------------+----------+----------+ | deptName | SchoolCode | n_majors | n_minors | +------------------+------------+----------+----------+ | Computer Science | CSE | 3 | 1 | +------------------+------------+----------+----------+ 1 row in set (0.090 sec) +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ 1 row in set (0.210 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: CSCI | +---------------------------------+ 1 row in set (0.311 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.385 sec) Query OK, 1 row affected (0.579 sec) MariaDB [toyu]> SELECT @dCode, @numFaculty; +--------+-------------+ | @dCode | @numFaculty | +--------+-------------+ | CSCI | 4 | +--------+-------------+ 1 row in set (0.000 sec) MariaDB [toyu]> SET @dCode = 'ITEC'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET @numFaculty = 1; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty); +-------+------------+ | dCode | numFaculty | +-------+------------+ | ITEC | 1 | +-------+------------+ 1 row in set (0.001 sec) +------------------------+------------+----------+----------+ | deptName | SchoolCode | n_majors | n_minors | +------------------------+------------+----------+----------+ | Information Technology | CSE | 2 | 2 | +------------------------+------------+----------+----------+ 1 row in set (0.069 sec) +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ 1 row in set (0.230 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: ITEC | +---------------------------------+ 1 row in set (0.319 sec) +-------+----------+-------+----------+-----------+ | facId | fname | lname | deptCode | rank | +-------+----------+-------+----------+-----------+ | 1017 | Deborah | Gump | ITEC | Professor | | 1019 | Benjamin | Yu | ITEC | Lecturer | +-------+----------+-------+----------+-----------+ 2 rows in set (0.484 sec) Query OK, 1 row affected (0.567 sec) MariaDB [toyu]> SELECT @dCode, @numFaculty; +--------+-------------+ | @dCode | @numFaculty | +--------+-------------+ | ITEC | 2 | +--------+-------------+ 1 row in set (0.001 sec) MariaDB [toyu]> 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 -> BEGIN -> DECLARE count INT DEFAULT 0; -> -> SELECT COUNT(*) INTO count -> FROM student -> WHERE major = dCode; -> -> RETURN count; -> END // Query OK, 0 rows affected (0.014 sec) MariaDB [toyu]> MariaDB [toyu]> DELIMITER ; MariaDB [toyu]> MariaDB [toyu]> SHOW CREATE FUNCTION n_major; +----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation | +----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | n_major | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`yue`@`localhost` FUNCTION `n_major`(dCode varchar(4)) RETURNS int(11) READS SQL DATA BEGIN DECLARE count INT DEFAULT 0; SELECT COUNT(*) INTO count FROM student WHERE major = dCode; RETURN count; END | cp850 | cp850_general_ci | utf8mb4_general_ci | +----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT n_major('CSCI'); +-----------------+ | n_major('CSCI') | +-----------------+ | 3 | +-----------------+ 1 row in set (0.001 sec) MariaDB [toyu]> SELECT n_major('ITEC'); +-----------------+ | n_major('ITEC') | +-----------------+ | 2 | +-----------------+ 1 row in set (0.001 sec) MariaDB [toyu]> SELECT n_major('CSCI'); +-----------------+ | n_major('CSCI') | +-----------------+ | 3 | +-----------------+ 1 row in set (0.002 sec) MariaDB [toyu]> SELECT n_major('ITEC'); +-----------------+ | n_major('ITEC') | +-----------------+ | 2 | +-----------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> -- A simple function MariaDB [toyu]> DELIMITER // MariaDB [toyu]> MariaDB [toyu]> -- function: no OUT or INOUT. Only IN MariaDB [toyu]> CREATE OR REPLACE FUNCTION n_major(dCode varchar(4)) RETURNS INT -> READS SQL DATA -> BEGIN -> DECLARE count INT DEFAULT 0; -> -> SELECT 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]> MariaDB [toyu]> -- using cursor. MariaDB [toyu]> DELIMITER // MariaDB [toyu]> MariaDB [toyu]> CREATE FUNCTION major_students(dept VARCHAR(4)) -> RETURNS VARCHAR(1000) -> READS SQL DATA -> BEGIN -> DECLARE result VARCHAR(1000) DEFAULT ''; -> DECLARE name VARCHAR(41) DEFAULT ''; -> DECLARE done INT DEFAULT FALSE; -> -> DECLARE cursor_1 CURSOR FOR -> SELECT DISTINCT CONCAT(fName, ' ', lName) AS name -> FROM student -> WHERE major = dept; -> -> DECLARE continue handler FOR NOT FOUND SET done = TRUE; -> -> OPEN cursor_1; -> SET result = ''; -> -> compute_loop: LOOP -> -- This is needed as there are other SQL statements that may set done to true. -> SET done = false; -> FETCH cursor_1 INTO name; -> IF done THEN -> LEAVE compute_loop; -> END IF; -> -> IF (result <> '') THEN -> SET result = CONCAT(result, ', '); -> END IF; -> -> SET result = CONCAT(result, name); -> END LOOP; -> -> CLOSE cursor_1; -> -> RETURN result; -> END // Query OK, 0 rows affected (0.007 sec) MariaDB [toyu]> MariaDB [toyu]> DELIMITER ; MariaDB [toyu]> MariaDB [toyu]> SELECT major_students('CSCI'); +----------------------------------+ | major_students('CSCI') | +----------------------------------+ | Tony Hawk, Mary Hawk, David Hawk | +----------------------------------+ 1 row in set (0.001 sec) MariaDB [toyu]> SELECT major_students('CINF'); +--------------------------------+ | major_students('CINF') | +--------------------------------+ | Linda Johnson, Lillian Johnson | +--------------------------------+ 1 row in set (0.002 sec) MariaDB [toyu]> notee