Pu2WUxcY

1create database if not exists demo;
2
3use demo;
4
5drop table if exists employees;
6
7CREATE TABLE `employees` (
8  `id` int(11) NOT NULL AUTO_INCREMENT,
9  `last_name` varchar(64) DEFAULT NULL,
10  `first_name` varchar(64) DEFAULT NULL,
11  `email` varchar(64) DEFAULT NULL,
12  `department` varchar(64) DEFAULT NULL,
13  `salary` DECIMAL(10,2) DEFAULT NULL,
14  PRIMARY KEY (`id`)
15) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
16
17INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (1,'Doe','John','john.doe@foo.com', 'HR', 55000.00);
18INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (2,'Public','Mary','mary.public@foo.com', 'Engineering', 75000.00);
19INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (3,'Queue','Susan','susan.queue@foo.com', 'Legal', 130000.00);
20
21INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (4,'Williams','David','david.williams@foo.com', 'HR', 120000.00);
22INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (5,'Johnson','Lisa','lisa.johnson@foo.com', 'Engineering', 50000.00);
23INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (6,'Smith','Paul','paul.smith@foo.com', 'Legal', 100000.00);
24
25INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (7,'Adams','Carl','carl.adams@foo.com', 'HR', 50000.00);
26INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (8,'Brown','Bill','bill.brown@foo.com', 'Engineering', 50000.00);
27INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (9,'Thomas','Susan','susan.thomas@foo.com', 'Legal', 80000.00);
28
29INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (10,'Davis','John','john.davis@foo.com', 'HR', 45000.00);
30INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (11,'Fowler','Mary','mary.fowler@foo.com', 'Engineering', 65000.00);
31INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (12,'Waters','David','david.waters@foo.com', 'Legal', 90000.00);
32
33--
34-- DEFINE STORED PROCEDURES
35-- 
36
37DELIMITER $$
38DROP PROCEDURE IF EXISTS `get_count_for_department`$$
39
40CREATE DEFINER=`student`@`localhost` PROCEDURE `get_count_for_department`(IN the_department VARCHAR(64), OUT the_count INT)
41BEGIN
42	
43	SELECT COUNT(*) INTO the_count FROM employees where department=the_department;
44
45END$$
46DELIMITER ;
47
48DELIMITER $$
49DROP PROCEDURE IF EXISTS `get_employees_for_department`$$
50
51CREATE DEFINER=`student`@`localhost` PROCEDURE `get_employees_for_department`(IN the_department VARCHAR(64))
52BEGIN
53
54	SELECT * from employees where department=the_department;
55
56END$$
57DELIMITER ;
58
59DELIMITER $$
60DROP PROCEDURE IF EXISTS `greet_the_department`$$
61
62CREATE DEFINER=`student`@`localhost` PROCEDURE `greet_the_department`(INOUT department VARCHAR(64))
63BEGIN
64
65	SET department = concat('Hello to the awesome ', department, ' team!');
66
67END$$
68DELIMITER ;
69
70DELIMITER $$
71DROP PROCEDURE IF EXISTS `increase_salaries_for_department`$$
72
73CREATE DEFINER=`student`@`localhost` PROCEDURE `increase_salaries_for_department`(IN the_department VARCHAR(64), IN increase_amount DECIMAL(10,2))
74BEGIN
75
76	UPDATE EMPLOYEES SET salary= salary + increase_amount where department=the_department;
77
78END$$
79DELIMITER ;