DataBase : group function

그룹함수

복수행 함수

• count(*) : null을 포함한 전체 레코드 숫자 반환, 전체레코드 숫자
• count(컬럼명) : null을 제외한 숫자 반환

• 전체 직원의 숫자 조회
  select count(*) from emp;
select count(empno) from emp;
• 현재 수당이 있는 직원의 숫자 조회
  select count(comm) from emp;
• 실질적으로 수당을 받는 직원의 숫자 조회
  select * from emp where comm > 0;
• 수당이 있는 직원 정보 조회
  select * from emp where comm is not null;

• max(col) : 최대값
• min(col) : 최소값
• sum(col) : 합계
• avg(col) : 평균

• 직원들의 최대급여, 최소급여, 급여총액, 평균급여, 최대급여-최소급여차이
  select
  max(sal) 최대급여,
  min(sal) 최소급여,
  sum(sal) 급여총액,
  round(avg(sal)) 평균급여,
  max(sal) - min(sal) "최대급여-최소급여"
  from emp;

• 화폐단위, 기호
  select
  to_char(max(sal), 'L999,999') 최대급여,
  to_char(min(sal), 'L999,999') 최소급여,
  to_char(sum(sal), 'L999,999') 급여총액,
  to_char(round(avg(sal)), 'L999,999') 평균급여,
  to_char(max(sal) - min(sal), 'L999,999') "최대급여-최소급여"
  from emp;

SELECT 전체형식

SELECT~~
FROM 테이블명1 [, 테이블명X, (SUB-QUERY)] 이런식으로 뒤에 더 올수잇음
WHERE 조건식1 [AND | OR | NOT] 조건식X
GROUP BY 그룹핑대상컬럼명 [, 그룹핑대상컬럼명X]
HAVING 그룹핑결과에대한조건식 // 해빙은 그룹바이없으면 못 씀
ORDER BY 정렬컬럼명 정렬방법, 정렬컬럼명X 정렬방법
;

-- GROUP 함수를 사용해서 그룹핑 결과를 조회
-- SELECT FROM WHERE GROUP BY HAVING ORDER BY
-- 셀렉트항목으로 올 수 있는거 : 그룹바이 지정한 컬럼명, 그룹함수

• 부서별 평균 급여 조회
  -- 출력형식 : 부서번호, 평균급여
  -- 소수이하 처리, 숫자 천단위 표기, 통화기호 표기
  select deptno, to_char(round(avg(sal)), '$999,999') from emp group by deptno order by deptno;
  `
• 부서별 평균 급여 조회
• 부서별 평균 급여가 2,000 초과 부서에 대해서만 조회
  select deptno, to_char(round(avg(sal)), '$999,999') from emp group by deptno having avg(sal) > 2000 order by deptno;

• 직원들의 직무 조회
  select job from emp order by 1;

• 조회시 중복을 제거하고 조회 : select distinct ~~> select한 결과에 대해서 중복을 제거하고 조회해줌
  select distinct job from emp order by 1;

• 부서별 직무의 종류를 조회
  -- 조회항목 : 부서번호, 직무
  -- 정렬 : 부서번호, 직무순서대로 조회
  select distinct deptno, job from emp order by deptno, job;

SELECT 조회(검색) 처리 순서

5. SELECT ~~
   1. FROM 테이블명1 [, 테이블명X, (SUB-QUERY)] 이런식으로 뒤에 더 올수잇음
   2. WHERE 조건식1 [AND | OR | NOT] 조건식X
   3. GROUP BY 그룹핑대상컬럼명 [, 그룹핑대상컬럼명X]
   4. HAVING 그룹핑결과에대한조건식 // 해빙은 그룹바이없으면 못 씀
6. ORDER BY 정렬컬럼명 정렬방법, 정렬컬럼명X 정렬방법

• rowid : 물리적 데이터(레코드) 저장위치 지칭 (동일한 레코드를 지칭 => 동일한 rowid)
• rownum : select 결과에 대한 일련번호 (select 결과에 따라서 일련번호는 동적으로 변경됨)

• 조회에 대한 일련번호(rownum), 저장위치(rowid), 부서번호, 사번, 이름, 급여정보를 조회
  select rownum, rowid, deptno, empno, ename, sal from emp;
  select rownum, rowid, deptno, empno, ename, sal from emp order by deptno;

• 조회에 대한 일련번호(rownum), 저장위치(rowid), 부서번호, 사번, 이름, 급여정보를 조회

• 10번 부서원에 대해서만 조회
  select rownum, rowid, deptno, empno, ename, sal from emp where deptno=10;

• 직무의 종류 중복 제거하고 조회
  selecet distinct job from emp order by 1;

-- 직무별 직원의 인원수를 조회
-- 출력형식 : 직무, 인원수
-- 인원수가 많은 순서대로 정렬조회
select job, count(*) from emp group by job order by 1 desc;

SUB-QUERY

• SQL 구문안에 있는 SQL 구문
• 내부에 있는 SUB-QUERY 먼저 수행 => 수행결과를 2. 외부 SQL 구문에게 전달
• 주의사항 : SUB-QUERY를 수행한 결과가 단일 컬럼, 다중컬럼, 단일행, 다중행 여부에 따라서 WHERE 조건연산자 제약이 발생될 수 있다.
• (SUB-QUERY)
• 위치
  SELECT 구문
  FROM 구문 : INLINE-VIEW
  WHERE 구문

  • 전체직원의 사번, 직무 조회
    select empno, job from emp;
  • sub-query
  • 7499 직원의 직무 조회
    select job from emp where empno=7499;
  • 7499 직원과 같은 직무를 담당하는 직원조회
    select * from emp
    where job = 'saleman';
  • 서브쿼리이용
    select * from emp
    where job = (select job from emp where empno=7499);

  • 직원중에서 급여를 많이 받는 최상위 3명의 정보를 조회

    • 출력형식 : 순번 사번 이름 급여
    • 힌트: select 검색순서, rownum, sub-query

    1. select empno, ename, sal from emp order by sal desc;

    2. select rownum, empno, ename, sal from emp
       where rownum <= 3
       order by sal desc;

    3. select rownum, empno, ename, sal from emp order by sal desc;

    최종. select rownum, empno, ename, sal
    from (select empno, ename, sal from emp order by sal desc)
    where rownum <=3;

통계함수

• decode()
  - 오라클 전용
  (자바의 다중조건 비교 : switch(epxr) - case "value" ; ~~~ default; 랑 비슷)
  - 형식: decode(
  expr, /컬럼
  search-1, /조건
  result-1, /결과
  search-x,
  result-x,
  [default]
  ) "alias"

직원들의 직무에 따라서 경조회비를 차등 계산

• 출력형식 : 사번, 직무, 이름, 급여, 경조회비
• 정렬 : 경조회비 많이 내는 순서
  - 경조회비 계산
  - president = 급여 * 30%
  - manager = 급여 * 25%
  - analyst, salesman = 급여 * 20%
  - 기타 직무 = 급여 * 5%

1. 직무, 정렬 조회
   select distinct job from emp;

2. select empno, job, ename, sal,
   trunc(
   decode(
   job,
   'PRESIDENT', sal * 0.3,
   'MANAGER', sal * 0.25,
   'ANALYST', sal * 0.2,
   'SALESMAN', sal * 0.2,
   sal * 0.05
   )) "경조회비"
   from emp
   order by 경조회비 desc;

• case()
  - ANSI 표준
  (자바의 다중조건 비교 : if(조건식) ~~ else if(조건식x) ~ default; 랑 비슷)
  • 조건식 : 자유롭게 비교 가능
    - 형식 :
    case
    when search-1 then result-1
    when search-x then result-x
    else default
    end "alias"

직원들의 직무에 따라서 경조회비를 차등 계산

• 출력형식 : 사번, 직무, 급여, 경조회비
• 정렬 : 경조회비 많이 내는 순서
  - 경조회비 계산
  - president = 급여 * 30%
  - manager = 급여 * 25%
  - analyst, salesman = 급여 * 20%
  - 기타 직무 = 급여 * 5%

select empno, ename, sal,
to_char(
ceil(
case
when job = 'PRESIDENT' then SAL * 0.3
when job = 'MANAGER' then SAL * 0.25
when job = 'ANALYST' then SAL * 0.20
when job = 'SALESMAN' then SAL * 0.20
else SAL * 0.05
end
)
, '$999,999') "경조회비"
from emp
order by 경조회비 desc;

DDL : 객체 생성, 변경, 삭제

• 객체 : table, sequence

table

• 도메인 데이터의 물리적인 저장구조 : 스키마(schema)
• 도메인 데이터 : 엔티티(Entity)
• table > record > column(속성)

table 생성

1. 새로운 테이블 생성
2. 기존 테이블 구조 참조해서 생성
3. 기존 테이블 구조 + 데이터 참조해서 생성

1. 새로운 테이블 생성

create table 테이블명 (
컬럼명 타입(길이) [제약지정],
컬럼명x 타입(길이) [제약지정],
) ;

• 제약(constaint)
  1. 식별키 (PK: primary key)

  2. 필수 : not null

  3. 중복불가 : unique

  4. 참조키 : FK(Foreign key)

  5. 기본값 : default

  6. 검증: check

     -- 부가적인 제약 : 테이블명, 컬럼명, 순서, 타입, 길이

학생의 성적 테이블 : 테이블명 student_score
-- 15001 홍길봉 92
-- 1. 학번 number(5), 식별키, student_no
-- 2. 이름 10자리, 필수, 중복허용, name
-- 3. 점수 number(3), 선택, score

create table student_score (
student_no number(5) primary key,
name varchar2(30) not null,
score number(3)
) ;

table 삭제

drop table 테이블명;
drop table 테이블명 cascade constraint;
=> 부모테이블과 자식테이블의 관계를 제거하면서 부모테이블 삭제

table 변경

Database Modeling tools

--ER-Win : 상용
--EXErd : 국내, 상용, 1달간 무료사용

회원테이블정의서