Backend Web/SQL · DB 기초

[SQL] Single Function / Group Function / Join

sofiaaa 2020. 11. 24. 09:45
반응형

 4장 Single Function

 

-- to_char(date, format model)

--날짜든 숫자들, char 로 강제 변환하고 싶을 때 to_char, 첫번째로 날짜, 두번째는 포맷

--숫자 형태

select to_char(sysdate, 'yyyy/MM/dd') from dual;

--문자형태 날짜에sp붙이며 서수 형태, DY 요일을 풀 네임으로 보여준다

select to_char(sysdate, 'YEAR, MONTH, DDsp DY(day)') from dual;

--시 24시간 분 초 

select to_char(sysdate, 'HH24:MI:SS AM') from dual;

--"" 붙이면 형식 아니라 일반 문자로 받아드림

select to_char(sysdate, 'DD "of" Month') from dual;



select to_char(sysdate, 'YYYY/MM/DD HH:MI:SS AM') from dual;

--날짜를 글자 형태로

select to_char(sysdate, 'ddspth') from dual;



--fm(fill mode)

select last_name, to_char(hire_date, 'fmDD Month YY') as tenure --03 07 같은 0이 들어간 형태

from employees; --fm 화이트 스페이스 제거, 꽉 눌러 담는다



--숫자값을 두번째 형식에 맞춰서 문자로 바꾼다

select to_char(salary, '$99,999.99') salary

from employees;

--소숫점 앞에서 9를 쓰면 그 자리에 값이 없으면 버리는데, 0을 쓰면 빈자리에 0을 채운다

select to_char(salary, '$99,999.00') salary

from employees;



select to_char(salary, '$00,000.99') salary

from employees;



select to_char(12.12, '9999.999') from dual;



select to_char(12.12, '0000.000') from dual;



select to_char(1237, 'L9999') from dual; --1237원



--문자를 날짜로 변환

--to_date(char, fm)

select last_name, hire_date

from employees

where hire_date = to_date('May 24, 2007', 'fxMon dd, yyyy'); --fx 안쓰면 캐릭터와 모델이 대충 맞으면 처리, fx 쓰면 첫번째 파라미터 두번째 파라미터 안맞으면 에러



--model 다양한 것들을 일반화, 추상화 시킨 것

select last_name, to_char(hire_date, 'DD-Mon-yyyy')

from employees

where hire_date < to_date('01-Jan-03', 'DD-Mon-RR');



--문자를 숫자로 바꾸는 것

select to_number('$1234.12', '$9999.00') from dual;



select to_number('1234') from dual;



select to_char(sysdate) from dual;



select to_char(1234) from dual;



select to_date('07-Mar-07') from dual;



select last_name,

    upper(concat(substr(last_name,1,8), '_US')) --함수 중첩시 가장 안쪽부터 실행

from employees

where department_id = 60;

       --소수점 이하 두자리까지        --Group, Decimal

select to_char(round((salary/7), 2), '99G999D99', 'NLS_NUMERIC_CHARACTERS=,.') --천단위 구분 기호

from employees;



--sysdate, nvl 현장에서 많이 사용

--nvl --> 첫번째 파라미터가 값이 있으면 그대로 리턴, null이면 두번째로 지정한 파라미터 리턴

select last_name, salary, nvl(commission_pct, 0), --첫번째 파라미터는 칼럼, 두번째 파라미터는 null이면 기본값 return, null이 아니면 값 return 

    (salary * 12) + (salary * 12 * nvl(commission_pct, 0)) --commission 안받는 애들도 제대로 계산

from employees;



--nvl2는 파라미터 타입 다르다

select last_name, salary, commission_pct, 

    nvl2(commission_pct, 'SAL+COMM', 'SAL') income --첫번째 파라미터가 null이 아니면 두번째 return, null이면 세번째 return 

from employees

where department_id in (50, 80);



select first_name, length(first_name) "exprl", --first name 의 길이

    last_name, length(last_name) "expr2", --last name의 길이

    nullif(length(first_name), length(last_name)) result --두개 파라미터가 같으면 null, 다르면 첫번째 파라미터 리턴

from employees;



select last_name, employee_id,

    coalesce(to_char(commission_pct), to_char(manager_id), 'None') result

from employees; 

--영업이 직업 아니면 null, 매니저 없으면 null, 그러면 마지막에 None return 된다.

--파라미터 첫번째에 null써도 문제 없다.



select to_char(null), to_number(null), to_date(null) --char ,number, date 타입인척 하는 기법

from dual;



--case end

select last_name, job_id, salary,

    case job_id when 'IT_PROG' then 1.10 * salary

                when 'ST_CLERK' then 1.15 * salary

                when 'SA_REP' then 1.20 * salary

    end salary --case end 가 컬럼 하나, case 기준값 when 비교값 동일하면 then 값 리턴, 어떤 값도 일치하지 않으면 null

from employees;



select (case job_id when '1' then 1

                    when '2' then 2

                    else 0

     end) grade

from employees;



select (case salary when 1 then '1'

                    when 2 then '2'

                    else '0'

        end) grade

from employees;



select (case salary when '1' then '1'

                    when '2' then '2'

                    else '0'

                    end) grade

from employees;

 --then 이후에는 데이터 타입 다 같아야 한다.

select (case salary when '1' then 1

                    when '2' then '2'

                    else '0'

                    end) grade

from employees;



select (case salary when '1' then '1'

                    when '2' then '2'

                    else 0

                    end) grade

from employees;



select last_name, salary, 

    case when salary < 5000 then 'Low'

         when salary < 10000 then 'Medium'

         when salary < 20000 then 'High'

         else 'Excellent'

    end qualified_salary

from employees;



select last_name, job_id, salary,

    decode(job_id, 'IT_PROG', 1.10 * salary,

                 'ST_CLERK', 1.15 * salary,

                 'SA_REP', 1.20 * salary,

         salary) revised_salary

from employees;



select last_name, salary,

    decode(trunc(salary / 2000, 0), --월급이 얼마냐에 따라 소득세율 결정

       0, 0.00, 

       1, 0.09,

       2, 0.20,

       3, 0.30,

       4, 0.40,

       5, 0.42,

       6, 0.44,

          0.45) tax_rate

from employees

where department_id = 80;



select decode(salary, 'a', 1) from employees; --107개의 record들어있는 테이블 받은 것

--salary 와 'a' 값 일치하지 않으니 null값이 return 되는 것

--salary 가 형변환 되서 'a'와 비교 가능

select decode(job_id, 1, 1) from employees; --에러

--job_id는 숫자로 변환 불가라서 에러



select decode(hire_date, 'a', 1) from employees;

--hire_date가 char타입으로 변환되서 a와 비교 



select decode(hire_date, 1, 1) from employees; --에러

 

 5장 Group Function

 

--그룹함수 --> 파라미터로 레코드가 그룹 지어서 들어오기 때문에

select avg(salary), max(salary), min(salary), sum(salary)

from employees;



--min 오래된날 max 최신날

select min(hire_date), max(hire_date)

from employees;



--count

--레코드가 그룹지어서 들어오는데 파라미터로 들어오는 레코드의 갯수 return 

--70번째 들어오는 모든 직원들 count 한 것

select count(*)        

from employees

where department_id = 70;



--null값 무시 

--70번 부서 레코드 한개이고, count에 투입되었지만, comm을 받지 않는 직원이기 때문에 무시되었다.

select count(commission_pct)

from employees 

where department_id =70;



--영업사원의 평균 커미션율 0.22

select avg(commission_pct)

from employees;



--전체 사원의 평균 커미션율

--그룹함수는 null 값 무시한다. 그래서 무시 못하도록 해줘야한다. --> nvl 사용

select avg(nvl(commission_pct,0))

from employees;



--distinct 레코드를 그룹으로 받고, 그 레코드에서 salary가 중복되면, 한개로 취급.

select sum(distinct salary) 

from employees;



--all 

select sum(all salary)

from employees;



select count(distinct department_id) 

from employees; -- 부서 11개  --department id 필드 값이 null이 하나 있기 때문에 106



--department id가 null인 사람 찾기

select last_name, department_id

from employees

where department_id is null;



--group by절 칼럼이 나와야 select절에 사용 가능

--group이 12개 , avg 함수 12번 호출, avg 리턴값도 12개 ,grant의 null도 추가된 것

select department_id, avg(salary)

from employees

group by department_id; 



select last_name, salary, department_id

from employees

where last_name = 'Grant';



select avg(salary)

from employees

group by department_id;



--모든 문법 사용

select department_id, job_id, sum(salary)

from employees

where department_id > 40

group by department_id, job_id 

order by department_id;



select count(last_name)

from employees;



--error -select절에 그룹과 칼럼이 같이 나오면 칼럼은 group by 절에 나와야 한다. 

select department_id, count(last_name) 

from employees;



--group by절에 등장한 칼럼이 select절에 올 수 있다.

select department_id, count(last_name) 

from employees

group by department_id; 



select job_id, count(last_name)

from employees

group by department_id;



--select 절에 그룹함수가 나왔을 때 일반 칼럼이 나오면 group by절에 잇어야 한다.

select job_id, count(last_name)

from employees

group by department_id, job_id;



select department_id, job_id, count(last_name)

from employees

where job_id = 'ST_CLERK';



--조사할 때 쓰는 것

--where은 레코드 하나하나를 조사할 때 사용

--having max는 그룹함수 return 값을 조사할 때 사용, 그룹을 골라내는 효과 발생

select department_id, max(salary)

from employees

group by department_id

having max(salary) > 10000;



select job_id, sum(salary) payroll

from employees

where job_id not like '%REP%'

group by job_id

having sum(salary) > 13000

order by sum(salary); --내 테이블 만든 상태에서 orderby로 정렬



select max(avg(salary))

from employees

group by department_id;



--그룹 함수를 중첩시킬 때는 group by가 있어야 한다.

select max(avg(salary))

from employees; --eror



--group by와 select에 나온 컬럼의 역할은 '그룹의 제목'

--그룹함수 중첩문이 있을 때는 칼럼이 등장하지 못한다. 

select department_id, max(avg(salary))

from employees

group by department_id; --error



select max(avg(salary))

from employees

group by department_id;

 

 6장 Join

 

Join의 종류

--테이블 결함 == 레코드 결합 --> 필드가 많아지면 많은 데이터 읽어낼 수 있기 때문

--       inner join                     <->            outer join

(natural join, using, on)                       (left outer join, right outer join, full outer join)

--필드값 같은 레코드끼리 join               join이 안된 레코드까지 포함

--범위 안에 포함된 것만                          범위 밖까지 내 테이블로 가져 가는 것



equijoin

--employees의 foreign key인 department_id가 departments의 primary key인 department_id를 바라보도록 한다.

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



natural join--

        --departments table                         --locations table

select department_id, department_name, location_id, city

from departments natural join locations; --테이블 두 개 join

--natural join은 equijoin 작용

--location id는 department에서 foreign key, locations에서 primary key



select department_name, location_id

from departments

where location_id = 1400;



select location_id, city

from locations

where location_id = 1400;



select department_id, department_name, location_id, city

from departments natural join locations

where department_id in (20, 50);



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



using 

select employee_id, last_name, location_id, department_id

from employees join departments 

using (department_id); -- 두 테이블의 공통 칼럼을 입력, department_id값이 같은 것끼리 join



select employee_id, last_name, location_id, department_id

from employees natural join departments;



--employees, departments 테이블 모두 manager_id가 foreign key

--manager_id는 employee_id를 바라보고 있다.

--natural join --> 공통 칼럼 모두 찾아낸다 --> 32개 레코드만 join이 된다. 이는 department_id, managerid 모두 같아야 join이 되는것,

--join --> department_id로만 join이 되는것 그래서 106개 레코드만 join이 된다.

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

접두사

--별명을 통해 이를 접두사로 사용하여 어느 테이블에 있는지 파악 가능

select city, department_name dept

from locations l join departments d

using(location_id)

where location_id = 1400;



select l.city, d.department_name

from locations l join departments d

using(location_id)

where location_id = 1400;



select locations.city, departments.department_name

from locations join departments

using(location_id)

where location_id = 1400;



select l.city, d.department_name

from locations l join departments d

using (location_id)

where d.location_id = 1400; --error --using에 사용한 칼럼에는 접두사 붙이면 x



select l.city, d.department_name

from locations l join departments d

using (location_id)

where l.city = 'Southlake'; --using에 사용하지 않으면 가능



select l.city, d.department_name

from locations l join departments d

using (location_id)

where location_id = 1400;  --접두사 사용하지 않으면 가능



select l.city, d.department_name, d.location_id

from locations l join departments d

using (location_id)

where location_id = 1400; --error using에 사용한 컬럼에는 접두사 x



select e.last_name, d.department_name

from employees e join departments d

using(department_id)

where department_id = 60;



select e.last_name, d.department_name

from employees e join departments d

using(department_id)

where d.department_id = 60; --error



select e.last_name, d.department_name

from employees e join departments d

using(department_id)

where manager_id = 60; --error --공통 칼럼인데 using에 사용하지 않았으면 접두사 붙여야 한다.



select e.last_name, d.department_name

from employees e join departments d

using(department_id)

where d.manager_id = 60;



--두 테이블에 공통 칼럼

--using에 사용되는 칼럼에 접두사 x

--using에 사용하지 않은 칼럼에는 접두사 o



--두 테이블의 공통 칼럼 아닌 경우 

--접두사 상관없이 사용 가능

select e.last_name, d.department_name 

from employees e join departments d

using(department_id)

where employee_id = 100;

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



on 

select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id

from employees e join departments d

on (e.department_id = d.department_id); --on 직접 equijoin 조건 작성



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

3개 테이블 join



--위에는 on 아래는 using 사용

select employee_id, city, department_name

from employees e join departments d

on d.department_id = e.department_id  --여기까지 만들어진 x테이블과 다시 밑에 l 조인

join locations l

on d.location_id = l.location_id;



select e.employee_id, l.city, d.department_name

from employees e join departments d

using (department_id)

join locations l

using (location_id);



레코드 선별 조건 추가

--and, where 둘 다 사용 

select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id

from employees e join departments d

on e.department_id = d.department_id

and e.manager_id = 149;



select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id

from employees e join departments d

on e.department_id = d.department_id

where e.manager_id = 149;





self join

--employee id가 110번인 매니저의 id가 108번인데, 그래서 그 108번이 누군지 찾으러 가는것 

--접두사 있어야 한다.



select worker.last_name, manager.last_name mgr

from employees worker join employees manager

on worker.manager_id = manager.employee_id;



select worker.last_name, manager.last_name mgr

from employees worker join employees manager

on manager_id = employee_id; --error



select last_name, last_name mgr

from employees worker join employees manager

on worker.manager_id = manager.employee_id; --error



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

non equijoin

select e.last_name, e.salary, e.job_id

from employees e join jobs j

on e.salary between j.min_salary and j.max_salary 

where j.job_id = 'IT_PROG';



select e.last_name, e.salary, e.job_id

from employees e

where e.salary between 4000 and 10000;



select * from jobs --프로그래머의 월급 범위

where job_id = 'IT_PROG';



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

outerjoin 

--join되지 않은 것까지 포함 시키겠다 

--상사할 때 보고 할 때 우리 조직에 grant있다는 것 알리기 위해 사용

select e.last_name, e.department_id, d.department_name

from employees e left outer join departments d

on e.department_id = d.department_id; 



--grant는 왼쪽에 있다. 오른쪽에서 버려진 걸 가져오는 것

select e.last_name, e.department_id, d.department_name

from employees e right outer join departments d

on e.department_id = d.department_id; 



--오른쪽, 왼쪽 모두 포함

select e.last_name, e.department_id, d.department_name

from employees e full outer join departments d

on e.department_id = d.department_id; 



과거문법

select d.department_id, d.department_name, d.location_id, l.city

from departments d, locations l

where d.location_id = l.location_id;



select d.department_id, d.department_name, d.location_id, l.city

from departments d, locations l

where d.location_id = l.location_id

and d.department_id in (20, 50);



select e.last_name, d.department_name, l.city

from employees e, departments d, locations l

where e.departement_id = d.department_id

and d.location_id = l.location_id;



select e.last_name, e.salary, e.job_id

from employees e, job j

where e.salary between j.min_salary and j.max_salary

and j.job_id = 'IT_PROG';



select e.last_name, e.department_id, d.department_name

from employees e, departments d

where e.department_id = d.department_id(+); --107개로 grant 포함



select e.last_name, e.department_id, d.department_name

from employees e, departments d

where e.department_id(+)= d.department_id;



select worker.last_name || ' works for ' || manager.last_name

from employees worker, employees manager

where worker.manager_id = manager.employee_id;
반응형

'Backend Web > SQL · DB 기초' 카테고리의 다른 글

[SQL] SQLD - ORDER BY 1, 2, 3  (0) 2022.03.04
[SQL] SQLD - 정규화  (0) 2022.03.01
[SQL] TABLE / View / Sequence  (0) 2020.11.26
[SQL] Subquery / SET / DML  (0) 2020.11.25
[SQL] SELECT / WHERE / ORDER BY / Single Function  (0) 2020.11.24

'Backend Web/SQL · DB 기초'의 다른글

  • 현재글[SQL] Single Function / Group Function / Join

관련글

댓글

티스토리툴바