create table dept( dept no varchar(IO) primary key,
dn ame varchar(10)
)
;
create table emp
(
emp no varchar(10) primary key,
en ame varchar(IO),
job varchar(IO),
mgr varchar(10),
sal varchar(10),
dept no varchar(10) referen ces dept(dept no)
);
insert into dept values ('1','
事业部
');
insert into dept values ('2','
销售部
');
insert into dept values ('3','
技术部
');
in sert into emp values ('01','jacky','clerk','tom','1000','1');
in sert into emp values ('02','tom','clerk',",'2000','1');
in sert into emp values ('07','biddy','clerk',”,'2000','1');
in sert into emp values ('03','je nn y','sales','pretty','600','2');
in sert into emp values ('04','pretty','sales',",'800','2');
in sert into emp values ('05','buddy','jishu','ca nn dy','1000','3');
in sert into emp values ('06','ca nn dy','jishu',",'1500','3'); select * from dept;
select * from emp;
--1
列出
emp
表中各部门的部门号,最高工资,最低工资
select dept no as
咅
部门号
,max(sal) as
最高工资
,
min( sal) as
最低工资
emp group by dept no;
--2
列出
emp
表中各部门
job
为
'CLERK'
的员工的最低工资,最高工资
select max(sal) as
最高
工资
,
min(sal) as
最低工资
,
dept
no as
咅部门号
from emp where job='clerk' group by dept no;
from