数据库表格的使用

已知2张基本表：部门表：dept （部门号，部门名称）;员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号）
1：dept表中有4条记录：
     部门号(dept1)  部门名称(dept_name )
     101     财务            
     102     销售            
     103     IT技术         
     104     行政            
2：emp表中有6条记录：
      员工号 员工姓名 年龄 入职时间 收入 部门号对应字段名称为： (sid name age worktime_start incoming dept2)
      1789    张三 35 1980/1/1 4000 101
      1674    李四 32 1983/4/1 3500 101
      1776    王五 24 1990/7/1 2000 101
      1568    赵六 57 1970/10/11 7500 102
      1564    荣七 64 1963/10/11 8500 102
      1879    牛八 55 1971/10/20 7300 103

1.列出每个部门的平均收入及部门名称; 

2.财务部门的收入总和； 
3.It技术部入职员工的员工号  
4.财务部门收入超过2000元的员工姓名 
emp   incoming>2000
dept  财务

5.找出销售部收入最低的员工的入职时间；
emp  
dept
max  
6.找出年龄小于平均年龄的员工的姓名，ID和部门名称  
7.列出每个部门收入总和高于9000的部门名称  
8.查出财务部门工资少于3800元的员工姓名 
财务  dept
 incoming  emp

name
<
9.求财务部门最低工资的员工姓名；
min   
dept  dept_name
emp  incoming  min
10.找出销售部门中年纪最大的员工的姓名   
11.求收入最低的员工姓名及所属部门名称：
12.求李四的收入及部门名称

13.求员工收入小于4000元的员工部门编号及其部门名称

14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；

15.求出财务部门收益最高的俩位员工的姓名，工号，收益

16.查询财务部低于平均收入的员工号与员工姓名：
17.列出部门员工数大于1个的部门名称； 

18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；

19.求入职于20世纪70年代的员工所属部门名称；

20.查找张三所在的部门名称；

21.列出每一个部门中年纪最大的员工姓名，部门名称；
22.列出每一个部门的员工总收入及部门名称；

23.列出部门员工收入大于7000的员工号，部门名称；

24.找出哪个部门还没有员工入职；

25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；

26.求出财务部门工资最高员工的姓名和员工号
27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称。
 

1.列出每个部门的平均收入及部门名称;

结果：dept_name dept表,avg(incoming) emp 表

条件：group  by  dept_name

方法：

SELECT dept_name,avg(incoming) from dept  left JOIN emp  on  dept.dept1=emp.dept2 group by dept_name;

从左链接里找部门名和平均收入，然后每组是group by

2.财务部门的收入总和； 

结果：sum（imcoming） emp；

条件：dept_name="财务"

语句：

方法1

SELECT sum(incoming) from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务";

从内连接表找。加上条件财务

方法2：

select sum(incoming) from(select * from dept INNER JOIN emp on dept.dept1=emp.dept2)a where dept_name="财务";

从内连接里的数据里找，加上条件财务

方法3：

SELECT SUM(incoming) FROM emp WHERE dept2=(SELECT dept1 FROM dept WHERE dept_name='财务');

从右边表里找，让右边序号等于左边财务那行的序号

3.It技术部入职员工的员工号  

结果：sid

条件：dept_name=" IT技术"

语句：

select sid from dept left JOIN emp on dept.dept1=emp.dept2 where dept_name="IT技术";

4.财务部门收入超过2000元的员工姓名

结果：name  emp表

条件：dept_name="财务" ，incoming>2000

语句：

方法1：

select name from dept INNER JOIN emp on dept.dept1=emp.dept2 where dept_name="财务" and incoming>2000

两个条件直接在内连接表搜

方法2：

select name from emp where incoming>2000 and dept2=(select dept1 from dept where dept_name="财务");

一个条件固定，另一个条件用序号连接左右两边

5.找出销售部收入最低的员工的入职时间；

结果：woektime_start

条件：dept_name=" 销售"   min（incoming） 或order  by   asc   

语句：

SELECT woektime_start FROM emp WHERE (dept2,incoming) in (SELECT dept1,MIN(incoming) FROM emp LEFT JOIN dept ON dept1=dept2 where  dept_name='销售');

从右边表找。条件让两个条件右边的等于左边（名字是销售的左连接）。

方法2：

 SELECT woektime_start from dept a INNER JOIN emp b on a.dept1=b.dept2 WHERE (dept2,incoming) in (SELECT dept2,min(incoming) FROM emp GROUP BY dept2) and dept_name='销售';

直接从内连接找。条件两个，一个是右边每个序号下最小收入那个，一个是部门。

  方法3：

select woektime_start from dept right join emp on dept.dept1=emp.dept2 where incoming=(select min(incoming) from dept inner join emp on dept.dept1=emp.dept2 and dept2=(select dept1 from dept where dept_name='销售')) AND dept_name='销售';

从右连接里找。条件是收入和部门。体现收入：从内连接来找最低，并且右边序号等于左边对应部门的序号。

6.找出年龄小于平均年龄的员工的姓名，ID和部门名称  

avg(age)>age(emp)

select name,sid,dept from dept inner join emp on dept.dept1=emp.dept2 where age<(select avg(age) from emp);

结果：name(emp)、sid(emp)、dept_name(dept)

条件：avg（age）

语句：

方法1：

SELECT emp.name,emp.sid,dept.dept_name from emp left join dept on emp.dept2 = dept.dept1 where emp.age < (SELECT avg(age) from emp)

7.列出每个部门收入总和高于9000的部门名称  

结果：dept_name

条件：group  by  having   sum(imcoming)

语句：

SELECT dept.dept_name from(SELECT sum(incoming) as s,dept2 as d  from emp GROUP BY dept2)as a left join dept on a.d = dept.dept1 where  a.s>9000;

把工资总额作为S，右边序号作为D，把根据右边序号排序后查到的工资总额和序号作为A

从左连接找左表的部门名

方法二：

select dept_name from (select sum(incoming), dept_name from dept inner join emp on dept.dept1=emp.dept2 group by dept_name having sum(incoming)>9000)a;

从内连接搜最大收入和部门每组 后边having跟条件

8.查出财务部门工资少于3800元的员工姓名

结果：姓名 emp

条件：dept_name=""财务   incomig小于3800

语句：

方法1

SELECT name from dept a INNER JOIN emp b on a.dept1=b.dept2 where incoming<3800 and dept_name='财务';

方法2：

select name from  emp  where incoming<3800 and dept2=(

select  dept1 from  dept  where dept_name="财务")

9.求财务部门最低工资的员工姓名；

结果：name

条件：dept_name="财务"  ，min（incoming）

语句：

select name from dept inner join emp on dept.dept1=emp.dept2 where incoming=(select min(incoming) from dept right join emp on dept.dept1=emp.dept2 where dept_name="财务") and dept_name="财务";

10.找出销售部门中年纪最大的员工的姓名   

结果：name

条件：dept_name='销售'     ，max（age）

语句：

方法1：

SELECT name from emp where (age,dept2) = (SELECT max(emp.age),dept1 from emp join dept on emp.dept2 = dept.dept1 where dept_name = '销售');

方法2：

select name from dept inner join emp on dept.dept1=emp.dept2 where age=(select max(age) from dept right join emp on dept.dept1=emp.dept2 where dept_name="销售") and dept_name="销售";

11.求收入最低的员工姓名及所属部门名称：

结果：name  ，dept_name

条件：min(incoming)

语句：

方法1：

SELECT name,dept_name from dept a INNER JOIN emp b on a.dept1=b.dept2 where incoming=(select min(incoming) from dept a INNER JOIN emp b on a.dept1=b.dept2);

方法2：

select name,dept_name from dept a INNER JOIN emp b on a.dept1=b.dept2 where incoming=(SELECT min(incoming) from emp);

12.求李四的收入及部门名称

结果：incoming ，dept_name

条件：name="李四"

语句:

select incoming,dept_name from dept INNER JOIN emp on dept.dept1=emp.dept2 where name="李四"

13.求员工收入小于4000元的员工部门编号及其部门名称

结果: dept2、dept_name   (dept表)

条件： incoming<4000

语句：

select dept_name,dept1 from dept inner join emp on dept.dept1=emp.dept2 where incoming<4000 ;

方法二：

select DISTINCT(dept_name),dept1 from dept where  dept1 in(

select  dept2  from  emp  where  incoming<4000)

14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；

结果：name    ，dept_name,incoming ,

条件：group  by  dept_name    ,max(incoming)  ,order  by    incoming

语句：

方法1：

SELECT dept.dept_name,emp.name,emp.incoming from emp LEFT JOIN dept on emp.dept2 = dept.dept1 where (dept2,incoming) in (SELECT dept2,max(incoming) from emp GROUP BY dept2) ORDER BY emp.incoming desc

15.求出财务部门收益最高的俩位员工的姓名，工号，收益

结果：name、 sid 、incoming

条件：dept_name , limit     order  by    desc

语句：

SELECT incoming as s,name ,sid from emp join dept on emp.dept2 = dept.dept1 where dept.dept_name = '财务' ORDER BY incoming desc limit 2;

16.查询财务部低于平均收入的员工号与员工姓名：

结果：sid，name

条件：dept_name=" 财务" ， 小于avg（incoming）

语句：

方法1:

SELECT sid,name from dept a INNER JOIN emp b on a.dept1=b.dept2 where incoming<(SELECT avg(incoming) from emp) and dept_name='财务';

方法2：

SELECT sid,name from emp,(SELECT avg(emp.incoming) as s,dept1 as d from emp join dept on emp.dept2 = dept.dept1 where dept.dept_name = '财务') as a where incoming < a.s and dept2 = a.d;

17.列出部门员工数大于1个的部门名称；

结果：detp_name

条件：conut(*)>1

语句:

方法1

select dept_name from(select count(*) ,dept_name from dept inner join emp on dept.dept1=emp.dept2 group by dept_name having count(*)>1)a;

方法2：

select dept_name from dept where dept1 in  (select dept2 from emp group by dept2 having count(*)>1)

方法3：

select dept_name from (select dept_name,count(*) a from dept a INNER JOIN emp b on a.dept1=b.dept2 GROUP BY dept1) b where a>1;

方法4:select dept_name from dept  left JOIN emp on dept.dept1=emp.dept2 group by dept_name having  count(*)>1

方法5：

SELECT dept_name from (SELECT COUNT(*) as s,dept2 as d from emp GROUP BY dept2) as a left join dept on a.d = dept.dept1 where  a.s >1;

18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；

结果：age、dept2  （emp）

条件：

方法1：

seLECT age,dept2 from emp where incoming<7500 and incoming>3000;

19.求入职于20世纪70年代的员工所属部门名称；

结果：dept_name

条件：work_timestart

语句：

方法1：

select dept_name from dept inner join emp on dept.dept1=emp.dept2 where woektime_start>=1970 and woektime_start<1980;

方法2：

select dept.dept_name from emp left join dept on emp.dept2 = dept.dept1 where woektime_start like '197%';

20.查找张三所在的部门名称；

结果：dept_name

条件：name=' 张三'

语句：SELECT dept_name from  dept  INNER JOIN emp  on  dept.dept1=emp.dept2 where name='张三';

21.列出每一个部门中年纪最大的员工姓名，部门名称；

结果：name 、dept_name

条件： group  by   dept_name     max(age) 

语句：

方法1

select dept_name,name from dept left join  emp on dept.dept1=emp.dept2 where (dept_name,age ) in  (select dept_name ,max(age) from dept left join   emp on dept.dept1=emp.dept2 GROUP BY dept_name);

方法2：

SELECT name,dept_name from emp left join dept on emp.dept2 = dept.dept1 where (age,dept1) in  (SELECT max(age),dept2 from emp GROUP BY dept2)

22.列出每一个部门的员工总收入及部门名称；

结果：sum（incomg），dept_name

条件：group  by   

语句：

方法1：

SELECT sum(incoming),dept_name from dept  left JOIN emp  on  dept.dept1=emp.dept2 GROUP BY dept1;

方法2：

SELECT dept.dept_name,sum(emp.incoming) from emp left join dept on emp.dept2 = dept.dept1 GROUP BY emp.dept2

UNION

SELECT dept.dept_name,sum(emp.incoming) from emp right  join dept on emp.dept2 = dept.dept1 GROUP BY emp.dept2

23.列出部门员工收入大于7000的员工号，部门名称；

结果：sid、dept_name

条件：incoming>7000

语句：

select sid,dept_name from  dept  INNER JOIN emp on dept.dept1=emp.dept2 where incoming>7000

24.找出哪个部门还没有员工入职；

结果：dept_name

条件：name  is  null

语句：

方法1：

select dept1,dept_name from dept  left JOIN emp  on  dept.dept1=emp.dept2 where sid is null

25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；

二次排序：desc    asc

结果：*

条件：order by   dept2  ，order  by  werk_timestrt asc

语句：

SELECT * from dept  INNER JOIN emp  on  dept.dept1=emp.dept2 ORDER BY dept1 desc,woektime_start asc;

26.求出财务部门工资最高员工的姓名和员工号

结果：name、sid

条件：dept_name=" 财务"     max（incoming）

语句：

方法1：SELECT name,sid from emp where (dept2,incoming) = (SELECT dept1,max(incoming) from dept  INNER JOIN emp  on  dept.dept1=emp.dept2 where dept_name='财务')

逻辑：先找部门是财务时左边序号，最高收入 从内连接的表里找，

再找员工号和姓名，条件是右边的序号工资对等

方法2： 

select sid,name from dept inner join emp on dept.dept1=emp.dept2 where incoming=(select max(incoming) from dept inner join emp on dept.dept1=emp.dept2 where dept_name="财务") and dept_name="财务";

逻辑：从内连接表里找这两个数据，条件是符合条件的工资和部门财务

符合条件的工资是部门是财务时从内连接查到的最大收入

27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称。

结果：name、dept_name

条件:incoming<=7500 and 8500>=incoming 或 between 7500 and 8500

语句：

方法1：

SELECT name,dept_name from dept  INNER JOIN emp  on  dept.dept1=emp.dept2 where incoming>=7500 and incoming<=8500 and age=(SELECT max(age) from emp  where incoming>=7500 and incoming<=8500);

逻辑：先找条件工资下的最大年龄SELECT max(age) from emp  where incoming>=7500 and incoming<=8500

再从两个表的内连接里面找名字和部门名，并列条件是工资和年龄