MySQL（06）——SQL练习题目（部门员工表）

Fenggms

于 2018-10-12 23:33:44 发布

阅读量3w

点赞数 22

分类专栏：数据库

本文链接：https://blog.csdn.net/Fenggms/article/details/83034175

版权

数据库专栏收录该内容

20 篇文章

订阅专栏

数据：
把该员工表作为一个自关联表。

-- 部门表
create table dept(
	deptno int primary key auto_increment, -- 部门编号
	dname varchar(14) ,	  -- 部门名字
	loc varchar(13)   -- 地址
) ;
-- 员工表
create table emp(
	empno int primary key auto_increment,-- 员工编号
	ename varchar(10), -- 员工姓名										-
	job varchar(9),	-- 岗位
	mgr int,	 -- 直接领导编号
	hiredate date, -- 雇佣日期，入职日期
	sal int, -- 薪水
	comm int,  -- 提成
	deptno int not null, -- 部门编号
	foreign key (deptno) references dept(deptno)
);
insert into dept values(10,'财务部','北京');
insert into dept values(20,'研发部','上海');
insert into dept values(30,'销售部','广州');
insert into dept values(40,'行政部','深圳');
insert into emp values(7369,'刘一','职员',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);
insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'郭十一','职员',7788,'1987-06-13',1100,null,20);
insert into emp values(7900,'钱多多','职员',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'大锦鲤','分析师',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'木有钱','职员',7782,'1983-01-23',1300,null,10);

练习题目：

-- 1．列出至少有一个员工的所有部门。

-- 2．列出薪金比"刘一"多的所有员工。

-- 3．***** 列出所有员工的姓名及其直接上级的姓名。

-- 4．列出受雇日期早于其直接上级的所有员工。

-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

-- 6．列出所有job为“职员”的姓名及其部门名称。

-- 7．列出最低薪金大于1500的各种工作。

-- 8．列出在部门 "销售部" 工作的员工的姓名，假定不知道销售部的部门编号。

-- 9．列出薪金高于公司平均薪金的所有员工。

-- 10．列出与"周八"从事相同工作的所有员工。

-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

-- 13．列出在每个部门工作的员工数量、平均工资。

-- 14．列出所有员工的姓名、部门名称和工资。

-- 15．列出所有部门的详细信息和部门人数。

-- 16．列出各种工作的最低工资。

-- 17．列出各个部门的 经理 的最低薪金。

-- 18．列出所有员工的年工资,按年薪从低到高排序。 

-- 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。

-- 20.查询出所有薪水在'陈二'之上的所有人员信息。

-- 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水

-- 22.查询出emp表中所有的工作种类（无重复）

-- 23.查询出所有奖金（comm）字段不为空的人员的所有信息。

-- 24.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）

-- 25.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）

-- 26.查询出名字中有“张”字符，并且薪水在1000以上（不包括1000）的所有员工信息。

-- 27.查询出名字第三个汉字是“多”的所有员工信息。

-- 28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。

-- 29.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。 order by convert(name using gbk) asc; 

-- 30.查询出最早工作的那个人的名字、入职时间和薪水。

-- 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.

-- 32.显示出薪水最高人的职位。

-- 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。

-- 34.删除10号部门薪水最高的员工。

-- 35.将薪水最高的员工的薪水降30%。

-- 36.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
-- 语法：case when ... then ... when ... then ... else ... end

sql语句：

-- 1．列出至少有一个员工的所有部门。
select dept.deptno,count(empno) from dept
left outer join emp
on dept.deptno=emp.deptno
group by dept.deptno
having count(empno)>=1
;

-- 2．列出薪金比"刘一"多的所有员工。
-- 2.1 刘一的薪金
select sal from emp where ename='刘一';
-- 2.2 比刘一高的员工
select * from emp where sal>(select sal from emp where ename='刘一');

-- 3．***** 列出所有员工的姓名及其直接上级的姓名。
select e1.ename,e2.ename from emp as e1,emp as e2
where e1.mgr=e2.empno;

-- 4．列出受雇日期早于其直接上级的所有员工。
select e1.ename,e2.ename from emp as e1,emp as e2
where e1.mgr=e2.empno
    and e1.hiredate<e2.hiredate;
;


-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select dept.dname,emp.* from dept
left outer join emp
on dept.deptno=emp.deptno;

-- 6．列出所有job为“职员”的姓名及其部门名称。
select emp.ename,dept.dname
from emp,dept
where emp.deptno=dept.deptno
    and emp.job='职员'
;

-- 7．列出最低薪金大于1500的各种工作。
select distinct job
from emp
where sal>1500;

-- 8．列出在部门 "销售部" 工作的员工的姓名，假定不知道销售部的部门编号。
select emp.ename
from emp,dept
where emp.deptno=dept.deptno
    and dept.dname='销售部';


-- 9．列出薪金高于公司平均薪金的所有员工。
select * from emp
where sal>(select avg(sal) from emp);


-- 10．列出与"周八"从事相同工作的所有员工。
-- 周八的工作
select job from emp where ename='周八';

select * from emp
where job = (select job from emp where ename='周八');

-- 方式二 使用exists
select * from emp e1
where exists (select * from emp e2 where e2.job=e1.job and e2.ename='周八' );


-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select * from emp
where sal in (select sal from emp where deptno=30);


-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select * from emp
where sal > all(select sal from emp where deptno=30);

-- 13．列出在每个部门工作的员工数量、平均工资。
select dept.deptno,count(emp.empno),avg(sal)
from dept
left outer join emp
on dept.deptno=emp.deptno
group by dept.deptno;

-- 14．列出所有员工的姓名、部门名称和工资。
select emp.ename, dept.dname,emp.sal
from emp, dept
where emp.deptno=dept.deptno;

-- 15．列出所有部门的详细信息和部门人数。
select deptno,count(empno) from emp
group by deptno;

select dept.*, ifnull(empCount,0) as 数量 from dept
left outer join (select deptno,count(empno) as empCount from emp
                    group by deptno) as temp
on dept.deptno=temp.deptno;

-- 16．列出各种工作的最低工资。
select job,min(sal)
from emp
group by job;

-- 17．列出各个部门的 经理 的最低薪金。
select deptno, min(sal)
from emp
where job='经理'
group by deptno;


-- 18．列出所有员工的年工资,按年薪从低到高排序。
select emp.*,(sal+ifnull(comm,0))*12 nianxin from emp
order by nianxin asc
;


-- 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。
select empno,ename,sal from emp
where sal>=3000;

-- 20.查询出所有薪水在'陈二'之上的所有人员信息。
select * from emp
where sal>(select sal from emp where ename='陈二');

-- 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，
-- 显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水
select empno as 员工编号, ename as 员工名字, sal as 薪水
from emp
where deptno=20
    and sal>2000;

-- 22.查询出emp表中所有的工作种类（无重复）
select distinct job from emp;

-- 23.查询出所有奖金（comm）字段不为空的人员的所有信息。
select * from emp
where comm is not null;

-- 24.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）
select * from emp
where sal >=800 and sal<=2500;

select * from emp
where sal between 800 and 2500;

-- 25.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）
select * from emp
where empno=7521 or empno=7900 or empno=7782;

select * from emp
where empno in (7521, 7900, 7782);

-- 26.查询出名字中有“张”字符，并且薪水在1000以上（不包括1000）的所有员工信息。
select * from emp
where ename like '%张%'
    and sal>1000
;

-- 27.查询出名字第三个汉字是“多”的所有员工信息。
select * from emp
where ename like '__多';

-- 28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。
select * from emp
order by sal asc, hiredate desc;

-- 29.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。
-- order by convert(name using gbk) asc;
select * from emp
order by convert(substring(ename,1,1) using gbk) asc, sal desc;

-- 30.查询出最早工作的那个人的名字、入职时间和薪水。
select min(hiredate) from emp;

select * from emp
where hiredate = (select min(hiredate) from emp);

-- 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.
select ename,sal,ifnull(comm,100)
from emp;

-- 32.显示出薪水最高人的职位。
select max(sal) from emp;
-- 方式一：
select job from emp
where sal = (select max(sal) from emp);

-- 方式二：
select job from emp
where sal >= all(select sal from emp);

-- 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。
select deptno,max(sal) as maxSal,min(sal) as minSal from emp
group by deptno;

select dept.dname as 部门名称,maxSal 最高薪水,minSal 最低薪水 from dept
left outer join (select deptno,max(sal) as maxSal,min(sal) as minSal from emp
                    group by deptno) temp
on dept.deptno=temp.deptno
where dept.deptno<>10;

-- 34.删除10号部门薪水最高的员工。
select empno from emp
where deptno=10 and sal>=all(select sal from emp where deptno=10);

delete from emp
where empno = (select empno from (select empno from emp
                where deptno=10
                    and sal>=all(select sal from emp where deptno=10))  as temp);

-- 35.将薪水最高的员工的薪水降30%。
select empno from emp
where sal >= all(select sal from emp);

update emp
set sal=sal*(1-0.3)
where empno in (select temp.empno from (select empno from emp
                where sal >= all(select sal from emp)) temp);


select * from emp;

-- 36.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
-- 语法：case when ... then ... when ... then ... else ... end
select ename,
    case when sal>3000 then '3级'
         when sal>2000 then '2级'
         else '1级'
    end
from emp;