数据库第二次作业

1. 显示所有职工的基本信息。
SELECT * FROM t_worker;
2. 查询所有职工所属部门的部门号，不显示重复的部门号。
SELECT DISTINCT department_id FROM t_worker;

 3. 求出所有职工的人数。
SELECT COUNT(*) AS total_workers FROM t_worker;

 4. 列出最高工和最低工资。
SELECT MAX(wages) AS max_wage, MIN(wages) AS min_wage FROM t_worker;

 5. 列出职工的平均工资和总工资。
SELECT AVG(wages) AS average_wage, SUM(wages) AS total_wage FROM t_worker;

6. 创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。
CREATE TABLE work_date_table AS
SELECT worker_id, name, worker_date FROM t_worker;

 7. 显示所有党员的年龄。
SELECT FLOOR(DATEDIFF(CURDATE(), borth_date) / 365) AS age FROM t_worker WHERE politics = '党员';

8. 列出工资在 4000 - 8000 之间的所有职工姓名。
SELECT name FROM t_worker WHERE wages BETWEEN 4000 AND 8000;

9. 列出所有孙姓和李姓的职工姓名。
SELECT name FROM t_worker WHERE name LIKE '孙%' OR name LIKE '李%';

10. 列出所有部门号为 102 和 103 且不是党员的职工号、姓名。
SELECT worker_id, name FROM t_worker WHERE (department_id = 102 OR department_id = 103) AND politics!= '党员';

 11. 将职工表 t_worker 中的职工按出生的先后顺序排序。
SELECT * FROM t_worker ORDER BY borth_date ASC;

12. 显示工资最高的前 3 名职工的职工号和姓名。
SELECT worker_id, name FROM t_worker ORDER BY wages DESC LIMIT 3;

 13. 求出各部门党员的人数。
SELECT department_id, COUNT(*) AS num_of_party_members FROM t_worker WHERE politics = '党员' GROUP BY department_id;

 14. 统计各部门的工资和平均工资并保留 2 位小数
SELECT department_id, SUM(wages) AS total_wage, ROUND(AVG(wages), 2) AS average_wage FROM t_worker GROUP BY department_id;

15. 列出总人数大于等于 3 的部门号和总人数。
SELECT department_id, COUNT(*) AS total_workers FROM t_worker GROUP BY department_id HAVING COUNT(*) >= 3;