본문 바로가기
교육/Java&Spring

kosta 클라우드 네이티브 애플리케이션 개발 과정 day 22

by Renechoi 2023. 1. 18.

SQL 문법 

 

 

그룹함수, 집계함수를 배워보자 ! 

 

-avg, count, max, min, sum 

 

goup함수의 결과는 한 개의 row만 남게 한다 

select salary, first_Name from employees;

이와 같은 쿼리를 할 때 집계함수에서 함께 사용되지 않는다. 컨텐츠가 다르기 때문에 

 

이러한 의미를 표현하려면 group by를 통해 표현해야 한다. 

 

select avg(salary), from .... 

group by department_id...

 

 

count() 

 

count(*) => null을 포함하는 전체 행의 개수를 반환한다. 

count(expr) => null을 제외한 개수를 반환한다. 

 

select count(*)
from employees;

select count(EMPLOYEES.COMMISSION_PCT)
from EMPLOYEES;

select count(*)
from EMPLOYEES
where SALARY > 16000;

 

 

 

sum()

 

같은 집계함수이기 때문에 같이 사용이 가능하다 

 

select count(*), sum(SALARY)
from EMPLOYEES;

 

 

 

avg() 

 

입력된 값들의 평균값을 구하는 함수 

null 값이 있는 경우 빼고 계산함 -> nvl 함수와 같이 사용 

 

select count(*), sum(SALARY), avg(SALARY)
from EMPLOYEES;

 

 

select count(*), sum(SALARY), avg(nvl(SALARY,0))
from EMPLOYEES;

 

 

 

null 값에 따라 평균값이 달라지므로 nvl로 0처리를 해서 계산해야 정확한 값이 나온다 ! 

 

 

 

 

 

max() / min()

 

입력된 값들중 가장 큰값/작은값을 구하는 함수 

여러 건의 데이터를 순서대로 정렬 후 값을 구하기 때문에 데이터가 많을 때는 느리다 

 

 

select count(*), max(SALARY), min(SALARY)
from EMPLOYEES

 

group by 

 

이와 같은 쿼리를 groupb 별로 보고 싶다 

 

select DEPARTMENT_ID, SALARY
from EMPLOYEES
order by DEPARTMENT_ID asc;

=> 

select DEPARTMENT_ID, avg(SALARY)
from EMPLOYEES
order by DEPARTMENT_ID asc;

department_id는 107건이 나오지만 avg는 집계함수이기 때문에 한 개가 나온다 

=> 에러 발생 

 

=> 그룹별 평균을 구한다. 

select DEPARTMENT_ID, avg(SALARY)
from EMPLOYEES
group by DEPARTMENT_ID
order by DEPARTMENT_ID asc;

 

 

 

group by 절에 참여했으면 select절에 올 수 있다.

 

 

select department_id, count(*), sum(salary)
from employees
group by department_id;

=> 가능 

 

select department)id, job_id, count(*), sum(salary)
from employees
group by department_id;

=> 불가능 

 

 

 

급여(salary)의 합계가 20000 이상인 부서의 부서 번호와, 인원수, 급여 합계를 출력하세요 

 

select DEPARTMENT_ID, count(*), sum(SALARY)
from EMPLOYEES
group by DEPARTMENT_ID

 

여기서 where 절을 추가하면 불가능하다 

=> where 절에는 group 함수를 쓸 수가 없다.

select DEPARTMENT_ID, count(*), sum(SALARY) as summation
from EMPLOYEES
group by DEPARTMENT_ID
having sum(salary) > 20000;

 

having 절은 groub by ㅇ벗이도 사용할 수 있다. 

 

 

작동 순서 

 

from -> where -> groupby -> having -> order by -> select 

 

 

 

case end 문 

 

case when 조건 then 출력 1 

 

end 컬럼 alias 

 

select EMPLOYEE_ID,
       SALARY,
       case
           when JOB_ID = 'ac_account' then SALARY + SALARY * 0.1
           when JOB_ID = 'ac_mgr' then SALARY + SALARY * 0.2
           else SALARY
           end job_idsssss
from HR.EMPLOYEES

 

 

 

 

 

 

 

직원의 이름, 부서, 팀을 출력하세요 

팀은 부서코드로 결정하며 부서코드가 10 ~ 50이면 'A-TEAM' 60~100이면 'B-TEAM' 110 ~ 150이면 'C-TEAM' 나머지는 '팀없음'으로 출력하세요 

 

select FIRST_NAME,
       DEPARTMENT_ID,
       case
           when DEPARTMENT_ID >= 10 and DEPARTMENT_ID < 50 then 'A-Team'
           when DEPARTMENT_ID between 60 and 100 then 'B-team'
           when DEPARTMENT_ID >= 110 and DEPARTMENT_ID <= 150 then 'C-team'
           else '팀없음'
           end "팀"
from employees

 

 

 

join 

 

 

직원의 이름과 직원이 속한 부서명을 함께 보고 싶다면  ? 

 

employee 

 

department 

 

 

중복을 최소화하여 별도의 table을 구성한다. (정규화) 

 

=> 정규화된 것을 다시 조인한다 => 반정규화 

=> join 

 

 

단순 카티션 곱 

 

select count(*)
from EMPLOYEES, DEPARTMENTS

 

 

 

equi join 

 

조인 조건이 모두 등호인 조인.

값이 동일한 경우에만 행이 반환된다. 

select EMPLOYEES.FIRST_NAME, EMPLOYEES.DEPARTMENT_ID, DEPARTMENTS.DEPARTMENT_NAME, DEPARTMENTS.DEPARTMENT_ID
from EMPLOYEES, DEPARTMENTS
where EMPLOYEES.EMPLOYEE_ID = DEPARTMENTS.DEPARTMENT_ID;

 

 

직원의 이름, 직급 명칭을 출력하세요 

join (employees, jobs) 

 

select FIRST_NAME, JOBS.JOB_ID, JOBS.JOB_TITLE
from EMPLOYEES, JOBS
where  EMPLOYEES.JOB_ID = JOBS.JOB_ID;

 

 

3개의 테이블 조인 

select FIRST_NAME, DEPARTMENT_NAME, JOB_TITLE
from EMPLOYEES,
     JOBS,
     DEPARTMENTS
where EMPLOYEES.JOB_ID = JOBS.JOB_ID
  and EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;

 

erd를 확인하여 관계 있는 것들을 찾아서 맵핑한다. 

 

 

 

 

employee table의 자기 자신 참조 

 

 

 

 

 

 

사원과 그 사원의 매니저 

 

자기 자신을 참조하는 테이블을 조인하기 

 

=> self join 

select emp.FIRST_NAME, mgr.FIRST_NAME
from EMPLOYEES emp, EMPLOYEES mgr
where emp.EMPLOYEE_ID = mgr.MANAGER_ID;

 

 

left outer join 

 

부서가 없는 결과 값에 대해서도 보여주기 

 

 

 

 

 

 

 

right outer join 

 

select d.DEPARTMENT_ID, FIRST_NAME, DEPARTMENT_NAME
from EMPLOYEES e right outer join DEPARTMENTS d
on e.DEPARTMENT_ID = d.DEPARTMENT_ID

 

 

 

full outer join 

 

select e.DEPARTMENT_ID, e.FIRST_NAME, DEPARTMENT_NAME
from EMPLOYEES e
         full outer join DEPARTMENTS d
                         on e.DEPARTMENT_ID = d.DEPARTMENT_ID;

 

 

 

 

 

 

안시 방법 + 기호 위치 쉽게 이해하기 

=> 널이 있어야 하는 쪽에 + 

 

 

 

 

연습문제 

 

 

집계(통계) SQL 문제입니다.

문제 1. 
직원중에 최고임금(salary)과  최저임금을 “최고임금, “최저임금”프로젝션 타이틀로 함께 출력해 보세요. 두 임금의 차이는 얼마인가요?  “최고임금 – 최저임금”이란 타이틀로 함께 출력해 보세요.

 

select max(SALARY) - min(SALARY) "최고임금- 최저임금"
from EMPLOYEES;




문제2.
마지막으로 신입사원이 들어온 날은 언제 입니까? 다음 형식으로 출력해주세요.
예) 2014년 07월 10일

 

select to_char(max(HIRE_DATE), 'yyyy"년" mm"월" dd"일"') "DATE"
from EMPLOYEES;




문제3.
부서별로 평균임금, 최고임금, 최저임금을 부서(department_id)와 함께 출력하고 정렬순서는
부서번호(department_id) 내림차순입니다.

 

select DEPARTMENT_ID, Round(avg(SALARY),0), max(SALARY), min(SALARY)
from EMPLOYEES
group by DEPARTMENT_ID
order by 1 desc




문제4.
업무(job_id)별로 평균임금, 최고임금, 최저임금을 업무(job_id)와 함께 출력하고 정렬순서는 업무(job_id) 내림차순입니다. 

select JOB_ID, Round(avg(SALARY), 0), max(SALARY), min(SALARY)
from EMPLOYEES
group by JOB_ID
order by 1;




문제5.
가장 오래 근속한 직원의 입사일은 언제인가요? 다음 형식으로 출력해주세요.
예) 2014년 07월 10일

 

select to_char(min(HIRE_DATE), 'yyyy"년" mm"월" dd"일"') 날짜
from EMPLOYEES;



문제6.
평균임금과 최저임금의 차이가 2000 미만인 부서(department_id), 평균임금, 최저임금 그리고 (평균임금 – 최저임금)를 (평균임금 – 최저임금)의 내림차순으로 정렬해서 출력하세요.

 

select Round(avg(SALARY), 0), min(SALARY), Round(avg(SALARY) - min(SALARY), 0)
from EMPLOYEES
group by DEPARTMENT_ID
having avg(SALARY) - min(SALARY) < 2000
order by 3 desc;

 



문제7.
업무(Job_id)별로 최고임금과 최저임금의 차이를 출력해보세요.
차이를 확인할 수 있도록 내림차순으로 정렬하세요? 

 

 

 

 

 

 

테이블간 조인(JOIN) SQL 문제입니다.

문제 1. 
각 사원(employee)에 대해서 사번(employee_id), 이름(first_name), 부서명(department_name), 매니저(manager)의 이름(first_name)을 조회하세요.
// 105명 

select e_name.FIRST_NAME "EMPLOYEE", m_info.FIRST_NAME "MANAGER", d.DEPARTMENT_NAME
from EMPLOYEES e_name,
     EMPLOYEES m_info,
     DEPARTMENTS d
where e_name.EMPLOYEE_ID = m_info.MANAGER_ID
  and d.DEPARTMENT_ID = e_name.DEPARTMENT_ID;

 





문제2.
지역(regions)에 속한 나라들을 지역이름(region_name), 나라이름(country_name)으로 출력하되 지역이름, 나라이름 순서대로 내림차순으로 정렬하세요.
//25개국 


select REGIONS.REGION_NAME, COUNTRIES.COUNTRY_NAME, COUNTRIES.REGION_ID
from COUNTRIES,
     REGIONS
where COUNTRIES.REGION_ID = REGIONS.REGION_ID
order by REGION_NAME desc, COUNTRY_NAME desc;





문제3.
각 부서(department)에 대해서 부서번호(department_id), 부서이름(department_name), 매니저(manager)의 이름(first_name), 위치(locations)한 도시(city), 나라(countries)의 이름(countries_name) 그리고 지역구분(regions)의 이름(region_name)까지 전부 출력해 보세요.
//11개 

select department.DEPARTMENT_ID,
       department.DEPARTMENT_NAME,
       manager.FIRST_NAME,
       locations.CITY,
       countires.COUNTRY_NAME
from DEPARTMENTS department,
     EMPLOYEES manager,
     LOCATIONS locations,
     COUNTRIES countires,
     REGIONS regions
where department.MANAGER_ID = manager.EMPLOYEE_ID
  and department.LOCATION_ID = locations.LOCATION_ID
  and locations.COUNTRY_ID = countires.COUNTRY_ID
  and countires.REGION_ID = regions.REGION_ID;







문제4.
‘Public Accountant’의 직책(job_title)으로 과거에 근무한 적이 있는 모든 사원의 사번과 이름을 출력하세요. (현재 ‘Public Accountant’의 직책(job_title)으로 근무하는 사원은 고려하지 않습니다.) 이름은 first_name과 last_name을 합쳐 출력합니다.
//2명

 

select concat(concat(employees.FIRST_NAME, ' '), employees.LAST_NAME) NAME
from EMPLOYEES employees,
     JOB_HISTORY job_history,
     JOBS jobs
where jobs.JOB_TITLE = 'Public Accountant'
  and jobs.JOB_ID = job_history.JOB_ID
  and employees.EMPLOYEE_ID = job_history.EMPLOYEE_ID






문제5.
직원들의 사번(employee_id), 이름(first_name), 성(last_name)과 부서 이름(department_name)을 조회하여 성(last_name)순서로 오름차순 정렬하세요
//106 명  (+  부서가 없는 사람도 조회 되도록.. 107명)





문제6.
자신의 매니저보다 채용일(hire_date)이 빠른 사원의 사번(employee_id), 성(last_name)과 채용일(hire_date)을 조회하세요 
// 37 명 

SELECT *
FROM EMPLOYEES employees,
     EMPLOYEES managers
WHERE employees.MANAGER_ID = managers.EMPLOYEE_ID
  AND employees.HIRE_DATE < managers.HIRE_DATE;

 

 

 

서브 쿼리 

 

하나의 sql 쿼리문 속에 다른 쿼리문이 포함되어 있는 형태 

 

Den 보다 급여를 많이 받는 사람의 이름과 급여는? 

 

den인 사람의 급여를 구해서 

 

salary > den인 사람의 급여 

 

 

맨 안쪽부터 본다 (서브쿼리 안쪽) 

 

 

조건절이 단일행이기 때문에 비교가 가능하다 


SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT SALARY
                FROM EMPLOYEES
                WHERE EMPLOYEES.FIRST_NAME = 'Den');

 

 

다중행 서브쿼리의 경우 

ANY, ALL, IN으로 처리를 한다 

 

=> 두 개 이상의 리턴 값을 boolean이나 단순 연산자로 해결할 수 없다. 

 

any = or 

all = and 

 

al

 

서브쿼리를 활용하는 두 가지 방식 

 

SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
where (DEPARTMENT_ID, SALARY) in (select DEPARTMENT_ID, max(SALARY) from EMPLOYEES GROUP BY DEPARTMENT_ID)



SELECT employees.DEPARTMENT_ID, employees.EMPLOYEE_ID, employees.FIRST_NAME, employees.SALARY
FROM EMPLOYEES employees, (select DEPARTMENT_ID, max(SALARY) salary from employees GROUP BY DEPARTMENT_ID) department_max_sal
where employees.DEPARTMENT_ID = department_max_sal.DEPARTMENT_ID
and employees.SALARY = department_max_sal.salary;

 

 

rowNum 

: 질의의 결과에 가상으로 부여되는 oracle의 가상의 column (일련번호) 

 

SELECT rownum,
       FIRST_NAME,
       SALARY
FROM EMPLOYEES;

 

 

where 절 끝나고 임시 결과를 생성할 때 rownum을 부여 받는다. 

 

 

따라서 orderby를 했을 때 rownum은 뒤죽박죽으로 바뀐다. 

 

SELECT rownum,
       FIRST_NAME,
       SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC;

 

 

따라서 orderby한 것을 순위로 부여하고자 한다면 

서브쿼리를 통해 orderby를 하고 나중에 순위를 부여 받으면 된다 

 

SELECT ROWNUM,
       FIRST_NAME,
       SALARY
FROM (SELECT FIRST_NAME, SALARY
      FROM EMPLOYEES
      ORDER BY SALARY DESC);

 

 

 

3위까지 

SELECT ROWNUM,
       FIRST_NAME,
       SALARY
FROM (SELECT FIRST_NAME, SALARY
      FROM EMPLOYEES
      ORDER BY SALARY DESC)
where ROWNUM <=3;

 

 

11위 ~ 20위 

SELECT ROWNUM,
       FIRST_NAME,
       SALARY
FROM (SELECT ROWNUM,
             FIRST_NAME,
             SALARY
      FROM (SELECT FIRST_NAME,
                   SALARY
            FROM EMPLOYEES
            ORDER BY SALARY DESC))
WHERE ROWNUM >= 11
  AND ROWNUM <= 20;

 

 

 

2007년 입사한 사람 중 급여 순위 3 ~7위 

 


SELECT row_num,
       FIRST_NAME,
       SALARY,
       HIRE_DATE
FROM (SELECT ROWNUM row_num,
             FIRST_NAME,
             SALARY,
             HIRE_DATE
      FROM (SELECT FIRST_NAME,
                   SALARY,
                   HIRE_DATE
            FROM EMPLOYEES employees
            WHERE TO_CHAR(HIRE_DATE, 'yyyy') = '2007'
            ORDER BY SALARY DESC))
WHERE row_num >= 3
  AND row_num <= 7;

 

 

 


-- 각 업무 별로 연봉의 총합 구하기
--     총합이 가장 높은 업무부터 업무명과 연봉 총합 조회하기


SELECT employees.JOB_ID, sum(employees.salary)
FROM EMPLOYEES employees
GROUP BY employees.JOB_ID;

SELECT jobs.JOB_TITLE, job_salary.salary_sum
FROM JOBS jobs,
     (SELECT employees.JOB_ID, SUM(EMPLOYEES.salary) salary_sum
      FROM EMPLOYEES employees
      GROUP BY employees.JOB_ID) job_salary
WHERE jobs.JOB_ID = job_salary.JOB_ID
ORDER BY  2 desc;
반응형
저작자표시 비영리 변경금지

'교육 > Java&Spring' 카테고리의 다른 글

kosta 클라우드 네이티브 애플리케이션 개발 과정 day 24  (0) 2023.01.25
kosta 클라우드 네이티브 애플리케이션 개발 과정 day 23  (0) 2023.01.19
kosta 클라우드 네이티브 애플리케이션 개발 과정 day 21 (docker)  (0) 2023.01.17
kosta 클라우드 네이티브 애플리케이션 개발 과정 day 20  (0) 2023.01.16
java & spring 3  (0) 2023.01.14

관련글

티스토리툴바