已知2张基本表:部门表:dept (部门号,部门名称);员工表 emp(员工号,员工姓名,年龄,入职时间,收入,部门号)
CREATE table dept(dept1 VARCHAR(6),dept_name VARCHAR(20)) default charset=utf8;
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 charset=utf8;
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 ('1568','赵六',57,'1970/10/11',7500,'102');
insert into emp VALUES ('1564','荣七',64,'1963/10/11',8500,'102');
insert into emp VALUES ('1879','牛八',55,'1971/10/20',7300,'103');
insert into emp VALUES ('1880','老九',55,'1971/10/20',8000,'105');
drop table dept ;
drop table emp ;
select * from dept;
select * from emp ;
1.列出每个部门的平均收入及部门名称;
结果:avg(incomming),dept_name
条件:group by
方法:
SELECT dept_name,avg(incoming) from dept RIGHT JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name;
2.财务部门的收入总和;
结果:sum(incoming)
条件:dept_name="财务"
select sum(incoming) from dept INNER JOIN emp on dept.dept1=emp.dept2 where dept_name="财务
3.It技术部入职员工的员工号
结果:sid
条件:dept_name="IT技术"
方法:SELECT sid FROM dept INNER JOIN emp on dept.dept1=emp.dept2 where dept_name='IT技术' ;
4.财务部门收入超过2000元的员工姓名
结果:name
条件: incoming>2000 dept_name="财务"
方法:SELECT name from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务" and incoming>2000;
5.找出销售部收入最低的员工的入职时间;
结果:worktime
条件: dept_name=" 销售" ,min(incoming)
方法:
SELECT worktime_start FROM dept inner JOIN emp on dept.dept1=emp.dept2 WHERE dept_name='销售' AND incoming=(SELECT min(incoming) FROM dept inner JOIN emp on dept.dept1=emp.dept2 WHERE dept_name='销售') ;
方法2:
select emp.worktime_start from dept join emp on dept.dept1 = emp.dept2 where dept.dept_name = "销售" ORDER BY emp.incoming LIMIT 1;
6.找出年龄小于平均年龄的员工的姓名,ID和部门名称
结果:name,sid,dept_name
条件:<(select avg(age) from emp )
语句:
SELECT emp.name,emp.sid,dept.dept_name from emp LEFT JOIN dept on dept.dept1 = emp.dept2 WHERE
emp.age < (SELECT avg(emp.age) from emp) ;
7.列出每个部门收入总和高于9000的部门名称
结果:dept_name
条件:sum(incoming)>9000 ,group by
方法:
select dept_name,sum(incoming) from dept left join emp on dept.dept1=emp.dept2 group by dept_name having sum(incoming)>9000;
8.查出财务部门工资少于3800元的员工姓名
结果:name
条件: dept_name=”财务" ,incoming<3800
方法:
select name from dept INNER JOIN emp on dept.dept1=emp.dept2 where dept_name="财务" AND incoming<3800;
9.求财务部门最低工资的员工姓名;
结果:name
条件:min( incoming),dept_name="财务"
方法:
SELECT name from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务" and incoming=(SELECT min(incoming) from dept INNER JOIN emp on dept.dept1=emp.dept2 where dept_name="财务" );
10.找出销售部门中年纪最大的员工的姓名
结果:name
条件:dept_name=" 销售" ,max(age)
方法:(缺陷)
select emp.name from dept join emp on dept.dept1 = emp.dept2 where dept.dept_name = "销售" ORDER BY emp.age desc LIMIT 1;
方法2:
SELECT name FROM dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name='销售' AND age=(SELECT min(age) FROM dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name='销售')
11.求收入最低的员工姓名及所属部门名称:
结果: name,dept_name
条件: min(incoming)
方法:SELECT NAME,dept_name from dept left join emp on dept.dept1=emp.dept2 WHERE incoming=
(SELECT MIN(incoming) FROM dept left join emp on dept.dept1=emp.dept2 );
方法2:
SELECT name,dept_name from dept RIGHT JOIN emp on dept.dept1=emp.dept2 WHERE incoming=(SELECT min(incoming) FROM emp);
方法3:
select emp.name,dept.dept_name from dept join emp on dept.dept1 = emp.dept2 ORDER BY emp.incoming LIMIT 1;
12.求李四的收入及部门名称
结果:incoming,dept_name
条件:name= '李四'
方法:select name,incoming,dept_name from dept join emp on dept.dept1 = emp.dept2 where name = "李四";
13.求员工收入小于4000元的员工部门编号及其部门名称
结果:
sid 、detp_name
条件:incoming<4000
方法:
select DISTINCT(dept.dept1),dept.dept_name from dept join emp on dept.dept1 = emp.dept2 where emp.incoming < 4000;
14.列出每个部门中收入最高的员工姓名,部门名称,收入,并按照收入降序;
结果: name,dept_name,incoming
条件 :group by dept_name ,max(incoming) incoming desc
语句:
SELECT name,dept_name,incoming from dept INNER JOIN emp on dept.dept1=emp.dept2 where (dept1,incoming) in (
SELECT dept1,max(incoming) from dept INNER JOIN emp on dept.dept1=emp.dept2
group by dept1 ) ORDER BY incoming desc;
15.求出财务部门收益最高的俩位员工的姓名,工号,收益
结果:name 、sid 、incoming
条件:dept_name="财务" order by desc limit 0,2
语句:
SELECT name,sid,incoming from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务" ORDER BY incoming desc LIMIT 0,2;
16.查询财务部低于平均收入的员工号与员工姓名:(低于所有平均工资)
结果:sid 、name
条件: dept_name="财务" avg(incoming)
方法:
select emp.name,emp.sid from dept join emp on dept.dept1 = emp.dept2 where dept.dept_name = "财务"
and emp.incoming < (
select avg(emp.incoming) from dept join emp on dept.dept1 = emp.dept2
);
17.列出部门员工数大于1个的部门名称;
结果: dept_name
条件: count(name)>1 group by dept_name
方法:SELECT dept_name,count(NAME) FROM dept INNER JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name HAVING count(name)>1 ;
18.列出部门员工收入不超过7500,且大于3000的员工年纪及部门编号;
结果“:age,detp_name
条件:incoming<7500,incoming>3000
语句:
select age,dept1 from emp left join dept on emp.dept2=dept.dept1
where incoming <=7500 and incoming>3000;
19.求入职于20世纪70年代的员工所属部门名称;
结果: dept_name
条件:woek_time like "197%"
语句:select dept1 from dept INNER JOIN emp on dept.dept1=emp.dept2 where worktime_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
语句:
SELECT name,dept_name from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE (dept_name,age) in (SELECT dept_name,max(age) from dept dept INNER JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name);
22.列出每一个部门的员工总收入及部门名称;
结果: dept_name ,sum(incoming)
条件: select dept_name,sum(incoming) from dept INNER JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name;
23.列出部门员工收入大于7000的员工号,部门名称;
结果: dept_name ,sid
条件:,incoming>7000
语句:SELECT emp.sid,dept.dept_name from emp LEFT JOIN dept ON dept.dept1 = emp.dept2 WHERE emp.incoming > 7000;
24.找出哪个部门还没有员工入职;
结果: dept_name
条件 is null
语句:
SELECT dept.dept_name from dept LEFT JOIN emp ON dept.dept1 = emp.dept2 WHERE emp.sid is null;
语句2:
select dept.dept_name from dept left join emp on dept.dept1 = emp.dept2 GROUP BY dept.dept_name having count(emp.name) = 0;
25.先按部门号大小排序,再依据入职时间由早到晚排序员工信息表 ;
结果:*
条件:oder by dept1 desc,oder by woektime asc
语句:
select * from dept INNER JOIN emp on dept.dept1=emp.dept2 ORDER BY dept1 desc,worktime_start ASC ;
26.求出财务部门工资最高员工的姓名和员工号
结果:name 、sid
条件:dept_name="财务"
语句:
SELECT name,sid from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务" and incoming=(SELECT max(incoming) FROM dept LEFT JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务");
27.求出工资在7500到8500之间,年龄最大的员工的姓名和部门名称。
结果: name、dept_name
条件:BETWEEN 7500 AND 8500,max(age)
语句:
select name,dept_name,incoming from dept INNER JOIN emp on dept.dept1=emp.dept2 where incoming between 7500 and 8500 and age=(select max(age) from emp where incoming between 7500 and 8500);