oracle sql语句练习1

说明：

使用scott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下：

Alter user scott account unlock;

1. emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
2. dept部门表(deptno部门编号/dname部门名称/loc地点)

考试要求：

1. 每个项目成员，2小时内上机操作完成；
2. 每题写出标准的sql语句答案；
3. 每题将语句运行后的slqplus界面文档粘贴到答卷中。

评分标准：

1. 全部答案均得到正确结果的，文档格式合符标准的为正确。
2. 每题分值为2，题选得分为：2分X45=90分；
3. 文档提交合符文档标准的，为10分；合计得分为100分。
4. 超时5分钟内扣5分，超时10分钟内扣10分，超时10分钟以上为0分

   --1、选择部门30中的雇员

   select *from t_dept where deptno=30;

   --2、列出所有办事员（clerk）的姓名、编号和部门

   select ename,empno,deptno from t_emp where job='CLERK';

   --3、找出佣金（comm）高于薪金（sal）的雇员

   select *from t_emp where comm > sal;

   --4、找出佣金高于薪金60%的雇员

   select *from t_emp where comm>sal*0.6;

   --5、找出部门10中所有经理和部门20中的所有办事员的详细资料

   select *from t_emp  where job='MANAGE' and deptno=10 or job='CLERK' and deptno=20

   --6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料

   select *from t_emp where deptno=10 or deptno=20 and sal >= 2000；

   --7、找出收取佣金的雇员的不同工作

   select distinct job from t_emp where comm > 0;

   --8、找出不收取佣金或收取的佣金低于100的雇员

   select *from t_emp where comm = 0 or comm < 100;

   --9、找出各月最后一天受雇的所有雇员

   select *from t_emp where trunc(hiredate,'dd') = last_day(hiredate)

   --10、找出早于25年之前受雇的雇员

   select *from t_emp where months_between(sysdate,hiredate) >25*12；

   --11、显示只有首字母大写的所有雇员的姓名

   select *from t_emp where ename=nls_initcap(ename);

   --12、显示正好为6个字符的雇员姓名

   select ename from t_emp where length(ename)=6;

   --13、显示不带有'R'的雇员姓名

   select ename from t_emp where ename not like '%R%';

   --14、显示所有雇员的姓名的前三个字符

   select substr(ename,1,3)from t_emp;

   --15、显示所有雇员的姓名，用a替换所有'A'

   select TRANSLATE(ename,'A','a') from t_emp;

   --16、显示所有雇员的姓名以及满10年服务年限的日期

   select ename,hiredate from t_emp where to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy') >=10；

   --17、显示雇员的详细资料，按姓名排序

   select *from t_emp order by ename；

   --18、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面

   select * from t_emp order by hiredate asc；

   --19、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序

   select ename,job,sal from t_emp order by job desc,sal asc;

   --20、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面

   select ename,to_char(hiredate,'yyyy') as year,to_char(hiredate,'MM') as month from t_emp order by month,year asc；

   --21、显示在一个月为30天的情况下所有雇员的日薪金

   select ename,hiredate,sal/30 from t_emp where to_char(last_day(hiredate),'dd')=30；

   --22、找出在（任何年份的）2月受聘的所有雇员

   select *from t_emp where to_char(hiredate,'MM')=2;

   --23、对于每个雇员，显示其加入公司的天数

   select ename,round(sysdate-hiredate) as day from t_emp；

   --24、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名

   select *from t_emp where ename like '%A%';

   --25、以年、月和日显示所有雇员的服务年限

   select ename,round((sysdate-hiredate)/365) as year, round((sysdate-hiredate)/30) as month, round(sysdate-hiredate) as day from t_emp；

   --26、列出至少有一个雇员的所有部门

   select distinct d.deptno,d.dname,d.loc from t_dept d ,t_emp e where d.deptno=e.deptno；

   --27、列出薪金比"SMITH"多的所有雇员

   select *from t_emp where sal > ( select sal from t_emp where ename='SMITH');

   --28、列出所有雇员的姓名及其直接上级的姓名

   select d.ename,b.ename as mgrname from t_emp d left join t_emp b on d.mgr = b.empno

   --29、列出入职日期早于其直接上级的所有雇员

   1、查出所有雇员的入职时间以及上级入职时间 select d.ename,d.hiredate,b.ename mgrname,b.hiredate mgrdate    from t_emp d join t_emp b on d.mgr = b.empno； 2、select *from   (select d.ename,d.hiredate,b.ename mgrname,b.hiredate mgrdate   from t_emp d join t_emp b on d.mgr = b.empno) a   where a.hiredate < a.mgrdate;

   --30、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门

   select dname,ename from t_dept,t_emp   where t_dept.deptno = t_emp.deptno(+);

   --31、列出所有“CLERK”（办事员）的姓名及其部门名称

   select ename,dname from t_emp,t_dept where t_emp.job='CLERK' and t_emp.deptno = t_dept.deptno;

   --32、列出各种工作类别的最低薪金，显示最低薪金大于1500的记录

   select min(sal) minsal,job from t_emp group by job having min(sal) > 1500;

   --33、列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号

   1、查找sales的部门编号 select deptno from t_dept where dname='SALES'; 2、select *from t_emp where deptno = ( select deptno from t_dept where dname='SALES')；

   --34、列出薪金高于公司平均水平的所有雇员

   先查找出平均工资:select avg(sal) avgsal from t_emp; 2、  select *from t_emp   where sal>(select avg(sal) avgsal   from t_emp);

   --35、列出与“SCOTT”从事相同工作的所有雇员

   1、先找出SCOTT的工作 select job from t_emp where ename='SCOTT'; 2、select *from t_emp  where job=(select job from t_emp  where ename='SCOTT') and ename != 'SCOTT';

   --36、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30中任何一个雇员的薪金

   先找出30部门的薪金 select sal from t_emp where deptno =30; 2、select *from t_emp   where sal in (select sal from t_emp   where deptno =30);

   --37、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30中所有雇员的薪金

   select *from t_emp where sal > (select max(sal) from t_emp where deptno =30);

   --38、列出每个部门的信息以及该部门中雇员的数量

   1、先找出所有部门所对应的员工数量 select deptno,count(ename) num from t_emp group by deptno; 2、select p.*,d.num from t_dept p, (select deptno,count(ename) num from t_emp group by deptno)d where p.deptno  =  d.deptno (+);

   --39、列出所有雇员的雇员名称、部门名称和薪金

   select ename,dname,sal from t_emp,t_dept where t_emp.deptno = t_dept.deptno;

   --40、列出从事同一种工作但属于不同部门的雇员的不同组合

   先查出所有的 select ename,job,deptno from t_emp; 2、select distinct a.ename,a.job,a.deptno from (select ename,job,deptno from t_emp)a, (select ename,job,deptno from t_emp)b where a.job = b.job and a.deptno != b.deptno;

   --41、列出分配有雇员数量的所有部门的详细信息，即使是分配有0个雇员

   select a.*,b.num from t_dept a,(select deptno,count(ename) num from t_emp group by deptno)b where a.deptno = b.deptno (+);

   --42、列出各种类别工作的最低工资

   select job, min(sal) from t_emp group by job;

   --43、列出各个部门的MANAGER（经理）的最低薪金

   select min(sal),deptno from t_emp where job='MANAGER' group by deptno;

   --44、列出按年薪排序的所有雇员的年薪

   select ename,(sal+nvl(comm,0))*12 yearsal from t_emp order by yearsal;

   --45、列出薪金水平处于第四位的雇员

   select * from (select d.*,row_number()over(order by d.sal desc) as grades  from t_emp d) where grades  = 4;