[SQL] 20190529

TO_CHAR : 숫자나 날짜를 문자열로 변환

TO_NUMBER : 문자를 숫자로 변환

TO_DATE : 문자를 날짜로 변환

 

select to_number('123456')

from dual;

 

select to_number('d123456')

from dual;

-> 'd' 때문에 에러

 

select to_number(to_char(123456))

from dual;

-> to_char(123456) : 문자

 

select to_char(123456789)

from dual;

 

select to_char(123456789) + 1
from dual;

select to_char(123456789) as result
from dual;

select to_char(123456789, '$999999999')
from dual;

select to_char(123456789, 'L999,999,999')
from dual;

 

* select to_date('20190529', 'ddmmyyyy')

* select to_date('20190529', 'yyyymmdd')

주의해서 사용

 

오늘 날짜

select to_char(sysdate)
from dual;

 

1. 모든 사원들에게 커미션을 200씩 추가하여 출력하시오.
select ename, nvl(comm, 0) + 200
from emp;

-> comm이 null값이면 0으로 초기화시키고 200 추가

 

2. JOB이 MANAGER가 아닌 사원의 모든 정보를 출력하시오. (!=, <>, ^=)
select *
from emp
where JOB != 'MANAGER';

3. 급여가 800 이상인 사원의 이름, 급여, 부서번호를 출력하시오.
select ename, sal, deptno
from emp
where sal >= 800;

4. 직책이 SALESMAN, ANALYST 인 사람의 모든 정보를 출력하시오.
select *
from emp
where job = 'SALESMAN'
or job = 'ANALYST';

 

select *
from emp
where job like 'S%'
or job like 'A%';

 

IN

SOME

ANY

select *
from emp
where job in ('SALESMAN', 'ANALYST');
where job = any ('SALESMAN', 'ANALYST');
where job = some ('SALESMAN', 'ANALYST');

 

5. 부서번호가 30번인 사원들 중 사원번호, 이름, 입사일을 출력하는데
사원번호를 오름차순으로 정렬하시오.
select empno, ename, hiredate
from emp
where deptno = 30
order by empno asc;

6. 사원들 중에 1981년에 입사한 사원의 이름과 사원번호를
사원번호가 내림차순으로 정렬하여 출력하시오.
select ename, empno
from emp
where hiredate >= to_date('19810101', 'yyyymmdd')
and hiredate <= to_date('19811231', 'yyyymmdd')
order by empno desc;

 

select ename, empno
from emp
where to_char(HIREDATE, 'yyyy') = '1981'
order by empno desc;

decode 함수

 

select empno, ename, sal,
decode(JOB, 'SALESMAN', SAL*1.1,
'CLERK', SAL*1.3, 
'MANAGER', SAL*1.5,
sal) as in_sal
from emp;

-> job이 salesman이면 sal을 10% 인상, clerk이면 30%인상, manager이면 50%인상

 

7. 사장님만 급여 2배 올리기
select empno, ename, sal,
decode(MGR, NULL, SAL*2,
sal)
from emp;

 

case문 연습

8. 학생들 중에 기계과 학생들은 등록금 10000원
전기전자학생들은 8000원, 컴퓨터정보 학생들은 6000원이라고 한다.
학번, 이름, 전공, 등록금을 출력하시오.
단, 2013년 학번만 출력하시오.
select stu_no, stu_name, stu_dept,
decode(stu_dept, '기계', '10000원',
'전기전자', '8000원',
'컴퓨터정보', '6000원', 0) as 등록금
from student
where stu_no like '2013%';

 

 

select count(*) from student;

select count(stu_height) from student;

select count(*) from student;

select sum(stu_height) from student; 키 다 합치기

select avg(stu_height) from student;

select max(stu_height) from student;

select min(stu_height) from student;

select stddev(stu_height) from student; 표준편차

select variance(stu_height) from student; 분산

 

9. 기계과 학생들의 가장 큰 신장, 가장 작은 신장, 평균 신장을 출력하시오.
select max(stu_height), min(stu_height), avg(stu_height)
from student
where stu_dept = '기계';

10. 1981년 입사자들의 평균 임금을 출력하시오.

select avg(sal) as 평균

from emp

where to_char(HIREDATE, 'yyyy') = '1981';

11. 사원번호, 사원이름, 총 급여(SAL+COMM)을 출력하시오.
select empno, ename, sal, nvl(comm,0)+sal as 총급여

from emp;

->comm null값을 0으로 바꿔야해서 nvl 쓰는게 좋음

12. 사원번호, 사원이름, 직책(사장, 사원)을 표시하시오.
select empno as 사원번호, ename as 사원이름,
case job when 'PRESIDENT' then '사장'
else '사원'
end as 직책
from emp;

SELECT EMPNO,ENAME,
DECODE(JOB,'PRESIDENT','사장',
      '사원')
FROM EMP;

 

전공별로 묶기
select stu_dept from student;

겹치는 전공 하나로 만들기 (그룹으로 묶은거 x)
select distinct stu_dept from student;

그룹으로 묶어서 하나로 만들기
select stu_dept
from student
group by stu_dept;

 

select distinct stu_dept, stu_height from student group by stu_dept;
->전공으로 묶었지만 학생들 키는 포함될수없어서 에러
->group by에 적은것만 select에 적혀있어야됨

select distinct stu_dept, avg(stu_height)
from student
group by stu_dept;
-> select 개수 group by 개수 같아서 괜찮음

 

select stu_dept, max(stu_height)
from student
group by stu_dept;

 

13. SALESMAN의 커미션 평균을 출력하시오.
select job, avg(nvl(comm,0))
from emp
where job = 'SALESMAN'
group by job;

 

14. 부서명, 부서별 사원들의 인원수를 출력하시오.
select job, count(*)
from emp
group by job
having count(*) >= 4;

15. JOB 중에 가장 적게 수입을 가지는 직무의 평균 월급을 출력하시오.
SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING MIN(SAL+NVL(COMM,0)) =
(SELECT MIN(SAL+NVL(COMM,0)) FROM EMP);

16. 사원수가 5명 이상인 부서의 부서번호와 사원수를 출력하시오.
select deptno, count(*)
from emp
group by deptno
having count(*) >= 5;
-> SELECT에 count(*) 쓰면 사원번호가 null값이라면 에러남

 

SELECT DEPTNO, COUNT(EMPNO) AS 사원수
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) >= 5;