ORACLEクエリー練習問題
7019 ワード
--01. ， *
-- ， ，
--01． （job） 'PRESIDENT'
Select ename,job,sal from emp
Where job=’PRESIDENT’;
--02． 0 null
Select * from emp where comm=0 or comm is null;
--03． 1981-5-1 1981-12-31
Select * from emp
Where
hiredate >to_date(‘1981-5-1’,’yyyy-mm-dd’)
and
hiredate<=to_date(‘1981-12-31’,’yyyy-mm-dd’);
--04．. 4 ，
Select ename,empno from emp
Where enaem like ‘____’;
--05． 10 20
Select ename,job,deptno from emp
Where (deptno=10 and job=’MANAGER’ OR DEPTNO=20);
--06． 'L' 'SM'
Select ename from emp
Where
(ename not like ‘%L%’)
Or
(ename like %SM%);
--07．
Select * from emp where job=’MANAGER’;
--08．
Select * from emp where comm>sal;
--10． hiredate ， ：
Select ename,hiredate from emp
Where extract(month from hriedate)=extract(month from sysdate)-1;
--11． hiredate ，
Where extract(month from hriedate)=extract(month from sysdate)+1;
--12. 1982
Select * from emp where extract(year from hiredate)=’1982’;
--13． 1981
Select * from emp where
hiredate > to_date(‘1981-6-1’,’yyyy-mm-dd’)
and
hiredate
(SELECT sal
FROM emp
WHERE ename='ALLEN')
--06． scott （ ）
SELECT *
FROM emp
WHERE job=
(SELECT job
FROM emp
WHERE ename='SCOTT')
AND ename<>'SCOTT'
--07． (‘SALES’)
SELECT ename
FROM emp
WHERE deptno=(
SELECT deptno
FROM dept
WHERE dname='SALES')
--08． 30 ’MARTIN’
SELECT ename,sal
FROM emp
WHERE sal=(
SELECT sal
FROM emp
WHERE deptno=30
AND ename='MARTIN')
--09． （ ）
SELECT ename,sal
FROM emp
WHERE job='ANALYST' AND sal>
(SELECT MAX(avg(sal))
FROM emp
GROUP BY deptno)
--10． ( )
SELECT e.ename,e.sal,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno
--11． (RESEARCH) ， ， ，
SELECT d.deptno,e.ename,d.dname,d.loc
FROM dept d,emp e
WHERE d.dname='RESEARCH'
AND e.deptno=d.deptno
select * from dept
--12．
SELECT d.dname,temp.count
FROM dept d,(
SELECT deptno dno,COUNT(empno) count
FROM emp GROUP BY deptno ) temp
WHERE d.deptno=temp.dno
--13． （ ） ( )
-- ，
SELECT d.dname,e.job,tmp.con
FROM emp e,dept d,(SELECT job j,COUNT(empno) con FROM emp GROUP BY job) tmp
WHERE sal>
(SELECT ROUND(AVG(sal),2)
FROM emp)
AND tmp.j=e.job
AND d.deptno=e.deptno;
--select count(*),job from emp where sal>(select avg(sal) from emp) group by job;
--14． （ ）
SELECT e.ename,e.sal
FROM emp e
WHERE (SELECT COUNT(*)
FROM emp
WHERE sal=e.sal GROUP BY sal )>1
--15． 3 ( )
SELECT *
FROM
(SELECT e.* ,
ROW_NUMBER() OVER(ORDER BY sal desc) id
FROM emp e)
WHERE id<=3
--16． ： 1 ，
--（ 1 2 ， ）
SELECT e.*,RANK()OVER(ORDER BY sal DESC)
FROM emp e
--17． （ ）
SELECT e.*
FROM emp e
WHERE (
SELECT COUNT(*)
FROM emp
WHERE e.hiredate=hiredate
GROUP BY hiredate )>1
--18．
SELECT e.deptno,MAX(sal)
FROM emp e
GROUP BY deptno;
--19． ，
SELECT deptno,job,MAX(SAL)
FROM emp
GROUP BY deptno,job
ORDER BY deptno;
--20． ， (Salgrade)
SELECT e.*,s.grade
FROM emp e,salgrade s
WHERE e.sal
BETWEEN s.losal AND s.hisal
SELECT * FROM salgrade;
--21. 6-10
：
R SAL
---------- -----
6 2450
7 1600
8 1500
9 1300
10 1250
Order by rownum ，
SELECT *
FROM (SELECT ROWNUM r,sal
FROM( SELECT sal
FROM emp ORDER BY sal DESC))
WHERE r BETWEEN 6 AND 10;
--22．
SELECT *
FROM emp
WHERE sal in (SELECT MAX(sal)
FROM emp
GROUP BY deptno)
--23． 2
SELECT *
FROM
(SELECT e.*,ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY sal) DES
FROM emp e)
WHERE DES<3
--24． 3 （ ）
SELECT *
FROM emp e
WHERE(
SELECT COUNT(*)
FROM emp WHERE e.empno=mgr)>=3
--25．
SELECT *
FROM emp e
WHERE e.sal >
(SELECT ROUND(AVG(sal),2) AVG
FROM emp
WHERE e.deptno=deptno)
--26．
SELECT d.*,avgsal
FROM dept d,
(SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) e
WHERE avgsal=
(SELECT MAX(AVG(sal)) maxsal FROM emp GROUP BY deptno)
AND d.deptno=e.deptno
--27．
SELECT d.deptno,d.dname,e.avg
FROM dept d,
(SELECT e.deptno dno, AVG(sal) avg FROM emp e GROUP BY deptno) e
WHERE e.avg=
(SELECT MAX(AVG(sal)) maxavg FROM emp GROUP BY deptno)
AND e.dno=d.deptno
--28．
SELECT e.*,d.*
FROM emp e,dept d
WHERE e.sal>
(SELECT MAX(AVG(sal))
FROM emp e
GROUP BY deptno)
AND d.deptno=e.deptno
--29．
SELECT d.*,e.count
FROM dept d,
(SELECT e.deptno dno,COUNT(empno) count
FROM emp e
GROUP BY deptno)e
WHERE e.dno(+)=d.deptno
AND e.count is null
--partIII
--1、 （ ）
SELECT d.*,e.* FROM dept d,
(SELECT e.deptno dno,e.* FROM emp e)e
WHERE d.deptno=e.dno
--2、 ( ) 'SMITH' （ ）
SELECT e.*
FROM emp e
WHERE e.sal>
(SELECT sal
FROM emp
WHERE ename='SMITH' )
--3、 （ ）
SELECT e.*,d.empno
FROM emp e,emp d
WHERE e.mgr=d.empno(+)
--4、 ( ) （ ）
SELECT e.*
FROM emp e,emp d
WHERE e.mgr=d.empno
AND e.hiredate1500
--8、 sales( ) ，
--（ ）
SELECT e.deptno,e.*
FROM dept d,emp e
WHERE dname='SALES'
AND e.deptno=d.deptno;
--9、 （ ）
SELECT e.*
FROM emp e
WHERE e.sal>
(SELECT MAX(AVG(sal))
FROM emp
GROUP BY deptno)
--10、 "SCOTT" （ ）
SELECT e.*
FROM emp e
WHERE e.job=
(SELECT job
FROM emp
WHERE ename='SCOTT')
AND e.ename<>'SCOTT'
--11、 30 （ ）
--12、 30 （ ）
SELECT e.ename,e.sal
FROM emp e
WHERE e.sal>(SELECT MAX(sal)
FROM emp
WHERE deptno=30)
--13、 （ ）
-- （ ） （ ）
SELECT d.*,e.count
FROM dept d,
(SELECT e.deptno dno,COUNT(deptno) count
FROM emp e
GROUP BY e.deptno)e
WHERE e.dno(+)=d.deptno
--14、 、 ( )
SELECT e.ename,d.dname,e.sal
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
--15、 ( )
SELECT e.deptno,d.deptno,e.job,d.job,e.ename,d.ename
FROM emp e,emp d
WHERE e.job=d.job AND e.deptno<>d.deptno AND e.ename