SQL server 链接、分组查询

创建部门表

create table dept(
   deptno int primary key,
   deptname varchar(30)
);
insert into dept values(101,'销售');
insert into dept values(102,'后勤');

创建员工表

create table emp(
no int primary key,
name varchar(20),
gender varchar(20),
age int,
deptno int foreign key references dept(deptno)
);
insert into emp values(1001,'王明','男',20,101);
insert into emp values(1002,'李曼莎','女',28,101);
insert into emp values(1003,'赵一萍','女',30,102);
insert into emp values(1004,'秦浩','男',32,102);
insert into emp values(1005,'李芬','女',39,102);

查看创建效果

select * from dept;
select * from emp;

1. 查询员工信息，要求输出员工编号、姓名、性别、年龄以及所在部门名称

select e.no,e.name,e.age,d.deptname
from emp e,dept d
where d.deptno=e.deptno;

2. 查询每个部门的员工人数，输出信息包括部门名称、员工人数

select d.deptname,count(e.deptno) deptnumber
from emp e,dept d
where d.deptno=e.deptno
group by d.deptname;

3. 统计不同性别人数，输出信息包括性别、人数

select e.age,count(e.age)agenum
from emp e
group by e.age;

4. 查询每个部门的员工最小年龄，输出信息包括部门名称、最小年龄

select d.deptname,min(e.age)minage
from emp e,dept d
where d.deptno=e.deptno
group by d.deptname;

5. 查询每种性别的最小员工年龄，输出信息包括部门名称？性别、最小年龄

select e.gender,min(e.age)minage
from emp e,dept d
where d.deptno=e.deptno
group by e.gender;

6. 查询年龄最小的员工信息

select *
from emp e,dept d
where e.deptno=d.deptno and e.age=(select MIN(age) from emp);

7. 查询每个部门年龄最小的员工信息

select *
from emp e1,dept d
where age=(select MIN(age) from emp e2 where e2.deptno=e1.deptno) and e1.deptno=d.deptno;

8. 查询每种性别年龄最小的员工信息

select *
from emp
where age=any(select MIN(age) from emp group by gender);