Mysql 简单练习题（1）

-- 创建部门表/
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(50),
loc VARCHAR(50)
);

-- 创建雇员表/
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
COMM DECIMAL(7,2),
deptno INT,
CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)
);

-- 创建工资等级表
CREATE TABLE salgrade(
grade INT PRIMARY KEY,
losal INT,
hisal INT
);

 

INSERT INTO dept VALUES (10, '教研部', '北京');
INSERT INTO dept VALUES (20, '学工部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '武汉');

 

INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);

 

INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);

-- 列出在销售部工作的员工的姓名
select ename from emp join dept on emp.deptno = dept.deptno where dname = '销售部';

-- 列出与庞统从事相同工作的所有员工及部门名称。
select emp.* , dname from emp join dept on emp.deptno = dept.deptno where ename != '庞统' and job = (
    select job from emp where ename = '庞统'
);

-- 列出薪金比关羽高的所有员工。

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

-- 列出至少有一个员工的所有部门
select distinct dname from dept join emp on emp.deptno = dept.deptno ;

-- 列出职位为文员的姓名及所在部门名
select ename , dname from  dept join emp on emp.deptno = dept.deptno where job = '文员' ;

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

-- 1.查询出部门编号为30的所有员工
select * from emp where deptno = 30 ;

-- 2.所有销售员的姓名、编号和部门编号。
select ename , empno , deptno from emp where job = '销售员';

-- 3.找出奖金高于工资的员工。
select * from emp where COMM > sal;

-- 4.找出奖金高于工资60%的员工。
select * from emp where COMM > sal * 0.6;

-- 5.找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。
select * from emp where deptno = 10 and job = '经理' or deptno = 20 and job = '销售员';

-- 6.找出部门编号为10中所有经理，部门编号为20中所有销售员，
-- 还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。
select * from emp where (deptno = 10 and job = '经理') or (deptno = 20 and job = '销售员') or ( job not in ('经理','销售员') and sal + IFNULL(COMM,0) >= 20000)

-- 7.有奖金的工种。
select distinct job from emp where COMM  is not null ;

-- 8.无奖金或奖金低于1000的员工。
select * from emp where COMM is null or COMM < 1000 ;

-- 9.查询名字由三个字组成的员工。
select * from emp where ename like '___';

-- 10.查询2000年入职的员工。
select * from emp where hiredate between '2000-1-1' and '2000-12-31' ;
select * from emp where hiredate like '2000%';

-- 11.查询所有员工详细信息，用编号升序排序
select * from emp order by empno; 

-- 12.查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序
select * from emp order by (sal + IFNULL(COMM,0)) desc ,hiredate ;

-- 13.返回员工姓名及其所在的部门名称。
select ename , dname from emp join dept on emp.deptno = dept.deptno 

-- 14.返回从事文员工作的员工姓名和所在部门名称
select ename , dname from  dept join emp on emp.deptno = dept.deptno where job = '文员' ;

-- 15.返回与30部门员工工资相同的员工姓名与工资。
select ename , sal + IFNULL(COMM,0) as 工资 from emp where deptno != 30 and sal + IFNULL(COMM,0) in (
    select sal + IFNULL(COMM,0) from emp where deptno = 30
);

-- 16.查出比经理薪水还高的员工信息
select * from emp where sal > (
    select sal from emp where job = '经理' order by sal limit 0,1
) and job != '经理';

-- 17.返回员工和所属上司的姓名
select yg.ename , ld.ename from emp yg , emp ld where yg.mgr = ld.empno

-- 18.返回雇员的雇佣日期早于其上司雇佣日期的员工及其上司姓名
select yg.ename , ld.ename from emp yg , emp ld where yg.mgr = ld.empno and yg.hiredate < ld.hiredate ;

-- 19.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
select yg.ename , yg.empno , dname from emp yg , emp ld ,dept where yg.mgr = ld.empno and yg.deptno = dept.deptno and yg.hiredate < ld.hiredate ;

-- 20.查询刘备所属的工资等级以及工资。 
select grade , sal + IFNULL(COMM,0) as 工资 from emp , salgrade where sal + IFNULL(COMM,0) between losal and hisal and ename = '刘备';

-- 21.返回工资处于第四级别的员工的姓名。
select ename from emp where sal + IFNULL(COMM,0) between (
    select losal from salgrade where grade = 4 
) and (
    select hisal from salgrade where grade = 4 
);

/*或*/

select ename from emp e,salgrade s where e.sal BETWEEN s.losal and s.hisal
and grade=4

-- 22.返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
select ename , loc , losal , hisal from emp , dept , salgrade where emp.deptno = dept.deptno and sal + IFNULL(COMM,0) between (
    select losal from salgrade where grade = 2
) and (
    select hisal from salgrade where grade = 2 
) and grade = 2;

/*或*/

select ename,loc,hisal,losal from emp e,salgrade s,dept d where 
e.sal BETWEEN s.losal and s.hisal
and e.deptno=d.deptno
and grade=2