Oracle SQL：经典查询练手第一篇
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle SQL：经典查询练手第一篇，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7007字，纯文字阅读大概需要11分钟。
内容图文
使用Oracle的Scott用户
表结构如下：
emp表
dept表
工资=薪金+佣金
工资=sal+comm
表中数据如下：
emp:
dept:
问题列表：
用SQL完成以下问题列表：
- 列出至少有一个员工的所有部门。
- 列出薪金比“SMITH”多的所有员工。
- 列出所有员工的姓名及其直接上级的姓名。
- 列出受雇日期早于其直接上级的所有员工。
- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
- 列出所有“CLERK”(办事员)的姓名及其部门名称。
- 列出最低薪金大于1500的各种工作。
- 列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。
- 列出薪金高于公司平均薪金的所有员工。
- 列出与“SCOTT”从事相同工作的所有员工。
- 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
- 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
- 列出在每个部门工作的员工数量、平均工资和平均服务期限。
- 列出所有员工的姓名、部门名称和工资。
- 列出所有部门的详细信息和部门人数。
- 列出各种工作的最低工资。
- 列出各个部门的MANAGER(经理)的最低薪金。
- 列出所有员工的年工资,按年薪从低到高排序。
我的答案如下，欢迎指正：
-- 1.列出至少有一个员工的所有部门 -- 先查出有员工的部门编号 select DISTINCT deptno from emp; -- 部门编号在里面就满足 select dname from dept where deptno in (select DISTINCT deptno from emp);
DNAME -------------- ACCOUNTING RESEARCH SALES
-- 2.列出薪金比“SMITH”多的所有员工 -- 先查询出‘SMITH’的薪金 SELECT SAL FROM emp WHERE ENAME =‘SMITH‘; -- SAL取值大于SMITH的薪金即可 -- SAL是数字 NUMBER(7,2) 可以直接比较 SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM emp WHERE ENAME =‘SMITH‘);
1 -- 3.列出所有员工的姓名及其直接上级的姓名 2 -- empno等于mgr 3 SELECT e.ename,(select ename from emp ee where ee.EMPNO=e.MGR) superior FROM emp e; 4 5 ENAME SUPERIOR 6 ---------- ---------- 7 SMITH FORD 8 ALLEN BLAKE 9 WARD BLAKE 10 JONES KING 11 MARTIN BLAKE 12 BLAKE KING 13 CLARK KING 14 SCOTT JONES 15 KING 16 TURNER BLAKE 17 ADAMS SCOTT 18 JAMES BLAKE 19 FORD JONES 20 MILLER CLARK 21 22 选定了 14 行 23 24 -- 没有思路 待议
1 -- 4.列出受雇日期早于其直接上级的所有员工 2 -- hiredate直接用大于小于比较 3 select e.ENAME from emp e where e.HIREDATE< (SELECT HIREDATE FROM emp ee where ee.EMPNO=e.mgr); 4 5 ENAME 6 ---------- 7 SMITH 8 ALLEN 9 WARD 10 JONES 11 BLAKE 12 CLARK 13 14 选定了 6 行
-- 没有思路 待议
1 -- 5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门 2 -- 为了列出没有员工的部门 可以使用左外连接 以部门表为基准 部门表记录全部显示出来 而不管员工表有不有对应的记录 3 select d.DNAME,e.ENAME,e.HIREDATE,e.JOB,e.MGR,e.HIREDATE,e.SAL from dept d left outer join emp e on d.deptno = e.deptno;
1 -- 6.列出所有“CLERK”(办事员)的姓名及其部门名称 2 -- 先查询出CLERK的姓名和部门编号 3 SELECT e.JOB,e.ENAME,e.DEPTNO FROM EMP e where JOB = ‘CLERK‘; 4 -- 再和部门表做关联 使用子查询 5 select aa.ename,aa.job,bb.dname from (SELECT e.JOB,e.ENAME,e.DEPTNO FROM EMP e where JOB = ‘CLERK‘) aa ,DEPT bb where aa.deptno= bb.deptno; 6 7 -- 还有一种方式 就是做内连接 8 select e.ENAME,d.DNAME from emp e join dept d on e.deptno=d.deptno where e.JOB=‘CLERK‘; 9 10 ENAME DNAME 11 ---------- -------------- 12 MILLER ACCOUNTING 13 SMITH RESEARCH 14 ADAMS RESEARCH 15 JAMES SALES
1 -- 7.列出最低薪金大于1500的各种工作 2 -- 先根据工作来分组 然后再此基础上过滤sal>1500 每组里面最低工资而不是工资大于1500 3 select job from emp group by job having(min(sal))>1500;
1 -- 8.列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号 2 -- 先找出销售部门编号 3 select d.deptno from dept d where d.DNAME = ‘SALES‘; 4 -- 再根据编号过滤 5 select e.ename from emp e where e.DEPTNO = (select d.deptno from dept d where d.DNAME = ‘SALES‘); 6 7 ENAME 8 ---------- 9 ALLEN 10 WARD 11 MARTIN 12 BLAKE 13 TURNER 14 JAMES 15 16 选定了 6 行
1 -- 9.列出薪金高于公司平均薪金的所有员工 2 -- 先算出平均薪金 3 select AVG(sal) from emp; 4 -- 再做比较 5 select e.ENAME from emp e where e.SAL > (select AVG(sal) from emp); 6 7 ENAME 8 ---------- 9 JONES 10 BLAKE 11 CLARK 12 SCOTT 13 KING 14 FORD 15 16 选定了 6 行
1 -- 10.列出与“SCOTT”从事相同工作的所有员工 2 -- 先算出SCOTT的工作 3 select JOB from emp where ENAME =‘SCOTT‘; 4 -- 再做过滤 5 select e.ENAME from emp e where e.job = (select JOB from emp where ENAME =‘SCOTT‘) 6 7 ENAME 8 ---------- 9 SCOTT 10 FORD
1 -- 11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金 2 -- 先算出部门编号为30的员工的薪金 3 select e.sal from emp e where e.deptno = 30; 4 -- 然后过滤条件sal在上面的薪金中 5 -- 同时部门编号不等于30 开始我没有加此限制条件 查询出来的结果全是部门30的人 应该就是答案 但是答案过滤掉了部门30 不知道其意思 6 select * from emp ee where ee.sal in (select e.sal from emp e where e.deptno = 30) and ee.DEPTNO<>30;
-- 12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金 -- 先查出部门30最高薪金 select max(e.sal) from emp e where e.deptno=30; -- 再过滤 此处没有过滤掉部门30 不懂 select * from emp ee where ee.sal>(select max(e.sal) from emp e where e.deptno=30);
1 -- 13.列出在每个部门工作的员工数量、平均工资和平均服务期限 2 -- 先算出部门名称 3 -- 再算部门人数 4 -- 再算平均服务期限以天为标准 to_date(sysdate) 5 -- 根据deptno分组再使用子查询 select (select d.dname from dept d where d.DEPTNO=e.deptno) deptname,count(1) deptcount,avg(sal) avgsalary,avg(TO_DATE(sysdate)-TO_DATE(e.hiredate)) avgdate from emp e group by e.deptno;
1 -- 14.列出所有员工的姓名、部门名称和工资 2 -- 我使用了左外连接 3 select e.ENAME,d.DNAME,e.SAL from emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;
4 -- 也可以使用子查询
5 select e.ENAME,(select d.dname from DEPT d where d.DEPTNO=e.DEPTNO),e.SAL from emp e;
1 -- 15.列出所有部门的详细信息和部门人数 2 -- 使用左外链接失败 3 -- 使用子查询算出部门人数 没思路 4 select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a;
1 -- 16.列出各种工作的最低工资 2 -- 先根据工作分组 3 -- 求最低 4 select min(sal),JOB from emp group by job;
1 -- 17.列出各个部门的MANAGER(经理)的最低薪金 2 -- 先找出job是manager的 3 -- 在根据deptno分组 4 -- 在计算最小值 5 select e.deptno,min(e.SAL) from emp e where e.job=‘MANAGER‘ group by e.deptno; 6 7 DEPTNO MIN(E.SAL) 8 ---------- ---------- 9 30 2850 10 20 2975 11 10 2450
1 -- 18.列出所有员工的年工资,按年薪从低到高排序 2 -- 年工资等于 sal+comm 3 -- 有的没有comm 空值 4 -- 使用nvl(comm,0) 给comm一个预设值 如果comm为null 则返回0 否则原样返回 5 select e.ENAME,(e.sal+nvl（e.COMM,0）)*12 annual_salary from emp e order by annual_salary; 6 7 ENAME ANNUAL_SALARY 8 ---------- ------------- 9 SMITH 9600 10 JAMES 11400 11 ADAMS 13200 12 MILLER 15600 13 TURNER 18000 14 WARD 21000 15 ALLEN 22800 16 CLARK 29400 17 MARTIN 31800 18 BLAKE 34200 19 JONES 35700 20 FORD 36000 21 SCOTT 36000 22 KING 60000 23 24 选定了 14 行
原文博客地址:http://database.51cto.com/art/201106/270214.htm
Oracle SQL：经典查询练手第一篇
标签：左外连接 计算 直接 外连接 super ada 意思 out date
本文系统来源：http://www.cnblogs.com/qingkongwanli/p/7373688.html
内容总结
以上是互联网集市为您收集整理的Oracle SQL：经典查询练手第一篇全部内容，希望文章能够帮你解决Oracle SQL：经典查询练手第一篇所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】