UIUX _국비과정 0610 [오라클DB SQL 쿼리문]

힘들게 만든 OE계정에서,, 

나이 구하기
+ 연령대 구하기

select date_of_birth,to_char(date_of_birth,'YYYY/MM/DD') as birth, 
        trunc((sysdate - date_of_birth) / 365) as age,
        case
            when trunc((sysdate - date_of_birth) / 365) >= 70 and trunc((sysdate - date_of_birth) / 365) < 80 then '70대'
            when trunc((sysdate - date_of_birth) / 365) >= 60 and trunc((sysdate - date_of_birth) / 365) < 70 then '60대'
            when trunc((sysdate - date_of_birth) / 365) >= 50 and trunc((sysdate - date_of_birth) / 365) < 60 then '50대'
            when trunc((sysdate - date_of_birth) / 365) >= 40 and trunc((sysdate - date_of_birth) / 365) < 50 then '40대'
            when trunc((sysdate - date_of_birth) / 365) >= 30 and trunc((sysdate - date_of_birth) / 365) < 40 then '30대'
            when trunc((sysdate - date_of_birth) / 365) >= 20 and trunc((sysdate - date_of_birth) / 365) < 30 then '20대'
            when trunc((sysdate - date_of_birth) / 365) >= 10 and trunc((sysdate - date_of_birth) / 365) < 20 then '10대'
            else '기타'
        end as Generation
        
from customers;




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

위 구문을 식으로 바꿔보았다 (아래)


select date_of_birth,to_char(date_of_birth,'YYYY/MM/DD') as birth, 
        trunc((sysdate - date_of_birth) / 365) as age,
        case
            when sysdate-date_of_birth >= 0 then floor(floor((sysdate-date_of_birth)/365)/10)*10 || '대' 
            else '기타'
        end as Generation
        
from customers;

조건이 반복되면 간략하게 만들어서 사용할 생각을 하자

다시 scott 계정으로 돌아왔다

 

 

단일행 함수 → input과 output의 수가 같음.

다중행 함수 → input이 여러개여도 output은 하나

 

-- 단일행 합수 : round(), floor()..

-- 다중행 함수 : sum(), avg(), max(), min(), count()..

-- 회사직원들의 급여 합, 평균
-- distinct 중복제거
select sum(sal), sum(distinct sal), avg(distinct sal)
from emp;

select sal
from emp;

-- 전체 사원 14명, 성과금받는 직원 4명, 성과금의 합 2200, 급여가 같은 사원 중복제거
-- 다중행함수는 null 값을 무시한다
select count(*), count(comm), sum(comm), count(distinct sal)
from emp;

select max(sal), min(sal)
from emp;

-- 일반컬럼과 단일행 그룹 함수는 함께 사용할 수 없다.
-- 각 컬럼에 조회된 결과의 개수가 같아야 한다.
select ename, max(sal)
from emp;

-- 일반컬럼과 단일행 그룹 함수는 함께 사용할 수 없다.
select max(sal)
from emp;

-- 추후 해결하는 방법을 배울 예정

 

 

group by절

-- group by 기준 컬럼
-- 테이블에 데이터를 논리적으로 묶어서 처리하는 역할

select *
from emp
order by deptno;

-- 부서 별 급여의 합계, 평균
-- group by로 묶이면 그룹의 갯수대로 값이 나온다
-- group by에 사용되는 컬럼은 그룹함수와 함께 사용될 수 있다
select deptno, sum(sal), avg(sal)
from emp
group by deptno; 

select *
from emp
order by deptno;

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

-- having 조건절
-- where 그룹함수를 조건식으로 사용 불가, group by절 보다 먼저 실행된다
select deptno, sum(sal)
from emp
group by deptno
having sum(sal) > 9000;

-- where절과 group by절을 함께 사용한 예제
select deptno, job, avg(sal)
from emp
where sal <= 3000
group by deptno, job
-- having avg(sal) >= 2000; -- 그룹함수를 사용하여 조건식을 구성한다.
having deptno = 10;

 

문제풀기

-- 문제 풀기

-- 1번
select deptno, trunc(avg(sal)) as AVG_SAL, max(sal) as MAX_SAL, min(sal) as MIN_SAL, count(*) as CNT
from emp
group by deptno;

-- 2번
select job, count(*)
from emp
group by job
having count(*) >= 3;

-- 3번
select to_char(hiredate,'YYYY') as HIRE_YEAR, deptno, count(*) as CNT
from emp
group by to_char(hiredate,'YYYY'), deptno
order by HIRE_YEAR;

 

group by 절 사용 방법

select
from
where 
group by
having
order by -- 가장 마지막에 작성

 

 

 

join

조인 
- 두개 이상의 테이블을 사용하여 데이터를 조회하는 것
- 두개의 테이블의 공통컬럼을 사용하여 데이터를 조회
- 하나의 쿼리문으로 데이터 통합 조회
- from절에 두개 이상의 테이블을 사용한다

--종류
-- cross join : 조건없이 양쪽 테이블을 모두 조회
-- equi join : 공통 컬럼에 값을 비교해서 같으면 조회(등가)
-- non equi join : 컬럼의 값을 범위 비교한다
-- self join : 하나의 테이블을 두개의 테이블처럼 사용하여 조회(등가)
-- outer join : 누락된 데이터를 조회(등가)

select *
from emp;

select *
from dept;

-- cross join 
select *
from emp, dept;

-- equi join
select ename, sal, emp.deptno, dname, loc
from emp, dept
where emp.deptno = dept.deptno; -- 조인 조건
-- 공통으로 가지고 있는 컬럼이 아니라면 생략이 가능하다
-- select emp.ename, emp.sal, emp.deptno, dept.dname, dept.loc

select ename, sal, e.deptno, dname, loc
from emp e, dept d -- 별칭사용 (원래 테이블명 사용불가)
where e.deptno = d.deptno -- 조인 조건
and ename = 'SMITH'; -- 일반 조건

-- non equi join
select *
from emp;

select *
from salgrade;

select ename, sal, grade
from emp e, salgrade s 
-- where e.sal >= s.losal and e.sal <= s.hisal; -- 범위 비교
where e.sal between s.losal and s.hisal;

-- slef join
select *
from emp;

select e.ename || '의 매니저는 ' || m.ename || ' 입니다'
from emp e, emp m -- emp(mgr) = emp(empno)
where e.mgr = m.empno; -- 조인 조건
-- null값 누락

-- outer join
-- 누락되는 null 값도 가지고 온다
-- 조인 조건식의 한쪽 컬럼에 (+)표시를 작성한다 -> 해당 데이터가 없는 쪽에 붙일 것. 
select e.ename || '의 매니저는 ' || m.ename || ' 입니다'
from emp e, emp m -- emp(mgr) = emp(empno)
where e.mgr = m.empno(+); -- 조인 조건

select  ename, sal, d.deptno, dname
from emp e,dept d
where e.deptno(+) = d.deptno;


-- 사원이름, 급여, 부서번호, 부서명, 급여등급

select ename, sal, e.deptno, dname, grade
from emp e, dept d, salgrade s 
where e.deptno = d.deptno
and e.sal between s.losal and s.hisal;

 

표준조인방식

-- 표준 조인 방식 (ANSI-JOIN)
-- 위의 조인 방식은 오라클에서 쓸 수있는 조인 
-- 이 표준 조인 방식은 어디에서나 사용 가능
-- 호환성을 위해 표준을 쓰는 것을 권고한다

-- 종류
-- cross join
-- inner join(equi join, non equi join, self join)
-- outer join

select *
from emp cross join dept;

-- 조건문 on
select ename, sal, e.deptno, dname, loc
from emp e inner join dept d -- inner 생략 가능
on e.deptno = d.deptno;

-- 일반조건 추가 where
select ename, sal, e.deptno, dname, loc
from emp e inner join dept d -- inner 생략 가능
on e.deptno = d.deptno -- 조인조건
where ename = 'SCOTT'; -- 일반조건

select ename, sal, e.deptno, dname, loc
from emp e inner join dept d 
-- on e.deptno = d.deptno
using(deptno); -- 조인 조건 (컬럼명이 동일할 경우)


select ename, sal, grade
from emp e inner join salgrade s
on e.sal between s.losal and s.hisal;

select e.ename || '의 매니저는 ' || m.ename || ' 입니다'
from emp e inner join emp m
on e.mgr = m.empno;

-- left, right, full을 outer 앞에 붙여줘야한다
select e.ename || '의 매니저는 ' || m.ename || ' 입니다'
from emp e left outer join emp m 
on e.mgr = m.empno;


-- 테이블 먼저 나열하고 쓰면 안된다
select ename, sal, e.deptno, dname, grade
from emp e inner join dept d
on e.deptno = d.deptno
inner join salgrade s
on e.sal between s.losal and s.hisal;

 

 

 

서브쿼리문

-- 서브 쿼리문
-- select 문 안에 중첩되는 select 구문
-- 단일행 서브쿼리 : 결과가 하나 (비교연산자)
-- 다중행 서브쿼리 : 결과가 여러 개 (비교연산자 사용 불가), in, any, all

-- select (select)
-- from (select)
-- where (select)

--------------------단일행 서브쿼리--------------------

-- SCOTT 보다 급여를 많이 받는 사람을 알고 싶을 때
select sal
from emp
where ename = 'JONES';

select *
from emp
where sal > (
    select sal
    from emp
    where ename = 'SCOTT'
);

-- 근무지가 뉴욕인 사람들
select *
from emp
where deptno = (
    select deptno
    from dept
    where loc = 'NEW YORK'
);

-- 급여를 가장 많이 받는 사원의 급여와 사원이름
select max(sal)
from emp;


select ename,sal 
from emp
where sal = (
    select max(sal)
    from emp
);

--------------------단일행 서브쿼리--------------------


--------------------다중행 서브쿼리--------------------

-- 급여를 3000 이상 받는 사람들
select ename, sal, deptno
from emp
where deptno in ( -- or 조건과 동일
    select distinct deptno
    from emp
    where sal >= 3000
);

-- 30번 부서에서 근무하는 사람들의 급여 조회, 그 급여보다 많은 급여를 받는 사람 조회
select ename, sal
from emp
where sal > all ( 
    select sal
    from emp
    where deptno = 30
);

-- any : 값 중에 가장 작은 값보다 큰 값을 가진 사원 조회 
-- all : 값 중에 가장 큰 값보다 큰 값을 가진 사원 조회

--------------------다중행 서브쿼리--------------------

 

 

✔️ 테이블 만들기

DDL (data definition language)
-- creat : 객체 생성
-- alter : 객체 수정
-- drop : 객체 삭제
-- truncate : 테이블을 초기화

create table 테이블명 (
		컬럼명1 타입, 
		컬럼명2 타입,
		컬럼명3 타입,
);


-- 컬럼이름을 만든 후 타입에 사용
-- 숫자 : number(4), number(7,2)
-- 문자 : char(10) : 고정형, varchar2(10) : 가변형
-- 문자를 다룰 때는 가변형을 주로 사용한다
-- 날짜 : date, timestamp