oracle-scott-练习题（3）

1.查询所有的部门编号： 
select DEPTNO from DEPT ;

2.查询所有有人的部门编号： 
select DEPTNO from EMP ;

3.查询所有岗位名称： 
select DISTINCT “JOB” from EMP ;

4.查询所有薪水超过两千的员工信息 
select * from EMP where SAL>2000;

5.查询所有20部门的员工姓名，编号及薪水 
select ENAME,EMPNO,SAL from EMP where DEPTNO=20;

6.查询所有没有奖金的员工信息 
select * from EMP where (COMM is NULL OR COMM = 0);

7.查询所有有奖金的员工信息 
select * from EMP where (COMM is NOT NULL );

8.查询最高领导的员工信息 
select * from EMP where MGR is NULL;

9.查询所有81年之后入职的员工信息 
select * from EMP where HIREDATE >= “TO_DATE”(‘1981-01-01’,’yyyy-mm-dd’); 
select * from EMP where HIREDATE >= ‘1-1月-81’;

10.查询所有薪水在2000-4000范围内的员工信息 
select * from EMP where SAL BETWEEN 2000 AND 4000;

11.查询所有部门编号是10或30的员工信息 
select * from EMP where DEPTNO IN (10,30);

12.查询所有20部门并且薪水超过2000的员工信息： 
select * from EMP where DEPTNO = 20 AND SAL > 2000;

13.查询所有薪水不在2000-4000范围内的员工信息 
select * from EMP where SAL NOT BETWEEN 2000 AND 4000;

14.查询所有部门编号不是10，30的员工信息 
select * from EMP where DEPTNO NOT IN (10,30);

15.查询用户名为scott的员工信息：注意区分大小写 
select * from EMP where ENAME = ‘SCOTT’;

16.查询姓名里面包含ALL的员工姓名 
select ENAME from EMP WHERE ENAME LIKE ‘%ALL%’;

17.查询所有以”S”开头的同学 
select * from EMP WHERE ENAME LIKE ‘S%’;

18.查询第二个字母为A的员工姓名 
select ENAME from EMP WHERE ENAME LIKE ‘_A%’;

19.查询所有员工的编号、姓名、部门编号、职位、薪水，按照薪水降序排列 
select EMPNO,ENAME,DEPTNO,”JOB”,SAL from EMP ORDER BY SAL DESC;

20.查询所有员工信息，按照部门降序排列，部门内按照薪水升序排列 
select * from EMP ORDER BY DEPTNO DESC,SAL ASC;

21.查询姓名中包含‘A’员工的姓名，编号，薪水，按照薪水降序排列 
select ENAME,EMPNO,SAL from EMP WHERE ENAME LIKE ‘%A%’ ORDER BY SAL DESC;

22.查询年收入超过10000的员工的姓名，编号，薪水，年收入，按照年收入降序排列 
SELECT ENAME,EMPNO,SAL,(SAL+”NVL”(COMM, 0))*12 AS YEARSAL 
FROM EMP WHERE (SAL+”NVL”(COMM, 0))*12 > 10000 ORDER BY YEARSAL DESC;

23.查询年薪超过10000的员工的姓名，编号，薪水，年收入，按照年薪降序排列 
SELECT ENAME,EMPNO,SAL,SAL*12 AS YEARSAL 
FROM EMP WHERE SAL*12 > 10000 ORDER BY YEARSAL DESC;

后续练习题：

24.查询雇员表中，姓名为SMITH的雇员，截止到今天共工作了多少周，则可以使用如下的SQL语句 
SELECT ROUND((SYSDATE-HIREDATE)/7) AS “Weeks” FROM EMP WHERE ENAME = ‘SMITH’;

25.查询各部门的最高薪水、最低薪水、平均薪水…. 
SELECT DEPTNO,”MAX”(SAL),”MIN”(SAL),”AVG”(SAL) FROM EMP GROUP BY DEPTNO;

26.查询‘SMITH’的领导姓名 
SELECT ENAME FROM EMP WHERE EMPNO = (SELECT MGR FROM EMP WHERE ENAME = ‘SMITH’);

27.查询部门名称是‘SALES’的员工信息 
SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = ‘SALES’);

28.查询公司中薪水最高的员工信息 
SELECT * FROM EMP WHERE SAL = (SELECT “MAX”(SAL) FROM EMP);

29.查询公司所有员工的个数 
SELECT “COUNT”(ENAME) FROM EMP ;

30.查询公司中最高薪水是多少 
SELECT “MAX”(SAL) FROM EMP ;

31.查询公司中平均奖金是多少 
SELECT “AVG”(“NVL”(COMM, 0)) FROM EMP ;

32.查询公司中最晚入职的时间 
SELECT “MAX”(HIREDATE) FROM EMP ;

33.查询公司中有奖金的人数 
SELECT “COUNT”(ENAME) FROM EMP WHERE (COMM IS NOT NULL) ;

34.查询20部门的最高薪水是多少 
SELECT “MAX”(SAL) FROM EMP WHERE DEPTNO = 20 ;

35.查询各部门的平均薪水及部门编号，部门名称。 
SELECT “AVG”(SAL),DNAME,EMP.DEPTNO FROM EMP,DEPT where EMP.DEPTNO = DEPT.DEPTNO GROUP BY DNAME,EMP.DEPTNO;

36.查询各部门中最高薪水的员工编号，姓名… 
select empno , ename,DEPTNO from emp WHERE SAL in (select “MAX”(SAL) from EMP GROUP BY DEPTNO);

37.查询所有员工姓名中包含‘A’的最高薪水 
SELECT “MAX”(SAL) FROM EMP WHERE ENAME LIKE ‘%A%’ ;

38.查询各岗位的最高薪水，最低薪水。要求只统计薪水>1000的 
SELECT “MAX”(SAL),”MIN”(SAL) FROM EMP WHERE SAL > 1000 GROUP BY “JOB”;

39.查询各部门的平均薪水及部门编号，要求只列出平均薪水>2000 
SELECT AVG(SAL),DEPTNO FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > 2000;

40.查询各部门的平均薪水及部门编号，要求只有员工姓名中包含 
‘A’才参与统计，只列出平均薪水>1500的，按照平均薪水降序排列 
SELECT AVG(SAL),DEPTNO FROM EMP where ENAME LIKE ‘%A%’ GROUP BY DEPTNO HAVING AVG(SAL) > 1500 ORDER BY AVG(SAL) DESC;

41.查询各部门最高薪水的员工信息（有坑）（薪水和部门编号都需要匹配） 
select * from EMP where (deptno,SAL) in (select deptno,MAX(sal) from emp GROUP BY DEPTNO);

42.查询最高薪水的员工信息 
SELECT * FROM EMP WHERE SAL = (SELECT “MAX”(SAL) FROM EMP);

43.查询薪水大于该部门平均薪水的员工信息 
select * from emp e1 where sal > ( 
select avg(sal) from emp e2 where e1.deptno = deptno 
);

44.查询最高薪水的员工信息 
SELECT * FROM EMP WHERE SAL = (SELECT “MAX”(SAL) FROM EMP); 
45.查询各部门最高薪水的员工信息 
select * from EMP where (deptno,SAL) in (select deptno,MAX(sal) from emp GROUP BY DEPTNO);

46.查询‘SMITH’的领导姓名 
select ENAME from EMP where EMPNO = (select MGR from EMP where ENAME = ‘SMITH’) ;

47.查询部门名称是‘SALES’的员工信息 
select * from EMP where DEPTNO = (SELECT DEPTNO FROM DEPT WHERE dname = ‘SALES’) ;

48.查询公司中薪水最高的员工信息 
select * from EMP where SAL = (SELECT MAX(SAL) FROM EMP) ;

49.查询薪水等级为4的员工信息 
select * from emp where sal between 
(select LOSAL from salgrade where grade = 4) AND 
(select HISAL from salgrade where grade = 4) ;

50.查询领导者是‘BLAKE’的员工信息 
select * from emp where MGR = (select EMPNO from emp where ENAME = ‘BLAKE’);

51.查询最高领导者的薪水等级 
select grade FROM SALGRADE where (SELECT SAL from emp where mgr is NULL) BETWEEN LOSAL AND HISAL;

52.查询薪水最低的员工信息 
select * from emp where SAL = (select MIN(sal) from emp);

53.查询和SMITH工作相同的员工信息 
select * from emp where JOB = (select JOB from emp where ENAME = ‘SMITH’) ;

54.查询不是领导的员工信息 
select * from emp where EMPNO not in (SELECT “NVL”(MGR, 0) FROM EMP) ;

select * from emp e1 where not EXISTS 
(select * from emp e2 where e2.mgr = e1.empno);

55.查询平均工资比10部门低的部门编号 
select deptno from emp group by deptno having avg(sal) < 
(select avg(sal) from emp where deptno = 10);

56.查询在纽约工作的所有员工 
select * from EMP where DEPTNO = (select DEPTNO from DEPT where LOC = ‘NEW YORK’);

57.查询‘SALES’部门平均薪水的等级 
select grade from SALGRADE where 
(select AVG(SAL) from emp where DEPTNO = 
(select DEPTNO FROM DEPT WHERE dname = ‘SALES’) ) BETWEEN LOSAL AND HISAL;

58.查询10号部门的员工在整个公司中所占的比例： 
select (select COUNT(DEPTNO) from EMP where DEPTNO = 10) / (select COUNT(*) from EMP) FROM dual;

59.每页显示5条。 
显示第一页内容： 
select rownum rn,EMP.* from emp where rownum <= 5； 
显示第二页的内容： 
select * from 
(select rownum rn,EMP.* from emp where rownum <= 10) 
where rn > 5; 
按照薪水降序排列，每页显示5条,显示第二页的内容: 
select emp.* from 
(select rownum rn,t1.* from 
(select * from emp order by sal desc) t1 
where rownum <= 10) emp 
where rn > 5;

60.查询各部门工资大于该部门平均工资的员工信息： 
select * from emp e1 where sal > ( 
select avg(sal) from emp e2 where e1.deptno = deptno 
);

61.查询各岗位工资小于该岗位平均工资的员工信息； 
select * from emp e1 where sal < (select avg(sal) from emp e2 where e1.job= job group by job);

62.查询所有领导的信息：要求使用exists关键字 
select * from emp e1 where EXISTS 
(select * from emp e2 where e2.mgr = e1.empno);

63.查询所有员工的姓名，薪水，部门名称 
select ename,sal,dname from emp,dept where EMP.DEPTNO = DEPT.DEPTNO;

64.查询所有员工的姓名，薪水，部门名称,薪水等级 
select ename,sal,dname,grade from emp,dept,SALGRADE 
where EMP.DEPTNO = DEPT.DEPTNO and SAL BETWEEN LOSAL AND HISAL;

65.查询员工姓名及领导者姓名 
select a.ename AS 员工姓名 ,b.ename AS 领导姓名 
from emp a LEFT JOIN emp b on a.mgr = b.empno;

66.查询所有员工的姓名，部门名称 
select ename, dname from emp ,dept where EMP.deptno = DEPT.deptno;

练习题：

1.查询员工表中工资大于1600的员工的姓名和工资 
select ename,sal from emp where sal > 1600;

2.查询员工表中员工号是17的员工的姓名和部门编号 
select ename,deptno from emp where empno = 17;

3.选择员工表中工资不在4000到5000内的员工的姓名和工资 
select ename,sal from emp where sal not BETWEEN 4000 and 5000;

4.选择员工表中在20和30部门工作的员工的姓名和部门号 
select ename,deptno from emp where deptno in (20,30);

5.选择员工表中没有管理者的员工姓名及职位，按职位排序 
select ename,job from emp where mgr is null ORDER BY job asc;

6.选择员工表中有奖金的员工姓名，工资和奖金，按工资倒序排列 
select ename,sal,comm from emp where comm is not null ORDER BY sal asc;

7.选择员工表中员工姓名的第三个字母是A的员工姓名 
select ename from emp where ename like ‘__A%’;

8.列出部门表中的部门名称和所在城市 
select dname,loc from dept ;

9.显示员工表中的不重复的岗位job 
select DISTINCT job from emp ;

10.连接员工表中的员工姓名、职位、薪水，列之间用逗号连接，列头显示成out_put 
select ename ||’,’|| job ||’,’|| sal AS ename_job_sal from emp ;

11.查询员工表中员工号，姓名，工资，以及工资提高百分之20之后的结果 
select empno ,ename,sal ,sal*1.2 from emp ;

12.查询员工的姓名和工资数，条件限定为工资数必须大于1200，并且查询结果按入职时间进行排序。早入职的员工排在前面 
select empno ,sal,HIREDATE from emp where sal > 1200 ORDER BY HIREDATE ;

13.列出除了ACCOUNTING部门之外还有什么部门 
select dname from dept where dname != ‘ACCOUNTING’;

14.把雇员按部门分组，求最高薪水，部门号 要求过滤掉名字中第二个字母是’A’的员工， 并且部门的平均薪水 > 3000,按照部门编号倒序排列 
select MAX(sal),deptno from emp where ename not like ‘_A%’ GROUP BY deptno having avg(sal) > 3000 ;

15.求工作职位是’manager’的员工姓名，部门名称和薪水等级 
select ename ,dname,grade from emp,dept,salgrade 
where job = ‘MANAGER’ and (sal BETWEEN LOSAL and HISAL) 
and EMP.DEPTNO = DEPT.DEPTNO;

按照部门分组统计，求最高薪水，平均薪水，最低薪水，只有薪水是1200以上的员工才参与统计，并且分组结果中只包含平均薪水在1500以上的部门，并且按照平均薪水倒序排列 
select max(sal),min(sal),avg(sal) from emp where sal > 1200 
group by deptno having avg(sal) > 1500 order by avg(sal) DESC;
17.求薪水最高的员工姓名 
select ename from emp where sal = (select max(sal) from emp);

18.查询各部门平均薪水等级，并且按平均薪水等级的降序排列 
select grade from salgrade s join 
(select avg(sal) avg_sal from emp e group by deptno) temp 
on TEMP.avg_sal between s.LOSAL and s.HISAL;

19.查询所有员工姓名以S或s开头的所有员工信息 
select * from emp where ename like ‘S%’ or ename like ‘s%’;

20.查询所有工作时间超过一年的员工编号，姓名及入职时间,要求雇用时间的格式为’yyyy年mm月dd日’ 
select empno,ename,TO_CHAR(HIREDATE,’yyyy”年”MM”月”dd”日”’) from emp where 
TO_CHAR(SYSDATE,’YYYY’) - TO_CHAR(hiredate,’YYYY’) > 1;

21.查询20部门的所有员工的员工姓名，实际收入 
select ename,sal+NVL(comm, 0) from emp where deptno = 20 ;

22.查询10部门工资大于3000的员工信息，要求按员工的入职时间由前到后排序 
select * from emp where deptno = 10 and sal > 3000 ORDER BY HIREDATE ;

23.查询10部门或20部门的所有员工的姓名，并截取前三位，按员工姓名升序排列 
select SUBSTR(ename, 1, 3) from emp where deptno in (20,10) ORDER BY ename ;

24、查询所有员工的姓名，要求所有员工的姓名显示成小写，雇用日期显示为”yyyy-mm-dd”这种格式，薪水转换成’99,999.999′这种格式selectlower(ename),TOCHAR(HIREDATE,′YYYY−MM−DD′),TOCHAR(sal,′99,999.999′这种格式selectlower(ename),TOCHAR(HIREDATE,′YYYY−MM−DD′),TOCHAR(sal,′99,999.999’) from emp ;

25、查询所有员工的姓名，所在部门名称，薪水，薪水等级、直接领导的姓名 （有问题，不显示最高领导） 
select e1.ename,DEPT.dname,e1.sal,grade,e2.ename AS leader from emp e1,emp e2,dept,salgrade 
where nvl(e1.MGR,0) = e2.empno and (e1.sal between SALGRADE.LOSAL and SALGRADE.HISAL) 
and e1.deptno = dept.deptno;

26、查询部门名称是’ACCOUNTING’的员工姓名及薪水等级 
select ename,grade from emp ,salgrade ,dept where 
dname=’ACCOUNTING’ and sal between LOSAL and HISAL 
and EMP.deptno = DEPT.deptno ;

27、不能使用组函数，查询薪水的最高值 
select sal from emp where sal >= all (select sal from emp);

28、统计平均薪水最高的部门名称

29、查询平均薪水等级最低的部门名称

选做

1、查询平均薪水最低的部门名称，要求：只有领导才参加统计 
2、查询比普通员工的最高薪水还要高的领导者姓名 
3、找出薪水最高的五个人 
4、查询第2到第7名的员工，按薪水降序排列 
5、查询最后入职的5名员工