ORACLE查询练习题

--01.查询员工表所有数据，并说明使用*的缺点
--会将所有数据显示，数据过于庞大，不直观
--01．查询职（job）为'PRESIDENT'的员工的工资 
 Select ename,job,sal from emp
				Where job=’PRESIDENT’;
        
--02．查询佣金为0或为null的员工的信息 
Select * from emp where comm=0 or comm is null;

--03．查询入职日期在1981-5-1到1981-12-31之间的所有员工的信息 
Select * from emp 
Where
hiredate >to_date(‘1981-5-1’,’yyyy-mm-dd’)
and
hiredate<=to_date(‘1981-12-31’,’yyyy-mm-dd’);

--04．.查询所有名字长度为4的员工的员工的编号，姓名 
Select ename,empno from emp 
Where enaem like ‘____’;

--05．显示10号部门饿所有经理和20号部门的所有员工 
Select ename,job,deptno from emp
Where (deptno=10 and job=’MANAGER’ OR DEPTNO=20);

--06．显示姓名没有'L'字的员工的详细信息或含有'SM'字的员工信息 
Select ename from emp 
Where
 (ename not like ‘%L%’)
 Or
 (ename like %SM%);
 
--07．显示各个部门经理的工资 
Select * from emp where job=’MANAGER’;

--08．显示佣金收入比工资高的员工的详细信息 
Select * from emp where comm>sal;

--10．把hiredate列看做是员工的生日，求本月过生日的员工： 
Select ename,hiredate from emp 
Where extract(month from hriedate)=extract(month from sysdate)-1;

--11．把hiredate列看做是员工的生日，求下月过生日的员工 
Where extract(month from hriedate)=extract(month from sysdate)+1;

--12.求1982年入职的员工 
Select * from emp where extract(year from hiredate)=’1982’;

--13．求1981年下半年入职的员工 
Select * from emp where 
hiredate > to_date(‘1981-6-1’,’yyyy-mm-dd’)
and
hiredate<to_date(‘1981-12-31’,’yyyy-mm-dd’);

--14．求1981年各个月入职的员工个数 
select count(*),to_char(hiredate,’mm’) from emp
where to_char(hiredate,'yyyy')=1981 group by to_char(hiredate,'mm')
Select * from emp 


--PartII 
--01 .查询各个部门的平均工资 
SELECT deptno,ROUND(avg(sal),2) avg
FROM emp
GROUP BY deptno

--02.显示各种职位的最低工资 
SELECT job,min(sal)
FROM emp
GROUP BY job

--03．按照入职日期由新到旧排列员工信息 
SELECT *
FROM emp
ORDER BY hiredate desc

--04．查询员工的基本信息，附加其上级的姓名（自关联）
SELECT e.*,d.ename
FROM emp e,emp d
WHERE e.mgr=d.empno(+)

--05．显示工资比’ALLEN’高的所有员工的姓名和工作 
SELECT ename,job,sal
FROM emp
WHERE sal>
(SELECT sal
FROM emp 
WHERE ename='ALLEN')

--06．显示与scott从事相同工作的员工的信息（子查询） 
SELECT * 
FROM emp
WHERE job=
  (SELECT job
  FROM emp
  WHERE ename='SCOTT')
  AND ename<>'SCOTT'

--07．显示销售部(‘SALES’)员工的姓名 
SELECT ename
FROM emp
WHERE deptno=(
SELECT deptno
FROM dept
WHERE dname='SALES')

--08．显示与30号门’MARTIN’员工工资相同的员工的姓名和工资 
SELECT ename,sal
FROM emp
WHERE sal=(
SELECT sal
FROM emp
WHERE deptno=30 
AND ename='MARTIN')

--09．查询所有工资高于平均工资（包括所有员工）的销售人员 
SELECT ename,sal
FROM emp
WHERE job='ANALYST' AND sal>
(SELECT MAX(avg(sal))
FROM emp
GROUP BY deptno)

--10．显示所有职员的姓名及其所在部门的名称和工资(表连接) 
SELECT e.ename,e.sal,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno

--11．查询在研发部(RESEARCH)工作人员的编号，姓名，工作部门，工作所在地 
SELECT d.deptno,e.ename,d.dname,d.loc
FROM dept d,emp e
WHERE d.dname='RESEARCH'
AND e.deptno=d.deptno
select * from dept
--12．查询各个部门的名称和员工人数 
SELECT d.dname,temp.count
FROM dept d,(
SELECT deptno dno,COUNT(empno) count
FROM emp GROUP BY deptno ) temp
WHERE d.deptno=temp.dno

--13．查询各个部门员工工资大于平均工资（平均工资包括所有员工）的人数和员工职位(子查询)
 --有问题，读不懂
 SELECT d.dname,e.job,tmp.con
 FROM emp e,dept d,(SELECT job j,COUNT(empno) con FROM emp GROUP BY job) tmp
 WHERE sal>
 (SELECT ROUND(AVG(sal),2)
 FROM emp)
 AND tmp.j=e.job
 AND d.deptno=e.deptno;
 --select count(*),job from emp where sal>(select avg(sal) from emp) group by job; 

--14．查询工资相同的员工的工资和姓名（子查询） 
SELECT e.ename,e.sal
FROM emp e
WHERE (SELECT COUNT(*)
FROM emp
WHERE sal=e.sal GROUP BY sal )>1

--15．查询工资最高的3名员工信息(排序) 
SELECT *
FROM 
(SELECT e.* ,
ROW_NUMBER() OVER(ORDER BY sal desc) id 
FROM emp e)
WHERE id<=3

--16．按工资进行排名：排名从1开始，工资相同排名相同
--（如果两个并列第1则没有地2名，从第三名继续排） 
SELECT e.*,RANK()OVER(ORDER BY sal DESC) 排名 
FROM emp e

--17．求入职日期相同的（年月日相同）的员工 
SELECT e.*
FROM emp e
WHERE (
SELECT COUNT(*) 
FROM emp 
WHERE e.hiredate=hiredate 
GROUP BY hiredate )>1

--18．查询每个部门的最高工资 
SELECT e.deptno,MAX(sal)
FROM emp e
GROUP BY deptno;

--19．查询每个部门，每个职位的最高工资 
SELECT deptno,job,MAX(SAL)
FROM emp
GROUP BY deptno,job 
ORDER BY deptno;

--20．查询每个员工的信息及工资级别，用到表(Salgrade) 
SELECT e.*,s.grade
FROM emp e,salgrade s
WHERE e.sal 
BETWEEN s.losal AND s.hisal
SELECT * FROM salgrade;

--21.查询工资最高的第6-10名员工 
结果： 
R SAL 
---------- ----- 
6 2450 
7 1600 
8 1500 
9 1300 
10 1250 
Order by不能和rownum合用，
SELECT * 
FROM (SELECT ROWNUM r,sal
FROM( SELECT sal 
FROM emp ORDER BY sal DESC))
WHERE r BETWEEN 6 AND 10;
 
--22．查询各个部门工资最高的员工信息 
SELECT *
FROM emp 
WHERE sal in (SELECT MAX(sal)
FROM emp
GROUP BY deptno)

--23．查询每个部门工资最高的前2名员工 
SELECT *
FROM 
(SELECT e.*,ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY sal) DES
FROM emp e) 
WHERE DES<3

--24．查询出有3个以上下属的员工信息（自关联） 
SELECT * 
FROM emp e
WHERE(
SELECT COUNT(*)
FROM emp WHERE e.empno=mgr)>=3

--25．查询所有大于本部平均工资的员工信息 
SELECT  * 
FROM emp e
WHERE e.sal >
(SELECT ROUND(AVG(sal),2) AVG
FROM emp 
WHERE e.deptno=deptno)

--26．查询平均工资最高的部门信息 
SELECT d.*,avgsal
FROM dept d,
(SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) e
WHERE avgsal=
(SELECT MAX(AVG(sal)) maxsal FROM emp GROUP BY deptno)
AND d.deptno=e.deptno

--27．查询大于各个部门总工资的平均值的部门信息 
SELECT d.deptno,d.dname,e.avg
FROM dept d,
(SELECT e.deptno dno, AVG(sal) avg FROM emp e GROUP BY deptno) e
WHERE e.avg=
(SELECT MAX(AVG(sal)) maxavg FROM emp GROUP BY deptno)
AND e.dno=d.deptno

--28．查询大于各个部门总工资平均值的部门下的员工信息 
SELECT e.*,d.*
FROM emp e,dept d
WHERE e.sal>
(SELECT MAX(AVG(sal))
FROM emp e
GROUP BY deptno)
AND d.deptno=e.deptno

--29．查询么有员工的部门信息 
SELECT d.*,e.count
FROM dept d,
(SELECT e.deptno dno,COUNT(empno) count
FROM emp e 
GROUP BY deptno)e
WHERE e.dno(+)=d.deptno
AND e.count is null

--partIII
--1、列出至少有一个雇员的所有部门信息（嵌套子查询）
SELECT d.*,e.* FROM dept d,
(SELECT e.deptno dno,e.* FROM emp e)e
WHERE d.deptno=e.dno

--2、列出薪金(工资)比'SMITH'多的所有雇员信息（嵌套子查询）
SELECT e.*
FROM emp e
WHERE e.sal>
(SELECT sal 
FROM emp 
WHERE ename='SMITH' )
 
--3、列出所有雇员的姓名及其上级的姓名（自表外连接）
SELECT e.*,d.empno
FROM emp e,emp d
WHERE e.mgr=d.empno(+)

--4、列出入职日期(雇佣日期)早于其直接上级的所有雇员（自表连接）
SELECT e.*
FROM emp e,emp d
WHERE e.mgr=d.empno
AND e.hiredate<d.hiredate

--5、列出部门名称和这些部门的雇员，同时列出那些没有雇员的部门（外连接）
SELECT e.*,d.*
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno

--6、列出所有'CLERK'(办事员)的姓名及其部门名称（内连接）
SELECT e.ename,e.job,d.dname
FROM emp e,dept d
WHERE e.job='CLERK'
AND d.deptno=e.deptno

--7、列出各种工作的最低薪金，并使最低薪金大于1500（分组和过滤）
SELECT job,MIN(sal)
FROM emp
GROUP BY job having MIN(sal)>1500

--8、列出从事sales(销售)工作的雇员的姓名，假定不知道销售部的部门编号
--（使用嵌套子查询进行连接）
SELECT e.deptno,e.*
FROM dept d,emp e
WHERE dname='SALES'
AND e.deptno=d.deptno;

--9、列出薪金高于公司平均水平的所有雇员信息（聚合函数）
SELECT e.*
FROM emp e
WHERE e.sal>
  (SELECT MAX(AVG(sal))
  FROM emp
  GROUP BY deptno)

--10、列出与"SCOTT"从事相同工作的所有雇员（嵌套自查询）
SELECT e.*
FROM emp e
WHERE e.job=
  (SELECT job
  FROM emp
  WHERE ename='SCOTT')
  AND e.ename<>'SCOTT'

--11、列出薪金等于在部门30工作的雇员的所有姓名和薪金（有问题）

--12、列出薪金高于在部门30工作的所有雇员的姓名和薪金（嵌套子查询）
SELECT e.ename,e.sal
FROM emp e
WHERE e.sal>(SELECT MAX(sal)
FROM emp
WHERE deptno=30)

--13、列出每个部门的信息（外连接）
--以及该部门中（分组）雇员的数量（外连接并分组）
SELECT d.*,e.count
FROM dept d,
(SELECT e.deptno dno,COUNT(deptno) count
FROM emp e
GROUP BY e.deptno)e
WHERE e.dno(+)=d.deptno

--14、列出所有雇员的姓名、部门名称和薪金(外连接)
SELECT e.ename,d.dname,e.sal
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno

--15、列出从事同一种工作但属于不同的部门的雇员的不同组合(自表笛卡尔积)
SELECT e.deptno,d.deptno,e.job,d.job,e.ename,d.ename
FROM emp e,emp d
WHERE e.job=d.job AND e.deptno<>d.deptno AND e.ename<d.ename

--16、列出分配有雇员数量（聚合函数）的所有部门（按部门分组并进行外连接）的详细信息，
--即使分配有零个雇员--这求出的是至少有一名雇员的部门信息.
SELECT d.*,e.count
FROM dept d,
  (SELECT e.deptno,COUNT(e.empno) count
  FROM emp e
  GROUP BY deptno)e
  WHERE e.deptno(+)=d.deptno

--17、列出各种类别工作（分组）的最低工资
SELECT e.job,MIN(sal)
FROM emp e
GROUP BY job

--19、列出按计算字段的排序的所有雇员的年薪(计算字段指的是年薪)
SELECT e.*,sal*12 year
FROM emp e
ORDER BY year DESC

--20、列出薪金水平处于第四位的雇员信息（多重嵌套查询）
SELECT *
FROM
(SELECT d.*,ROW_NUMBER()OVER(ORDER BY d.sal DESC) grade
FROM emp d)
WHERE grade=4


--21、查询各部门薪水最高的员工
SELECT e.*
FROM emp e
WHERE e.sal in
  (SELECT MAX(sal)
  FROM emp
  GROUP BY deptno)