[DB] 문제풀이

• 이번에는 여지껏 쌓아온 DB 기본 쿼리 문법을 사용하여 문제를 풀어봅시다.
• # 으로 작성된 문장은 주석처리가 되어있는 문제의 조건이다.

 

 1. 데이터베이스 생성 및 테이블 생성과 데이터 입력

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

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

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

# 사원(홍길동/홍보/5000만원, 홍길순/홍보/6000만원, 임꺽정/기획/4000만원)
## IT부서는 아직 사원이 없음
CREATE TABLE emp (
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
`name` CHAR(100) NOT NULL,
deptId INT(10) UNSIGNED NOT NULL,
salary INT(10) UNSIGNED 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;

# 각 테이블의 구조 및 데이터 조회
DESC dept;
DESC emp;
SELECT * FROM dept;
SELECT * FROM emp;

 

생성된 dept 테이블

생성된 emp 테이블

 

 

 

2. 사용자가 원하는 여러가지 방식으로 데이터 조회하기

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

조회 결과

 

# 가장 큰 사원 번호 출력
SELECT MAX(id) AS `가장 큰 사원번호`FROM emp;

조회 결과

 

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

조회 결과

 

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

조회 결과

 

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

조회 결과

 

# 부서별, 1년 고정 지출(인건비)
SELECT D.name AS 부서명,
IFNULL(SUM(E.salary), 0) AS `1년 고정 지출`
FROM dept AS D
LEFT JOIN emp AS E
ON D.id = E.deptId
GROUP BY D.id;

조회 결과

 

# 부서별 사원 리스트, 최고, 최저, 평균연봉
SELECT D.name AS 부서명,
GROUP_CONCAT(E.name) AS `사원리스트`,
IFNULL(MAX(E.salary), 0) AS `최고연봉`,
IFNULL(MIN(E.salary), 0) AS `최저연봉`,
IFNULL(TRUNCATE(AVG(E.salary), 0), 0) AS `평균연봉`
FROM dept AS D
LEFT JOIN emp AS E
ON D.id = E.deptId
GROUP BY D.id;

조회 결과