company练习

表结构

--部门表create tabledept(deptnoint(3)primary key,dnamevarchar(14),locvarchar(13));--雇员表create tableemp(empnoint(4)notnullprimary key,enamevarchar(10),jobvarchar(10),mgrint(4),hiredate datetime,saldouble,commdouble,deptnoint(3),foreignkey(deptno)referencesdept(deptno));--工资级别表create tablesalgrade(gradeint(3)primary key,losalint(3),hisalint(3));2.插入数据（进行初始化）use company;--往部门表中查数据insert into deptvalues(10,'Accounting','New York');insert into deptvalues(20,'Research','Dallas');insert into deptvalues(30,'Sales','Chicago');insert into deptvalues(40,'Operations','Boston');insert into deptvalues(50,'Admin','Washing');--往雇员表中插数据insert into empvalues(7369,'Smith','Clerk',7902,'1980-12-17',800,0,20);insert into empvalues(7499,'Allen','Salesman',7698,'1981-2-20',1600,300,30);insert into empvalues(7844,'Turner','Salesman',7499,'1981-9-8',1500,0,30);insert into empvalues(7698,'Tom','Manager',0,'1981-9-8',6100,600,40);insert into empvalues(7876,'Adams','Clerk',7900,'1987-5-23',1100,0,20);insert into empvalues(7900,'James','Clerk',7698,'1981-12-3',2400,0,30);insert into empvalues(7902,'Ford','Analyst',7698,'1981-12-3',3000,null,20);insert into empvalues(7901,'Kik','Clerk',7900,'1981-12-3',1900,0,30);--往工资级别表中插数据insert into salgradevalues(1,700,1200);insert into salgradevalues(2,1201,1400);insert into salgradevalues(3,1401,2000);insert into salgradevalues(4,2001,3000);insert into salgradevalues(5,3001,5000);insert into salgradevalues(6,5001,10000);

1.查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。

SELECT    empno,    ename,    sal,    deptnoFROM empWHERE sal = (SELECTmax(sal)FROM emp);

2.薪水大于1200的雇员，按照部门编号进行分组，分组后的平均薪水必须大于1500，查询各分组的平均工资，按照工资的倒序进行排列。

SELECTavg(sal)avg_sal,    deptnoFROM    empWHERE    sal > 1200GROUP BY    deptnoHAVING    avg_sal > 1500ORDER BY    avg_sal DESC;

3.查询每个雇员和其所在的部门名

SELECT    ename,dnameFROM    emp,deptWHERE(emp.deptno = dept.deptno);

-- 或者（推荐）(on中就写连接条件，where中就写过滤条件，各司其职)

SELECT    ename,dnameFROM    empJOIN deptON(emp.deptno = dept.deptno);

4.查询每个雇员姓名及其工资所在的等级

SELECTename,gradeFROMempeJOINsalgradesON(e.salBETWEENs.losalANDs.hisal);

5.查询雇员名第2个字母不是a的雇员的姓名、所在的组名、工资所在的等级。

SELECT    ename,    dname,    gradeFROM    emp eJOIN dept d ON (e.deptno = d.deptno)JOIN salgrade s ON (    e.sal BETWEEN s.losalANDs.hisal)WHERE    ename NOT LIKE'_a%';

6.查询每个雇员和其经理的姓名

SELECT    e1.ename,    e2.enameFROM    emp e1,emp e2WHERE(e1.mgr = e2.empno);SELECT    e1.ename,    e2.enameFROM    emp e1JOIN emp e2ON(e1.mgr = e2.empno);

7.查询每个雇员和其经理的姓名（包括公司老板本身（他上面没有经理））

SELECT    e1.ename,    e2.enameFROM    emp e1LEFT JOIN emp e2ON(e1.mgr = e2.empno);

8.查询每个雇员的姓名及其所在部门的部门名（包括没有雇员的部门）

SELECT    ename,dnameFROM    emp eRIGHT JOIN dept dON(e.deptno = d.deptno);

9.查询每个部门中工资最高的人的姓名、薪水和部门编号

-- 先求出每个部门中的最高工资：select max(sal) max_sal,deptnofromemp group by deptno-- 在使用连接查询：select ename,sal,e.deptnofromemp e join(select max(sal) max_sal,deptnofromemp group by deptno) ton(e.sal = t.max_salande.deptno = t.deptno);

10.查询每个部门平均工资所在的等级

select deptno,avg_sal,gradefromsalgradejoin(select deptno,avg(sal) avg_salfromemp group by deptno) ton(t.avg_sal between salgrade.losalandsalgrade.hisal);或者：select deptno,avg_sal,gradefrom(select deptno,avg(sal) avg_salfromemp group by deptno) tjoin salgrade s on(t.avg_sal between s.losalands.hisal);

11.查询每个部门内平均的薪水等级

先求每个人的薪水等级selectename,deptno,gradefromempjoinsalgradeson(emp.salbetweens.losalands.hisal);再按组进行分组求平均selectdeptno,avg(grade)from(selectename,deptno,gradefromempjoinsalgradeson(emp.salbetweens.losalands.hisal))tgroupbydeptno;

12.查询雇员中有哪些人是经理人：

select enamefromemp where empnoin(select distinct mgrfromemp);或者：select enamefromemp join(select distinct mgrfromemp) ton(emp.empno=t.mgr);

13.平均薪水最高的部门的部门编号

1：先求出每个部门的平均薪水和部门号（把这个看成一张表）select avg(sal) avg_sal,deptnofromemp group by deptno;2：再求出平均薪水最高值（把这个看成一个值）select max(avg_sal)from(select avg(sal) avg_sal,deptnofromemp group by deptno) t;3：对1表使用2条件进行查询即可select avg_sal,deptnofrom(select avg(sal) avg_sal,deptnofromemp group by deptno) t1where avg_sal=(select max(avg_sal)from(select avg(sal) avg_sal,deptnofromemp group by deptno) t2);

14.求平均薪水最高的部门的部门名称

select dnamefromdept where deptno = ( select deptnofrom(select avg(sal) avg_sal,deptnofromemp group by deptno) t1  where avg_sal=    (select max(avg_sal)from(select avg(sal) avg_sal,deptnofromemp group by deptno) t2));

15.查询平均薪水的等级最低的部门名称

1.求平均薪水select avg(sal) avg_sal,deptnofromemp group by deptno;2.求平均薪水的等级select avg_sal,deptno,gradefrom(select avg(sal) avg_sal,deptnofromemp group by deptno) tjoin salgrade s on(t.avg_sal between s.losalandhisal);3.求平均薪水的等级最低的那个值select min(grade)from(  select avg_sal,deptno,gradefrom(select avg(sal) avg_sal,deptnofromemp group by deptno) t  join salgrade s on(t.avg_sal between s.losalandhisal)) t3;4.平均薪水的等级最低的部门的部门编号(将2和3组合起来，将2看成要查询的表，将3看成查询条件)select deptnofrom(  select avg_sal,deptno,gradefrom(select avg(sal) avg_sal,deptnofromemp group by deptno) t    join salgrade s on(t.avg_sal between s.losalandhisal)) t2where grade=(  select min(grade)from(  select avg_sal,deptno,gradefrom(select avg(sal) avg_sal,deptnofromemp group by deptno) t            join salgrade s on(t.avg_sal between s.losalandhisal)    ) t3);5.平均薪水的等级最低的部门名称select dname,deptnofromdept where deptnoin(  select deptnofrom(  select avg_sal,deptno,gradefrom(select avg(sal) avg_sal,deptnofromemp group by deptno) t        join salgrade s on(t.avg_sal between s.losalandhisal)    ) t2  where grade=      (  select min(grade)from(  select avg_sal,deptno,gradefrom(select avg(sal) avg_sal,deptnofromemp group by deptno) t                join salgrade s on(t.avg_sal between s.losalandhisal)      ) t3      ));

作者：foreknow

链接：https://www.jianshu.com/p/77f0c5c78921

来源：简书

简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。