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

1、建表

-- 部门表
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);

2、练习

create database test;
use test;
create table dept(
  deptno smallint(5) primary key auto_increment comment '部门编号',
  deptname varchar(20) comment '部门名称',
  loc varchar(20)
)engine=INNODB,charset =UTF8;
desc dept;

create table emp(
  empno smallint(5) primary key auto_increment comment '员工编号',
  empname varchar(10) comment '员工姓名',
  job varchar(10) comment '岗位',
  mgr smallint(5) comment '直属领导编号',
  hiredate date comment '入职日期',
  sal int(10) comment '薪水',
  comm  int(10) comment '提成',
  deptno smallint(5) not null comment '部门编号',
  foreign key (deptno) references dept(deptno)
)engine=INNODB,charset =UTF8;

desc emp;

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

select * from dept;

select * from emp;

# 查询的是同一张表中的不同字段，————————————》使用内连接
# 查询的是不同表中的不同字段，——————————————》使用外查询
# 查询的条件有多个，———————————————————————》使用子查询
# 更新和删除都是根据的ID，所以先子查询出ID，在删除和更新
-- 1．列出至少有一个员工的所有部门。
select deptno ,count(deptno) deptcount from emp group by deptno;
select d.deptno ,count(e.deptno) deptcount,deptname from emp e  left join dept d on e.deptno = d.deptno group by e.deptno;
-- 2．列出薪水比"刘一"多的所有员工。（查询有两层及以上条件的，使用子查询）
select emp.empname from emp where sal>(select sal from emp where empname='刘一');
-- 3．列出所有员工的姓名及其直接上级的姓名。(查询的是同一张表中的不同字段，使用内连接查询）
select   a.empname,b.empname mgrname from emp a inner join emp b where a.mgr=b.empno;
-- 4．列出受雇日期早于其直接上级的所有员工。
select a.empname from emp a inner join emp b where a.mgr=b.empno and a.hiredate<b.hiredate;
-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select d.deptname,e.* from emp e  left join dept d on d.deptno = e.deptno;
-- 6．列出所有job为“职员”的姓名及其部门名称。
select e.empname,d.deptname from emp e left join dept d on e.deptno = d.deptno where e.job='职员';
-- 7．列出最低薪金大于1500的各种工作。
select distinct(job) from emp where hiredate>1500 ;
-- 8．列出在部门 "销售部" 工作的员工的姓名，假定不知道销售部的部门编号。
select e.empname from emp e left join dept d on e.deptno = d.deptno where d.deptname='销售部';
-- 9．列出薪金高于公司平均薪金的所有员工。
select empname, sal from  emp where sal>(select avg(sal) from emp )  ;
-- 10．列出与"周八"从事相同工作的所有员工。
select empname from emp where  job=(select job from emp where empname='周八');
-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select a.empname,b.sal from emp a inner join emp b where a.deptno=30 and a.sal=b.sal;
-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select a.empname,a.sal from emp a inner join emp b where a.deptno=30 and a.sal>b.sal;
-- 13．列出在每个部门工作的员工数量、平均工资。
select deptname,count(d.deptno) countOfDept,avg(e.sal) from dept d left join emp e on d.deptno = e.deptno group by d.deptno;
-- 14．列出所有员工的姓名、部门名称和工资。
select e.empname,d.deptname,e.sal  from emp e left join dept d on e.deptno = d.deptno ;
-- 15．列出所有部门的详细信息和部门人数。
select d.*,count(e.deptno) from dept d left join emp e on d.deptno = e.deptno group by d.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  empname,sal*12 yearOfSal  from emp order by sal asc ;
-- 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。
select empno,empname,sal from emp where sal>=3000;
-- 20.查询出所有薪水在'陈二'之上的所有人员信息。
select * from emp where sal>(select sal from emp where empname='陈二');
-- 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水
select empno 员工编号,empname 员工名字,sal 薪水 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 empname like '%张%' and sal>1000;
-- 27.查询出名字第三个汉字是“多”的所有员工信息。
select * from emp where empname 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(empname using gbk) asc,sal desc ;
-- 30.查询出最早工作的那个人的名字、入职时间和薪水。
select empname,hiredate,sal from emp order by hiredate asc limit 1;
-- 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.
select empname,sal,ifnull(comm,100) from emp ;
-- 32.显示出薪水最高人的职位。
select empname,sal,job from emp order by sal desc limit 1;
-- 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。
select max(sal), min(sal) from emp where deptno not in (10) ;
-- 34.删除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%。

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

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