oracle基本sql练习

扎实的基础是搭起高楼的必须。

SCOTT > desc emp;

SCOTT > select * from tab;

SCOTT > select distinct deptno from emp;

SCOTT > select ename,sal,sal+300 from emp;

SCOTT > select ename,sal,sal*12+100 from emp;

SCOTT > select ename,sal,12*(sal+100) from emp;

SCOTT > select empno,ename,job,hiredate "STARTDATE" from emp;

SCOTT > select ename||' ---&gt '||job "Employee and Title" from emp;

SCOTT > select ename,sal from emp where sal>1200;

SCOTT > select ename,deptno from emp where empno=7788;

SCOTT > select ename,sal from emp where sal <=2000 and sal>=1200;

SCOTT > select ename,sal from emp where sal between 1200 and 2000;

 SCOTT > select ename,sal from emp where sal >=1500 and (deptno=10 or deptno=30);

 SCOTT > select ename,sal from emp where sal >=1500 and deptno in (10,30);

 SCOTT > select ename,job from emp where mgr is null;

 SCOTT > select ename,job,hiredate from emp where hiredate >=to_date('1981-02-01','yyyy-mm-dd') and hiredate <=to_date('1981-05-01','yyyy-mm-dd') order by hiredate;

 SCOTT > select ename,deptno from emp where deptno=20 or deptno=30 order by ename;

 SCOTT > select ename,sal,comm from emp where comm is not null order by sal,comm;

 SCOTT > select ename from emp where ename like '__A%';

 SCOTT > select ename from emp where ename like '%A%' and ename like '%E%';

SCOTT > select ename,job,sal from emp
  2  where job in ('SALESMAN','CLERK') and sal not in (1600,1000);

 SCOTT > select ename "Employee",sal "Monthly Salary" from emp
  2  where sal between 1200 and 2000 and (deptno=20 or deptno=30);

 SCOTT > select sysdate from dual;

 SCOTT > select empno,ename,sal,trunc(sal*(1+0.15)) as "New Salary" from emp;

 SCOTT > select initcap(ename) name,length(ename) lengrh from emp
  2  where substr(ename,1,1) in ('J','A','M')
  3  order by ename;

 SCOTT > select ename,sal,nvl(comm,0) from emp;

SCOTT > select ename,months_between(sysdate,hiredate)/12 from emp;

SCOTT > select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

 SCOTT > select deptno,ename,sal,case deptno when 10 then sal*1.1
  2  when 20 then sal*1.2
  3  else sal end salary
  4  from emp;

 SCOTT > select dname,count(*) from emp e,dept d where e.deptno=d.deptno group by dname having count(*)>=1;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23203417/viewspace-625616/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23203417/viewspace-625616/