87quJTx5

1```
2CREATE TABLE departments (
3	id INT NOT NULL AUTO_INCREMENT,
4	name VARCHAR(255) NOT NULL,
5	PRIMARY KEY(id)
6);
7
8CREATE TABLE employees (
9	id INT NOT NULL AUTO_INCREMENT,
10	name VARCHAR(255) NOT NULL,
11	department_id INT NOT NULL,
12	PRIMARY KEY(id),
13	FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE
14);
15
16INSERT INTO departments (name) VALUES ('Administration'), ('Sales');
17INSERT INTO employees (name, department_id) VALUES ('John Doe', 1), ('Jane Doe', 1), ('Janie Doe', 2), ('Johnny  Doe', 2);
18
19
20DROP FUNCTION IF EXISTS getDepartmentId;
21
22DELIMITER $$
23
24CREATE FUNCTION getDepartmentId(depName VARCHAR(255)) RETURNS INT
25
26BEGIN
27  DECLARE depId INT;
28  SELECT id INTO depId FROM departments WHERE name = depName;
29  RETURN depId;
30END$$
31
32DELIMITER ;
33
34
35SELECT * FROM employees WHERE department_id = getDepartmentId('Sales');