数据库系统原理--第3章练习--习题答案

题目

现有某公司HR（人力资源）系统的关系模式如下：
Emp (emp_id，ename，age，salary，address)
// 职工（职工编号，姓名，年龄，工资，住址）
Dep (dep_id，dname，address)
// 部门（部门编号，部门名称，部门所在地）
Works(emp_id，dep_id，begin_date, end_date)
// 工作（职工编号，部门编号，开始工作日期，离职日期）
注：Works表记录了职工在不同时间段在公司各部门工作的履历，
如果离职日期（end_date）为空，表明该职工目前仍在此部门工作。
请用SQL语言实现下列7小题：
（1）查询在部门编号为20或者30工作的，工资大于2500元的职工信息。
（2）查询在部门编号为20的，并于2000年开始工作的职工姓名和年龄。
（3）将部门编号为10的部门所有人员工资提高20%。
（4）在工作表中删除在部门10的，工资在2000到3000元之间的职工信息。
（5）找出职工不足3人的部门，结果显示部门编号及其相应的职工数。
（6）从职工中找出工资大于平均工资的职工信息。
（7）查询员工人数最多的部门编号。

答案

（1）查询在部门编号为20或者30工作的，工资大于2500元的职工信息。

SELECT *
FROM Emp,Dep,Works
WHERE salary>'2500'
	AND Works.dep_id IN(20,30)
	AND Emp.emp_id=Works.emp_id
	AND Dep.dep_id=Works.dep_id

（2）查询在部门编号为20的，并于2000年开始工作的职工姓名和年龄。

SELECT ename,age
FROM Emp,Works
WHERE Works.dep_id='20'
	AND begin_date='2000'
	AND Emp.emp_id=Works.emp_id

（3）将部门编号为10的部门所有人员工资提高20%。

UPDATE Emp
SET salary=salary*1.2
WHERE emp_id IN(
				SELECT emp_id
				FROM Works
				WHERE dep_id='10')

（4）在工作表中删除在部门10的，工资在2000到3000元之间的职工信息。

DELETE FROM Emp
WHERE salary >= '2000'
	AND salary <= '3000'
	AND emp_id IN(
				SELECT emp_id
				FROM Works
				WHERE dep_id='10')

（5）找出职工不足3人的部门，结果显示部门编号及其相应的职工数。

SELECT Works.dep_id,COUNT(emp_id)AS'职工数'
FROM Emp,Works
WHERE Emp.emp_id=Works.emp_id
GROUP BY Works.dep_id
HAVING COUNT(emp_id)<=3

（6）从职工中找出工资大于平均工资的职工信息。

SELECT *
FROM Emp
WHERE salary >(
				SELECT AVG(salary)AS '平均工资'
				FROM Emp)

（7）查询员工人数最多的部门编号。

--查询员工人数最多的部门编号。
SELECT dep_id
FROM (--把"查询部门编号和职工编号的人数"当作虚表A
	SELECT dep_id,COUNT(emp_id) Ce
	FROM Works
	GROUP BY dep_id)A
WHERE Ce=(--查询虚表A中职工编号的人数Ce的最大值
		SELECT MAX(Ce)
		FROM (--把"查询部门编号和职工编号的人数"当作虚表A
			SELECT dep_id,COUNT(emp_id) Ce
			FROM Works
			GROUP BY dep_id)A)

本题解答参考：【数据库】如何查询选修人数最大的课程号