创建部门表
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;
- 查询员工信息，要求输出员工编号、姓名、性别、年龄以及所在部门名称
select e.no,e.name,e.age,d.deptname
from emp e,dept d
where d.deptno=e.deptno;
- 查询每个部门的员工人数，输出信息包括部门名称、员工人数
select d.deptname,count(e.deptno) deptnumber
from emp e,dept d
where d.deptno=e.deptno
group by d.deptname;
- 统计不同性别人数，输出信息包括性别、人数
select e.age,count(e.age)agenum
from emp e
group by e.age;
- 查询每个部门的员工最小年龄，输出信息包括部门名称、最小年龄
select d.deptname,min(e.age)minage
from emp e,dept d
where d.deptno=e.deptno
group by d.deptname;
- 查询每种性别的最小员工年龄，输出信息包括部门名称？性别、最小年龄
select e.gender,min(e.age)minage
from emp e,dept d
where d.deptno=e.deptno
group by e.gender;
- 查询年龄最小的员工信息
select *
from emp e,dept d
where e.deptno=d.deptno and e.age=(select MIN(age) from emp);
- 查询每个部门年龄最小的员工信息
select *
from emp e1,dept d
where age=(select MIN(age) from emp e2 where e2.deptno=e1.deptno) and e1.deptno=d.deptno;
- 查询每种性别年龄最小的员工信息
select *
from emp
where age=any(select MIN(age) from emp group by gender);
