class-notes/2207/存储函数练习题.md

语法结构

DELIMITER //

CREATE FUNCTION calculate_annual_salary (
    monthly_salary DECIMAL(10, 2)
)
RETURNS DECIMAL(10, 2)
DETERMINISTIC | NOT DETERMINISTIC #结果确定/不确定
BEGIN
    RETURN monthly_salary * 12;
END //

DELIMITER ;

表结构与数据

-- 创建数据库
CREATE DATABASE company CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE company;

-- 创建 department 表
CREATE TABLE department (
    dept_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '部门ID，主键',
    dept_name VARCHAR(100) NOT NULL COMMENT '部门名称',
    location VARCHAR(100) NOT NULL COMMENT '部门所在地'
);

-- 创建 job 表
CREATE TABLE job (
    job_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '职位ID，主键',
    job_title VARCHAR(100) NOT NULL COMMENT '职位名称',
    min_salary DECIMAL(10, 2) NOT NULL COMMENT '最低工资',
    max_salary DECIMAL(10, 2) NOT NULL COMMENT '最高工资'
);

-- 创建 employee 表
CREATE TABLE employee (
    emp_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID，主键',
    emp_name VARCHAR(100) NOT NULL COMMENT '员工姓名',
    salary DECIMAL(10, 2) NOT NULL COMMENT '员工工资',
    hire_date DATE NOT NULL COMMENT '入职日期',
    dept_id INT COMMENT '部门ID，外键',
    job_id INT COMMENT '职位ID，外键',
    FOREIGN KEY (dept_id) REFERENCES department(dept_id),
    FOREIGN KEY (job_id) REFERENCES job(job_id)
);

INSERT INTO department (dept_name, location) VALUES
('HR', 'New York'),
('IT', 'San Francisco'),
('Finance', 'Chicago'),
('Marketing', 'Los Angeles'),
('Sales', 'Boston'),
('Operations', 'Houston'),
('Research', 'Seattle'),
('Customer Support', 'Austin');

INSERT INTO job (job_title, min_salary, max_salary) VALUES
('Manager', 60000.00, 100000.00),
('Developer', 50000.00, 80000.00),
('Analyst', 45000.00, 70000.00),
('Designer', 40000.00, 65000.00),
('Sales Executive', 35000.00, 60000.00),
('HR Specialist', 40000.00, 70000.00),
('Finance Analyst', 50000.00, 75000.00),
('Marketing Coordinator', 45000.00, 70000.00),
('Operations Manager', 55000.00, 90000.00),
('Research Scientist', 60000.00, 95000.00),
('Customer Support Representative', 35000.00, 55000.00);

INSERT INTO employee (emp_name, salary, hire_date, dept_id, job_id) VALUES
('Alice', 70000.00, '2020-01-15', 1, 1),
('Bob', 65000.00, '2019-03-22', 2, 2),
('Charlie', 55000.00, '2018-07-30', 2, 3),
('David', 60000.00, '2021-05-10', 3, 4),
('Eva', 50000.00, '2017-11-05', 4, 5),
('Frank', 75000.00, '2016-09-12', 5, 1),
('Grace', 48000.00, '2022-02-20', 1, 2),
('Henry', 52000.00, '2021-08-15', 2, 3),
('Ivy', 58000.00, '2020-04-10', 3, 4),
('Jack', 62000.00, '2019-12-01', 4, 5),
('Katie', 53000.00, '2021-07-25', 5, 6),
('Leo', 67000.00, '2018-05-18', 6, 7),
('Mona', 49000.00, '2020-11-30', 7, 8),
('Nina', 71000.00, '2019-02-14', 8, 9),
('Oscar', 56000.00, '2022-03-05', 1, 10),
('Paul', 63000.00, '2021-09-12', 2, 11),
('Quincy', 54000.00, '2020-06-20', 3, 1),
('Rachel', 59000.00, '2019-08-15', 4, 2),
('Steve', 68000.00, '2018-12-10', 5, 3),
('Tina', 51000.00, '2022-01-22', 6, 4),
('Uma', 72000.00, '2021-04-18', 7, 5),
('Victor', 57000.00, '2020-07-30', 8, 6),
('Wendy', 64000.00, '2019-10-05', 1, 7),
('Xander', 50000.00, '2022-05-12', 2, 8),
('Yara', 69000.00, '2021-03-25', 3, 9),
('Zack', 55000.00, '2020-09-15', 4, 10),
('Amy', 61000.00, '2019-06-20', 5, 11),
('Brian', 74000.00, '2018-04-10', 6, 1),
('Cathy', 52000.00, '2022-07-18', 7, 2),
('Derek', 67000.00, '2021-12-01', 8, 3);

练习题

练习题 1: 计算员工年薪

创建一个存储函数 calculate_annual_salary，接受员工的月薪作为参数，返回其年薪（假设年薪为月薪的 12 倍）。

练习题 2: 计算员工工龄

创建一个存储函数 calculate_years_of_service，接受员工的入职日期作为参数，返回该员工的工龄（以年为单位）。

练习题 3: 根据部门计算平均工资

创建一个存储函数 calculate_avg_salary_by_dept，接受部门 ID 作为参数，返回该部门的平均工资。

练习题 4: 根据职位计算平均工资

创建一个存储函数 calculate_avg_salary_by_job，接受职位 ID 作为参数，返回该职位的平均工资。

练习题 5: 计算员工税后工资

创建一个存储函数 calculate_net_salary，接受员工的工资作为参数，返回其税后工资（假设税率为 15%）。

练习题 6: 判断员工是否满足加薪条件

创建一个存储函数 is_eligible_for_raise，接受员工的工资和工龄作为参数，返回一个布尔值（1 或 0）。加薪条件如下：

• 如果工资低于 60000 且工龄大于等于 3 年，返回 1（满足条件）。
• 否则返回 0（不满足条件）。

练习题 7: 计算部门总工资

创建一个存储函数 calculate_total_salary_by_dept，接受部门 ID 作为参数，返回该部门所有员工的总工资。

练习题 8: 计算职位总工资

创建一个存储函数 calculate_total_salary_by_job，接受职位 ID 作为参数，返回该职位所有员工的总工资。

练习题 9: 判断员工是否属于高薪部门

创建一个存储函数 is_high_salary_department，接受部门 ID 作为参数，返回一个布尔值（1 或 0）。如果该部门的平均工资大于等于 70000，返回 1（高薪部门），否则返回 0（非高薪部门）。

练习题 10: 计算员工的总收入

创建一个存储函数 calculate_total_income，接受员工的工资和奖金（假设奖金为工资的 10%）作为参数，返回其总收入（工资 + 奖金）。