Day2 SQL II多表查询的练习

Work01:多表查询的练习

create table dept(
	deptno varchar(10) primary key,
	dname varchar(10)
)

create table emp(
	empno varchar(10) primary key,
	ename varchar(10),
	job varchar(10),
	mgr varchar(10),
	sal float,
	deptno varchar(10) references deft(depton)

)

insert into dept values ('1','事业部'); 
insert into dept values ('2','销售部'); 
insert into dept values ('3','技术部'); 
 
insert into emp values ('01','jacky','clerk','tom','1000','1'); 
insert into emp values ('02','tom','clerk','','2000','1'); 
insert into emp values ('07','biddy','clerk','','2000','1'); 
insert into emp values ('03','jenny','sales','pretty','600','2'); 
insert into emp values ('04','pretty','sales','','800','2'); 
insert into emp values ('05','buddy','jishu','canndy','1000','3'); 
insert into emp values ('06','canndy','jishu','','1500','3'); 
select * from dept; 
select * from emp; 

# 列出emp表中各部门的部门号，最高工资，最低工资
select deptno,max(sal) as 最高工资,min(sal) as 最低工资 from emp group by deptno


# 列出emp表中各部门job为'CLERK'的员工的最低工资，最高工资
select deptno,max(sal) as 最高工资,min(sal) as 最低工资 from emp where job = 'CLERK' group by deptno;


# 根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资
select ename,deptno,sal from emp order by deptno desc, sal asc;


# 列出'buddy'所在部门中每个员工的姓名与部门号
select ename,deptno from emp where  deptno = (select deptno from emp where ename = 'buddy');


# 列出每个员工的姓名，工作，部门号，部门名
select ename,job,emp.deptno,dname from emp,dept where emp.deptno = dept.deptno;


# 列出emp中工作为'CLERK'的员工的姓名，工作，部门号，部门名
select ename,job,emp.deptno,dname from emp,dept where emp.deptno = dept.deptno and job = 'CLERK';


# 对于emp中有管理者的员工，列出姓名，管理者姓名(管理者外键为mgr)
select ename as 姓名,mgr as 管理者姓名 from emp where mgr != '';


# 对于dept表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与工作
select dname,dept.deptno,ename,job from emp right outer join dept on dept.deptno = emp.deptno and job = 'CLERK';