当前位置:Gxlcms > 数据库问题 > sql语句练习题

sql语句练习题

时间:2021-07-01 10:21:17 帮助过:302人阅读

KING并没有上级,所以添加一个(+)号表示左连接

 

29.列出受雇日期早于直接上级的所有员工的编号,姓名,部门名称

 SELECT e.ename,e.empno,d.dname

 FROM emp e,emp m,dept d

 WHERE e.mgr=m.empno AND e.deptno=d.deptno AND e.hiredate<m.hiredate

 ;

 

30.列出部门名称和这些部门员工的信息,同时列出那些没有员工的部门。

·左右关联的问题,即使没有员工也要显示

SELECT d.deptno,d.dname,e.empno,e.ename

FROM dept d,emp e

WHERE d.deptno=e.deptno(+);

 

 

 

31.列出“CLERK”的姓名和部门名称,部门人数:

·找出所有办事员的姓名和部门编号:

SELECT e.ename,d.dname,ed.cou

FROM emp e,dept d,(SELECT deptno,COUNT(empno) cou FROM emp GROUP BY deptno) ed

WHERE e.deptno=d.deptno AND job=‘CLERK‘ AND ed.deptno=d.deptno;

 

 

32.列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数

·按工作分组,分组条件是最低工资大于1500

SELECT job,MIN(sal) FROM emp

GROUP BY job HAVING MIN(sal)>1500;

·在按照

SELECT e.job,COUNT(empno)

FROM emp e

WHERE job IN(

 SELECT job FROM emp

 GROUP BY job HAVING MIN(sal)>1500

)

GROUP BY e.job;

 

33.列出在部门销售部工作的员工姓名,假设不知道销售部的部门编号

·根据DEPT表查询销售部的部门编号(子查询)

SELECT deptno

FROM dept

WHERE dname=‘SALES‘;

·上述为子查询

SELECT ename FROM emp

WHERE deptno=(

SELECT deptno

FROM dept

WHERE dname=‘SALES‘);

 

34.列出薪金高于工资平均薪金的所有员工,所在部门,上级领导,公司的工资等级。

·求出平均工资:

SELECT AVG(sal) FROM emp;

·列出薪金高于平均工资的所有雇员信息

SELECT * FROM emp WHERE sal>(

SELECT AVG(sal) FROM emp

);

·和部门表关联,查询所在部门的信息(注意KING 是没有上级的 注意右连接)

SELECT e.*,d.dname,d.loc,m.ename

FROM emp e,dept d,emp m

WHERE

e.mgr=m.empno(+) AND

e.deptno=d.deptno AND

e.sal>(

SELECT AVG(sal) FROM emp

);

 

·求出雇员的工资等级

SELECT e.*,d.dname,d.loc,m.ename,s.grade

FROM emp e,dept d,emp m,salgrade s

WHERE

e.mgr=m.empno(+) AND

e.deptno=d.deptno AND

e.sal>(SELECT AVG(sal) FROM emp)

AND e.sal BETWEEN s.losal AND s.hisal

;

 

 

35.列出和“SCOTT”从事相同工作的所有员工及部门名称:

·SCOTT从事的工作

SELECT job FROM emp WHERE ename=‘SCOTT‘;

·做子查询

SELECT e.*,d.dname FROM emp e,dept d

WHERE e.job=(SELECT job FROM emp WHERE ename=‘SCOTT‘)

AND e.deptno=d.deptno;

·以上的结果存在SCOTT,应该去掉

SELECT e.*,d.dname FROM emp e,dept d

WHERE e.job=(SELECT job FROM emp WHERE ename=‘SCOTT‘)

AND e.deptno=d.deptno

AND e.ename!=‘SCOTT‘;

 

 

36.列出薪金等于部门30中员工薪金的所有员工的姓名和薪金

·求出部门30中的员工薪金

SELECT sal FROM emp WHERE deptno=30;

·子查询

SELECT ename,sal FROM emp

WHERE sal IN (SELECT sal FROM emp WHERE deptno=30)

AND deptno!=30;

 

 

37.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称

·在之前的程序进行修改使用>ALL ,比最大还大

SELECT ename,sal FROM emp

WHERE sal >ALL(SELECT sal FROM emp WHERE deptno=30)

AND deptno!=30;

·再和dept关联,求出部门名称

SELECT e.ename,e.sal,d.dname

FROM emp e,dept d

WHERE e.sal >ALL(SELECT sal FROM emp WHERE deptno=30)

AND e.deptno!=30

AND e.deptno=d.deptno;

 

 

38.列出每个部门工作的员工数量、平均工资和平均服务期限

·每个部门工作的员工数量:

SELECT d.dname,COUNT(e.empno)

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY d.dname;

·求出平均工资和服务年限

SELECT d.dname,COUNT(e.empno),AVG(sal),AVG(MONTHS_BETWEEN(sysdate,hiredate)/12) 年

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY d.dname;

 

39.列出所有员工的姓名、部门和工资

SELECT e.ename,d.dname,e.sal FROM emp e,dept d WHERE e.deptno=d.deptno;

 

 

40.列出所有部门的相信信息和部门人数

·列出所有部门的人数

SELECT deptno dno,COUNT(empno) cou

FROM emp

GROUP BY deptno;

·把上表当成临时表:【由于40部门没有雇员,所以应该使用0表示】

SELECT d.*,NVL(ed.cou,0) FROM dept d,(SELECT deptno dno,COUNT(empno) cou

FROM emp

GROUP BY deptno) ed

WHERE  d.deptno=ed.dno(+);

 

 

41、列出各种工作的最低工资以及从事此工作的雇员姓名:

·按工作分组求出最低工资

SELECT MIN(sal) m FROM emp GROUP BY job;

·子查询

SELECT e.ename FROM emp e

WHERE e.sal IN(SELECT MIN(sal) m FROM emp GROUP BY job);

 

42、列出各个部门的MANAGER 的最低薪金:

·求出各个部门MANAGER的工资,按照部门分组

SELECT deptno,MIN(sal) FROM emp WHERE job=‘MANAGER‘ GROUP BY deptno;

 

43、列出所有员工的年工资,按照年薪从低到高排序:

·注意奖金,奖金要用NVL函数处理

SELECT ename,(sal+NVL(comm,0))*12 income FROM emp ORDER BY income

 

 

44、查询出某个员工的上级主管,并要求这些主管中的薪水超过3000

SELECT DISTINCT m.*

FROM emp e,emp m

WHERE e.mgr=m.empno AND m.sal>3000;

 

 

45、求出部门名称中带有’S‘字符的部门员工的工资合计,部门人数

·查询部门表中的部门名称,使用模糊查询,以确定部门编号

SELECT deptno FROM dept WHERE dname LIKE ‘%S%‘;

 

·再根据上面作为子查询,求出工资合计和部门人数

SELECT SUM(sal),COUNT(empno)

FROM emp e

WHERE e.deptno IN (SELECT deptno FROM dept WHERE dname LIKE ‘%S%‘)

GROUP BY deptno;

 

46、给任职日期超过10年的人加薪10%;

UPDATE emp SET sal=sal*1.1 WHERE MONTHS_BETWEEN(sysdate,hiredate)/12>10;

sql语句练习题

标签:添加   日期   des   upper   tin   isa   行修改   关键字   mon   

人气教程排行