编码妙♂妙♂屋
首页 > 经验记录 > 查询练习..（鬼知道我经历了什么）

查询练习..（鬼知道我经历了什么）

PartI的是一些简单的限定查询，就不贴了
 

--PartII
SELECT * FROM emp_pyb;
SELECT * FROM dept;
--01 .查询各个部门的平均工资
SELECT deptno,AVG(sal)
FROM emp_pyb
GROUP BY deptno
--02.显示各种职位的最低工资
SELECT job,MIN(sal)
FROM emp
GROUP BY job;
--03．按照入职日期由新到旧排列员工信息
SELECT *
FROM emp
ORDER BY hiredate DESC;
--04查询员工的基本信息，附加其上级的姓名（自关联）
SELECT e.*,e2.ename 上司名字
FROM emp e,emp e2
WHERE  e.mgr=e2.empno;
--05显示工资比’ALLEN’高的所有员工的姓名和工作
SELECT ename,job,sal
FROM emp_pyb
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,job
FROM emp
WHERE sal=(SELECT sal FROM emp WHERE ename='MARTIN') AND ename<>'MARTIN';
--09．查询所有工资高于平均工资（包括所有员工）的销售人员
SELECT *
FROM emp
WHERE sal>(SELECT AVG(NVL(sal,0)) FROM emp) AND job='SALESMAN';
--10．显示所有职员的姓名及其所在部门的名称和工资(表连接)
SELECT e.ename,e.sal,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno;
--11．查询在研发部(RESEARCH)工作人员的编号，姓名，工作部门，工作所在地
SELECT e.empno,e.ename,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno AND d.dname='RESEARCH';
--12．查询各个部门的名称和员工人数
SELECT d.dname,COUNT(*)
FROM emp e,dept d
WHERE e.deptno=d.deptno GROUP BY d.dname;
--看不懂题目意思
--13．查询各个部门员工工资大于平均工资（平均工资包括所有员工）的人数和员工职位(子查询)
SELECT COUNT(*),deptno
FROM emp_pyb
WHERE sal>(SELECT AVG(NVL(sal,0)) FROM emp)
GROUP BY deptno;
--14．查询工资相同的员工的工资和姓名（子查询）
SELECT ename,sal
FROM emp e
WHERE sal in (SELECT sal FROM emp WHERE e.ename<>ename AND e.sal=sal);
--15．查询工资最高的3名员工信息(排序)
SELECT *
FROM emp e,(SELECT empno,DENSE_RANK() OVER (ORDER BY sal DESC) xuhao FROM emp) p
WHERE e.empno=p.empno AND  p.xuhao in (1,2,3)
ORDER BY p.xuhao;
--16．按工资进行排名：排名从1开始，工资相同排名相同（如果两个并列第1则没有地2名，从第三名继续排）
SELECT RANK() OVER (ORDER BY sal DESC) AS 排名,emp.* FROM emp;
--17．求入职日期相同的（年月日相同）的员工
SELECT *
FROM emp_pyb e
WHERE hiredate IN (SELECT hiredate FROM emp WHERE e.ename<>ename AND e.hiredate=hiredate);
--18．查询每个部门的最高工资
SELECT deptno,MAX(sal)
FROM emp_pyb
GROUP BY deptno
--19．查询每个部门，每个职位的最高工资
SELECT deptno,job,MAX(sal) FROM emp GROUP BY deptno,job
ORDER BY deptno;
--20．查询每个员工的信息及工资级别，用到表(Salgrade)
SELECT * FROM salgrade;
SELECT e.*,s.grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;
--21.查询工资最高的第6-10名员工
SELECT *
FROM emp e,(SELECT empno,ROW_NUMBER() OVER (ORDER BY sal DESC) xuhao FROM emp) p
WHERE e.empno=p.empno AND  p.xuhao in (6,7,8,9,10)
ORDER BY p.xuhao;
--22．查询各个部门工资最高的员工信息
SELECT * FROM emp
WHERE sal in (SELECT MAX(sal) FROM emp e GROUP BY deptno)
--23．查询每个部门工资最高的前2名员工
SELECT * FROM (
SELECT e.*,ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) xuhao FROM emp e
) WHERE xuhao<3
--24．查询出有3个以上下属的员工信息（自关联）
SELECT e.*,e2.下属人数 FROM emp e,
(SELECT e2.ename,COUNT(*) 下属人数 FROM emp e1,emp e2 WHERE e1.mgr=e2.empno GROUP BY e2.ename) e2
WHERE e.ename=e2.ename AND e2.下属人数>=3
--25．查询所有大于本部平均工资的员工信息
SELECT e1.*,e2.avg_sal 本部平均工资 FROM emp e1,
(SELECT AVG(sal) avg_sal,deptno FROM emp GROUP BY deptno) e2
WHERE e1.deptno=e2.deptno AND e1.sal>e2.avg_sal
--26．查询平均工资最高的部门信息
SELECT *
FROM dept
WHERE deptno=(
SELECT deptno FROM (
       SELECT 平均工资,deptno,ROW_NUMBER() OVER(ORDER BY 平均工资 DESC) 排序
       FROM (SELECT AVG(sal) 平均工资,deptno FROM emp GROUP BY deptno)
)
WHERE 排序=1)
--27．查询大于 各个部门总工资 的 平均值 的部门信息
SELECT * FROM dept WHERE deptno=(
      SELECT deptno FROM emp GROUP BY deptno HAVING SUM(sal)>=(
             SELECT AVG(总) 平均 FROM(
                    SELECT sum(sal) 总,deptno FROM emp GROUP BY deptno
              )
       )
)
--28．查询大于各个部门总工资平均值的部门下的员工信息
SELECT * FROM emp WHERE deptno=
(SELECT deptno FROM emp GROUP BY deptno HAVING SUM(sal)>=(
       SELECT AVG(总) 平均 FROM(
              SELECT sum(sal) 总,deptno FROM emp GROUP BY deptno
               )
       )
)
--29．查询么有员工的部门信息
SELECT * FROM dept WHERE dname=(
       SELECT dname FROM (
              SELECT * FROM emp e,dept d
              WHERE e.deptno(+)=d.deptno
       ) WHERE ename IS NULL
)

 
这尼玛难得一批，PartII有的题目是真的恶心，PartIII的简单的太多了
 

--partIII
--1、列出至少有一个雇员的所有部门信息（嵌套子查询）
SELECT * FROM dept WHERE deptno in (SELECT deptno FROM emp)
--2、列出薪金(工资)比'SMITH'多的所有雇员信息（嵌套子查询）
SELECT * FROM EMP WHERE sal>(
SELECT sal FROM emp WHERE ename='SMITH')
--3、列出所有雇员的姓名及其上级的姓名（自表外连接）
SELECT e1.ename 员工,e2.ename 上司 FROM emp e1,emp e2 WHERE e1.mgr=e2.empno
--4、列出入职日期(雇佣日期)早于其直接上级的所有雇员（自表连接）
SELECT e1.* FROM emp e1,emp e2 WHERE e1.mgr=e2.empno AND e1.hiredate(
       SELECT AVG(sal) FROM emp
)
--5、列出部门名称和这些部门的雇员，同时列出那些没有雇员的部门（外连接）
SELECT * FROM emp e,dept d WHERE e.deptno(+)=d.deptno
--6、列出所有'CLERK'(办事员)的姓名及其部门名称（内连接）
SELECT e.*,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno AND e.job='CLERK';
--7、列出各种工作的最低薪金，并使最低薪金大于1500（分组和过滤）
SELECT MIN(sal),job FROM emp GROUP BY JOB
--8、列出从事sales(销售)工作的雇员的姓名，假定不知道销售部的部门编号（使用嵌套子查询进行连接）
SELECT ename FROM emp WHERE deptno=(
       SELECT deptno FROM dept WHERE dname='SALES'
)
--9、列出薪金高于公司平均水平的所有雇员信息（聚合函数）
SELECT * FROM emp WHERE sal>(
       SELECT AVG(sal) FROM emp
)
--10、列出与"SCOTT"从事相同工作的所有雇员（嵌套自查询）
SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='SCOTT') AND ename<>'SCOTT';
--11、列出薪金等于在部门30工作的雇员的所有姓名和薪金（有问题）
SELECT ename,sal FROM emp WHERE sal in
(SELECT sal FROM emp WHERE deptno=30) AND deptno<>30;
--12、列出薪金高于在部门30工作的所有雇员的姓名和薪金（嵌套子查询）
SELECT ename,sal FROM emp WHERE sal >ALL
(SELECT sal FROM emp WHERE deptno=30)
--13、列出每个部门的信息（外连接）以及该部门中（分组）雇员的数量（外连接并分组）
SELECT d1.*,NVL(d2.人数,0) 人数 FROM dept d1,(
SELECT COUNT(*) 人数,deptno FROM (
SELECT d.*
FROM emp e,dept d
WHERE e.deptno=d.deptno)
GROUP BY deptno) d2
WHERE d1.deptno=d2.deptno(+)
--14、列出所有雇员的姓名、部门名称和薪金(外连接)
SELECT e.ename,d.dname,e.comm
FROM emp e,dept d
WHERE e.deptno=d.deptno
--15、列出从事同一种工作但属于不同的部门的雇员的不同组合(自表笛卡尔积)
SELECT * FROM emp e1,emp e2 WHERE e1.ename<>e2.ename AND e1.empno<>e2.empno AND e1.job=e2.job
--16、列出分配有雇员数量（聚合函数）的所有部门（按部门分组并进行外连接）的详细信息，即使分配有零个雇员,这求出的是至少有一名雇员的部门信息.
SELECT d1.*,NVL(d2.人数,0) 人数 FROM dept d1,(
SELECT COUNT(*) 人数,deptno FROM (
SELECT d.*
FROM emp e,dept d
WHERE e.deptno=d.deptno)
GROUP BY deptno) d2
WHERE d1.deptno=d2.deptno(+)
--17、列出各种类别工作（分组）的最低工资
SELECT MIN(sal),job FROM emp GROUP BY job
--19、列出按计算字段的排序的所有雇员的年薪(计算字段指的是年薪)
SELECT sal*12+NVL(comm,0)*12 FROM emp ORDER BY sal*12+NVL(comm,0)*12 DESC
--20、列出薪金水平处于第四位的雇员信息（多重嵌套查询）
SELECT e.* FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal AND s.grade=4;
--21、查询各部门薪水最高的员工
SELECT * FROM emp WHERE sal in(
SELECT MAX(sal) FROM emp GROUP BY deptno);
           


CAPTCHAis initialing...
EA PLAYER &

历史记录 [ 注意：部分数据仅限于当前浏览器 ]清空

      00:00/00:00