Oracle SQL 练习题 3

1
2
3

-- 1.	显示 dname 和 loc 中间用 - 分隔
select dname||'-'||loc
from dept;

1
2
3

-- 2.	将部门名称左填充为 10 位
select lpad(dname, 10, ' ')
from dept;

1
2
3

-- 3.	将员工名字的‘S’，替换为‘s’
select replace(ename, 'S', 's')
from emp;

1
2
3

-- 4.	求员工名字的前三位
select substr(ename, 1, 3)
from emp;

1
2
3

-- 5.	查找员工名字中‘S’第一次出现的位置
select instr(ename, 'S', 1, 1)
from emp;

1
2
3

-- 6.	查找员工名字中第二次出现‘T’的位置
select instr(ename, 'T', 1, 2)
from emp;

1
2
3

-- 7.	查找员工名字的长度
select length(ename)
from emp;

1
2
3
4

-- 8.	显示一年前的今天日期和一年以后今天的日期
select to_char(sysdate, 'yyyy')-1||to_char(sysdate, '-mm-dd'),
        to_char(sysdate, 'yyyy')+1||to_char(sysdate, '-mm-dd')
from dual;

1
2
3

-- 9.	显示本月第三天的日期
select to_char(sysdate, 'yyyy-mm-')||'03'
from dual;

1
2
3

-- 10.	显示今天到'2012-12-12'相隔的月数
select months_between(sysdate, to_date('2012-12-12', 'yyyy-mm-dd'))
from dual;

1
2
3

-- 11.	显示员工工资的货币表示形式，比如RMB5,000
select to_char(sal, 'L9999') salary
from emp;

1
2
3

-- 12.	显示1949年10月1日的字符串格式,最后显示结果为1949年10月1日
select to_char(to_date('1949-10-01','yyyy-mm-dd'), 'yyyy"年"mm"月"dd"日"')
from dual;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

--13.	按照如下要求显示数据：
--	sal=800  显示低工资   
--     	sal=3000  正常工资
--     	sal=5000  高工资
select (
  case sal
    when 800 then '低工资'
    when 3000 then '正常工资'
    when 5000 then '高工资'
  end)
from emp;

1
2
3
4

-- 14.	显示以下字段及字符串的连接："The job id for "，姓名(大写)，" is " ,工作(小写)  如：The job id for ALLEN is salesman
-- job为关键字，可以使用双引号表示它只是一个字段
select 'The job id for ' || upper(ename) || ' is ' || lower("JOB")
from emp;

1
2
3
4

-- 15.	显示出姓名中最后一个字母为"N"的员工姓名 (使用substr或者instr)
select ename
from emp
where substr(ename, length(ename), 1) = 'N';

1
2
3
4

-- 16.	查询参加工作时间在每月15日之后的员工姓名，参加工作时间
select ename, hiredate
from emp
where extract(day from hiredate) > 15;

1
2
3

-- 17.	用"*"的个数表示员工的工资中包含多少"千"，如3500，显示'***',5600,显示'*****'
select lpad(' ', trunc(sal/1000, 0)+1, '*'), sal
from emp;

1
2
3

-- 18.	显示员工名，参加工作时间，参加工作6个月后的第一个周一
select ename, hiredate, next_day(add_months(hiredate, 6),'星期一')
from emp;

1
2
3

-- 19.	显示员工姓名，月薪，年薪（13个月的月薪+10000元奖金+comm）要求别名为annual_salary,要求所有人的年薪都显示出来
select ename, sal, (13*sal+10000+nvl(comm, 0)) annual_salary
from emp;

1
2
3
4
5

-- 20.	创建一个缩进报告显示经理层次，从名字为 KING的雇员开始，显示雇员的名字、经理ID和部门ID。
select ename, mgr, deptno
from emp
start with ename='KING'
connect by prior empno = mgr;

1
2
3
4
5
6

-- 21.	产生一个公司组织图表显示经理层次。从最顶级的人开始，排除所有job为CLERK的人。
select *
from emp
where "JOB" <> 'CLERK'
start with mgr is null
connect by prior empno = mgr;

1
2
3
4

-- 22.	用集合运算，列出不包含job为SALESMAN 的部门的部门号。
(select deptno from emp)
minus 
(select deptno from emp where "JOB" = 'SALESMAN');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

/*23.	写一个联合查询，列出下面的信息：
	EMP表中所有雇员的名字和部门编号,不管他们是
	否属于任何部门。
	DEPT表中的所有部门编号和部门名称，不管他们
	是否有员工。*/
select ename, deptno
from emp
union
select dname, deptno
from dept;

1
2
3
4

-- 24.	用集合运算查询出职位为SALESMAN和部门编号为	10的人员编号、姓名、职位，不排除重复结果。
(select empno, ename, "JOB" from emp where "JOB" = 'SALESMAN')
union all
(select empno, ename, "JOB" from emp where deptno = 10);

1
2
3
4
5
6
7
8

-- 25.	用集合查询出部门为10和20的所有人员编号、姓名、所在部门名称。
(select empno, ename, dname
from emp left join dept on emp.deptno = dept.deptno
where emp.deptno = 10) 
union 
(select empno, ename, dname
from emp left join dept on emp.deptno = dept.deptno
where emp.deptno = 20);

1
2
3
4

-- 26.	查询薪水多于他所在部门平均薪水的雇员名字，部门号。
select ename, deptno
from emp e1
where sal > (select avg(sal) from emp e2 group by deptno having e1.deptno = e2.deptno);

1
2
3
4

-- 27.	查询员工姓名和直接上级的名字。
select e1.ename, e2.ename mgrname
from emp e1, emp e2
where e1.mgr = e2.empno;

1
2
3
4

-- 28.	查询每个部门工资最高的员工姓名，工资。
select ename, sal
from emp e1
where sal = (select max(sal) from emp e2 group by deptno having e1.deptno = e2.deptno);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 29.	查询每个部门工资前两名高的员工姓名，工资，部门号
select deptno, ename, sal      
from emp e1     
where      
   (  
    select count(*)     
    from emp e2     
    where e2.deptno=e1.deptno and e2.sal>e1.sal  
   ) <2
order by deptno, sal;