dcsxlh

导航

 

已知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);

posted on 2025-04-29 15:01  多测师_肖sir  阅读(32)  评论(0)    收藏  举报