




下载本文档
版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1、1找出佣金高于薪金 60%的雇员。SELECT * FROM emp WHERE comm>sal*;2找出部门10中所有经理和部门 20中所有办事员的详细资料。SELECT * FROM emp WHERE deptno=10 AND JOB='MANAGER' OR deptno=20 AND job='CLERK'3找出部门10中所有经理,部门 20中所有办事员以及既不是经理又不是办事员但其薪金 大于或等2000的所有雇员的详细资料。SELECT * FROM emp WHERE deptno=10 AND JOB='MANAGER'
2、 OR deptno=20 AND job='CLERK' OR JOB NOT IN('MANAGER','CLERK') AND SAL>=2000;SELECT * FROM emp WHERE deptno=10 AND JOB='MANAGER' OR deptno=20 AND job='CLERK' OR (JOB<>'MANAGER' AND JOB<>'MANAGER' AND SAL>=2000);4找出收取佣金的雇员的不同工作
3、。SELECT DISTINCT JOB FROM EMP WHERE COMM IS NOT NULL;5找出不收取佣金或收取的佣金低于300的雇员。SELECT * FROM EMP WHERE COMM IS NULL OR COMM<300;6找出各月最后一天受雇的所有雇员。SELECT * FROM EMP WHERE HIREDATE=LAST_DAY(HIREDATE);-找出各月最后受雇的所有雇员SELECT* FROM emp WHERE hiredate IN (SELECTmaxh FROM (SELECTMAX(HIREDATE) maxh,EXTRACT(MON
4、TH FROM hiredate)FROM EMP GROUP BY EXTRACT(MONTH FROM hiredate);7找出晚于26年之前受雇的雇员。SELECT * FROM emp WHERE months_between(SYSDATE,hiredate)<=26*12;8显示只有首字母大写的的所有雇员的姓名。SELECT * FROM emp WHERE ename=initcap(ename);9显示正好为5个字符的雇员的姓名。SELECT * FROM emp WHERE length(ename)=5;10显示不带有“ R”的雇员姓名。SELECT * FROM
5、emp WHERE instr(ename,'R')=0;SELECT * FROM emp WHERE ename NOT LIKE '%R%'11显示所有雇员的姓名的前三个字符。SELECT substr(ename,1,3) AS en3 FROM emp;12显所有雇员的姓名,用 a替换所有“ A”。SELECT REPLACE(ename,'A','a') FROM emp;13显示所有雇员的姓名以及满10年服务年限的日期。SELECT ENAME,HIREDATE,ADD_MONTHS(HIREDATE,120) AS
6、 H_10Y FROM EMP;14显示雇员的详细资料,按姓名排序。SELECT * FROM EMP ORDER BY ENAME;15显示雇员姓名,根据其服务年限,将最老的雇员排在最前面。SELECT ENAME,HIREDATE FROM EMP ORDER BY HIREDATE;16显示所有雇员的姓名、工作和薪金,按工作内的工作的降序顺序顺序排序,而工作按薪金排序。SELECT ENAME,JOB,SAL FROM EMP ORDER BY JOB DESC,SAL;17显示所有雇员的姓名和加入公司的年份和月份,按雇员受雇日所在月排序,并将最早年份的项目排在最前面。SELECT EN
7、AME,TO_CHAR(HIREDATE,'YYYY-MM') AS Y_M FROM EMP ORDER BY TO_CHAR(HIREDATE,'MM'),TO_CHAR(HIREDATE,'YYYY');18显示在一个月为30天的情况下所有雇员的日薪金,取整。SELECT ROUND(SA30) AS SAL FROM EMP;19找出在(任何年份的)2月受聘的所有雇员。SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'MM')='02'20对于每个雇员,显示其加入公司的天数
8、。SELECT CEIL(SYSDATE-HIREDATE) AS DY FROM EMP;21显示姓名字段的任何位置,包含“A”的所有雇员的姓名。SELECT ENAME FROM EMP WHERE ENAME LIKE '%A%'SELECT ENAME FROM EMP WHERE INSTR(ENAME,'A')>0;22以年、月和日显示所有雇员的服务年限。SELECTHIREDATE,FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) ASYMOD(FLOOR(MONTHS_BETWEEN(SYSDATE,HI
9、REDATE),12)ASM,MOD(FLOOR(SYSDATE-HIREDATE),30) AS D FROM EMP;23列出至少有一个雇员的所有部门。SELECT * FROM DEPT WHERE DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP);24列出薪金比“ SMITH”多的所有雇员。SELECT * FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SMITH');25列出所有雇员的姓名及其上级的姓名。SELECT AS empnm, AS mgrnm FROM
10、emp ygb,emp sjb WHERE =;26列出入职日期早于其直接上级的所有雇员。SELECT * FROM emp ygb WHERE hiredate<(SELECT hiredate FROM emp sjb WHERE =;27列出部门和这些部门的雇员,同时列出那些没有雇员的部门。SELECT * FROM dept WHERE deptno IN (SELECT DISTINCT deptno FROM emp)UNIONSELECT * FROM dept WHERE deptno NOT IN (SELECT DISTINCT deptno FROM emp)SEL
11、ECT dname,ename FROM dept a LEFT JOIN emp b ON =;28列出所有“ CLERK (办事员)的姓名及其部门名称。SELECT ename,dname FROM dept a,emp b WHERE = AND job='CLERK'29列出各种类型的最低薪金,并使最低薪金大于1500。SELECT job,MIN(sal) FROM emp GROUP BY job HAVING MIN(sal)>1500;30列出从事“ SALES (销售)工作的雇员的姓名,假定不知道销售部的部门编号。SELECT ename FROM em
12、p WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES');31列出薪金高于公司平均水平的所有雇员。SELECT * FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP);32列出与“SCOTT从事相同工作的所有雇员。SELECT* FROM EMP WHERE JOB=(SELECJOB FROM EMP WHERE ENAME='SCOTT') AND ENAME<>'SCOTT'33列出薪金等于在部门30工作的所有雇员的姓名
13、和薪金。SELECT * FROM EMP WHERE SAL IN(SELECT SAL FROM EMP WHERE DEPTNO=30);34列出薪金高于在部门30工作的所有雇员的姓名和薪金。SELECT * FROM EMP WHERE SAL >ALL(SELECT SAL FROM EMP WHERE DEPTNO=30);35列出在每个部门工作的雇员的经理以及其他信息。SELECT A.*,B.* FROM DEPT A,EMP B WHERE = AND JOB='MANAGER'SELECT A.*,B.* FROM (SELECT * FROM EMP
14、 WHERE JOB<>'MANAGER') A,(SELECT * FROM EMPWHERE JOB='MANAGER') B WHERE = AND ='MANAGER'ORDER BY ;36列出所有雇员的雇员名称、部门名称和薪金。SELECT ENAME,DNAME,SAL FROM DEPT A,EMP B WHERE =;37列出从事同一种工作但属于不同部门的雇员的不同组合。SELECT * FROM emp ORDER BY job,deptno;38列出分配有雇员数量的所有部门的详细信息,即使是分配有0个雇员。SEL
15、ECT * FROM dept WHERE deptno IN (SELECT DISTINCT deptno FROM emp) UNIONSELECT * FROM dept WHERE deptno NOT IN (SELECT DISTINCT deptno FROM emp)39列出各种类型工作的最低工资。SELECT job,MIN(sal) FROM emp GROUP BY job;40列出各个部门的 MANAGER (经理)的最低薪金。SELECT MIN(sal) FROM emp WHERE job='MANAGER'41列出按年薪排序的所有雇员的年薪。S
16、ELECT sal*12 AS y_sal FROM emp ORDER BY sal*12;42列出薪金水平处于第四位的雇员。SELECT * FROM(SELECT a.*,row_number() over(ORDER BY sal DESC) AS rn FROM emp a)WHERE rn=4;43查找EMP表中前5条记录SELECT * FROM emp WHERE ROWNUM<=5;44查找EMP表中10条以后的记录SELECT * FROM (SELECT a.*,ROWNUM AS rn FROM emp a) WHERE rn>10;45查找EMP表中薪水第
17、5高的员工SELECT * FROM (SELECT a.*,row_number() over(ORDER BY sal DESC) AS rn FROM emp a) WHERE rn=5;46查找EMP表部门30中薪水第3的员工SELECT * FROM (SELECT a.*,row_number() over(PARTITION BY deptno ORDER BY sal DESC) AS rn FROM emp a) WHERE rn=5 AND deptno=30;47查找EMP表中每部门薪水第 3的员工SELECT * FROM (SELECT a.*,row_number(
18、) over(PARTITION BY deptno ORDER BY sal DESC) AS rn FROM emp a) WHERE rn=3;48统计各部门的薪水总和.SELECT deptno,SUM(sal) AS sumsal FROM emp GROUP BY deptno;-每个部门员工和经理的详细信息:编号 姓名,薪水,入职日期,部门编号SELECT AS eno, AS enm, AS ejob, AS esal, AS edate, AS edept, AS mno, AS mnm, AS mjob, ASmsal, AS mdate, AS mdept FROM (S
19、ELECT* FROM emp WHERE job<>'MANAGER' AND job<>'PRESIDENT') a,(SELECT * FROM emp WHERE job='MANAGER') bWHERE =;- -1.列出至少有一个员工的所有部门。select * from dept where deptno in(select distinct deptno from emp);select count(*),deptno from emp group by deptno having count(*)>
20、;1;- -2.列出薪金比“ SMITH”多的所有员工。select * from emp where sal>(select sal from emp where ename='SMITH');- -3.列出所有员工的姓名及其直接上级的姓名select ename,(select ename from emp where empno= from emp a;select , from emp a,emp b where =(+);- -4.列出受雇日期早于其直接上级的所有员工。select ename from emp e where hiredate <(sel
21、ect hiredate from emp where empno=;- -5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。select dname,ename from dept left join emp on =;select dname,ename from dept a,emp b where = (+);- -6.列出所有“ CLERK (办事员)的姓名及其部门名称。select dname,ename from dept a,emp b where = and job='CLERK'select (select dname from dept w
22、here deptno= as dname ,ename from emp a where job='CLERK'- -7.列出最低薪金大于 1500的各种工作。select job from emp group by job having min(sal)>1500;select job,min(sal) msal from emp group by job having min(sal)>1500;- -8.列出在部门“ SALES (销售部)工作的员工的姓名,假定不知道销售部的部门编号。select ename from emp where deptno=(s
23、elect deptno from dept where dname='SALES');- -9.列出薪金高于公司平均薪金的所有员工。select ename from emp where sal>(select avg(sal) from emp);- -10.列出与“ SCOTT从事相同工作的所有员工。select * from emp where job=(select job from emp where ename='SCOTT');- -11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。select ename,sal from emp where sal=any(select sal from emp where deptno=30);select * from emp where sal in (select sal from emp where deptno=30);- -12.列出薪金高于在部门30工作的所有员工的
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 拔高冲刺卷-【05卷·模拟卷】2025届中考地理测试卷(广东专用)(解析版)
- 特许金融分析师考试必知技巧总结与试题及答案
- 维稳工作总结11
- 常见误区2024年特许金融分析师考试试题及答案
- 货币政策与经济发展的关系试题及答案
- 【广西卷】广西壮族自治区2025年3月高三毕业班第二次高考适应性测试(广西二模)(3.5-3.7)历史试卷
- 特许金融分析师考试技能提升试题及答案
- 学校餐厅食品安全教育知识
- 2024年CFA考试梳理试题及答案
- 特许金融分析师模拟测试试题及答案
- 初中八年级音乐-《星星索》课件
- 台湾省2021年中考数学真题卷(含答案与解析)
- 煤矿井下设备安装工程专项方案
- 9806H基本操作和配置
- 妇产医院重点专科建设执行情况汇报总结
- 自愿放弃补偿金协议书
- 南京交通技师学院招聘真题
- 2022年高考英语全国Ⅱ卷(真题+答案+详细解析)
- 空压机自动控制系统设计
- MMPI14个量表得分题目号码
- 爱泼斯坦小结
评论
0/150
提交评论