DBMS #3일차

a6 DB 삭제/생성/선택
DROP DATABASE IF EXISTS a6;
CREATE DATABASE a6;
USE a6;

부서(홍보, 기획)
CREATE TABLE dept (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),
regDate DATETIME NOT NULL,
name CHAR(100) NOT NULL UNIQUE
);

INSERT INTO dept SET regDate = NOW(),name = '홍보';
INSERT INTO dept SET regDate = NOW(),name = '기획';

SELECT * FROM dept;

사원(홍길동/홍보/5000만원, 홍길순/홍보/6000만원, 임꺽정/기획/4000만원)
CREATE TABLE emp (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),
regDate DATETIME NOT NULL,
name CHAR(100) NOT NULL UNIQUE,
deptId INT(10) NOT NULL,
salary INT(10) NOT NULL
);

INSERT INTO emp SET regDate = NOW(), name="홍길동", deptId = 1, salary=5000;
INSERT INTO emp SET regDate = NOW(), name="홍길순", deptId = 1, salary=6000;
INSERT INTO emp SET regDate = NOW(), name="임꺽정", deptId = 2, salary=4000;

SELECT * FROM emp;

사원 수 출력
SELECT COUNT(*) AS '사원 수' FROM emp;

가장 큰 사원 번호 출력
SELECT id AS '가장 큰 사원 번호' FROM emp ORDER BY id DESC LIMIT 1;

가장 고액 연봉
SELECT MAX(salary) AS '고액 연봉' FROM emp;

가장 저액 연봉
SELECT MIN(salary) AS '저액 연봉' FROM emp;

회사에서 1년 고정 지출(인건비)
SELECT SUM(salary) AS '인건비' FROM emp;

부서별, 1년 고정 지출(인건비)
SELECT deptId, SUM(salary) AS '부서별 인건비' FROM emp
GROUP BY deptId;

부서별, 최고연봉
SELECT deptId, MAX(salary) AS '부서별 최고연봉' FROM emp
GROUP BY deptId;

부서별, 최저연봉
SELECT deptId, MIN(salary) AS '부서별 최저연봉' FROM emp
GROUP BY deptId;

부서별, 평균연봉
SELECT deptId, AVG(salary) AS '부서별 평균연봉' FROM emp
GROUP BY deptId;

부서별, 부서명, 사원리스트, 평균연봉, 최고연봉, 최소연봉, 사원수
V1(조인 안한 버전)
SELECT deptId AS '부서번호', dept.name AS '부서명', GROUP_CONCAT(emp.name) AS '사원리스트', TRUNCATE(AVG(salary), 0) AS '부서별 평균연봉', MAX(salary) AS '부서별 최고연봉', MIN(salary) AS '부서별 최소연봉', COUNT(*) AS '사원수'
FROM emp, dept
WHERE dept.Id = emp.deptId
GROUP BY deptId;

V2(조인해서 부서명까지 나오는 버전)
SELECT dept.name AS 부서명, GROUP_CONCAT(emp.name) AS '사원리스트', TRUNCATE(AVG(salary), 0) AS '부서별 평균연봉', MAX(salary) AS '부서별 최고연봉', MIN(salary) AS '부서별 최소연봉', COUNT(*) AS 사원수
FROM emp
INNER JOIN dept
ON emp.deptId = dept.id
GROUP BY dept.name
ORDER BY dept.id;

V3(V2에서 평균연봉이 5000이상인 부서로 추리기)
SELECT dept.name AS 부서명, GROUP_CONCAT(emp.name) AS '사원리스트', TRUNCATE(AVG(salary), 0) AS '평균연봉', MAX(salary) AS '최고연봉', MIN(salary) AS '최소연봉', COUNT(emp.name) AS 사원수
FROM emp
INNER JOIN dept
ON emp.deptId = dept.id
GROUP BY dept.name HAVING AVG(salary)>=5000
ORDER BY dept.id;

개념

• GROUP_CONCAT
  group by 로 문자열을 합칠땐 group_concat 을 이용
  기본형 : group_concat(필드명)
  구분자 변경 : group_concat(필드명 separator '구분자')
  중복제거 : group_concat(distinct 필드명)
  문자열 정렬 : group_concat(필드명 order by 필드명)

• TRUNCATE
  결과값을 지정한 자릿수까지 버림하여 정돈
  (1) 숫자를 소수점 아래 N째 자리까지 버림하여 정리한 산술연산 결과를 가상테이블 DUAL로 출력하려는 경우 >> trunc(숫자, 자릿수N)
  (2) 컬럼의 레코드 데이터 검색결과를 소수점 아래 N째 자리까지 일괄 버림하여 정돈된 결과를 조회하려는 경우 >> trunc(컬럼이름1, 자릿수N)
  (3) 그룹화된 집계함수 결과값을 버림하여 정돈된 결과를 조회하려는 경우 >>
  trunc(집계함수A(컬럼이름1), 자릿수)