数据库第三章编程作业答案

数据库第三章编程作业，共 10道SQL评测题，涵盖UPDATE、GROUP_CONCAT、ORDER BY、子查询、JOIN、视图创建、聚合函数等核心知识点。

---

题目与答案

第1题：将所有获取奖金的员工当前的薪水增加10%

题目描述： 将所有获取奖金的员工当前的薪水增加10%。

表结构：

CREATE TABLE emp_bonus(
    emp_no int NOT NULL,
    recevied datetime NOT NULL,
    btype smallint NOT NULL
);

CREATE TABLE salaries (
    emp_no int(11) NOT NULL,
    salary int(11) NOT NULL,
    from_date date NOT NULL,
    to_date date NOT NULL,
    PRIMARY KEY (emp_no, from_date)
);

答案：

UPDATE salaries SET salary = salary * 1.1
WHERE emp_no IN (SELECT emp_no FROM emp_bonus)
AND to_date = '9999-01-01';

---

第2题：按照dept_no进行汇总

题目描述： 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees。

表结构：

CREATE TABLE dept_emp (
    emp_no int(11) NOT NULL,
    dept_no char(4) NOT NULL,
    from_date date NOT NULL,
    to_date date NOT NULL,
    PRIMARY KEY (emp_no, dept_no)
);

输出格式：

dept_no    employees
d001    10001,10002
d002    10006
d003    10005
d004    10003,10004
d005    10007,10008,10010
d006    10009,10010

答案：

SELECT dept_no, GROUP_CONCAT(emp_no ORDER BY emp_no SEPARATOR ',') AS employees
FROM dept_emp
GROUP BY dept_no
ORDER BY dept_no;

---

第3题：获取Employees中的first_name

题目描述： 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列。

表结构：

CREATE TABLE employees (
    emp_no int(11) NOT NULL,
    birth_date date NOT NULL,
    first_name varchar(14) NOT NULL,
    last_name varchar(16) NOT NULL,
    gender char(1) NOT NULL,
    hire_date date NOT NULL,
    PRIMARY KEY (emp_no)
);

答案：

SELECT first_name FROM employees
ORDER BY RIGHT(first_name, 2) ASC, first_name ASC;

---

第4题：查找最晚入职员工的所有信息

题目描述： 查找最晚入职员工的所有信息。

表结构：

CREATE TABLE employees (
    emp_no int(11) NOT NULL,
    birth_date date NOT NULL,
    first_name varchar(14) NOT NULL,
    last_name varchar(16) NOT NULL,
    gender char(1) NOT NULL,
    hire_date date NOT NULL,
    PRIMARY KEY (emp_no)
);

输出格式：

emp_no    birth_date    first_name    last_name    gender    hire_date
10008    1958-02-19    Saniya    Kalloufi    M    1994-09-15

答案：

SELECT * FROM employees
WHERE hire_date = (SELECT MAX(hire_date) FROM employees);

---

第5题：查找所有员工入职时候的薪水情况

题目描述： 查找所有员工入职时候的薪水情况，给出emp_no以及salary，并按照emp_no进行逆序。

表结构：

CREATE TABLE employees (
    emp_no int(11) NOT NULL,
    birth_date date NOT NULL,
    first_name varchar(14) NOT NULL,
    last_name varchar(16) NOT NULL,
    gender char(1) NOT NULL,
    hire_date date NOT NULL,
    PRIMARY KEY (emp_no)
);

CREATE TABLE salaries (
    emp_no int(11) NOT NULL,
    salary int(11) NOT NULL,
    from_date date NOT NULL,
    to_date date NOT NULL,
    PRIMARY KEY (emp_no, from_date)
);

输出格式：

emp_no    salary
10011    25828
...
10001    60117

答案：

SELECT e.emp_no, s.salary
FROM employees e
JOIN salaries s
ON e.emp_no = s.emp_no AND e.hire_date = s.from_date
ORDER BY e.emp_no DESC;

---

第6题：获取所有部门当前manager的当前薪水情况

题目描述： 获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date=‘9999-01-01’。

表结构：

CREATE TABLE dept_manager (
    dept_no char(4) NOT NULL,
    emp_no int(11) NOT NULL,
    from_date date NOT NULL,
    to_date date NOT NULL,
    PRIMARY KEY (emp_no, dept_no)
);

CREATE TABLE salaries (
    emp_no int(11) NOT NULL,
    salary int(11) NOT NULL,
    from_date date NOT NULL,
    to_date date NOT NULL,
    PRIMARY KEY (emp_no, from_date)
);

输出格式：

dept_no    emp_no    salary
d001    10002    72527
d004    10004    74057
d003    10005    94692
d002    10006    43311
d006    10010    94409

答案：

SELECT dm.dept_no, dm.emp_no, s.salary
FROM dept_manager dm
JOIN salaries s
ON dm.emp_no = s.emp_no
WHERE dm.to_date = '9999-01-01'
AND s.to_date = '9999-01-01';

---

第7题：获取所有员工当前的manager

题目描述： 获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date=‘9999-01-01’。结果第一列给出当前员工的emp_no，第二列给出其manager对应的manager_no。

表结构：

CREATE TABLE dept_emp (
    emp_no int(11) NOT NULL,
    dept_no char(4) NOT NULL,
    from_date date NOT NULL,
    to_date date NOT NULL,
    PRIMARY KEY (emp_no, dept_no)
);

CREATE TABLE dept_manager (
    dept_no char(4) NOT NULL,
    emp_no int(11) NOT NULL,
    from_date date NOT NULL,
    to_date date NOT NULL,
    PRIMARY KEY (emp_no, dept_no)
);

输出格式：

emp_no    manager_no
10001    10002
10003    10004
10009    10010

答案：

SELECT de.emp_no, dm.emp_no AS manager_no
FROM dept_emp de
JOIN dept_manager dm
ON de.dept_no = dm.dept_no
WHERE de.to_date = '9999-01-01'
AND dm.to_date = '9999-01-01'
AND de.emp_no <> dm.emp_no
ORDER BY de.emp_no;

---

第8题：针对actor表创建视图actor_name_view

题目描述： 针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v。

表结构：

CREATE TABLE IF NOT EXISTS actor (
    actor_id smallint(5) NOT NULL PRIMARY KEY,
    first_name varchar(45) NOT NULL,
    last_name varchar(45) NOT NULL,
    last_update timestamp NOT NULL DEFAULT (datetime('now','localtime'))
);

答案：

CREATE VIEW actor_name_view AS
SELECT first_name AS first_name_v, last_name AS last_name_v
FROM actor;

---

第9题：统计各个部门对应员工涨幅的次数总和

题目描述： 统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum。

表结构：

CREATE TABLE departments (
    dept_no char(4) NOT NULL,
    dept_name varchar(40) NOT NULL,
    PRIMARY KEY (dept_no)
);

CREATE TABLE dept_emp (
    emp_no int(11) NOT NULL,
    dept_no char(4) NOT NULL,
    from_date date NOT NULL,
    to_date date NOT NULL,
    PRIMARY KEY (emp_no, dept_no)
);

CREATE TABLE salaries (
    emp_no int(11) NOT NULL,
    salary int(11) NOT NULL,
    from_date date NOT NULL,
    to_date date NOT NULL,
    PRIMARY KEY (emp_no, from_date)
);

输出格式：

dept_no    dept_name    sum
d001    Marketing    24
d002    Finance    14
d003    Human Resources    13
d004    Production    24
d005    Development    25
d006    Quality Management    25

答案：

SELECT d.dept_no, d.dept_name, COUNT(s.salary) AS sum
FROM departments d
JOIN dept_emp de ON d.dept_no = de.dept_no
JOIN salaries s ON de.emp_no = s.emp_no
GROUP BY d.dept_no, d.dept_name
ORDER BY d.dept_no;

---

第10题：统计出当前各个title类型对应的员工当前薪水对应的平均工资

题目描述： 统计出各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。

表结构：

CREATE TABLE salaries (
    emp_no int(11) NOT NULL,
    salary int(11) NOT NULL,
    from_date date NOT NULL,
    to_date date NOT NULL,
    PRIMARY KEY (emp_no, from_date)
);

CREATE TABLE IF NOT EXISTS titles (
    emp_no int(11) NOT NULL,
    title varchar(50) NOT NULL,
    from_date date NOT NULL,
    to_date date DEFAULT NULL
);

输出格式：

title    avg
Engineer    94409.0
Senior Engineer    69009.2
Senior Staff    91381.0
Staff    72527.0

答案：

SELECT t.title, AVG(s.salary) AS avg
FROM titles t
JOIN salaries s
ON t.emp_no = s.emp_no
GROUP BY t.title
ORDER BY t.title;

---

知识点总结

常用SQL函数

函数	说明	示例
GROUP_CONCAT()	将分组中的值连接成字符串	GROUP_CONCAT(emp_no SEPARATOR ',')
COUNT()	统计行数	COUNT(*) 或 COUNT(column)
AVG()	计算平均值	AVG(salary)
MAX()	获取最大值	MAX(hire_date)
RIGHT()	从右侧截取字符串	RIGHT(first_name, 2)

JOIN连接类型

类型	说明
INNER JOIN	返回两表中匹配的行
LEFT JOIN	返回左表所有行，右表匹配的行
RIGHT JOIN	返回右表所有行，左表匹配的行

SQL执行顺序

FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

视图操作

-- 创建视图
CREATE VIEW view_name AS
SELECT column1, column2 FROM table_name;

-- 删除视图
DROP VIEW view_name;

子查询使用场景

-- WHERE中使用子查询
SELECT * FROM employees
WHERE hire_date = (SELECT MAX(hire_date) FROM employees);

-- IN中使用子查询
UPDATE salaries SET salary = salary * 1.1
WHERE emp_no IN (SELECT emp_no FROM emp_bonus);