쿼리문

select * from jsp_board order by idx desc;

select ename,job,hiredate

from emp
where hiredate >= '81/02/20' and hiredate <='81/05/01'

order by hiredate desc;

 

select ename,hiredate

from emp
where to_char(hiredate,'YYYY') ='1987';

 

select lower('HAPPY DAY') from dual;
select initcap('char') from dual;
select 2*9 from dual;

 

select empno,ename,lower(job),deptno

from emp

where ename=upper('scott');

 

select concat('abcd','가나달') FROM dual;

select concat(name,age) as newcol from member;

select concat(a,b) as "고객정보"
from (
        select concat(name,': ') a,concat(age,'세') b
        from member

       );

select substr('ABCDEFG',-3,3) FROM DUAL;
SELECT SUBSTR('890312-7899105',-2,2) FROM dual;
  
SELECT ENAME FROM EMP
WHERE SUBSTR(ENAME,1,1) >'K'  AND substr(ENAME,1,1) <'Y'
ORDER BY ENAME;
  
SELECT ENAME,LPAD(ENAME,15,'*') FROM EMP;
SELECT SAL,rPAD(SAL,15,'-') "A" FROM EMP;
 
  select deptno,ename,sal, first_value(sal)

            over(partition by deptno order by sal desc) "부서 최고 급여"
  from emp;

  select ename,sal from emp order by sal;
  select empno,ename,sal, count(*) over(order by sal) "cnt"
  from emp;

  select deptno,ename,sal, sum(sal) over(order by deptno) a,
           sum(sal) over(partition by deptno order by empno) b
  from emp;
 
  select empno,ename,deptno,sal,
            rank() over(partition by deptno order by sal desc) r
  from emp;
 
  select round(4567.678), round(4567.678,2), round(4567.678,-2)

  from dual;
 
  select hiredate, sysdate, trunc(sysdate-to_date('11/03/24/01/51',
           'YY/MM/DD/hh12/mi'))||'일' "차이"
  from emp;
 
  select hiredate, sysdate, trunc(sysdate-hiredate)||'일' "차이"
  from emp;

  select hiredate,sysdate,sysdate-hiredate,
           (sysdate-hiredate)/365 "년",
           (sysdate-hiredate)/7 "주",
           mod(sysdate-hiredate,7) "days"
  from  emp;
 
  select  months_between(sysdate, '11/02/25') "월"
  from emp;

select add_months(sysdate, -3) from dual;
select last_day('12/02/01') from dual;

 

select ename,hiredate,last_day(hiredate) , last_day(hiredate)-hiredate "근무일수"
from emp

order by 4 desc;

 

select to_char(sysdate) from dual;
select to_char(to_date('11/03/27'),'cc yyyy-mon-dd day hh:mi:ss') from dual;
select to_char(100.23456,'$999999.999') from dual;
select job,sum(sal) from emp group by job;

select job,sum(sal) from emp group by rollup(job);

 

select b.dname,a.job, sum(a.sal),count(a.empno)
from emp a, dept b
where a.deptno=b.deptno
GROUP by rollup(b.dname, a.job);

select a.*, b.*
from supply_comp a,
                              (select ep_code_fk, avg(input_price)
                               from products group by ep_code_fk) b
where a.ep_code=b.ep_code_fk;

select empno,ename,sal, deptno, s.grade

from emp e join salgrade s on e.sal
between s.losal and s.hisal

where deptno=10;

select ep_code_fk, input_price from products order by ep_code_fk;

select * from supply_comp;
select ep_code_fk,count(*) from products group by ep_code_fk;
select count(*) from products where input_price>=100000;
select ep_code_fk,input_price from products;

select p.products_name, s.ep_name, p.input_price
from products p join supply_comp s
on s.ep_name='공급업체B' and p.input_price>=100000;

select p.products_name, s.ep_name, p.input_price
from products p, supply_comp s
where s.ep_name='공급업체B' and p.input_price>=100000;

select ep_code_fk from products where input_price=5000000;

select * from dept;
select deptno from emp;

 

select e.deptno,e.ename, d.deptno,d.dname
from emp  e
full outer join dept d
on e.deptno=d.deptno order by d.deptno;

 

select distinct(a.deptno), b.deptno, a.ename
from emp a, dept b
where a.deptno(+)=b.deptno
and a.ename(+) like '%';

select distinct(a.deptno), b.deptno, a.ename
from emp a right outer join dept b
on a.deptno=b.deptno and a.ename like '%';

 

select empno,ename,job, sal
from emp where sal >
                              (select sal from emp where ename='SCOTT');

 

select empno,ename,job,hiredate,sal
from emp where job =
                               (select job from emp where empno=7521) and
                                 sal > (select sal from emp where empno=7934);

 

select empno,ename,job,sal,deptno
from emp where sal <
                                (select avg(sal) from emp) order by deptno;

 

select deptno,min(sal) from emp
group by deptno
having min(sal) >
                        (select min(sal) from emp where deptno=20);

 

select ename,job,sal from
emp where (job,sal) in
                                (select job, max(sal) from emp
                                 group by job);

 

SELECT EMPNO,ENAME,SAL,DEPTNO,
COUNT(*) OVER(PARTITION BY DEPTNO ORDER BY SAL) "CNT"
FROM EMP;

 

SELECT EMPNO,ENAME,SAL,DEPTNO,
             SUM(SAL) OVER (ORDER BY SAL),
             SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) "CNT"
FROM EMP;

 

SELECT b.dname, a.job, SUM(a.sal) sal,
            COUNT(a.empno) emp_count
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(b.dname, a.job);