8/8 [data base] - 그룹 함수를 사용한 데이터 집계 [java]-다중if문

Full Stack 교육 회고록

8/8 [data base] - 그룹 함수를 사용한 데이터 집계 [java]-다중if문

순두부 호랑이 2022. 8. 8. 13:56

728x90

SMALL

<<오전 data base 수업>>

[5.그룹 함수를 사용한 데이터 집계]

단일행은 nvl이 포함되어 있다.

그룹 함수는 sum, max가 있다.

윈도우 함수는 순위,통계 함수가 있다

그룹별로 1개의 결과를 돌려주는 함수이다

[그룹 함수란?]

그룹 함수는 행 집합에 작용하여 그룹 당 하나의 결과를 생성합니다. 이 때 집합은 전체 테이블 또는 그룹화된 테이블입니다.

[그룹 함수 종료]

AVG
COUNT
MAX
MIN
STDDEV
SUM
VARIANCE

함수	설명
AVG( [distinct\|all]n)	n의 평균 값이며 널 값은 무시합니다.
count({*\|distibnct\|all}exper})	expr이 널이 아닌 행의 수입니다. *를 사용하면 중복 행 및 널이 있는 행을 포함하여 선택한 모든 행을 셉니다.
max{(disonct\|all}expr})	expr의 최대값이며 널 값은 무시합니다.
min([distinct\|all]expr)	expr의 최소값이며 널 값은 무시합니다.
stddev(distinct\|all]n)	n의 표준 편차이며 널 값은 무시합니다.
sum{(distinct\|all]n)	n의 합계이며 널 값은 무시합니다.
variance([distinct\|all]n)	n의 분산이며 널 값은 무시합니다.

stddev-> 표준편차

variance -> 배열

이 두개는 잘 사용안한다

avg -> 평균

count -> 출력이 된 행의 갯수 출력

[그룹 함수 구문]

select [column,] group frunction(column), ...
from table
[wehre condition]
[group by column]
[order by column];

[그룹 함수 사용 지침]

distinct를 지정하면 함수는 중복되지 않는 값만 고려하고 all을 지정하면 중복 값을 포함한 모든 값을 고려합니다. 기본값은 all이므로 지정할 피료가 없습니다.
expr 인수를 가진 함수의 데이터 유형은 char, varchar2, number 또는 date가 될 수 있습니다.
모든 그룹 함수는 널 값을 무시합니다. 널 값을 특정 값으로 치환하려면 NVL, NVL2 또는 COALESCE 함수를 사용합니다.
GROUP BY 절을 사용하면 Oracle server 가 결과 집합을 암시적(implict)으로 오름차순으로 정렬합니다

[avg 및 sum 함수 사용]

숫자 데이터에 avg 및 sum 을 사용할 수 있습니다.

select avg(salary), max(salary), min(salary), sum(salary)
from employees
where job_id loke '%REP%';

[min 및 max 함수 사용]

모든 데이터 유형에 대한 min 및 max를 사용할 수 있습니다. -> 이유는 아스키코드로 이루어져 있기 때문 입니다.

Q. 최근에 입사한 사원과 가장 오래전에 입사한 사원을 표시합니다.

select min(hire_date),max(hire_date)
from employees;

Q. 우리회사의 월급의 평균과 합을 구하여라

SQL> select avg(salary), sum(salary)
  2  from employees;

[COUNT 함수 사용]

count(*)는 테이블의 행 수를 반환합니다.

Q. 부서 50의 사원 수를 표시 합니다.

select count(*)
from employees
where department_id = 50;

- count(expr)은 expr에 대해 널이 아닌 값을 가진 행수를 반환합니다.

- employees 테이블에서 널 값을 제외한 부서 값의 수를 표시합니다.

select count(commission_pct)
from employees
where department_id =80;

[distinct 키워드 사용]

- count(distinct expr)은 expr에 대해 중복되지 않은 널이 아닌 값의 수를 반환합니다.

- employees 테이블에서 중복되지 않는 부서 값의 수를 표시합니다.

select count(distinct department_id)
from employees;

[그룹 함수 및 널 값]

그룹 함수는 해당 열의 널 값을 무시합니다.

select avg(commission_pct)
from employees;

[그룹 함수에 nul 함수 사용]

nul 함수는 그룹 함수가 널 값을 포함하도록 강제로 지정 합니다.= 중첩이 되면 nvl함수를 사용한다

select avg(nvl(commission_pct,0))
from employees;

avg,sum,max,min,count는 인수를 column으로 받았다->null 무시

count c* -> null 행단위기 때문에 인정한다

distinct->중복 제거

department_id에서 제거를 하고 count에 받아드린다

SQL>  1  select count(distinct department_id)
  2* from employees

[데이터 그룹 생성: group by절 구문]

select column, group_function(column)
from table
[where condition]
[group by group_by_expression]
[order by column];

group by 절을 사용하여 테이블 행을 더 작은 그룹으로 나눕니다.

group by 절을 사용하여 테이블 행을 그룹으로 나눈 후 그룹 함수를 사용하여 각 그룹에 대한 요약 정보를 반환할 수 있습니다.

group_by_expression 행 그룹화 기준을 결정하는 값을 가진 열을 지정 합니다.

- select 절에 그룹 함수를 포함시킨 경우 group by 절에 개별 열을 지정하지 않으면 개별 결과를 선택할 수 없으며, group by절에 열 목록을 포함시키지 않으면 오류 메시지가 나타납니다.

- where 절을 사용하면 그룹으로 나누기 전에 행을 제외시킬 수 있습니다.,

- group by 절에 열을 포함시켜야 합니다

- group by 절에는 열 별칭을 사용할 수 없습니다

- 기본적으로 행은 group by 목록에 포함된 열의 오름차순으로 정렬되는데 ordet by 절을 사용하면 이 순서를 무시할 수 있습니다.

[group by 절 사용]

select 목록의 열 중 그룹 함수에 없는 열은 모두 group by 절에 포함되어야 합니다.

group by 절을 사용할 때 select 목록의 열 중 그룹 함수가 아닌 열은 모두 group by 절에 포함되어야 합니다.

Q. 각 부서의 부서 번호 및 평균 급여를 표시합니다

select department_id, avg(salary)
from employees
group by department_id

group by 열을 select 목록에 포함시키지 않아도 됩니다. 예를 들어 아래의 코드와 같이 해당 부서의 번호는 표시하지 않고 각 부서의 평균 급여만 표시합니다. 그러나 부서 번호가 없으면 그 결 과는 의미가 없습니다.

select avg(salary)
from employees
group by department_id

[여러 열에 group by절 사용]

select department_id dept_id, job_id, sum(salary)
from employees
group by department_id, job_id

- 하나 이상의 group by 열을 나열하여 그룹 및 하위 그룹에 대한 요약 결과를 반환 할 수 있으며, group by절에서의 열 순서에 따라 결과의 기본 정렬 순서를 결정할 수 있습니다. group by절을 포함하는 슬라이드 select문은 다음과 같이 평가됩니다.

[그룹 함수를 사용한 잘못된 질의]

- select 목록의 열 또는 표현식 중 그룹 함수가 아닌 것은 group by절에 포함시켜야 합니다.

- where절을 사용하여 그룹을 제한할 수 없습니다.

- having절을 사용하여 그룹을 제한할 수 있습니다.

- where 절에서 그룹 함수를 사용할 수 없습니다.

select department_id, count(last_name)
from employees;

이렇게 개별 항목(department_id)및 그룹 함수(count)를 동일한 select 문에 함께 사용할 때는 개별 항목 (department_id)을 지정하는 group by절을 항상 포함시켜야 합니다.

select department_id, count(last_name)
from employees
group by department_id

[그룹 결과 제외: having 절]

having 절을 사용하여 그룹을 제한합니다.

1. 행이 그룹화됩니다.

2. 그룹 함수가 적용됩니다.

3. having 절과 일치하는 그룹이 표시됩니다.

Q. 최고 급여가 $10,000를 넘는 부서의 부서 번호 및 최고 급여를 표시합니다.

select department_id, MAX(salary)
from employees
group by department_id
having max(salary)>10000;

group 함수안에 꼭 명시가 되어있어야 된다 그래야 출력이 된다

[ having 절 사용 ]

 select job_id,sum(salary) payroll
  2  from employees
  3  where job_id not like '%REP%'
  4  group by job_id
  5  having sum(salary)>13000
  6  order by sum(salary);

<순서 기억하기>

select 4

from 1

where 2

group by 3

order by 5

[여러 열에

그룹화 작업을 꼭해주고 group by절로 명시가 된다.

그룹함수를 사용하면 select절에 무슨절을 사용하든 그

평균급여가 8000을 넘는 부서를 구하시오.

where->행에 대한 조건을 주는것 sla*12>12000

having-> 그룹에 대한 조건을 주는 것 avg(sal) >10000

select 6

from 1

where 2

orderby3

having 4

orderby 5

having

orderby두개의 순서가 바뀌어도 된다

having함수는 그룹함수가 들어갈때만 사용한다

[having 절 사용]

q. 급여가

SQL> select department_id, max(salary)
  2  from employees
  3  group by department_id
  4  having max(salary)>10000;

having절은 group by이 없으면 사용 불가능하다

[having절 사용]

Q. 월급 총액이 $13,000를 넘는 각 업무에 대해 업무ID와 월급 총액을 표시하되, 영업 사원을 제외시킨 후 월급 총액에 따라 목록을 정렬합니다.

SQL> select job_id, sum(salary) payroll
  2  from employees
  3  where job_id not like '%REP'
  4  group by job_id
  5  having sum(salary)>13000
  6  order by sum(salary);

[그룹 함수 중첩]

최고 평균 급여를 표시합니다.

select max(avg(salary))
from employees
group by department_id;

총 내용 요약

select distinct * column alias 출력에서 결과를 보고자하는 것

select ->출력과 관련된 절 / distinct->중복제거 1번만 사용 가능 / *모든col / column 특정col / alias->열머리글,1개의 col,1개의 alias 명 사용 as 블랭크 " " 복수문장,대소문자를 구분

from data(col)을 소요한 table을 명시

table이름지정, 조인(테이블 여러개사용가능) n-1 조인조건 where 명시/ 2. 똑같은 이름의 col 명시할때는 반드시 소유주 tavle이름을 접두어로 사용한다 table full name , from alias/ equ-> outer join -> 어느 안쪽 data를 많을때 부족한 쪽 (+) 추가 -> null을 갖는 행 삽입

where 사전에 출력되기 전에 행들을 제한을 하는 조건

행에대한 조건 / 조건식을 명시하여야된다 col 연산자 값으로 구성되어있다 2. 값을 명시할 때는 문자와 날짜 '' / 3. 값을 명시할때는 table 저장 형식 그대로/ 단일 비교 = > < <> ; = between a and b in 여러개 똑같은 복수연산자 in(30,90)

like 전체가 아닌 일부만 알고 있을때 % -> 문장 전체 - 문자 하나 is null 조건추가할때 사용하는 연산자는 and ,or 이다새로운 조건식 추가 is not null 산술연사(col+숫자) ( col+col) 숫자가능 문자 불가능 날짜 일수에 대해서만 +- 사용가능

null ->null nul(col,대체값)

group by 스룹함수 시용시 소그룹별 그룹 생성

avg sum (숫자) max min count col (all datatype) ->col -> null 무시 nul 중심, 그룹화되지는 않은 col을 그룹화 시켜서 그룹함수의 결과

having 그룹 by에 의해 생성이 된 그룹에 대한 조건을 주는 절 (혼자서 사용 불가)

그룹 조건 , 조건식 avg(sal)>1000

order by 정렬 하여 출력

col asc(default값으로 생략 가능, 오름차순),desc(내림차순)

group by가 없으면 전체의 값

group by가 있으면 여러개의 값

22.회사 전체의 최대 급여, 최소 급여, 급여 총 합 및 평균 급여를 출력하시오

SQL> select max(salary),min(salary), sum(salary),avg(salary)
  2  from employees;

23. 각 직업별, 최대 급여, 최소 급여, 급여 총 합 및 평균 급여를 출력하시오. 단 최대 급여는 max, 최소 급여는 min, 급여 총 합은 sum 및 평균 급여는 avg 로 출력하고, 직업을 오름차순으로 정렬하시오

SQL> 1  select job_id, max(salary) MAX,min(salary) MIN ,sum(salary) SUM ,avg(salary) AVG
  2  from employees
  3  group by job_id
  4* order by job_id

24. 100번 부서를 제외한 각 부서별 평균급여가 7,000 이상인 부서 및 평균 급여를 출력하시오

SQL> 1  select department_id, avg(salary)
  2  from employees
  3  where department_id != 100
  4  group by department_id
  5* having avg(salary)>=7000

25. 50번 부서에 근무하는 매니저별 평균 급여를 출력하시오.

select manager_id, avg(salary) avg
from employees
where department_id = 50
group by manager_id

26. 동일한 직업을 가진 사원들의 총 수를 출력하시오.

SQL> 1  select job_id, count(employee_id)
  2  from employees
  3* group by job_id

27.직원이 4명 이상인 부서의 부서번호와 인원을 출력하시오

select department_id, count(employee_id)
from employees
group by department_id
having count(employee_id) >=4

28. 매니저로 근무하는 사원들의 총 수를 출력하시오

SQL>1  select count(distinct manager_id)
  2* from employees

29. 사내의 최대 급여 및 최소 급여의 차이를 출력하시오

SQL> select max(salary)-min(salary)
  2  from employees;

30.매니저의 사번 및 그 매니저 밑 사원들 중 최소 급여를 받는 사원의 급여를 출력하시오

- 매니저가 없는 사람들은 제외한다

- 최소 급여가 5000미만인 경우는 제외한다.

- 급여 기준 역순으로 조회한다.

select manager_id, min(salary)
from employees
where manger_id is not null
group by manger_id
having min(salary)>=5000
order by min(salary) desc

31. 부서명, 부서위치id, 각 부서 별 사원 총 수, 각 부서 별 평균 급여를 출력하되, 부서위치를 오름차순으로 출력하시오

select d.department_name, d.location_id, count(e.employee_id), avg(e.salary) avg_salary
from employees e. departments d
where e.department_id = d.department_id
group by d.department_name, d.location_id
order by d.location_id;

<<오후 java 수업>>

Q.다중IF~ELSE문 예제

알파벳 하나를 입력받은 후 A를 입력한 경우는 "A입력", B를 입력한 경우는 "B입력" ,C를 입력한 경우는 "C입력"을 출력하고 그 외의 알파벳을 입력한 경우는 "A,B,C외 입력" 을 출력하시오.

package 다중if문;

import java.util.Scanner;

public class Ex03다중if3 {

	public static void main(String[] args) {
		Scanner sc = new Scanner(System.in);
		
		System.out.print("알파벳 입력 : ");
		String al = sc.next();
		
		if(al.equals("A")) {
			System.out.println("A입력");
		}else if(al.equals("B")) {
			System.out.println("B입력");
		}else if(al.equals("C")) {
			System.out.println("c입력");
		}else {
			System.out.println("A,B,C 외 입력");
		}
		
		
		
		System.out.print("알파벳 입력(char) : ");
		char b = sc.next().charAt(0);
		
		if(b=='A') {
			System.out.println("A입력");
		}else if(b=='B') {
			System.out.println("B입력");
		}else if(b=='C') {
			System.out.println("c입력");
		}else {
			System.out.println("A,B,C 외 입력");
		}
		
		
		
		System.out.print("알파벳 입력(char-아스키코드) : ");
		char c = sc.next().charAt(0);
		
		if(c==65) {
			System.out.println("A입력");
		}else if(c==66) {
			System.out.println("B입력");
		}else if(c==67) {
			System.out.println("c입력");
		}else {
			System.out.println("A,B,C 외 입력");
		}

	}

}

charAt(int index) = String 문자열에서 index 번째 문자 값 1개를 가져온다.

[switch문]

:값에 따라 여러 방향으로 분기하는 경우

: '식'을 계산한 후 그 결과 값과 일치하는 case로 분기

switch(식){

case 값1:

실행문장1

break;

case 값2:

실행문장2

break;

case 값3.:

...

case 값n:

실행문장n

break;

default:

실행문장n+1

}

다음문장

[switch문 break]

break:조건문, 반복믄을 탈출하는 키워드

switch~case문은 식과 값의 연산 결과가 일치할 경우 break문을 만날 때 까지 실행된다.

Q. switch문 예제

String 형 변수 data를 선언하고 키보드로 값을 입력받으시오. switch~case 문을 사용하여 입력받은 값이 "한국어"이면 "안녕하세요", "영어"이면 "헬로", "중국어"이면 "니하오", "일본어"이면 "곤니찌와"를 출력하고 그 외 다른 단어를 입력할 경우에는 "다른 값 입력"을 출력하시오.

package 스위치;

import java.util.Scanner;

public class Ex01스위치1 {

	public static void main(String[] args) {
		Scanner sc = new Scanner(System.in);
		
		System.out.print("입력 : ");
		String input = sc.next(); //한국어, 영어, 일본어, 중국어

		//switch(식(변수))
		switch(input) {
		case "한국어" : 
			System.out.println("안녕하세요");
			break;
		case "영어" :
			System.out.println("헬로");
			break;
		case "중국어":
			System.out.println("니하오");
			break;
		case "일본어":
			System.out.println("곤니찌와");
			break;
		default:
			System.out.println("다시 입력하세요");
		}
	}

}

swich문 예제2

월(1~12)을 입력받아 해당 월이 봄, 여름, 가을, 겨울 중 어느 계절인지 출력하시오.

(1,2,12월 -> 겨울/ 3,4,5월 ->봄 / 6,7,8 -> 여름 / 9,10,11월 -> 가을)

package 스위치;

import java.util.Scanner;

public class Ex02스위치2 {

	public static void main(String[] args) {
		Scanner sc = new Scanner(System.in);
		
		System.out.print("월(1~12) 입력 : ");
		int input = sc.nextInt();
		
		switch(input) {
		case 3,4,5 :
			System.out.println(input + "월은 봄입니다");
		    break;
		case 6,7,8 :
			System.out.println(input + "월은 여름입니다");
		    break;
		case 9,10,11 :
			System.out.println(input + "월은 가을입니다");
		    break;
		case 12,1,2 :
			System.out.println(input + "월은 겨울입니다");
		    break;
		default:
			
		}
	}

}

System.out.print("넣을 금액 : ");
		int money = sc.nextInt();
		System.out.println("--메뉴--");
		System.out.print("1. 콜라(1800원) 2.파워에이드(2000원) 3.탄산수(1200원) >>");
		int choice = sc.nextInt();
		int pay = 0;
		
		switch(choice) {
		case 1:
			pay = 1800;
			break;
		case 2:
			pay = 2000;
			break;
		case 3:
			pay = 1200;
			break;
		default:
			System.out.println("메뉴를 다시 선택하세요.");
		}
		if((money>=1800&&choice==1)||(money>=2000&&choice==2)||(money>=1200&&choice==3)) {
			System.out.println("잔돈 : "+(money-pay)+"원");
			System.out.println("천원 : "+(money-pay)/1000);
			System.out.println("오백원 : "+(money-pay)%1000/500);
			System.out.println("백원 : "+(money-pay)%500/100);
		}else if(money<1200||(money>=1800&&choice!=1)||(money>=2000&&choice!=2)||(money>=1200&&choice!=3)) {
			System.out.println("돈이 부족해요ㅠㅡㅜ");

package 스위치;

import java.util.Scanner;

public class Ex04자판기 {

	public static void main(String[] args) {
		Scanner sc = new Scanner(System.in);
		
		System.out.print("넣을 금액  : ");
		int money = sc.nextInt();
	
		System.out.println("==메뉴==");
		System.out.print("1.콜라(1800원) 2.파워에이드(2000원) 3.탄산수(1200원) >> ");
		int menu = sc.nextInt();
		
		int change = 0; //사용자가 받은 잔돈을 저장할 변수
		//잔돈 계산
		if(menu==1) { //콜라
			//System.out.println("잔돈 : "+ (money-1800)+"원" );
			change = money - 1800;
		}else if(menu==2) {//파워에이드
			//System.out.println("잔돈 : "+ (money-2000)+"원");
			change = money - 2000;
		}else if(menu==3) {//탄산수
			//System.out.println("잔돈 : "+(money-1200)+"원");
			change = money - 1200;
		}else { //1,2,3외 다른 숫자
			System.out.println("메뉴를 다시 선택하세요");
		}
		
		
		//잔돈 계산
		//돈이 넉넉한 경우 : 잔돈 >= 0
		//돈이 부족한 경우 : 잔돈 <0
		if(change>=0) {
			System.out.println("잔돈 : "+change+"원");
			//천원,오백원,백원
			System.out.println("천원 : "+ (change/1000));
			System.out.println("오백원 : "+ ((change%1000)/500));
			System.out.println("백원 : "+ ((change%500)/100));
		}else {
			System.out.println("돈이 부족해요ㅜㅜ");
		}
	}
}

728x90

LIST