創(chuàng)建部門表
create table dept(
deptno int primary key,
deptname varchar(30)
);
insert into dept values(101,'銷售');
insert into dept values(102,'后勤');
創(chuàng)建員工表
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);
查看創(chuàng)建效果
select * from dept;
select * from emp;
- 查詢員工信息僻爽，要求輸出員工編號(hào)春弥、姓名呛哟、性別、年齡以及所在部門名稱
select e.no,e.name,e.age,d.deptname
from emp e,dept d
where d.deptno=e.deptno;
- 查詢每個(gè)部門的員工人數(shù)惕稻，輸出信息包括部門名稱竖共、員工人數(shù)
select d.deptname,count(e.deptno) deptnumber
from emp e,dept d
where d.deptno=e.deptno
group by d.deptname;
- 統(tǒng)計(jì)不同性別人數(shù)，輸出信息包括性別俺祠、人數(shù)
select e.age,count(e.age)agenum
from emp e
group by e.age;
- 查詢每個(gè)部門的員工最小年齡公给，輸出信息包括部門名稱、最小年齡
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);
- 查詢每個(gè)部門年齡最小的員工信息
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);
