sql练习1
merge into T_obj t
using (SELECT'1004'AS a,3 AS b FROM dual) d
on (t.a=d.a)
when matched then
update set t.b=8
when not matched then
insert (a,b) values('200',55)
select d ,add_months(d,-1) as lastmouth from
(select Date '2018-11-27' + level as d from dual connect by level<=4)
select trunc( select months_between (date '2018-03-31',date '2018-2-25') from dual as t) as datew from dual
select hiredate ,to_date(to_char(hiredate ,'yyyy-mm') || -1,'yyyy-mm-dd') as dd from emp where rownum<=1
with a1 as (select timestamp '1981-01-02 11:02:33.55' as t1,timestamp '1981-01-01 16:30:33.55' as t2 from dual)
select extract(DAY from t1-t2)*24*60 from a1
create table emp2 as select * from emp where 1=2
select * from emp2
select job,sal from emp where deptno=20 order by job
select
sum(case job when 'ANALYST' then sal end ) as an,
sum(case job when 'CLERK' then sal end ) as an1 ,
sum(case job when 'MANAGER' then sal end ) as an2
from emp where deptno=20
select
case job when 'ANALYST' then sum(sal) end as an,
case job when 'CLERK' then sum(sal) end as an1 ,
case job when 'MANAGER' then sum(sal) end as an2
from emp where deptno=20
select
sum(decode(job,'ANALYST' ,sal)) as a1,
sum(decode(job,'CLERK' , sal)) as a1,
sum(decode(job,'MANAGER', sal)) as a1
from emp where deptno=20
select empno,ename,sal,hiredate ,sum(sal) over(order by hiredate) as工资 from emp where deptno=20
select deptno, empno,ename,sal,hiredate ,sum(sal) over( partition by deptno order by hiredate) as工资 from emp
select * from
(select empno,ename,sal,hiredate ,sum(sal) over(order by hiredate) as ss from emp ) where ss<=6000
select banner from sys.v_$version;