Creating Depratment and Employee Tables with Data Insetrion

1. Departmant Table Creation

CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Department ID',
    department_name VARCHAR(50) COMMENT 'Department Name'
);

2. Inserting Department Data

INSERT INTO departments (department_name) VALUES
('Sales'),
('Finance'),
('Production'),
('Human Resources');

3. Employee Table Creation

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Employee ID',
    employee_name VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'Employee Name',
    gender CHAR(1) NOT NULL DEFAULT 'M' COMMENT 'Gender',
    birth_date DATE NOT NULL DEFAULT '1990-01-01' COMMENT 'Birth Date',
    salary DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Salary',
    address VARCHAR(200) NOT NULL DEFAULT '' COMMENT 'Address',
    department_id INT COMMENT 'Department ID'
);

4. Cretaing Indexes

CREATE INDEX idx_employee_name ON employees (employee_name);
CREATE INDEX idx_birth_date ON employees (birth_date);
CREATE INDEX idx_dept_name ON employees (department_id, employee_name);

5. Inserting Employee Data

INSERT INTO employees (employee_name, gender, birth_date, salary, address, department_id) VALUES
('Zhang Xiaohong', 'F', '1980-01-23', 5800, 'Zhongyuan Road 10, Zhengzhou, Henan', 1),
('Zhang Jingjing', 'F', '1987-10-03', 5400, 'Pingyuan Road 38, Xinxiang, Henan', 1),
('Wang Yunfei', 'M', '1992-11-15', 5600, 'Renmin Road 28, Xinxiang, Henan', 1),
('Wang Pengfei', 'M', '1987-10-01', 6800, 'Dongming Avenue 12, Xinxiang, Henan', 1),
('Wang Dapeng', 'M', '1989-02-11', 5900, 'Dongfeng Road 15, Zhengzhou, Henan', 1),
('Wang Mengmeng', 'F', '1986-12-30', 5000, 'Wuyi Road 14, Kaifeng, Henan', 2),
('Wang Daguang', 'M', '1988-11-08', 6200, 'Bayi Road 124, Kaifeng, Henan', 2),
('Wang Xiaoming', 'M', '1998-01-03', 4800, 'Xuesong Road 128, Zhumadian, Henan', 2),
('Wang Nana', 'F', '1994-03-05', 5200, 'Chezhan Road 2, Zhumadian, Henan', 2),
('Liu Yunfei', 'M', '1992-08-13', 6800, 'Minsheng Road 255, Nanyang, Henan', 3),
('Zhang Lujun', 'M', '1991-09-06', 6200, 'Zhang Zhongjing Road 14, Nanyang, Henan', 3);

Creating Views

1. Sales Department Employee View

CREATE VIEW sales_employees AS
SELECT employee_name, address
FROM employees
WHERE department_id = 1;

2. Sales Department Employee Details View

CREATE VIEW sales_employee_details AS
SELECT e.employee_name, e.address, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id = 1;

3. Department Statistics View

CREATE VIEW department_statistics AS
SELECT d.department_name, COUNT(e.employee_id) AS employee_count, AVG(e.salary) AS average_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

4. Modifying Sales Employee Details View

CREATE OR REPLACE VIEW sales_employee_details AS
SELECT e.employee_name, e.address, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id = 1;

View Management

1. Listing All Views

SHOW FULL TABLES IN your_database WHERE TABLE_TYPE LIKE 'VIEW';

2. Viewing View Structure

SHOW CREATE VIEW sales_employees;
SHOW CREATE VIEW sales_employee_details;
SHOW CREATE VIEW department_statistics;

3. Dropping Views

DROP VIEW IF EXISTS sales_employees;
DROP VIEW IF EXISTS sales_employee_details;
DROP VIEW IF EXISTS department_statistics;

Creating Stored Procedures

1. Total Salary Procedure

DELIMITER $$
CREATE PROCEDURE calculate_total_salary()
BEGIN
    SELECT SUM(salary) AS total_salary FROM employees;
END$$
DELIMITER ;

2. Executing Total Salary Procedure

CALL calculate_total_salary();

3. Employee Address Lookup Procedure

DELIMITER $$
CREATE PROCEDURE find_employee_address(IN emp_name VARCHAR(50))
BEGIN
    SELECT address FROM employees WHERE employee_name = emp_name;
END$$
DELIMITER ;

4. Executing Address Lookup

CALL find_employee_address('Zhang Xiaohong');

5. Average Salary by Department and Gender

DELIMITER $$
CREATE PROCEDURE calculate_average_salary(
    IN dept_id INT,
    IN emp_gender CHAR(1),
    OUT avg_salary DECIMAL(10,2)
)
BEGIN
    SELECT AVG(salary) INTO avg_salary
    FROM employees
    WHERE department_id = dept_id AND gender = emp_gender;
END$$
DELIMITER ;

6. Executing Average Salary Procedure

CALL calculate_average_salary(1, 'M', @avg_salary);
SELECT @avg_salary;

7. Removing Stored Procedures

DROP PROCEDURE IF EXISTS calculate_total_salary;
DROP PROCEDURE IF EXISTS find_employee_address;
DROP PROCEDURE IF EXISTS calculate_average_salary;