Oracle day7

--round(n,s):n：数字；s：精度，默认为0
select round(3.14) from dual;

-- 返回系统当前时间
select (sysdate+8) from dual;
select current_date from dual;

-- 加1/24天，加一个小时
select sysdate+1/24 from dual; 

-- 加月份
select add_months(sysdate,3) from dual;
select add_months(sysdate,-100) from dual;

-- 计算两个日期之间间隔多少个月
-- to_date()：格式化日期字符串
select months_between(sysdate,to_date('20010908','yyyyMMdd')) from dual;

--返回当前月的最后一天
select last_day(to_date('200109','yyyyMM')) from dual;

--trunc()：截断函数

-- 数字类型
select trunc(3.1415926,3) from dual;
-- 把小数点左边截断多少位，结果返回0
select trunc(3.1415926,-1) from dual; 
--默认直接截断小数点后面的部分
select trunc(3.888) from dual;
--日期类型
--截取到年，返回当年第一天
select trunc(sysdate,'yyyy') from dual;
select trunc(sysdate,'year') from dual;
--截取到季度，返回当前季度的第一天
select trunc(sysdate,'q') from dual;
--截取到月，返回当前月第一天
select trunc(sysdate,'mm') from dual;
select trunc(sysdate,'month') from dual;
--截取到周,返回本周第一天，从周日开始
select trunc(sysdate,'d') from dual;
select trunc(sysdate,'day') from dual;
--截取到天，只保留年月日
select trunc(sysdate) from dual;
--截取到时
select trunc(sysdate,'hh') from dual;
select trunc(sysdate,'hh24') from dual;
--截取到分
select trunc(sysdate,'mi') from dual;

select initcap('hello world') from dual;
select initcap('helloworld') from dual;

-- instr(str,n1,n2)
select instr('asdoqwerjldajsfoqjwelfjaoidsfj','j') from dual; 
--查字符串首次出现j的位置
select instr('asdoqwerjldajsfoqjwelfjaoidsfj','j',10) from dual;
--从字符串的第10个字符开始查找第一次出现j的位置
select instr('asdoqwerjldajsfoqjwelfjaoidsfj','j',10,3) from dual;
--从字符串的第10个字符开始查找第3次出现j的位置
select instr('asdoqwerjldajsfoqjwelfjaoidsfj','j',-3,3) from dual;
--从字符串的倒数第3个字符开始反向查找第3次出现j的位置

-- ASCII码
select ascii('A') from dual;
select ascii('a') from dual;

--1.将所有员工的名字按小写的方式显示：
select lower(ename) from emp;

--2.将所有员工的名字按大写的方式显示：
select upper(ename) from emp;

--3.显示正好为5个字符的员工的姓名：
select ename from emp where length(ename) = 5;

--4.显示所有员工姓名的前三个字符：
select substr(ename,1,3) from emp;

--5.以首字母大写,后面小写的方式显示所有员工的姓名：
select initcap(ename) from emp;

--6.以首字母小写后面大写方式显示所有员工姓名：
select lower(substr(ename,1,1)) || upper(substr(ename,2)) from emp;

select t1.lename || t2.uename from 
(select empno, substr(lower(ename),1,1) lename from emp) t1
 join  
(select empno,substr(upper(ename),2) uename from emp) t2
on t1.empno = t2.empno;

--7.显示所有员工的姓名，用“我是老虎”替换所有“A”：
select replace(ename,'A','我是老虎') from emp;

--8.查找已经入职8个月多的员工：
select * from emp where months_between(sysdate,hiredate) > 8;

--9.显示满10年工作年限的员工的姓名和受雇日期：
select ename,hiredate from emp where months_between(sysdate,hiredate)/12 > 10;

--10.对于每个员工，显示其加入公司的天数：
select round((sysdate-hiredate)) from emp ;

--21.求入职日期相同的（年月日相同）的员工：
select e1.ename from emp e1 join emp e2 on e1.hiredate = e2.hiredate where  e1.empno != e2.empno

--22.使用select语句取出‘ABC-D123EF’字符串中，‘-’符号后面的字符串，不含 ‘-’符号:（假设不确定‘-’符号的位置）：
select substr('ABC-D123EF',instr('ABC-D123EF','-')+1) from dual;

--23.根据不同部门查询平均工资，保留两位小数并在前面带‘$’字符：
select deptno,'$'||round(avg(sal),2) from emp group by deptno;

--24.查询学过姓名为两个字的老师的课的同学的姓名和成绩：
select s.sname,t.score from student s join 
（select sno,score from sc where cno in(
select cno from course where tno in(
select tno from teacher where length(tname) = 2))）t
on t.sno = s.sno;

--25.请用sql语句输出今天距离‘1949-10-01’的天数：
select round(sysdate-to_date('1949-10-01','yyyy-MM-dd')) from dual;

select to_char(sysdate,'y'),to_char(sysdate,'yy')
,to_char(sysdate,'yyy'),to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'hh24:mi:ss') from dual;
select to_char(sysdate,'day') from dual; --当前星期
select to_char(sysdate,'q') from dual; --季度
select to_char(sysdate,'ww') from dual; --一年中的第几周
select to_char(sysdate,'w') from dual; --某月的第几周
select to_char(sysdate,'ddd') from dual; --一年中的第几天
select to_char(sysdate,'dd') from dual; --某月的第几天
select to_char(sysdate,'d') from dual; --一周中的第几天

-- '''' 第二个为转义字符,第三个为'

select '抽''象艺''术' from dual; 

select 'I''m a String!' from dual;-- 第二个'为转义字符，返回I'm a String!
select '''' from dual; -- 第二个'为转义字符，返回'
select ',''' from dual;-- 第二个'为转义字符 ,返回,'

--11.找出各月倒数第3天受雇的所有员工：
select * from emp where extract(day from hiredate)
 =  extract(day from (last_day(hiredate))-2);
 
select * from emp where to_char(last_day(hiredate),'dd') - to_char(hiredate,'dd') = 2;
--12.显示1981年入职的所有员工：
select * from emp where extract(year from hiredate) = '1981';
select * from emp where to_char(hiredate,'yyyy') = '1981';

--13.显示所有10月份入职的员工：
select * from emp where extract(month from hiredate) = '10';
select * from emp where to_char(hiredate,'MM') = '10';

--14.查询入职日期在‘1981-5-1’到‘1981-12-31’之间的所有员工的信息：
select * from emp where hiredate between to_date('1981-5-01','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd');

--15.查询入职日期最早的前5名员工姓名：
select * from (
select e.*,rownum r from emp e order by hiredate) t
where t.r < 6;

--16.查询最早入职的员工和最晚入职的员工日期相差多少个月：
select round(months_between((select max(hiredate)  from emp),(select min(hiredate)  from emp))) from dual;

--17.把hiredate列看做是员工的生日，求下月过生日的员工：
select * from emp where to_char(hiredate,'MM') = to_char(sysdate,'MM')+1;

--18.查询在2019年入职并且薪水超过3000的员工姓名和入职日期：
select ename,hiredate from emp where sal > 3000 and extract(year from hiredate) = '2019';

--19.求1981年下半年入职的员工：
select * from emp where hiredate between to_date('1981-7-01','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd');
select * from emp where to_char(hiredate,'MM') in (7,8,9,10,11,12);

--20.求1981年各个月入职的员工个数：

select to_char(hiredate,'MM') ,count(empno) from emp where extract(year from hiredate) = '1981' group by to_char(hiredate,'MM');

--31.将学号为奇数的学生信息取出：
select * from student where mod(substr(sno,2),2) = 1;

--32.查询于1981年来公司所有员工的总收入（SAL和COMM）：(假设没有人离职也没有新人，并且每个人的工资稳定不变)
select sum(t1.sal+t2.sal) from (
select empno,(sal+nvl(comm,0))*(12-extract(month from hiredate)+1) sal from emp order by empno) t1 
join
(select empno,(sal+nvl(comm,0))*12*(extract(year from sysdate)-extract(year from hiredate)) sal from emp order by empno) t2
on t1.empno = t2.empno;

--33.对员工表进行脱敏，将员工编号（7**9）和姓名中间两位用‘*’表示：
select replace(empno,substr(empno,2,2),'**'),replace(ename,substr(ename,round(length(ename)/2),2),'**') from emp;

--34.显示姓名、hiredate和雇员开始工作日是星期几？
select ename,hiredate,to_char(hiredate,'day') from emp;

--35.找出平均工资最高的两种职位：
select tt.job from(
select t.*,rownum r from(
select job,avg(sal) from emp group by job order by avg(sal) desc) t) tt where tt.r < 3;

--36.求明天早上8点30分：
select to_date(to_char(sysdate+1,'yyyyMMdd'),'yyyy/MM/dd')+8.5/24 from dual;

--37.查询所有名字的开始字母是J、A或M的雇员，用首字母大写、其它字母小写显示雇员的全名，
--以及显示名字的长度，并对查询结果按雇员名字的长度升序排序：

select initcap(ename),length(ename) from emp where 
ename like 'J%' or ename like 'A%' or ename like 'M%' order by length(ename);

--38.查找字符串‘10,A,B,c,#’中逗号‘,’出现的次数：
select length('10,A,B,c,#')-length(replace('10,A,B,c,#',',','')) from dual;

--39.使用dual表取到今日凌晨一点，凌晨一点半两个时间：
select to_date(
to_char(sysdate,'yyyyMMdd'),'yyyy/MM/dd')+1/24 凌晨一点,
to_date(to_char(sysdate,'yyyyMMdd'),'yyyy/MM/dd')+1.5/24 "凌晨一点半"
 from dual;

--40.求当前季度的最后一天23点：
select last_day(add_months(trunc(sysdate,'q'),2))+23/24 from dual;