Oracle select 문제풀이

SELECT *

From emp

where substr(to_char(HIREDATE,'yy/mm/dd'),1,2) ='87'; 

SELECT * From emp;

SELECT *

From emp

where substr(ename,-1,1) = 'E';

select * from emp where ename like '__N%';

select * from emp where instr(ename,'N')= 3;

SELECT * FROM all_users;

SELECT empno,ename,nvl(mgr,0) manager from emp where mgr is null;

SELECT empno,ename,job,sal, DECODE (job, 'ANALYST', sal*1.05, 

                             'SALESMAN', sal*1.1, 

                             'MANAGER', sal*1.15, 

                             'PRESIDENT', sal*1.20,

                                sal) as upsal

from emp ;

select sysdate "Currnet date"

from dual;

select EMPNO,ename,job,sal,(sal*1.15) as upsal,((sal*1.15) - sal) as plue

from emp;

select EMPNO,ename,job,sal,TRUNC(sal*1.15) "upsal",TRUNC((sal*1.15) - sal) "plue"

from emp;

select ename,HIREDATE,(TO_DATE(TO_CHAR(HIREDATE, 'YY/mm/dd'),'YY/mm/dd')+5*30) as his

from emp;

select ename,hIREDATE, add_months(HIREDATE,5)

from emp;

select ename,HIREDATE,trunc(months_between(to_date(sysdate,'YY/mm/dd'),TO_CHAR(HIREDATE, 'YY/mm/dd'))) as his

from emp;

SELECT ename, HIREDATE, trunc(months_between(sysdate,HIREDATE)) "between", trunc(sal*months_between(sysdate,HIREDATE))

from emp;

SELECT ename || '  earns' || to_char(sal,'L9,999') "dream salary"

from emp;

select ename, job,HIREDATE, to_char(HIREDATE,'DAY') 

from emp;

select ename,job,sal,comm,nvl(comm,0),sal+nvl(comm,0)

from emp;

SELECT count(DISTINCT JOB)

from emp;

select deptno,avg(sal)

from emp

group by deptno;

5. EMP 테이블에서 아래의 결과를 출력하는 SELECT 문장을 작성하여라.

H_YEAR  COUNT(*)  MIN(SAL)  MAX(SAL)  AVG(SAL)  SUM(SAL)

------ --------- --------- --------- --------- ---------

80             1       800       800       800       800

81            10       950      5000    2282.5     22825

82             2      1300      3000      2150      4300

1      1100      1100      1100      1100

select DISTINCT(to_char(HIREDATE,'yy')) as H_YEAR,count(*),min(sal),max(sal),avg(sal),sum(sal)

from emp

group by to_char(HIREDATE,'yy')

order by to_char(HIREDATE,'yy') asc;

6. EMP 테이블에서 아래의 결과를 출력하는 SELECT 문장을 작성하여라.

 TOTAL      1980      1981      1982      1983

--------- --------- --------- --------- ---------

       14         1        10         2         1

SELECT count(*) total,count(decode(to_char(hiredate,'yyyy'),1980,0)) "1980",

    count(decode(to_char(hiredate,'yyyy'),1981,0)) "1981",

    count(decode(to_char(hiredate,'yyyy'),1982,0)) "1982",

    count(decode(to_char(hiredate,'yyyy'),1987,0)) "1987"

from emp;

7. EMP 테이블에서 아래의 결과를 출력하는 SELECT 문장을 작성하여라.

JOB       Deptno 10 Deptno 20 Deptno 30     Total

--------- --------- --------- --------- ---------

ANALYST                  6000                6000

CLERK          1300      1900       950      4150

MANAGER        2450      2975      2850      8275

PRESIDENT      5000                          5000

SALESMAN                           5600      5600

select job, sum(decode(deptno,10,sal)) det10,

sum(decode(deptno,20,sal)) det20,

sum(decode(deptno,30,sal)) det30,

sum(sal) totel

from emp

group by job;

select *

from dept,emp

where dept.DEPTNO = emp.DEPTNO;

select ename,deptno

from emp

where deptno =

(select deptno

from emp

where ename = 'SCOTT'); 

select *

from emp

where job =

(select job

from emp

where ename = 'SCOTT'); 

부서별로 가장 급여를 많이 받는 사원의 정보(사원 번호, 사원이름, 급여, 부서번호)를 출력하시오.(IN 연산자 이용)

select emp.deptno, dname, loc

from emp hatural join dept

where dept.deptno in (

select deptno

from emp

where job ='MANAGER')and emp.deptno = dept.deptno;