已知2张基本表部门表dept 部门号部门名称;员工表 emp员工号员工姓名年龄入职时间收入部门号CREATE table dept(dept1 VARCHAR(6),dept_name VARCHAR(20)) default charsetutf8;INSERT into dept VALUES (101,财务);INSERT into dept VALUES (102,销售);INSERT into dept VALUES (103,IT技术);INSERT into dept VALUES (104,行政);CREATE table emp (sid VARCHAR(6),name VARCHAR(20),age TINYINT(2),woektime_start VARCHAR(10),incoming SMALLINT(10),dept2 VARCHAR(6))default charsetutf8;insert into emp VALUES (1789,张三,35,1980/1/1,4000,101);insert into emp VALUES (1674,李四,32,1983/4/1,3500,101);insert into emp VALUES (1776,王五,24,1990/7/1,2000,101);insert into emp VALUES (1789,十三,24,1990/8/1,2000,101);insert into emp VALUES (1980,马六,24,1990/7/1,1000,101);insert into emp VALUES (1568,赵六,57,1970/10/11,7500,102);insert into emp VALUES (1564,荣七,64,1963/10/11,8500,102);insert into emp VALUES (1777,十一,24,1990/7/1,2000,102);insert into emp VALUES (1778,十二,24,1990/7/1,1000,102);insert into emp VALUES (1879,牛八,55,1971/10/20,7300,103);insert into emp VALUES (1880,老九,55,1971/10/20,7500,105);insert into emp VALUES (1900,老十,64,1990/8/1,2000,106);drop table dept ;drop table emp ;select * from dept;select * from emp ;1.列出每个部门的平均收入及部门名称;结果dept_name,avg(incoming)条件group by dept_name语句select dept_name,avg(incoming) from dept left join emp on dept.dept1emp.dept2 group by dept_name2.财务部门的收入总和结果sumincoming条件dept_name“财务”语句select sum(incoming) from dept inner join emp on dept.dept1emp.dept2 where dept_name财务语句2select dept_name, sum(s.incoming) from (select * from dept inner join emp on dept.dept1 emp.dept2) as s where s.dept_name 财务;3.It技术部入职员工的员工号结果sid条件dept_name“ it技术”语句1select sid from dept inner join emp on dept.dept1emp.dept2 where dept_nameit技术语句2select sid from (select * from dept inner join emp on dept.dept1 emp.dept2) as s where s.dept_name IT技术;语句3select sid from emp where dept2(select dept1 from dept where dept_nameit技术)4.财务部门收入超过2000元的员工姓名结果 name条件dept_name“ 财务” incoming2000语句SELECT name from dept inner join emp on dept.dept1emp.dept2 where dept_name财务 and incoming2000;5.找出销售部收入最低的员工的入职时间结果woektime_start条件dept_name销售 minincoming语句1select *from deptleft join empon dept.dept1emp.dept2where dept_name销售 and incoming(select min(incoming) from deptleft join emp on dept.dept1emp.dept2 where dept_name销售)语句2select woektime_start from dept INNER JOIN emp on dept.dept1emp.dept2 where (dept_name,incoming) IN(select dept_name,min(incoming) from dept left join emp on dept.dept1emp.dept2 where dept_name销售)语句3有缺陷排序select woektime_start from dept INNER JOIN emp on dept.dept1emp.dept2 where dept_name销售 ORDER BY incoming asc LIMIT 0,1;6.找出年龄小于平均年龄的员工的姓名ID和部门名称 结果name、sid、dept_name条件avgage语句1select name,sid,dept_name from emp inner join dept on dept.dept1emp.dept2 where age (select avg(age) from emp);7.列出每个部门收入总和高于9000的部门名称结果dept_name条件group by dept_name having sum(imcoming) 9000语句select dept_name,sum(incoming) from dept inner join emp on dept.dept1emp.dept2 group by dept_name having sum(incoming) 9000;8.查出财务部门工资少于3800元的员工姓名结果name条件dept_name 财务 incoming3800结果select name from dept inner join emp on dept.dept1emp.dept2 where dept_name财务 and incoming 3800 ;9.求财务部门最低工资的员工姓名结果name条件maxincoming dept_name财务;语句1SELECT name from dept left JOIN emp on dept.dept1emp.dept2 where incoming(SELECT min(incoming) from dept LEFT JOIN emp on dept.dept1emp.dept2 where dept_name财务) and dept_name财务;语句2SELECT e.nameFROM dept dINNER JOIN emp e ON d.dept1 e.dept2WHERE d.dept_name 财务ORDER BY e.incoming ASC LIMIT 1;语句3select name from dept INNER JOIN emp on dept.dept1emp.dept2 where (dept_name,incoming) IN(select dept_name,min(incoming) from dept left join emp on dept.dept1emp.dept2 where dept_name财务);10.找出销售部门中年纪最大的员工的姓名结果name条件maxage dept_name销售;语句1SELECT name from dept LEFT JOIN emp on dept.dept1emp.dept2 where age(SELECT max(age) from dept LEFT JOIN emp on dept.dept1emp.dept2 where dept_name销售) and dept_name销售;语句2select name from dept INNER JOIN emp on dept.dept1emp.dept2 where (dept_name,age) IN(select dept_name,max(age) from dept left join emp on dept.dept1emp.dept2 where dept_name销售);方法311.求收入最低的员工姓名及所属部门名称结果name、dept_name条件minincoming语句1select name,dept_name from dept inner join emp on dept.dept1emp.dept2 where incoming(select min(incoming) from dept left join emp on dept.dept1emp.dept2 ) ;语句2select s.dept_name, s.name from (select * from dept inner join emp on dept.dept1 emp.dept2) as s order by s.incoming asc limit 1;12.求李四的收入及部门名称结果incoming 、dept_name条件name“ 李四”语句select incoming,dept_name from emp,dept where dept1dept2 and name李四;13.求员工收入小于4000元的员工部门编号及其部门名称结果siddept_name条件incoming4000语句select sid,dept_name from emp,dept where dept1dept2and incoming4000;语句2select sid,dept_name from dept inner join emp on dept.dept1 emp.dept2 where incoming4000;14.列出每个部门中收入最高的员工姓名部门名称收入并按照收入降序结果name、dept_name、incoming条件group by dept_name ,order by incoming desc语句select name,dept_name,incoming from dept INNER JOINemp on dept.dept1emp.dept2 where (dept_name,incoming) in (select dept_name,max(incoming) from dept INNER JOIN emp on dept.dept1emp.dept2 GROUP BY dept_name) ORDER BY incoming desc语句2select name,dept_name,incoming from ( select * from dept left join emp on dept.dept1emp.dept2 order by incoming desc) as a group by a.dept_name order by a.incoming desc15.求出财务部门收益最高的俩位员工的姓名工号收益结果name、sid、incoming条件dept_name“ 财务”order by incomig desc limit 2语句 select name,sid,incoming from dept inner join emp on dept.dept1emp.dept2 where dept_name财务 order by incoming desc limit 2;16.查询财务部低于全部员工平均收入的员工号与员工姓名结果:sid、name条件dept_name“财务” avgimcoming语句:select sid,name from dept inner join emp on dept.dept1emp.dept2 where dept_name财务 and incoming(select avg(incoming) from emp) ;17.列出部门员工数大于1个的部门名称结果 dept_name条件count(*)1语句select dept_namefrom deptleft join empon dept.dept1emp.dept2GROUP BY dept_nameHAVING count(*)118.列出部门员工收入不超过7500且大于3000的员工年纪及部门编号结果sid、age条件incoming7500 and incoming3000语句SELECT age,dept2 from dept RIGHT JOIN emp on dept.dept1emp.dept2 where incoming3000 and incoming750019.求入职于20世纪70年代的员工所属部门名称结果dept_name条件 1970-1979 或 like “197%”语句1SELECT dept_name from dept RIGHT JOIN emp on dept.dept1emp.dept2 where woektime_start BETWEEN 1970 and 1979语句2select dept_name from dept INNER JOIN emp on dept.dept1emp.dept2 where woektime_start like 197% ;20.查找张三所在的部门名称结果dept_name条件name“张三”语句select dept_name from dept,emp where dept2dept1 and name‘张三’;语句2select dept_name from emp INNER JOIN emp ON dept.dept1 emp.dept2 WHERE name 张三21.列出每一个部门中年纪最大的员工姓名部门名称结果 name、dept_name条件 group by dept_name max(age)语句select name,dept_name from dept left join emp on dept.dept1emp.dept2 where (dept2,age ) in (select dept2,max(age)from emp group by dept2);语句select dept_name,namefrom(select *from deptleft join empon dept.dept1emp.dept2ORDER BY age desc )as aGROUP BY dept_name22.列出每一个部门的员工总收入及部门名称结果sumincomingdept_name条件group by dept _name语句:select dept_name,sum(incoming) from emp left join dept on dept.dept1emp.dept2 group by dept_name23.列出部门员工收入大于7000的员工号部门名称结果sid 、dept_name条件incoming7000结果select sid,dept_name from dept right join emp on dept.dept1emp.dept2 where incoming700024.找出哪个部门还没有员工入职结果dept_name条件 is null语句SELECT dept_name FROM dept left JOIN emp on dept.dept1emp.dept2WHERE emp.name is null;25.先按部门号大小排序再依据入职时间由早到晚排序员工信息表 结果*条件 dept2 desc , woektime_strt asc语句select * from emp order by dept2 asc, worktime_start asc;26.求出财务部门工资最高员工的姓名和员工号结果name、sid条件dept_name 财务 maxincoming语句1select sid,name from dept inner join emp on dept.dept1emp.dept2 where dept_name“财务” order by incoming desc limit 1 ;语句2select name,sidfrom deptleft join empon dept.dept1emp.dept2where dept_name财务and incoming(select max(incoming) from deptleft join empon dept.dept1emp.dept2where dept_name财务)27.求出工资在7500到8500之间年龄最大的员工的姓名和部门名称。结果name,dept_name条件incoming BETWEEN 7500 and 8500语句SELECT name,dept_name from dept right jOIN emp on dept.dept1emp.dept2 where age(SELECT MAX(age) from dept LEFT JOIN emp on dept.dept1emp.dept2 WHERE incoming BETWEEN 7500 and 8500) and incoming BETWEEN 7500 and 8500;
mysql多表练习
已知2张基本表部门表dept 部门号部门名称;员工表 emp员工号员工姓名年龄入职时间收入部门号CREATE table dept(dept1 VARCHAR(6),dept_name VARCHAR(20)) default charsetutf8;INSERT into dept VALUES (101,财务);INSERT into dept VALUES (102,销售);INSERT into dept VALUES (103,IT技术);INSERT into dept VALUES (104,行政);CREATE table emp (sid VARCHAR(6),name VARCHAR(20),age TINYINT(2),woektime_start VARCHAR(10),incoming SMALLINT(10),dept2 VARCHAR(6))default charsetutf8;insert into emp VALUES (1789,张三,35,1980/1/1,4000,101);insert into emp VALUES (1674,李四,32,1983/4/1,3500,101);insert into emp VALUES (1776,王五,24,1990/7/1,2000,101);insert into emp VALUES (1789,十三,24,1990/8/1,2000,101);insert into emp VALUES (1980,马六,24,1990/7/1,1000,101);insert into emp VALUES (1568,赵六,57,1970/10/11,7500,102);insert into emp VALUES (1564,荣七,64,1963/10/11,8500,102);insert into emp VALUES (1777,十一,24,1990/7/1,2000,102);insert into emp VALUES (1778,十二,24,1990/7/1,1000,102);insert into emp VALUES (1879,牛八,55,1971/10/20,7300,103);insert into emp VALUES (1880,老九,55,1971/10/20,7500,105);insert into emp VALUES (1900,老十,64,1990/8/1,2000,106);drop table dept ;drop table emp ;select * from dept;select * from emp ;1.列出每个部门的平均收入及部门名称;结果dept_name,avg(incoming)条件group by dept_name语句select dept_name,avg(incoming) from dept left join emp on dept.dept1emp.dept2 group by dept_name2.财务部门的收入总和结果sumincoming条件dept_name“财务”语句select sum(incoming) from dept inner join emp on dept.dept1emp.dept2 where dept_name财务语句2select dept_name, sum(s.incoming) from (select * from dept inner join emp on dept.dept1 emp.dept2) as s where s.dept_name 财务;3.It技术部入职员工的员工号结果sid条件dept_name“ it技术”语句1select sid from dept inner join emp on dept.dept1emp.dept2 where dept_nameit技术语句2select sid from (select * from dept inner join emp on dept.dept1 emp.dept2) as s where s.dept_name IT技术;语句3select sid from emp where dept2(select dept1 from dept where dept_nameit技术)4.财务部门收入超过2000元的员工姓名结果 name条件dept_name“ 财务” incoming2000语句SELECT name from dept inner join emp on dept.dept1emp.dept2 where dept_name财务 and incoming2000;5.找出销售部收入最低的员工的入职时间结果woektime_start条件dept_name销售 minincoming语句1select *from deptleft join empon dept.dept1emp.dept2where dept_name销售 and incoming(select min(incoming) from deptleft join emp on dept.dept1emp.dept2 where dept_name销售)语句2select woektime_start from dept INNER JOIN emp on dept.dept1emp.dept2 where (dept_name,incoming) IN(select dept_name,min(incoming) from dept left join emp on dept.dept1emp.dept2 where dept_name销售)语句3有缺陷排序select woektime_start from dept INNER JOIN emp on dept.dept1emp.dept2 where dept_name销售 ORDER BY incoming asc LIMIT 0,1;6.找出年龄小于平均年龄的员工的姓名ID和部门名称 结果name、sid、dept_name条件avgage语句1select name,sid,dept_name from emp inner join dept on dept.dept1emp.dept2 where age (select avg(age) from emp);7.列出每个部门收入总和高于9000的部门名称结果dept_name条件group by dept_name having sum(imcoming) 9000语句select dept_name,sum(incoming) from dept inner join emp on dept.dept1emp.dept2 group by dept_name having sum(incoming) 9000;8.查出财务部门工资少于3800元的员工姓名结果name条件dept_name 财务 incoming3800结果select name from dept inner join emp on dept.dept1emp.dept2 where dept_name财务 and incoming 3800 ;9.求财务部门最低工资的员工姓名结果name条件maxincoming dept_name财务;语句1SELECT name from dept left JOIN emp on dept.dept1emp.dept2 where incoming(SELECT min(incoming) from dept LEFT JOIN emp on dept.dept1emp.dept2 where dept_name财务) and dept_name财务;语句2SELECT e.nameFROM dept dINNER JOIN emp e ON d.dept1 e.dept2WHERE d.dept_name 财务ORDER BY e.incoming ASC LIMIT 1;语句3select name from dept INNER JOIN emp on dept.dept1emp.dept2 where (dept_name,incoming) IN(select dept_name,min(incoming) from dept left join emp on dept.dept1emp.dept2 where dept_name财务);10.找出销售部门中年纪最大的员工的姓名结果name条件maxage dept_name销售;语句1SELECT name from dept LEFT JOIN emp on dept.dept1emp.dept2 where age(SELECT max(age) from dept LEFT JOIN emp on dept.dept1emp.dept2 where dept_name销售) and dept_name销售;语句2select name from dept INNER JOIN emp on dept.dept1emp.dept2 where (dept_name,age) IN(select dept_name,max(age) from dept left join emp on dept.dept1emp.dept2 where dept_name销售);方法311.求收入最低的员工姓名及所属部门名称结果name、dept_name条件minincoming语句1select name,dept_name from dept inner join emp on dept.dept1emp.dept2 where incoming(select min(incoming) from dept left join emp on dept.dept1emp.dept2 ) ;语句2select s.dept_name, s.name from (select * from dept inner join emp on dept.dept1 emp.dept2) as s order by s.incoming asc limit 1;12.求李四的收入及部门名称结果incoming 、dept_name条件name“ 李四”语句select incoming,dept_name from emp,dept where dept1dept2 and name李四;13.求员工收入小于4000元的员工部门编号及其部门名称结果siddept_name条件incoming4000语句select sid,dept_name from emp,dept where dept1dept2and incoming4000;语句2select sid,dept_name from dept inner join emp on dept.dept1 emp.dept2 where incoming4000;14.列出每个部门中收入最高的员工姓名部门名称收入并按照收入降序结果name、dept_name、incoming条件group by dept_name ,order by incoming desc语句select name,dept_name,incoming from dept INNER JOINemp on dept.dept1emp.dept2 where (dept_name,incoming) in (select dept_name,max(incoming) from dept INNER JOIN emp on dept.dept1emp.dept2 GROUP BY dept_name) ORDER BY incoming desc语句2select name,dept_name,incoming from ( select * from dept left join emp on dept.dept1emp.dept2 order by incoming desc) as a group by a.dept_name order by a.incoming desc15.求出财务部门收益最高的俩位员工的姓名工号收益结果name、sid、incoming条件dept_name“ 财务”order by incomig desc limit 2语句 select name,sid,incoming from dept inner join emp on dept.dept1emp.dept2 where dept_name财务 order by incoming desc limit 2;16.查询财务部低于全部员工平均收入的员工号与员工姓名结果:sid、name条件dept_name“财务” avgimcoming语句:select sid,name from dept inner join emp on dept.dept1emp.dept2 where dept_name财务 and incoming(select avg(incoming) from emp) ;17.列出部门员工数大于1个的部门名称结果 dept_name条件count(*)1语句select dept_namefrom deptleft join empon dept.dept1emp.dept2GROUP BY dept_nameHAVING count(*)118.列出部门员工收入不超过7500且大于3000的员工年纪及部门编号结果sid、age条件incoming7500 and incoming3000语句SELECT age,dept2 from dept RIGHT JOIN emp on dept.dept1emp.dept2 where incoming3000 and incoming750019.求入职于20世纪70年代的员工所属部门名称结果dept_name条件 1970-1979 或 like “197%”语句1SELECT dept_name from dept RIGHT JOIN emp on dept.dept1emp.dept2 where woektime_start BETWEEN 1970 and 1979语句2select dept_name from dept INNER JOIN emp on dept.dept1emp.dept2 where woektime_start like 197% ;20.查找张三所在的部门名称结果dept_name条件name“张三”语句select dept_name from dept,emp where dept2dept1 and name‘张三’;语句2select dept_name from emp INNER JOIN emp ON dept.dept1 emp.dept2 WHERE name 张三21.列出每一个部门中年纪最大的员工姓名部门名称结果 name、dept_name条件 group by dept_name max(age)语句select name,dept_name from dept left join emp on dept.dept1emp.dept2 where (dept2,age ) in (select dept2,max(age)from emp group by dept2);语句select dept_name,namefrom(select *from deptleft join empon dept.dept1emp.dept2ORDER BY age desc )as aGROUP BY dept_name22.列出每一个部门的员工总收入及部门名称结果sumincomingdept_name条件group by dept _name语句:select dept_name,sum(incoming) from emp left join dept on dept.dept1emp.dept2 group by dept_name23.列出部门员工收入大于7000的员工号部门名称结果sid 、dept_name条件incoming7000结果select sid,dept_name from dept right join emp on dept.dept1emp.dept2 where incoming700024.找出哪个部门还没有员工入职结果dept_name条件 is null语句SELECT dept_name FROM dept left JOIN emp on dept.dept1emp.dept2WHERE emp.name is null;25.先按部门号大小排序再依据入职时间由早到晚排序员工信息表 结果*条件 dept2 desc , woektime_strt asc语句select * from emp order by dept2 asc, worktime_start asc;26.求出财务部门工资最高员工的姓名和员工号结果name、sid条件dept_name 财务 maxincoming语句1select sid,name from dept inner join emp on dept.dept1emp.dept2 where dept_name“财务” order by incoming desc limit 1 ;语句2select name,sidfrom deptleft join empon dept.dept1emp.dept2where dept_name财务and incoming(select max(incoming) from deptleft join empon dept.dept1emp.dept2where dept_name财务)27.求出工资在7500到8500之间年龄最大的员工的姓名和部门名称。结果name,dept_name条件incoming BETWEEN 7500 and 8500语句SELECT name,dept_name from dept right jOIN emp on dept.dept1emp.dept2 where age(SELECT MAX(age) from dept LEFT JOIN emp on dept.dept1emp.dept2 WHERE incoming BETWEEN 7500 and 8500) and incoming BETWEEN 7500 and 8500;