– 部门表 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．列出至少有一个员工的所有部门。
SELECT deptno,COUNT(*) FROM emp GROUP BY deptnoHAVING COUNT(1)>1;
– 2．列出薪金比"刘一"多的所有员工。
– SELECT * FROM emp WHERE ename =‘刘一’ ; – sal 800
SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename ='刘一')
ORDER BY sal;
– 3．列出所有员工的姓名及其直接上级的姓名。
SELECT e1.ename,(SELECT ename FROM emp b WHERE b.empno =e1.mgr) FROMemp e1
select e.ename,m.ename from emp e left join emp m on e.mgr=m.empno;
– 4．列出受雇日期早于其直接上级的所有员工。
select e.ename,e.hiredate,m.ename, m.hiredatefrom emp e left join emp m on e.mgr=m.empno
WHERE e.hiredate– 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
SELECT dname,emp.* FROM emp
LEFT JOIN dept
ON emp.deptno =dept.deptno;
– 6．列出所有job为“职员”的姓名及其部门名称。
SELECT dname,ename FROM emp
LEFT JOIN dept
ON emp.deptno =dept.deptno
WHERE job='职员';
– 7．列出最低薪金大于1500的各种工作。
SELECT * FROM emp
WHERE sal>1500;
– 8．列出在部门 “销售部” 工作的员工的姓名，假定不知道销售部的部门编号。
SELECT dname,emp.* FROM emp
LEFT JOIN dept
ON emp.deptno =dept.deptno
WHERE emp.deptno=(SELECT deptno FROMdept WHERE dept.dname='销售部');
– 9．列出薪金高于公司平均薪金的所有员工。
SELECT * FROM emp WHERE sal > (SELECT avg(sal) FROM emp);
– 10．列出与"周八"从事相同工作的所有员工。
SELECT * FROM emp
WHERE emp.job =(SELECT job FROM emp e WHERE e.ename = '周八');
– 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select ename ,sal,deptno from emp
where sal in(select sal from emp where deptno=30) AND emp.deptno !=30;
– 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ename ,sal,deptno from emp
where sal > (select max(sal) from emp where deptno=30) AND emp.deptno !=30;
– 13．列出在每个部门工作的员工数量、平均工资。
SELECT deptno,COUNT(ename) 员工数量,ROUND(AVG(sal),2) 平均工资 FROM emp GROUP BY deptno
– 14．列出所有员工的姓名、部门名称和工资。
SELECT ename,dname,sal FROM emp
LEFT JOIN dept ON emp.deptno =dept.deptno;
– 15．列出所有部门的详细信息和部门人数。
SELECT dept.*,COUNT(1) FROM emp
LEFTJOIN dept
ON emp.deptno =dept.deptno
GROUP BY emp.deptno;
– 16．列出各种工作的最低工资。
SELECT job,MIN(sal) FROM emp
GROUP BY job;
– 17．列出各个部门的 经理 的最低薪金。
SELECT deptno,job ,MIN(sal)FROM emp e
WHERE e.job='经理'
GROUP BY deptno;
– 18．列出所有员工的年工资,按年薪从低到高排序。
SELECT sal*12 年工资, emp.* FROM emp
ORDER BY 年工资;
– 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。
SELECT empno,ename,sal FROM emp WHERE sal>=3000;
– 20.查询出所有薪水在’陈二’之上的所有人员信息。
SELECT * FROM emp
WHERE sal> (SELECT e.sal FROM emp e WHERE e.ename = '陈二' );
– 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水
SELECT deptno,ename,salFROM emp
WHERE sal>2000 AND deptno =20;
– 22.查询出emp表中所有的工作种类（无重复）
SELECT job FROM emp
GROUP BY job HAVING count(1)=1;
– 23.查询出所有奖金（comm）字段不为空的人员的所有信息。
SELECT * FROM emp WHERE comm NOT NULL;
【mysql|SQL综合题 员工单位综合题】– 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 empORDER BY sal,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 ename,hiredate,sal FROM emp e WHERE hiredate = (select min(hiredate) from emp);
– 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.
– ifnull 函数在一个表达式是NULL时从另一个表达式获得值
select ename,sal, ifnull(comm,100) from emp
– 32.显示出薪水最高人的职位。
SELECT job FROM emp WHERE sal = (select max(sal) from emp);
– 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。
SELECT deptno 部门编号, MAX(sal) 最高薪水 , min(sal) 最低薪水 FROM emp
WHERE deptno !=10
GROUP BY deptno;
– 34.删除10号部门薪水最高的员工。
delete from emp
where empno =
(select empno from
(SELECT empno FROM emp WHERE deptno =10 AND job != '总裁'
and sal>=all(select sal from emp where deptno=10 AND job != '总裁'))as temp);
SELECT * FROM emp WHERE deptno=10; 5
– 35.将薪水最高的员工的薪水降30%。
UPDATEempSET sal = sal*0.7
WHERE empno =(select empno from
(SELECT empno FROM emp WHERE job != '总裁'
and sal>=all(select sal from emp where job != '总裁') LIMIT 1)as temp);
– 36.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
select ename, sal,
case when sal>3000 then '3级'
when sal>2000 then '2级'
else '1级'
end
from emp
ORDER BY sal DESC;
推荐阅读
- 04_（终结版）通过App实现对数据库的增删改
- 如何在SQL中执行Python/R
- SQL中的远程Python和R
- java|多级分类、菜单等的数据库设计（一张表），以及mybatis-plus的多级分类查询（一条SQL语句）
- SQL（报告和分析）
- 在Python中使用PostgreSQL
- SQL如何增强你的子查询（）
- 在SQLite中修改日期函数
- SQL索引简介