Create a table named 'course' with the fields: id$,$ course$\_$name, dept$\_$name.

Insert $6$ records $(2$ for the English department, $3$ for the Math department, and $1$ for a different department of your choice$)$

Create a function that returns the count of the number of courses in each department.

Query the number of courses in the Math department.

Please include explanations if possible.

Code is below:

create database chapter$5$;

use chapter$5$;

CREATE TABLE student

$($ID varchar$(5),$

name varchar$(20)$ not null,

dept$\_$name varchar$(20),$

tot$\_$cred int$)$;

INSERT INTO student $($id$,$ name, dept$\_$name, tot$\_$cred$)$ values $(\text{'}44553\text{'},$ 'Peltier', 'Physics', $56)$;

INSERT INTO student $($id$,$ name, dept$\_$name, tot$\_$cred$)$ values $(\text{'}45678\text{'},$ 'Levy', 'Physics', $46)$;

INSERT INTO student $($id$,$ name, dept$\_$name, tot$\_$cred$)$ values $(\text{'}54321\text{'},$ 'Williams', 'Comp. Sci.$\text{'},54)$;

INSERT INTO student $($id$,$ name, dept$\_$name, tot$\_$cred$)$ values $(\text{'}55739\text{'},$ 'Sanchez', 'Music', $38)$;

INSERT INTO student $($id$,$ name, dept$\_$name, tot$\_$cred$)$ values $(\text{'}70557\text{'},$ 'Snow', 'Physics', $0)$;

INSERT INTO student $($id$,$ name, dept$\_$name, tot$\_$cred$)$ values $(\text{'}55740\text{'},$ 'Jacobs', 'Music', $42)$;

SELECT $*$ FROM student;

DELIMITER $$

CREATE FUNCTION dept$\_$count $($dept$\_$name varchar$(20))$

RETURNS int

DETERMINISTIC

BEGIN

DECLARE d$\_$count int;

SELECT count$(*)$ INTO d$\_$count FROM student WHERE student.dept$\_$name $=$ dept$\_$name;

RETURN $($d$\_$count$)$;

END; $$

DELIMITER ;

SELECT dept$\_$count$(\text{'}$Music$\text{'})$;