1. select DEPTNO from DEPT ;
2. select DEPTNO from EMP ;
3. select DISTINCT “JOB” from EMP ;
4. select * from EMP where SAL>2000;
5. select ENAME,EMPNO,SAL from EMP where DEPTNO=20;
6. select * from EMP where (COMM is NULL OR COMM = 0);
7. select * from EMP where (COMM is NOT NULL );
8. select * from EMP where MGR is NULL;
9. select * from EMP where HIREDATE >= “TO_DATE”(‘1981-01-01’,’yyyy-mm-dd’);
select * from EMP where HIREDATE >= ‘1-1月-81’;
10. select * from EMP where SAL BETWEEN 2000 AND 4000;
11. select * from EMP where DEPTNO IN (10,30);
12. select * from EMP where DEPTNO = 20 AND SAL > 2000;
13. select * from EMP where SAL NOT BETWEEN 2000 AND 4000;
14. select * from EMP where DEPTNO NOT IN (10,30);
15. select * from EMP where ENAME = ‘SCOTT’;
16. select ENAME from EMP WHERE ENAME LIKE ‘%ALL%’;
17. select * from EMP WHERE ENAME LIKE ‘S%’;
18. select ENAME from EMP WHERE ENAME LIKE ‘_A%’;
19. select EMPNO,ENAME,DEPTNO,”JOB”,SAL from EMP ORDER BY SAL DESC;
20. select * from EMP ORDER BY DEPTNO DESC,SAL ASC;
21. select ENAME,EMPNO,SAL from EMP WHERE ENAME LIKE ‘%A%’ ORDER BY SAL DESC;
22. SELECT ENAME,EMPNO,SAL,(SAL+”NVL”(COMM, 0))*12 AS YEARSAL
FROM EMP WHERE (SAL+”NVL”(COMM, 0))*12 > 10000 ORDER BY YEARSAL DESC;
23. SELECT ENAME,EMPNO,SAL,SAL*12 AS YEARSAL
FROM EMP WHERE SAL*12 > 10000 ORDER BY YEARSAL DESC;
24. SELECT ROUND((SYSDATE-HIREDATE)/7) AS “Weeks” FROM EMP WHERE ENAME = ‘SMITH’;
25. SELECT DEPTNO,”MAX”(SAL),”MIN”(SAL),”AVG”(SAL) FROM EMP GROUP BY DEPTNO;
26. SELECT ENAME FROM EMP WHERE EMPNO = (SELECT MGR FROM EMP WHERE ENAME = ‘SMITH’);
27. SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = ‘SALES’);
28. SELECT * FROM EMP WHERE SAL = (SELECT “MAX”(SAL) FROM EMP);
29. SELECT “COUNT”(ENAME) FROM EMP ;
30. SELECT “MAX”(SAL) FROM EMP ;
31. SELECT “AVG”(“NVL”(COMM, 0)) FROM EMP ;
32. SELECT “MAX”(HIREDATE) FROM EMP ;
33. SELECT “COUNT”(ENAME) FROM EMP WHERE (COMM IS NOT NULL) ;
34. SELECT “MAX”(SAL) FROM EMP WHERE DEPTNO = 20 ;
35. SELECT “AVG”(SAL),DNAME,EMP.DEPTNO FROM EMP,DEPT where EMP.DEPTNO = DEPT.DEPTNO GROUP BY DNAME,EMP.DEPTNO;
36. select empno , ename,DEPTNO from emp WHERE SAL in (select “MAX”(SAL) from EMP GROUP BY DEPTNO);
37. SELECT “MAX”(SAL) FROM EMP WHERE ENAME LIKE ‘%A%’ ;
38. SELECT “MAX”(SAL),”MIN”(SAL) FROM EMP WHERE SAL > 1000 GROUP BY “JOB”;
39. SELECT AVG(SAL),DEPTNO FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > 2000;
40. SELECT AVG(SAL),DEPTNO FROM EMP where ENAME LIKE ‘%A%’ GROUP BY DEPTNO HAVING AVG(SAL) > 1500 ORDER BY AVG(SAL) DESC;
41. select * from EMP where (deptno,SAL) in (select deptno,MAX(sal) from emp GROUP BY DEPTNO);
42. SELECT * FROM EMP WHERE SAL = (SELECT “MAX”(SAL) FROM EMP);
43. select * from emp e1 where sal > (
select avg(sal) from emp e2 where e1.deptno = deptno
);
44. SELECT * FROM EMP WHERE SAL = (SELECT “MAX”(SAL) FROM EMP);
45. select * from EMP where (deptno,SAL) in (select deptno,MAX(sal) from emp GROUP BY DEPTNO);
46. select ENAME from EMP where EMPNO = (select MGR from EMP where ENAME = ‘SMITH’) ;
47. select * from EMP where DEPTNO = (SELECT DEPTNO FROM DEPT WHERE dname = ‘SALES’) ;
48. select * from EMP where SAL = (SELECT MAX(SAL) FROM EMP) ;
49. select * from emp where sal between
(select LOSAL from salgrade where grade = 4) AND
(select HISAL from salgrade where grade = 4) ;
50. select * from emp where MGR = (select EMPNO from emp where ENAME = ‘BLAKE’);
51. select grade FROM SALGRADE where (SELECT SAL from emp where mgr is NULL) BETWEEN LOSAL AND HISAL;
52. select * from emp where SAL = (select MIN(sal) from emp);
53. select * from emp where JOB = (select JOB from emp where ENAME = ‘SMITH’) ;
54. select * from emp e1 where not EXISTS
(select * from emp e2 where e2.mgr = e1.empno);
55. select deptno from emp group by deptno having avg(sal) <
(select avg(sal) from emp where deptno = 10);
56. select * from EMP where DEPTNO = (select DEPTNO from DEPT where LOC = ‘NEW YORK’);
57. select grade from SALGRADE where
(select AVG(SAL) from emp where DEPTNO =
(select DEPTNO FROM DEPT WHERE dname = ‘SALES’) ) BETWEEN LOSAL AND HISAL;
58. select (select COUNT(DEPTNO) from EMP where DEPTNO = 10) / (select COUNT(*) from EMP) FROM dual;
59. select rownum rn,EMP.* from emp where rownum <= 5;select * from
(select rownum rn,EMP.* from emp where rownum <= 10)
where rn > 5; select emp.* from
(select rownum rn,t1.* from
(select * from emp order by sal desc) t1
where rownum <= 10) emp
where rn > 5;
60. select * from emp e1 where sal > (
select avg(sal) from emp e2 where e1.deptno = deptno
);
61. select * from emp e1 where sal < (select avg(sal) from emp e2 where e1.job= job group by job);
62. select * from emp e1 where EXISTS
(select * from emp e2 where e2.mgr = e1.empno);
63. select ename,sal,dname from emp,dept where EMP.DEPTNO = DEPT.DEPTNO;
64. select ename,sal,dname,grade from emp,dept,SALGRADE
where EMP.DEPTNO = DEPT.DEPTNO and SAL BETWEEN LOSAL AND HISAL;
65. select a.ename AS 员工姓名 ,b.ename AS 领导姓名
from emp a LEFT JOIN emp b on a.mgr = b.empno;
66. select ename, dname from emp ,dept where EMP.deptno = DEPT.deptno;
1. select ename,sal from emp where sal > 1600;
2. select ename,deptno from emp where empno = 17;
3. select ename,sal from emp where sal not BETWEEN 4000 and 5000;
4. select ename,deptno from emp where deptno in (20,30);
5. select ename,job from emp where mgr is null ORDER BY job asc;
6. select ename,sal,comm from emp where comm is not null ORDER BY sal asc;
7. select ename from emp where ename like ‘__A%’;
8. select dname,loc from dept ;
9. select DISTINCT job from emp ;
10. select ename ||’,’|| job ||’,’|| sal AS ename_job_sal from emp ;
11. select empno ,ename,sal ,sal*1.2 from emp ;
12. select empno ,sal,HIREDATE from emp where sal > 1200 ORDER BY HIREDATE ;
13. select dname from dept where dname != ‘ACCOUNTING’;
14. select MAX(sal),deptno from emp where ename not like ‘_A%’ GROUP BY deptno having avg(sal) > 3000 ;
15. select ename ,dname,grade from emp,dept,salgrade
where job = ‘MANAGER’ and (sal BETWEEN LOSAL and HISAL)
and EMP.DEPTNO = DEPT.DEPTNO;
16. select max(sal),min(sal),avg(sal) from emp where sal > 1200
group by deptno having avg(sal) > 1500 order by avg(sal) DESC;
17. select ename from emp where sal = (select max(sal) from emp);
18. select grade from salgrade s join
(select avg(sal) avg_sal from emp e group by deptno) temp
on TEMP.avg_sal between s.LOSAL and s.HISAL;
19. select * from emp where ename like ‘S%’ or ename like ‘s%’;
20. select empno,ename,TO_CHAR(HIREDATE,’yyyy”年”MM”月”dd”日”’) from emp where
TO_CHAR(SYSDATE,’YYYY’) - TO_CHAR(hiredate,’YYYY’) > 1;
21. select ename,sal+NVL(comm, 0) from emp where deptno = 20 ;
22. select * from emp where deptno = 10 and sal > 3000 ORDER BY HIREDATE ;
23. select SUBSTR(ename, 1, 3) from emp where deptno in (20,10) ORDER BY ename ;
24. select lower(ename),TOCHAR(HIREDATE,′YYYY−MM−DD′),TOCHAR(sal,′99,999.999’) from emp ;
25. select e1.ename,DEPT.dname,e1.sal,grade,e2.ename AS leader from emp e1,emp e2,dept,salgrade
where nvl(e1.MGR,0) = e2.empno and (e1.sal between SALGRADE.LOSAL and SALGRADE.HISAL)
and e1.deptno = dept.deptno;
26. select ename,grade from emp ,salgrade ,dept where
dname=’ACCOUNTING’ and sal between LOSAL and HISAL
and EMP.deptno = DEPT.deptno ;
27. select sal from emp where sal >= all (select sal from emp);
Oracle练习题答案
最新推荐文章于 2021-04-03 00:01:13 发布