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;