2017-07-24

 

 

----1
select * from emp where deptno=30;
-----2
select empno, ename,deptno from emp where JOB='CLERK';
-----3
select * from emp where comm>sal;
-----4
select * from emp where comm>sal*(60/100);
-----5
select * from emp where deptno=10 AND job='MANAGER';
-----6
select* from emp where(deptno=10 and job='MANAGER')or(job!='MANAGER' and job!='CLERK'and sal>=2000);
-----7
select * from emp where comm >0;
-----8
select * from emp where comm is null or comm<100;
-----9
select * from emp where last_day(hiredate)-2=hiredate;
-----10
select * from emp where hiredate months_between(sysdate,to_date('12-02-98','yyyymmdd'));
-----11
select upper(ename) from emp;
-----12
select * from emp where ename like '_____'; -----where length(ename)=5;
-----13
select ename from emp where ename not like '%R%';
-----14
select substr(ename,0,3) from emp;
-----15
select replace(ename,'A','L') from emp;
-----16
select ename,hiredate from emp where hiredate<=add_months(sysdate,-12*30);

------17
select * from emp order by ename;
------18
select ename,hiredate from emp order by hiredate asc;
------19
select ename,job,sal from emp order by job desc,sal;
------20
select ename,hiredate from emp order by to_char(hiredate,'fmmm') ,to_char(hiredate,'fmyy') asc;
------21
select ename, round(sal/30,0) from emp ;
------22
select * from emp where to_char(hiredate,'fmmm')=2;
------23
select ename,trunc(sysdate-hiredate) days from emp;
------24
select ename from emp where ename like '%A%';