Oracle 的sql习题

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

select *

from scott.emp

where deptno = 30;

--2、列出所有办事员的姓名、编号和部门。“CLERK”

select empno,ename,deptno

from scott.emp

where job in('CLERK');

--3、找出佣金高于薪金的雇员

select *

from scott.emp

where sal < comm;

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

select *

from scott.emp

where  comm > sal*0.6;

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

select *

from scott.emp

where  (job = 'MANAGER' and deptno = 10 ) or (job = 'CLERK' and deptno = 20) ;

--6、找出部门10中所有经理、部门20中所有办事员以及既不是经理又不是办事员但其薪金大于或等于2000的所有雇员的详细资料

select *

from scott.emp

where  (job = 'MANAGER' and deptno = 10 ) or (job = 'CLERK' and deptno = 20) or ((job not in('MANAGER','CLERK')) and sal >= 2000);

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

select  distinct job

from scott.emp

where comm is not null;

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

select *

from scott.emp

where (comm is null) or (comm < 100);

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

select *

from scott.emp

where hiredate = last_day(hiredate);

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

select *

from scott.emp

where months_between(sysdate , hiredate)/12 > 12;

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

select ename

from scott.emp

where substr(ename,1,1) = substr(initcap(ename),1,1);

select ename

from scott.emp

where substr(ename,1,1) >= 'A' and substr(ename,1,1)<= 'Z';

--姓名的首字母

select distinct substr(ename,1,1)from scott.emp;

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

select ename

from scott.emp

where length(ename) = 15;

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

select  ename

from scott.emp

where   ename not like '%R%';

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

select substr(ename,1,3)

from scott.emp;

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

select replace(ename,'A','a') from scott.emp;

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

select ename as "姓名",add_months(hiredate,10*12) as "满10年服务年限的日期"

from scott.emp;

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

select *

from scott.emp

order by ename ;

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

select ename

from scott.emp

order by hiredate;

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

select ename,job,sal

from scott.emp

order by job desc,sal asc;

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

select ename,hiredate

from scott.emp

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

select ename,trunc(sal/30)

from scott.emp;

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

select *

from scott.emp

where to_char(hiredate,'MM') = '02';

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

select ename,trunc(sysdate-hiredate)

from scott.emp;

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

select ename

from scott.emp

where ename like '%A%';

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

select ename as "姓名",trunc(months_between(sysdate,hiredate)/12) as "年",trunc(months_between(sysdate,hiredate)) as "月",trunc(sysdate-hiredate) as "日"

from scott.emp;

--复制scott的表

create table emp_test

as

(

select *

from scott.emp

);

create table salgrade_test

as

(

select *

from scott.salgrade

);

create table dept_test

as(

select *

from scott.dept

);

--1、列出至少有一个雇员的所有部门信息

select distinct d.deptno as 部门号,dname as 部门名,loc as 部门地址

from emp_test e,dept_test d

where e.deptno = d.deptno;

--2、列出薪金(工资)比'SMITH'多的所有雇员信息

select ename,sal

from emp_test

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

--3、列出所有雇员的姓名及其上级的姓名

select t1.empno as下属编号 ,t1.ename as 下属姓名,t2.mgr as 上司编号,t2.ename as 上司姓名

from emp_test t1,emp_test t2

where t1.empno = t2.mgr;

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

select e2.ename as 雇员姓名,e2.hiredate as 入职日期,e1.ename as 直接上级,e1.hiredate as 入职日期

from emp_test e1,emp_test e2

where e1.empno = e2.mgr and e1.hiredate > e2.hiredate;

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

select dname,ename

from dept_test d,emp_test e

where d.deptno(+) = e.deptno

--6、列出所有'CLERK'(办事员)的姓名及其部门名称

select ename,dname

from dept_test d,emp_test e

where d.deptno = e.deptno and job = 'CLERK';

--7、找出最低薪金大于1500的所有工作类型和相应最低薪金

select job,min(sal)

from emp_test

group by job

having min(sal) > 1500;

--8、列出从事sales(销售)工作的雇员的姓名，假定不知道销售部的部门编号

select ename

from emp_test

where deptno = (select deptno from dept_test where dname = 'SALES');

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

select *

from emp_test

where sal > (select avg(sal) from emp_test);

--10、列出与"SCOTT"从事相同工作的所有雇员

select ename

from emp_test

where job = (select job from emp_test where ename = 'SCOTT');

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

select ename, sal

from emp_test

where sal in (select sal from emp_test where deptno = 30) and deptno <> 30;

--12、列出薪金高于在部门30工作的所有雇员的姓名和薪金

select ename, sal

from emp_test

where sal > all(select sal from emp_test  where deptno = 30) ;

--13、列出在每个部门工作的雇员的数量以及其他信息

select t.dname as 部门名称 ,t.n as 部门人数 ,t.l as 部门地址

from (select dname,count(ename) as n ,min(loc) as l

      from emp_test e,dept_test d

      where e.deptno = d.deptno

      group by dname) t;

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

select ename as 雇员姓名, dname as 部门名称, sal as 薪金

from emp_test e, dept_test d

where e.deptno = d.deptno;

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

Select e1.ename,e2.ename,e1.job,e2.job,e1.deptno,e2.deptno

From emp_test e1,emp_test e2

Where e1.job = e2.job and e1.deptno <> e2.deptno;

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

select distinct d.deptno, dname, loc ,count(empno)

from dept_test d, emp_test e

where e.deptno(+) = d.deptno

group by d.deptno,dname,loc;

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

select dname as 部门名称, t.m as 最低工资

from (select deptno,min(sal) as m

      from emp_test

      group by deptno) t, dept_test d

where t.deptno = d.deptno;

--18、列出各个部门的MANAGER(经理)的最低薪金

select deptno,min(sal)

from emp_test

where job = 'MANAGER'

group by deptno;

--19、列出按计算字段的排序的所有雇员的年薪(计算字段指的是年薪)

select ename as 姓名, (sal+nvl(comm,0))*12 as 年薪

from emp_test

order by  年薪 ;

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

select b.*

from (select rownum as rm , a.* from (select ename,sal from emp_test order by sal desc) a) b

where b.rm = 4;