8,127
----------------------------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
- select add_months(q_end,-2) q_start,
- last_day(q_end) q_end
- from (select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') q_end
- from (
- select 20071 yrq from dual union all
- select 20072 yrq from dual union all
- select 20073 yrq from dual union all
- select 20074 yrq from dual
- ) x
- ) y
----------------------------------------------
Expand|Select|Wrap|Line Numbers
- with x
- as (
- select add_months(start_date,level-1) start_date
- from (
- select min(trunc(hiredate,'y')) start_date,
- add_months(max(trunc(hiredate,'y')),12) end_date from emp) connect by level <= months_between(end_date,start_date))
- select x.start_date MTH, count(e.hiredate) num_hired from x, emp e where x.start_date = trunc(e.hiredate(+),'mm')
- group by x.start_date
- order by 1
-----------------------------------
Expand|Select|Wrap|Line Numbers
- with x
- as (
- select add_months(start_date,level-1) start_date
- from (
- select min(trunc(hiredate,'y')) start_date,
- add_months(max(trunc(hiredate,'y')),12) end_date
- from emp)
- connect by level <= months_between(end_date,start_date)
- )
- select x.start_date MTH, count(e.hiredate) num_hired
- from x left join emp e
- on (x.start_date = trunc(e.hiredate,'mm'))
- group by x.start_date
- order by 1
-----------------------------------------------------------
Expand|Select|Wrap|Line Numbers
- select ename from emp
- where rtrim(to_char(hiredate,'month')) in ('february','december')
- or rtrim(to_char(hiredate,'day')) = 'tuesday'
----------------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
- select a.ename ||
- ' was hired on the same month and weekday as '||
- b.ename as msg
- from emp a, emp b
- where to_char(a.hiredate,'DMON') =
- to_char(b.hiredate,'DMON')
- and a.empno < b.empno
- order by a.ename
---------------------------------------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
- select deptno, ename, sal, hiredate,
- lpad(nvl(to_char(sal-next_sal), 'N/A'), 10) diff from ( select deptno, ename, sal, hiredate,
- lead(sal)over(partition by deptno
- order by hiredate) next_sal from emp)
------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
- select proj_id, proj_start, proj_end
- from (
- select proj_id, proj_start, proj_end,
- lead(proj_start)over(order by proj_id) next_proj_start from job)
- where next_proj_start = proj_end
===================================
Expand|Select|Wrap|Line Numbers
- select proj_grp, min(proj_start), max(proj_end)
- from (
- select proj_id,proj_start,proj_end,
- sum(flag)over(order by proj_id) proj_grp
- from ( select proj_id,proj_start,proj_end,
- case when
- lag(proj_end)over(order by proj_id) =proj_start
- then 0 else 1 end flag from job
- )) group by proj_grp
-----------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
- select x.yr, coalesce(cnt,0) cnt
- from (
- select extract(year from min(hiredate)over( )) -
- mod(extract(year from min(hiredate)over( )),10) +
- rownum-1 yr
- from emp
- where rownum <= 10
- ) x,
- (
- select to_number(to_char(hiredate,'YYYY')) yr, count(*) cnt
- from emp
- group by to_number(to_char(hiredate,'YYYY'))
- ) y
- where x.yr = y.yr(+)
------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
- with x
- as (
- select level id
- from dual
- connect by level <= 10
- )
- select * from x
-----------------------------------------
Expand|Select|Wrap|Line Numbers
- with x
- as (
- select *
- from (
- select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
- to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
- to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
- to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
- to_char(sysdate,'mm') mth
- from dual
- connect by level <= 31
- )
- where curr_mth = mth
- )
- select max(case dw when 2 then dm end) Mo,
- max(case dw when 3 then dm end) Tu,
- max(case dw when 4 then dm end) We,
- max(case dw when 5 then dm end) Th,
- max(case dw when 6 then dm end) Fr,
- max(case dw when 7 then dm end) Sa,
- max(case dw when 1 then dm end) Su
- from x
- group by wk
- order by wk
