B站_千锋2020MySQL_6.04练习题

创建数据

-- 已知三张表 部门表、员工表和工资等级表

-- 部门表[部门编号deptno,部门名称dname,部门地址loc]
create table dept(
    deptno int primary key ,
    dname varchar(14),
    loc varchar(13)
);
-- 添加数据
insert into dept values (10,'accounting','NEW YORK');
insert into dept values (20,'research','DALLAS');
insert into dept values (30,'sales','CHICAGO');
insert into dept values (40,'operations','BOSTON');

-- 员工表 emp
-- [员工编号empno, 员工姓名 ename，员工工作 job，员工直属领导编号mgr，入职时间hiredate，
-- 工资sal，奖金comn，部门编号deptno]
create table emp(
    empno int primary key ,
    ename varchar(10),
    job varchar(9),
    mgr int,
    hiredate date,
    sal double,
    comm double,
    deptno int
);
-- 添加数据
insert into emp values (7369,'smith','clerk',7902,'1980-12-17',800,NULL,20);
insert into emp values (7499,'allen','salesman',7698,'1981-02-20',1600,300,30);
insert into emp values (7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
insert into emp values (7566,'jones','manager',7839,'1981-04-02',2975,NULL,20);
insert into emp values (7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);
insert into emp values (7698,'blake','manager',7839,'1981-05-01',2850,NULL,30);
insert into emp values (7782,'clarks','manager',7839,'1981-06-09',2450,NULL,10);
insert into emp values (7788,'scott','analyst',7566,'1987-07-03',3000,NULL,20);
insert into emp values (7839,'king','president',NULL,'1981-11-17',5000,NULL,10);
insert into emp values (7844,'turner','salesman',7698,'1981-09-28',1500,0,30);
insert into emp values (7876,'adadms','clerk',7788,'1987-07-13',1100,NULL,20);
insert into emp values (7900,'james','clerk',7698,'1981-12-03',950,NULL,30);
insert into emp values (7902,'ford','analyst',7566,'1981-12-03',3000,NULL,20);
insert into emp values (7934,'miller','clerk',7782,'1981-01-23',1300,NULL,10);
-- 工资等级表 salgrade[等级grade，最低工资lowsal,最高工资hisal]
create table salgrade(
    grade int,
    lowsal double,
    higsal double
);
-- 添加数据
insert into salgrade values (1,700,1200);
insert into salgrade values (2,1201,1400);
insert into salgrade values (3,1401,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (5,3001,9999);

题目

1.找出姓名以a,b,s开始的员工信息

select * from emp where ename REGEXP '^[a|b|s].*'; -- 正则表达式
select * from emp where left(ename,1) in ('a','b','s');

2.返回员工的详细信息并按姓名排序

select * from emp order by ename ; -- 升序
select * from emp order by ename desc ; -- 降序

3.返回员工的信息并按工作降序，工资升序排列

select * from emp order by sal, job desc ;

4.返回拥有员工的部门名、部门号

select dept.deptno,dept.dname from dept 
inner join emp on dept.deptno = emp.deptno 
group by dept.deptno;

-- 显示部门下的员工
select dept.deptno,dept.dname ,group_concat(ename) as 'enames'from dept
inner join emp on dept.deptno = emp.deptno
group by dept.deptno;

5.工资高于smith员工信息

select * from emp where sal >
(select sal from emp where ename='smith');

6.返回员工和所属经理的名字(自查询)

select emp.ename,manager.ename from emp 
inner join emp as manager 
on emp.mgr = manager.empno;

7.返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名

select emp.ename,manager.ename from emp 
inner join emp as manager 
on emp.mgr = manager.empno 
where emp.hiredate<manager.hiredate;

8.返回员工姓名及其所在部门名称

select ename,dname from emp 
inner join dept 
on emp.deptno=dept.deptno;

9.返回从事clerk 工作的员工姓名和所在部门名称

select ename,dname from emp 
inner join dept 
on emp.deptno=dept.deptno
where emp.job='clerk';

10.返回部门号及其本部门的最低工资

select deptno,min(sal) from emp
group by deptno;

11.返回销售部(sales)所有员工的姓名

select ename from emp
inner join dept
on emp.deptno=dept.deptno
where dname='sales';

12.返回与’scott’从事相同工作的员工信息

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

-- 去掉scott本人
select * from emp 
where job = 
(select job from emp where ename = 'scott') 
having  ename != 'scott';

13.返回员工的详细信息（包括部门名称和部门地址）

select emp.*,dept.dname,dept.loc from emp
inner join dept
on emp.deptno=dept.deptno;

14.返回员工工作及其从事此工作的最低工资

select job,min(emp.sal) from emp group by job;

15.返回工资处于第四级别的员工的姓名

select emp.ename from emp,salgrade 
where sal 
between salgrade.lowsal and salgrade.higsal 
and salgrade.grade='4';

16.返回工资为二等级的职员姓名、部门所在地和二等级的最低工资和最高工资

select emp.ename,emp.sal,dept.loc,salgrade.*
from emp,salgrade,dept
where salgrade.grade=2 and
emp.deptno=dept.deptno
and emp.sal between salgrade.lowsal
and salgrade.higsal;

17.工资等级高于‘smith’的员工信息

-- 利用视图表
-- 根据最高最低分将员工划分薪资登记
create view v_17 as select 
emp.*,salgrade.grade from salgrade,emp where sal between lowsal and higsal;
-- 大于smith的工资等级
select * from v_17
where v_17.grade > (select grade from v_17 where ename='smith');

-- 第二种方法
select emp.*,grade
from emp,salgrade
where sal between lowsal and higsal
and grade >
(select grade
from salgrade,emp
where ename='smith' and sal
between lowsal and higsal);