文档介绍: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 最彳氐工资 from emp group by dept no;
-2列出emp表中各部门job为'CLERK'的员工的最低工资,最高工资 select max(sal) as 最高
工资 , min(sal) as 1H氐工资 , dept no as 吾部门号 from emp where job='clerk' group by dept no;
--3对于emp中最低工资小于 2000的部门,列出job为'CLERK'的员工的部门号, 最低工
资,最高工资
select as 吾吾门号,max(sal) as 最高工资, min(sal) as 1H氐工资 from emp as b
where job='clerk' and (select min( sal)from emp as a where no= no )<2000 group by no;
-4 根据部门号由高而低,工资有