Oracle查询语句面试题

select * from emp;

select sal from emp where job='PRESIDENT';

select * from emp where comm is null or comm=1;

select * from emp where hiredate between to_date(‘1-5月-81’) and to_date(‘31-12月-81’)

select empno,ename from emp where length(ename)=4

select * from emp where job='MANAGER' and deptno=10 or job='CLERK' and deptno=20;

select * from emp where ename not like '%L%' or ename like '%SM%';

select deptno,sal from emp where job='MANAGER';

select * from emp where comm>sal;

SQL> select * from emp
where to_char(hiredate,'mm')=to_char(sysdate,'mm');

SQL> select * from emp
where to_char(hiredate,'mm')=to_char(add_months(sysdate,1),'mm');

SQL> select * from emp where to_char(hiredate,'yyyy')='1982';

select * from emp
where hiredate between to_date('1981-7-1','yyyy-mm-dd')
and to_date('1982-1-1','yyyy-mm-dd')-1;

SQL> select to_char(hiredate,'mm'),count(*) from emp where to_char(hiredate,'yyyy')='1981' group by to_char(hiredate,'mm') order by to_char(hiredate,'mm');

select to_char(hiredate,'mm'),count(*) from emp where to_char(hiredate,'yyyy')='1981' group by to_char(hiredate,'mm') order by to_char(hiredate,'mm');

SQL> select deptno,avg(nvl(sal,0)) from emp group by deptno;

SQL> select job,min(sal) from emp group by job;

SQL> select hiredate from emp order by hiredate desc;

SQL> select e.*,e1.ename from emp e,emp e1 where e.mgr=e1.empno;

SQL> select ename,sal from emp where sal>(select sal from emp where ename='ALLEN');

SQL> select * from emp where job=(select job from emp where ename='SCOTT');

SQL> select e.ename from emp e inner join dept d on e.deptno=d.deptno where d.dname='SALES';

SQL> select ename,sal from emp where sal=(select sal from emp where deptno=30 and ename='MARTIN');

SQL> select * from emp where sal>(
select avg(sal) from emp) and job='SALESMAN';

SQL> select * from emp where job='SALESMAN' and sal>(select avg(sal) from emp);

SQL> select e.*,e.sal,d.dname from emp e inner join dept d on e.deptno=d.deptno;

SQL> select e.empno,e.ename,d.dname,d.loc from emp e inner join dept d on e.deptno=d.deptno where dname='RESEARCH';

select e.deptno,d.dname,count(*) from emp e inner join dept d on e.deptno=d.deptno group by e.deptno,d.dname

SQL> select d.dname from emp e inner join dept d on e.deptno=d.deptno group by e.deptno,d.dname;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
SQL> select e.deptno,d.dname from emp e inner join dept d on e.deptno=d.deptno group by e.deptno,d.dname;
DEPTNO DNAME
------ --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
SQL> select e.deptno,d.dname,count(*) from emp e inner join dept d on e.deptno=d.deptno group by e.deptno,d.dname;
DEPTNO DNAME COUNT(*)
------ -------------- ----------
10 ACCOUNTING 3

SQL> select count(*),job from emp where sal>(select avg(sal) from emp) group by job;

SQL> select sal,ename from emp e where(select count(*) from emp where sal=e.sal group by sal)>1;

SQL> select e.sal,e.ename from emp e,emp e1 where e.sal=e1.sal and e.ename<>e1.ename;