use mydb3; -- 创建部门表 create table if not exists dept3( deptno varchar(20) primary key , -- 部门号 name varchar(20) -- 部门名字 ); -- 创建员工表 create table if not exists emp3( eid varchar(20) primary key , -- 员工编号 ename varchar(20), -- 员工名字 age int, -- 员工年龄 dept_id varchar(20) -- 员工所属部门 ); -- 给dept3表添加数据 insert into dept3 values('1001','研发部'); insert into dept3 values('1002','销售部'); insert into dept3 values('1003','财务部'); insert into dept3 values('1004','人事部'); -- 给emp3表添加数据 insert into emp3 values('1','乔峰',20, '1001'); insert into emp3 values('2','段誉',21, '1001'); insert into emp3 values('3','虚竹',23, '1001'); insert into emp3 values('4','阿紫',18, '1001'); insert into emp3 values('5','扫地僧',85, '1002'); insert into emp3 values('6','李秋水',33, '1002'); insert into emp3 values('7','鸠摩智',50, '1002'); insert into emp3 values('8','天山童姥',60, '1003'); insert into emp3 values('9','慕容博',58, '1003'); insert into emp3 values('10','丁春秋',71, '1005'); 1、查询每个部门的所属员工 2、查询研发部门的所属员工 3、查询研发部和销售部的所属员工 4、查询每个部门的员工数,并升序排序 5、查询人数大于等于3的部门，并按照人数降序排序

1、查询每个部门的所属员工：

SELECT d.name AS department, e.ename AS employee
FROM dept3 d
JOIN emp3 e ON d.deptno = e.dept_id;

2、查询研发部门的所属员工：

SELECT e.ename AS employee
FROM emp3 e
JOIN dept3 d ON e.dept_id = d.deptno
WHERE d.name = '研发部';

3、查询研发部和销售部的所属员工：

SELECT e.ename AS employee
FROM emp3 e
JOIN dept3 d ON e.dept_id = d.deptno
WHERE d.name IN ('研发部', '销售部');

4、查询每个部门的员工数，并升序排序：

SELECT d.name AS department, COUNT(e.eid) AS employee_count
FROM dept3 d
LEFT JOIN emp3 e ON d.deptno = e.dept_id
GROUP BY d.name
ORDER BY employee_count ASC;

5、查询人数大于等于3的部门，并按照人数降序排序：

SELECT d.name AS department, COUNT(e.eid) AS employee_count
FROM dept3 d
LEFT JOIN emp3 e ON d.deptno = e.dept_id
GROUP BY d.name
HAVING employee_count >= 3
ORDER BY employee_count DESC;

注意：以上SQL语句是基于MySQL语法的，如果使用其他数据库可能会有些许差异。