数据库查询实验50道习题答案

员工表，部门表综合查询

---

注：该实验答案是本人一个一个经mysql数据库操作得出，如有错误地方，请评论或留言告知我，望与君共同进步！

---

-- （1）查询20号部门的所有员工信息。

SELECT * FROM emp where deptno=20;

-- （2）查询所有工种为CLERK的员工的工号、员工名和部门名。

SELECT e.EMPNO,e.ENAME,d.dname from emp e JOIN dept d ON(e.deptno=d.deptno) WHERE e.JOB='clerk';

SELECT e.EMPNO,e.ENAME,d.dname from emp e ,dept d WHERE e.JOB='clerk' and e.deptno=d.deptno;

-- （3）查询奖金（COMM）高于工资（SAL）的员工信息。

SELECT * FROM emp where COMM>SAL;

-- （4）查询奖金高于工资的20%的员工信息

SELECT * FROM emp where COMM>sal*0.2;

-- （5）查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息。

SELECT * FROM emp where (JOB='manager' and deptno=10) or (job='clerk' and deptno=20);

-- （6）查询所有工种不是MANAGER和CLERK，且工资大于或等于2000的员工的详细信息。

SELECT * from emp where job NOT IN('manager','clerk') AND SAL>=2000;

-- （7）查询有奖金的员工信息。

select * from emp where COMM is not null and comm>0;

-- （8）查询所有员工工资和奖金的和。

select ENAME,(sal+IFNULL(COMM ,0)) as sum from emp;

-- （9）查询没有奖金或奖金低于100的员工信息。

SELECT * from emp where comm is null or comm<100;

SELECT * from emp where ISNULL(COMM) or comm<100;

-- （10）查询各月倒数第3天入职的员工信息。

SELECT * from emp where LAST_DAY(HIREDATE)-HIREDATE=2;

-- （13） 查询员工名正好为6个字符的员工的信息。

SELECT * from emp where LENGTH(ENAME)=6;

SELECT * from emp where ENAME LIKE '______';-- 6个下划线

-- （14）查询员工名字中不包含字母"S"员工。(逆向)

SELECT * from emp where ENAME not LIKE '%s%';

-- （15）查询员工姓名的第2个字母为"M"的员工信息。

SELECT * from emp where ENAME LIKE '_m%';

-- （16）查询所有员工姓名的前3个字符。

SELECT SUBSTR(ENAME,1,3) as name from emp;

-- （17）查询所有员工的姓名，如果包含字母"S"，则用"s"替换。

SELECT REPLACE(ENAME,'S','s') as ename from emp;

-- （18）查询员工的姓名和入职日期，并按入职日期从先到后进行排列。

SELECT ENAME,HIREDATE from emp ORDER BY HIREDATE ;

-- （19）显示所有的姓名、工种、工资和奖金，按工种降序排列，若工种相同则按工资升序排列。

SELECT ENAME,JOB,SAL,COMM from emp  ORDER BY JOB desc , sal ;

-- （23）查询至少有一个员工的部门信息。

select d.* from emp e,dept d where e.deptno=d.deptno GROUP BY e.deptno HAVING COUNT(*)>1;

-- （24）查询工资比SMITH员工工资高的所有员工信息。

SELECT * FROM emp WHERE SAL > ( SELECT SAL FROM emp WHERE ENAME = 'smith');

-- （25）查询所有员工的姓名及其直接上级的姓名。

SELECT e.ENAME '员工姓名',p.ENAME '上级姓名' FROM emp e,emp p WHERE e.MGR = p.EMPNO;

-- （26）查询入职日期早于其直接上级领导的所有员工信息。

select * FROM emp where HIREDATE<();

-- （27）查询所有部门及其员工信息，包括那些没有员工的部门。

SELECT d.deptno,d.dname,e.* FROM emp e RIGHT JOIN dept d ON (e.deptno = d.deptno);

-- （29）查询所有工种为CLERK的员工的姓名及其部门名称。

SELECT e.ENAME,d.dname FROM emp e JOIN dept d ON (e.deptno = d.deptno) WHERE e.job = 'clerk';

-- （30）查询最低工资大于2500的各种工作。

SELECT job,MIN(sal) FROM emp GROUP BY job HAVING min(sal) > 2500;

-- （31）查询最低工资低于900的部门及其员工信息。

SELECT e.* FROM emp e WHERE e.deptno = (SELECT e.deptno FROM emp e GROUP BY e.deptno HAVING MIN(e.sal) < 900);

-- （32）查询在SALES部门工作的员工的姓名信息。

SELECT e.ename FROM emp e JOIN dept d ON (e.deptno = d.deptno) WHERE d.dname = 'sales';

-- （33）查询工资高于公司平均工资的所有员工信息。

SELECT * FROM emp WHERE SAL > (SELECT AVG(sal) FROM emp);

-- （34）查询与SMITH员工从事相同工作的所有员工信息。

SELECT * FROM emp WHERE job = (SELECT job FROM emp WHERE ENAME = 'smith');

-- （35）查询出工资和30号部门中员工相同的所有员工的姓名和工资。

SELECT ENAME,SAL from emp WHERE sal in (SELECT sal from emp WHERE deptno=30) and deptno!=30;

SELECT ENAME,sal from emp WHERE deptno=30;-- 查询30号部门员工的姓名和工资

-- （37）查询每个部门中的员工数量、平均工资。

SELECT d.deptno,COUNT(e.EMPNO) as count,ifnull(round(avg(e.sal),2),0) as avg FROM emp e RIGHT JOIN dept d ON (e.deptno = d.deptno) GROUP BY d.deptno;

-- （39）查询各个部门的详细信息以及部门人数、部门平均工资。

SELECT d.*,COUNT(e.EMPNO) AS count,ifnull(round(avg(e.sal), 2), 0) AS avg FROM dept d LEFT JOIN emp e ON (e.deptno = d.deptno) GROUP BY d.deptno;

-- （40）查询各种工作的最低工资。

SELECT job,min(sal) from emp GROUP BY job;

-- （41）查询各个部门中的不同工种的最高工资。

SELECT d.deptno,IFNULL(e.job,'无') as job,IFNULL(MAX(sal),'无') as max FROM emp e RIGHT JOIN dept d ON (e.deptno = d.deptno) GROUP BY d.deptno,e.JOB;

-- （42）查询10号部门员工以及领导的信息。

SELECT e.*from emp e where e.deptno=10;

-- （43）查询各个部门的人数及平均工资。

SELECT d.deptno,COUNT(e.EMPNO) as count,ifnull(round(avg(e.sal),2),0) as avg FROM emp e RIGHT JOIN dept d ON (e.deptno = d.deptno) GROUP BY d.deptno;

-- （44）查询工资为某个部门平均工资的员工信息。

SELECT * FROM emp WHERE sal IN (SELECT deptno,ifnull(round(avg(sal)), 0) FROM emp GROUP BY deptno);

-- （45）查询工资高于本部门平均工资的员工的信息。

SELECT e.* FROM emp e WHERE e.sal > (SELECT avg(p.sal) FROM emp p WHERE e.deptno = p.deptno);

-- (46）查询工资高于本部门平均工资的员工的信息及其部门的平均工资。

SELECT e.*,(SELECT ROUND(avg(p.sal),2) FROM emp p WHERE e.deptno = p.deptno) as avg FROM emp e WHERE e.sal > (SELECT avg(p.sal) FROM emp p WHERE e.deptno = p.deptno);

-- （47）查询工资高于20号部门平均工资的所有员工的信息。

SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 20);

-- （48）统计各个工种的人数与平均工资。

SELECT COUNT(empno),ROUND(avg(sal)) FROM emp GROUP BY job;

-- （51）查询部门人数大于5的部门的员工的信息。

SELECT e.* from emp e where (SELECT COUNT(p.empno) from emp p where e.deptno=p.deptno)>5;

-- （52）查询所有员工工资都大于1000的部门的信息。(反向思维)

SELECT d.* FROM dept d JOIN emp e ON (d.deptno = e.deptno) WHERE (SELECT min(e1.sal) FROM emp e1 WHERE e.deptno = e1.deptno) > 1000;

-- （53）查询所有员工工资都大于1000的部门的信息及其员工信息。

SELECT d.*,e.* FROM dept d JOIN emp e ON (d.deptno = e.deptno) WHERE (SELECT min(e1.sal) FROM emp e1 WHERE e.deptno = e1.deptno) > 1000;

-- （57）查询人数最多的部门信息。

SELECT d.* FROM dept d WHERE d.deptno = (SELECT deptno FROM emp GROUP BY deptno ORDER BY COUNT(EMPNO) DESC LIMIT 1);

SELECT deptno from emp GROUP BY deptno ORDER BY COUNT(EMPNO) desc LIMIT 1;-- 获取人数最多的部门

-- （58）查询30号部门中工资排序前3名的员工信息。

SELECT * FROM emp WHERE deptno = 30 ORDER BY sal DESC LIMIT 3;

-- （59）查询所有员工中工资排在5~10名之间的员工信息。

SELECT * FROM emp ORDER BY sal DESC LIMIT 4,6;