SQL题目练习总结

1-10

1.查看员工信息表EMP中员工的所有信息
select * from emp;
select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO from emp;
–2.查看员工信息表EMP中员工的部分信息，例如姓名、岗位和薪资，或员工编号、姓名和部门编号
select ename, job, sal from emp;
select empno, ename, deptno from emp;
–3.查看员工信息表EMP中员工的姓名、部门编号、岗位和薪资
select ename, deptno, job, sal from emp;
–4.查看员工信息表EMP中员工的姓名，并在姓名列前加一列常量，内容是：“员工姓名”这几个字
select ‘员工名字’，ename from emp;
–5.用“员工姓名”代替ENAME，用“工作岗位”代替JOB
select ename 员工姓名, job 工作岗位 from emp;
–6.用T代替EMP表，查看EMP表中的ENAME和JOB字段
select ename, job from emp T;
–7.同时使用列别名与表别名
select ename 姓名,sal from emp T;
–8.对常量’员工姓名’命列别名“员工姓名”
select ‘员工姓名’ 员工姓名,ename from emp;
–9.将ENAME和JOB字段合并在一起
select ename||job from emp;
–10.将常量与表字段合并在一起
select ‘员工姓名是：’||ename from emp;

11-20

1.去除重复的部门编号
select distinct deptno from emp;
–2.工资大于1500的员工信息
select * from emp where > 1500
–3.查询姓名当中含有% 的员工的信息
select * from emp where ename like ‘%%%’ escape '’;
–4.查询10号部门的员工信息
select * from emp where deptno = 10;
–5.薪资超过2500的员工信息
select * from emp where sal > 2500;
–6.查询薪资小于1000的员工
select * from emp where sal < 1000;
–7.查询10号部门之外的员工信息
select * from emp where deptno != 10;
–8.查询有奖金的员工信息
select * from emp where comm > 0;
–9.查询无奖金的员工信息
select * from emp where comm = 0;
–10.查询入职日期为1980年12月17号的员工信息
select * from emp where hiredate = to_date(‘1980/12/17’,‘YYYY/MM/DD’);

21-30

1.查找姓名前边是SMIT、最后一位不确定的员工信息
select * from emp where ename like ‘SMIT_’;
–2.查找姓名前边是SM、后边是TH、中间有一位不确定的员工信息
select * from emp where ename like ‘SM_TH’;
–3.查找姓名总共有五位的员工信息
select * from emp where ename like ‘_____’;
–4.查找姓名前边是S、后边是H、中间有三位不确定的员工信息
select * from emp where ename like ‘S___H’;
–5.查找姓名以S开头的员工信息
select * from emp where ename like ‘S%’;
–6-9
–6.查找姓名不以S开头的员工信息
select * from emp where ename not like ‘S%’;
–7.查找名字中带有A字母的员工信息
select * from emp where ename like ‘%A%’;
–8.查找姓名总共有5位且首字母是A的员工信息
select * from emp where ename like ‘A____’;
–9.查找姓名是以A开头且倒数第二位是M的员工信息
select * from emp where ename like ‘A%M_’;
–10.查询1981年劳动节以前入职的员工信息
select * from emp where hiredate < to_date(‘1981/05/01’,‘YYYY/MM/DD’);
select * from emp where to_char(hiredate,‘YYYY/MM/DD’) < ‘1981/05/01’;
select * from emp where to_char(hiredate,‘YYYYMMDD’) < ‘19810501’;
select ename, hiredate from emp;

31-40

1.查询姓名是SMITH的员工信息
select * from emp where ename = ‘SMITH’;
–2.查询姓名是SMiTH的员工信息
select * from emp where ename = ‘SMiTH’;–不存在、严格区分大小写
–3.查询姓名是smith的员工信息
select * from emp where ename = ‘smith’;
–4.查询姓名是SMIT的员工信息
select * from emp where ename = ‘SMIT’;
–5.查询岗位是SALESMAN的员工信息
select * from emp where job = ‘SALESMAN’;
–6.查询岗位是SALESMaN的员工信息
select * from emp where job = ‘SALESMAN’;
–7.查询岗位是SALESMAN的员工信息（不区分大小写）
select * from emp where job = ‘SALESMAN’;
–8.将员工的入职日期转换为字符类型
select to_char(hiredate,‘YYYY/MM/DD’) from emp;
select * from emp
where to_char(hiredate,‘YYYY/MM/DD’) in (‘1980/12/17’, ‘1981/02/20’);
select ‘1980/12/17’ from dual;
–9.查找有奖金的员工信息
select * from emp where comm > 0;–3
select * from emp where nvl(comm,0) > 0;–3
–10.查找无奖金的员工信息
select * from emp where comm = 0;–1
select * from emp where nvl(comm,0) = 0;–11

41-50

1.查询部门编号是10号或20号的员工信息
select * from emp where deptno = 20 or deptno = 10;
select * from emp where deptno in (20,10);
–2.查询薪资是3000或5000的员工信息
select * from emp where sal = 3000 or sal = 5000;
select * from emp where sal in (3000,5000);
–3.查询岗位是SALESMAN或者MANAGER的员工信息
select * from emp where job = ‘SALESMAN’ or job = ‘MANAGER’;
select * from emp where job in (‘SALESMAN’, ‘MANAGER’);

–4.查询岗位既不是SALESMAN也不是MANAGER的员工信息
select * from emp where job not in (‘SALESMAN’,‘MANAGER’);

–5.查询入职时间是1980年12月17号或者1981年2月20号的员工信息

–6.将hiredate转换为字符类型，再和需要查询的日期进行比较
select to_char(hiredate,‘YYYYMMDD’) from emp;
select * from emp where to_char(hiredate,‘YYYYMMDD’) in (‘19801217’,‘19810220’);
–7.查找公司的大领导信息
select * from emp where job = ‘PRESIDENT’;
select * from emp where mgr is null;
–8.查询（20号部门的）员工姓名、岗位、薪资
select ename, job, sal from emp where deptno = 20;
–9.查询工资超过3000的员工的姓名、薪资
select ename, sal from emp where sal > 3000;
–10.查询非10号部门的员工的所有信息
select * from emp where deptno != 10;

51-60

1.查询薪资超过1000并且小于3000的员工信息
select * from emp where sal > 1000 and sal <3000;
–2.查询部门编号是10号或20号的员工信息
select * from emp where deptno = 20 or deptno = 10;
–3.查询岗位是销售SALESMAN，并且奖金超过400的员工信息
select * from emp where job = ‘SALESMAN’ and nvl(comm,0) > 400;
–4.查询20号部门的经理
select * from emp where job = ‘MANAGER’ and deptno = 20;
–5.查询所有20号部门的员工或岗位是MANAGER的员工信息
select * from emp where deptno = 20 or job = ‘MANAGER’;
–6.查询10号部门的部门经理或20号部门的分析师ANALYST
select *
from emp
where (deptno = 10 and job = ‘MANAGER’) or (deptno = 20 and job = ‘ANALYST’);
–7.查询员工姓名为SMITH的员工的所有信息
select * from emp where ename = ‘SMITH’;
–8.查询1981年2月20号入职的员工的姓名和薪资
select ename, sal from emp where hiredate = to_date(‘19810220’,‘YYYYMMDD’);
select ename, sal from emp where to_char(hiredate,‘YYYYMMDD’) = ‘19810220’;
–9.查询入职日期早于1982年1月1日的所有员工的姓名、入职日期
select ename, hiredate from emp where hiredate < to_date(‘19820101’,‘YYYYMMDD’);
select ename, hiredate from emp where to_char(hiredate,‘YYYYMMDD’) < ‘19820101’;
–10.找出奖金（COMM字段）大于0的员工信息
select * from emp where nvl(comm,0) > 0;

61-70

–1.查询姓名中首位是S的员工的姓名、岗位
select ename, job from emp where ename like ‘S%’;
–2.查询姓名中包含S的员工的姓名、岗位
select ename, job from emp where ename like ‘%S%’;
select * from emp;
–3.查询员工姓名以S开头，且姓名总长度为5位的员工的所有信息
select * from emp where ename like ‘S____’;
–4.查询员工姓名前两位是SM，后两位是TH，中间一位不清楚具体字母的员工的所有信息
select * from emp where ename like ‘SM_TH’;
–5.查询员工姓名至少有四位，且倒数第三位是I的员工的所有信息
select * from emp where ename like ‘%I_’;
select * from emp where ename like ‘_%I__’;
–6.查询员工姓名中包含IT的员工的所有信息
select * from emp where ename like ‘%I%T%’ or ename like ‘%T%I%’ ;

–7.查询岗位是MANAGER或SALESMAN的员工信息
select * from emp where job = ‘MANAGER’ or job = ‘SALESMAN’;
select * from emp where job in(‘MANAGER’ ,‘SALESMAN’);
–8.查询SMITH和SCOTT的详细信息
select * from emp where ename = ‘SMITH’ or ename = ‘SCOTT’;
–9.查询10号或20号部门的员工信息
select * from emp where deptno = 10 or deptno = 20;
select * from emp where deptno in(10,20);
–10.查找工资在1000-3000之间的员工姓名、工作、工资（包含1000和3000）
select ename, job, sal from emp where sal between 1000 and 3000;
select ename, job, sal from emp where sal<=3000 and sal >=1000;

71-80

1.查询入职时间在1980年或1981年的员工信息
select * from emp where to_char(hiredate,‘YYYY’) between ‘1980’ and ‘1981’;
select * from emp where to_char(hiredate,‘YYYY’) in(‘1980’,‘1981’);
– （查询入职时间在1980年1月1日到1981年12月31日之间的员工信息）
select * from emp where to_char(hiredate,‘YYYYMMDD’) between ‘19800101’ and ‘19811231’;
select * from emp where hiredate between to_date(‘19800101’,‘YYYYMMDD’) and to_date(‘19811231’,‘YYYYMMDD’);
–2.查询没有部门归属的员工姓名和编号
select ename, empno,deptno from emp where deptno = 0 or deptno is null;
select ename,empno from emp where deptno not in(10,20,30,40);
–3.查询公司大BOSS的详细信息
select * from emp where job = ‘PRESIDENT’;
select * from emp where mgr is null;

–4.查找姓名总共有5位、且不是以A开头的员工信息
select * from emp where ename not like ‘A____’ and ename like ‘_____’;
–5.查出10号部门的办事员和30号部门的经理
select * from emp where deptno = 10 and job = ‘CLERK’ or deptno = 30 and job = ‘MANAGER’;
–6.查出10号部门的所有人、30号部门的部门经理以及所有岗位是分析师的员工
select * from emp where deptno = 10 or (deptno = 30 and job = ‘MANAGER’) or job = ‘ANALYST’;
–7.查出10号及30号部门的办事员CLERK及经理MANAGER
select * from emp where (deptno=10 or deptno=30) and (job = ‘CLERK’ or job = ‘MANAGER’);
select * from emp where deptno in(10,30) and job in(‘CLERK’,‘MANAGER’);
select * from emp where (deptno=10 and job = ‘CLERK’ ) or (deptno=30 and job = ‘CLERK’ ) or (deptno=10 and job = ‘MANAGER’) or (deptno=30 and job = ‘MANAGER’);
–8.找出奖金（COMM）高于工资的员工
select * from emp where nvl(comm,0) > sal;
–9.找出工资高于奖金3倍的员工
select * from emp where sal > (nvl(comm,0)*3);
–10.找出不收取奖金或收取的奖金低于1000的雇员
select * from emp where (nvl(comm,0)=0 or nvl(comm,0)<1000);

81-90

1.显示所有雇员的姓名和日工资(一个月假设为30天)
select ename, sal/30 from emp ;
–2.查找工资在1000-3000之间的员工姓名、工作、工资（不含1000和3000）
select ename, job, sal from emp where sal < 3000 and sal > 1000;
–3.查询姓名中只有一个A字母的员工
select * from emp where ename like ‘%A%’ and ename not like ‘%A%A%’;

select * from emp;
–4.查询员工姓名、岗位及薪资，并将查询结果按薪资升序排列
select ename,job,sal from emp order by sal ;
–.5.查询员工姓名、岗位及部门编号，并将查询结果按部门编号降序排列
select ename,job,deptno from emp order by deptno desc;
–6.查询所有员工信息，并按工资由高到低排序，如果工资相同按部门编号由高到低排序
select * from emp order by sal,deptno desc;
–7.按照部门编号进行分组查询，行数减少
select deptno from emp group by deptno;

–8.查询整个公司的薪资合计
select sum(sal) from emp;
–9.查询整个公司的平均薪资
select avg(sal) from emp;

91-100

1.工资最大值
select max(sal) from emp;
–2.工资最小值
select min(sal) from emp;
–3.工资平均值
select avg(sal) from emp;–工资平均值
–4.查询个部门最高工资
select max(sal), deptno from emp group by deptno;

–5.查询部门最低薪资大于1000的部门号
select deptno from emp group by deptno having min(sal) > 1000;
–6.统计各部门中，最高薪资低于5000的部门有哪些
select deptno from emp group by deptno having max(sal) < 5000;
–7.统计除10号部门的其他部门中，哪些部门的最高薪资达到三千
select deptno from emp
where deptno != 10 group by deptno having max(sal) > 3000;
–8.查询员工编号、姓名、薪资，按薪资升序排列
select empno,ename,sal from emp order by sal;–列名
select empno,ename,sal from emp order by 3;–列号
select empno,ename,sal 薪资 from emp order by 薪资;–别名
–9.查询员工编号、姓名、薪资、部门编号，按部门升序及薪资降序排列
select empno,ename,sal,deptno from emp order by deptno,sal desc;
–10.查询除20号部门外，员工编号、姓名、薪资、部门编号，按部门升序、薪资升序、工号降序排列
select empno,ename,sal,deptno
from emp
where deptno != 20
order by deptno,sal,empno desc;

101-110

1.查询整个公司的薪资合计和最高薪资
select sum(sal),max(sal) from emp;
–2.查询各个部门的薪资合计和平均薪资
select deptno,sum(sal),avg(sal)
from emp
group by deptno ;
–3.查询部门人数超过4人的部门编号及平均薪资
select deptno,avg(sal)
from emp
group by deptno
having count(empno) > 4;

–4.查询部门20的员工，每个月的工资总和及平均 工资
select sum(sal+nvl(comm,0)) 总工资,avg(sal)
from emp
group by deptno
having deptno = 20;
select sum(sal),avg(sal)from emp where deptno=20;
–5.查询部门人数大于2的部门编号，最低工资、最高工资,部门人数
select deptno,min(sal),max(sal),count(empno)
from emp
group by deptno
having count(empno) > 2;
–6.查询部门平均工资大于2000，且人数大于2的 部门编号，部门人数，部门平均工资 ，并按照部门人数升序排序。
select deptno,count(empno),avg(sal)
from emp
group by deptno
having avg(sal) > 2000 and count(empno) > 2
order by count(empno);
–7.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序
select job,avg(sal)
from emp
group by job
having job not like ‘SA%’ and avg(sal) > 2500–聚合函数放在having当中
order by avg(sal) desc;

select job,avg(sal)
from emp
where job not like ‘SA%’ --其他条件放在where当中
group by job
having avg(sal) > 2500
order by avg(sal) desc;
–8.查询岗位不为SALESMAN，工资和大于等于2500的岗位及 每种岗位的工资合计值
select job,sum(sal)
from emp
where job not like ‘SALESMAN’–一般条件放在where当中
group by job
having sum(sal)>=2500;–聚合函数条件放在having 当中
–9.查询员工姓名、薪资、奖金、薪资佣金合计，按薪资奖金合计值升序排列，薪资奖金以“总计” 二字显示
select ename,sal,comm,sal+nvl(comm,0) 总计 from emp order by 总计;
–10.查询员工姓名、薪资、12个月工资（以“年薪”二字展示），按年薪降序排列
select ename,sal,sal*12+nvl(comm,0)*12 年薪 from emp order by 年薪 desc;

111-120

1.获取当前日期
select sysdate,systimestamp from dual;
–2.将当前日期转换为字符型
select to_char(sysdate,‘YYYY/MM/DD’) from dual;
–3.查看’20220908’所属季度
select to_date(‘20220908’,‘YYYYMMDD’),
to_char(to_date(‘20220908’,‘YYYYMMDD’),‘Q’) from dual;–3
–4.查看当前时间的月份全称
–MONTH 全称
select sysdate,to_char(sysdate,‘MONTH’)from dual;–1月
select sysdate,to_char(sysdate,‘MONTH’,‘nls_date_language=american’)from dual;–JANUARY
select to_date(‘20220910’,‘YYYYMMDD’),
–4.查看当前时间的月份缩写
–MON 缩写
to_char(to_date(‘20220910’,‘YYYYMMDD’),‘MON’) from dual;–9月
select to_date(‘20220910’,‘YYYYMMDD’),
to_char(to_date(‘20220910’,‘YYYYMMDD’),‘MON’,‘nls_date_language=american’) from dual;–SEP
–5.查看’20220910’是一年中第几个月
– MM 一年中 第几个月
select to_date(‘20220910’,‘YYYYMMDD’),
to_char(to_date(‘20220910’,‘YYYYMMDD’),‘MM’) from dual;–09
–6.查看’20220107’、‘20220109’是星期几使用WWWW
–星期
–WW 从第一天开始算，和星期无关
select to_date(‘20220107’,‘YYYYMMDD’),
to_char(to_date(‘20220107’,‘YYYYMMDD’),‘WW’) from dual;–01
select to_date(‘20220109’,‘YYYYMMDD’),
to_char(to_date(‘20220109’,‘YYYYMMDD’),‘WW’) from dual;–02
–7.查看’20220109’ 、‘20211231’、‘20220101’、‘20191231’分别是星期几，使用IW
–IW 一年中的而第几个星期，和星期几有关 最后一个星期不足四天，归下一年第一个星期，足四天位今年最后一周
select to_date(‘20220109’,‘YYYYMMDD’),
to_char(to_date(‘20220109’,‘YYYYMMDD’),‘IW’) from dual;–01，
select to_date(‘20211231’,‘YYYYMMDD’),
to_char(to_date(‘20211231’,‘YYYYMMDD’),‘IW’) from dual;–52
select to_date(‘20220101’,‘YYYYMMDD’),
to_char(to_date(‘20220101’,‘YYYYMMDD’),‘IW’) from dual;–52,算2021最后一周
select to_date(‘20191231’,‘YYYYMMDD’),
to_char(to_date(‘20191231’,‘YYYYMMDD’),‘IW’) from dual;–01 ,算2020第一周
8.查看’20220101’、‘20220131’、'20220201’是当月第几天 使用W
–W 每月第一天开始算位第一周第一天，和星期几无关
select to_date(‘20220101’,‘YYYYMMDD’),
to_char(to_date(‘20220101’,‘YYYYMMDD’),‘W’) from dual;–1
select to_date(‘20220131’,‘YYYYMMDD’),
to_char(to_date(‘20220131’,‘YYYYMMDD’),‘W’) from dual;–5
select to_date(‘20220201’,‘YYYYMMDD’),
to_char(to_date(‘20220201’,‘YYYYMMDD’),‘W’) from dual;–1
–9.查看’20220101’是星期几，全称，并且英文显示
–DAY 全称
select to_char(to_date(‘20220101’,‘YYYYMMDD’),‘DAY’) from dual;–星期六
select to_char(to_date(‘20220101’,‘YYYYMMDD’),‘DAY’,‘nls_date_language=american’) from dual;–SATUDAY
–10.查看’20220101’是星期几，缩写，并且英文显示
–DY 缩写
select to_char(to_date(‘20220101’,‘YYYYMMDD’),‘DY’) from dual;–星期六
select to_char(to_date(‘20220101’,‘YYYYMMDD’),‘DY’,‘nls_date_language=american’) from dual;–SAT

121-130

1.查看’20211231’星期中的第几天使用D
–D 星期中的第几天–星期日算第一天
select to_char(to_date(‘20211231’,‘YYYYMMDD’),‘D’) from dual;–6
–2.查看’20211231’月份当中第几天使用DD
–DD 月份当中第几天
select to_char(to_date(‘20221231’,‘YYYYMMDD’),‘DD’) from dual;–31
–3.查看’20211231’一年当中第几天使用DD
–DDD 一年当中第几天
select to_char(to_date(‘20221231’,‘YYYYMMDD’),‘DDD’) from dual;–365
–4.获取’20221231193030’的小时
select to_char(to_date(‘20221231193030’,‘YYYYMMDD HH24:MI:SS’),‘HH24’) from dual;–19
5.获取’20221231193030’的分钟
select to_char(to_date(‘20221231193030’,‘YYYYMMDD HH24:MI:SS’),‘MI’) from dual;–30
6.获取’20221231193030’ 的秒钟
select to_char(to_date(‘20221231193030’,‘YYYYMMDD HH24:MI:SS’),‘SS’) from dual;–30
–7.查询当前时间 精确到毫秒
select systimestamp,to_char(systimestamp,‘FF’) from dual;

131-140

–1.–去掉’1,111.1’千位符
select to_number(‘1,111.1’,‘9999.99’) from dual;
–2.去掉’$99.99’、‘￥123.45’货币单位
select to_number(’$99.99’,’$999.99’) from dual;
select to_number(‘￥123.45’,‘L999.99’) from dual;
–3.使用concat 改写select ename||sal||job from emp;
select concat(concat(ename,sal),job) from emp;
–4.将’AMITH ALLEN’ 改写为 Amith Allen
select ‘AMITH ALLEN’,initcap(‘AMITH ALLEN’) from dual;–Amith Allen
–5.将’ABCDE’ B用b替代
select replace(‘ABCDE’,‘B’,‘b’) from dual;–AbCDE
–6.去除’ AAA ‘两侧空格
select trim(’ AAA ‘) from dual;–去除两侧空格
–7.去除’ AAA ‘左侧空格
select ltrim(’ AAA ‘) from dual;
–8.去除’ AAA ‘右侧空格
select rtrim(’ AAA ') from dual;
–9.是分析下面语句的输出是什么
select ‘ABABBAABCAB’, ltrim(‘ABABBAABCAB’,‘AB’) from dual;–CAB 看作个体
–10.截取字符串’ABCDEF’中的前三个字符
select substr(‘ABCDEF’,1,3) from dual;–AB

151-160

1.去除姓名左侧的S
select ltrim(ename,‘S’) from emp;
select trim(leading ‘S’ from ename) from emp;
–2.去除姓名右侧的S
select rtrim(ename,‘S’) from emp;
select trim(trailing ‘S’ from ename) from emp;
–3.去除姓名两侧的S（造数测试）
select * from emp;
select trim(‘S’ from ename) from emp;
–4.去除姓名左侧的SM
select ltrim(ename,‘SM’) from emp;
select trim(leading ‘SM’ from ename) from emp;–

–5.去除姓名右侧的TH
select rtrim(ename,‘TH’) from emp;
select trim(trailing ‘S’ from trim(trailing ‘T’ from ename)) from emp;

–6.去除左右两侧的AB（造数测试）
select rtrim(ltrim(ename,‘AB’),‘AB’) from emp;
select trim(leading ‘B’ from trim(leading ‘A’ from trim(trailing ‘B’ from trim(trailing ‘A’ from ename)))) from emp;
–7.去除字符@@@@@左侧的@（原理）
select ‘@@@@@’,ltrim(’@@@@@’,’@’) from dual;
select ‘@@@@@’,ltrim(’@@@@@’,’@’) from dual;
select ‘@@@@@’,trim(leading ‘@’ from ‘@@@@@’) from dual;
select '@@@@@’,trim(leading ‘@’ from ‘@@@@@’) from dual;
–8.截取名字中的前两位字符
select ename,substr(ename,1,2) from emp;
–9.从名字中的第二位字符开始，截取三位长度
select ename,substr(ename,2,3) from emp;
–10.截取员工姓名第一位
select ename,substr(ename,1,1) from emp;

161-170

1）求字符串’ABCDE’的长度
select length(‘ABCDE’) from dual;–5
–2）求每个员工姓名的长度
select ename,length(ename) from emp;
–3）求姓名总共有五个字符的员工
select ename from emp where ename like ‘_____’;
select ename from emp where length(ename) = 5;
–4）求姓名总共有五位、且首字母是A的员工
select ename from emp where ename like ‘A____’;
select ename from emp where substr(ename,1,1) = ‘A’;
–5）将员工按照姓名长度分组，计算每个分组的人数，并按长度升序排列
select length(ename) 长度, count(*)
from emp
group by length(ename)
order by length(ename);
–6）查询字符串’小猪佩奇’的长度
select length(‘小猪佩奇’) from dual; --4
select lengthb(‘小猪佩奇’) from dual;–8
–7）录入员工信息：1000号员工’小猪佩奇’
select * from emp where ename = ‘小猪佩奇’;

–8.在名字左侧添加字符使总长度达到10
select ename, lpad(ename,10,’’) from emp;–*****SMITH
–9.在名字右侧添加字符]使总长度达到10
select ename, rpad(ename,10,’]’) from emp;–SMITH]]]]]
–10.求-123.45的绝对值
select abs(123.45),abs(-123.45) from dual;

171-180

1.求名字中至少有两个A的员工
select * from emp where ename like ‘%A%A%’ ;
select * from emp where instr(ename,‘A’,1,2)!= 0 or instr(ename,‘A’,1,3) > 0;

–2.求名字中正好有两个A的员工
select * from emp where ename like ‘%A%A%’ and ename not like ‘%A%A%A%’;
select * from emp where instr(ename,‘A’,1,2)!=0 and instr(ename,‘A’,1,3)=0 ;
–3.对5.5向上取整
select ceil(5.5) from dual;–6
–4.对5.5向下取整
select floor(5.5) from dual;–5
–5.求13/5的余数
select mod(13,5) from dual;–3
–6.对5.4321进行四舍五入
select round(5.4321,2.222),round(5.4321,2.99999) from dual;–5.43–5.43
–7.对5.4321进行截取
select trunc(5.4321,2.2222),trunc(5.4321,2.99999) from dual;–5.43–5.43
–8.当前时间减去1天
select sysdate-1 from dual;–减一天
–9.当前时间减去一小时
select sysdate-1 from dual;–减一天
–10.当前时间减去一分钟
select sysdate-1/24/60 from dual;–减一分钟

181-190

1）计算从元旦到今天经过了几个月（整数月）
select to_date(‘20220107’,‘YYYYMMDD’) from dual;–2022/01/07
select to_date(‘20220101’,‘YYYYMMDD’) from dual;–2022/01/01
select months_between(to_date(‘20220107’,‘YYYYMMDD’),to_date(‘20220101’,‘YYYYMMDD’)) from dual;–0.193548387096774
select (7-1)/31 from dual;–0.193548387096774
select ceil(months_between(to_date(‘20220107’,‘YYYYMMDD’),to_date(‘20220101’,‘YYYYMMDD’))) from dual;–1
select round(months_between(to_date(‘20220107’,‘YYYYMMDD’),to_date(‘20220101’,‘YYYYMMDD’))) from dual;–0
select trunc(months_between(to_date(‘20220107’,‘YYYYMMDD’),to_date(‘20220101’,‘YYYYMMDD’))) from dual;–0
select floor(months_between(to_date(‘20220107’,‘YYYYMMDD’),to_date(‘20220101’,‘YYYYMMDD’))) from dual;–0

–2）计算从元旦到今天经过了几个月（非整数月）
select months_between(to_date(‘20220107’,‘YYYYMMDD’),to_date(‘20220101’,‘YYYYMMDD’)) from dual;–0.193548387096774

–3）计算从今天到明年元旦还要等待几个月（整数月）
select to_date(‘20220107’,‘YYYYMMDD’),–2022/01/07
to_date(‘20230101’,‘YYYYMMDD’),–2023/01/01
months_between(to_date(‘20230101’,‘YYYYMMDD’),to_date(‘20220107’,‘YYYYMMDD’)),–11.8064516129032
round(months_between(to_date(‘20230103’,‘YYYYMMDD’),to_date(‘20220107’,‘YYYYMMDD’)))–12
from dual;
–25=2022/12/07-2023/01/01
select 25/31 from dual;–0.806451612903226
–4.获取月末日期
select last_day(to_date(‘20240204’,‘YYYYMMDD’)) from dual;–2024/02/29
–获取当月最后一天
select sysdate,systimestamp,last_day(sysdate) from dual;–2022/01/31 11:18:03
–5.查询某月份的最大天数
select last_day(to_date(‘20240804’,‘YYYYMMDD’)) from dual;–2024/08/31
–6.获取给定两日期的月份差（整数月）
select last_day(to_date(‘20240804’,‘YYYYMMDD’)),–2024/08/31
last_day(to_date(‘20240804’,‘YYYYMMDD’)),–2024/08/31
months_between(last_day(to_date(‘20240804’,‘YYYYMMDD’)),last_day(to_date(‘20240204’,‘YYYYMMDD’)))–6
from dual;
–7.当前时间减去两个月
select sysdate,add_months(sysdate,-2) from dual;
–8.返回日期当月最后一天
select to_date(‘20220107’,‘YYYYMMDD’),–2022/01/07
last_day(to_date(‘20220107’,‘YYYYMMDD’)),–2022/01/31
add_months(last_day(to_date(‘20220107’,‘YYYYMMDD’)),-1),–2021/12/31
add_months(last_day(to_date(‘20220107’,‘YYYYMMDD’)),-1)+1 from dual;–2022/01/01

select trunc(to_date(‘20220107’,‘YYYYMMDD’),‘MM’) from dual;–2022/01/01
select round(to_date(‘20220107’,‘YYYYMMDD’),‘MM’) from dual;–2022/01/01
select to_char(‘20220107’,‘YYYY/MM’) from dual;
–9.–给定任意日期，如何获取下个周的星期五
select to_date(‘20220107’,‘YYYYMMDD’)，–2022/01/07
trunc(to_date(‘20220107’,‘YYYYMMDD’),‘IW’),–2022/01/03 找到本周第一天 星期一
trunc(to_date(‘20220107’,‘YYYYMMDD’),‘IW’)+5,–2022/01/08 星期六
next_day(trunc(to_date(‘20220107’,‘YYYYMMDD’),‘IW’)+5,‘星期五’)–下个星期五
from dual;

select next_day(to_date(‘20220107’,‘YYYYMMDD’),‘星期五’) from dual;–2022/01/14

select * from emp;

190-200

1.将所有员工的名字按小写的方式显示。
select lower(ename) from emp;
–2.显示所有员工姓名的前三个字符。
select substr(ename,1,3) from emp;
–3.显示正好为5个字符的员工的姓名。
select ename from emp where ename like ‘_____’;
–4.以首字母大写，后面小写的方式显示所有员工的姓名。
select initcap(ename) from emp;
–5.以首字母小写，后面大写的方式显示所有员工的姓名。
select lower(substr(ename,1,1))||upper(substr(ename,2)) from emp;
–6.以首字母大写，中间小写，末尾字母大写的方式显示所有员工的姓名。concat
select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-2))||upper(substr(ename,length(ename)-1,1)) from emp;
–7.显示所有员工的姓名，用“我是老虎”替换所有“A”
select replace(ename,‘A’,‘我是老虎’) from emp;
–8.显示薪水的时候，把本地货币单位加在前面
select to_char(sal,‘L99999.99’) from emp;
–9.显示1982年和1987年入职的所有员工
select * from emp where to_char(hiredate,‘YYYY’) = 1982 or to_char(hiredate,‘YYYY’) = 1987;
—10.显示所有10月份入职的员工
select * from emp where to_char(hiredate,‘MM’) = 10;

201-210

1.以字符长度为10的方式显示员工职位，多余的位数在以来填充
select rpad(job,10,’’) from emp;
–2.找出字符串“oracle training”中第二个ra出现的位置
select instr(‘oracle training’,‘ra’,1,2) from dual;–9
–3.去除字符串“ aadde gf ”两边的空格
select trim(’ aadde gf ‘) from dual;
–4.以指定格式显示员工的工资（格式：SMITH 的工资是 800）
select ename||’ 的工资是 '||sal from emp;
–5.显示所有员工的姓名,用a替换所有"A"
select replace(ename,‘A’,‘a’) from emp;
–6.显示员工姓名中包含“H”的员工
select ename from emp where ename like ‘%H%’;
–7.显示员工姓名中第二个字符是“L”的员工
select ename from emp where ename like ‘_L%’;
–8.显示员工姓名中最后一个字符是“T”的员工
select ename from emp where ename like ‘%T’;
–9.显示所有员工的姓名、加入公司的年份和月份，并且按照年份升序排列
select ename,to_char(hiredate,‘YYYY/MM’) from emp order by to_char(hiredate,‘YYYY’);
–10.查询当前日期(精确到秒)
select systimestamp from dual;

211+

1.显示员工工资，加上$
select ‘$’||sal from emp;
–2.将字符串"1980-12-17"转成日期
select to_date(‘1980-12-17’,‘YYYY-MM-DD’) from dual;
–3.求入职最早员工和入职最晚员工的入职日期
select to_date(min(to_number(to_char(hiredate,‘YYYYMMDD’))),‘YYYYMMDD’),to_date(max(to_number(to_char(hiredate,‘YYYYMMDD’))),‘YYYYMMDD’) from emp;
–4.求名字中至少有两个A的员工
select * from emp where ename like ‘%A%A%’;

学习练习记录，仅供参考！