DAY.12
1. 모델링 연습
[ 1 ]
강사에 대해서는 강사번호, 이름, 나이, 성별 정보가 유지되어야 함.
학생에 대해서는 학번, 이름, 주소 정보가 유지되어야 함.
한 강사가 여러 개의 강의를 할 수 있으며, 각 강의를 듣는 학생은 학점이 부여됨.
과목에 대해서는 과목번호, 과목명이 유지되어야 함.
과목은 여러 시간을 가질 수 있고 각 시간은 강의라고 명한다.
같은 과목이라도 시간과 장소가 다를 수 있다. (강의 별 시간과 장소가 다를 수 있다.)
강의는 한 명의 강사가 강의할 수 있다.
학생( [학번], 이름, 주소 )
강사( [강사번호], 이름, 나이, 성별 )
과목( [과목번호], 과목명 )
강의( [강의번호], {과목번호, 시간, 강사번호}, 장소 )
수강( [강의번호, 학번], 학점 )
[ 2 ]
자동차 보험회사는 많은 고객을 관리하고 있음
고객에 대해서는 고객번호, 이름, 최초보험가입일과 주소 정보를 관리함
고객은 회사원과 자영업자로 분류함 (둘 중 하나만 가능)
회사원은 회사명과 차량용도, 자유업자는 업종과 주당운행거리의 정보를 유지함
각 고객은 승용차를 보유함. (여러 대 가능) 이 때 각 차는 사고기록을 유지함
승용차에 대해서는 등록번호, 제작 년도 및 색상 정보를 관리하고
사고 기록을 위해서는 사고번호, 사고 발생 일시, 사고 장소의 정보를 관리함
Ver1.
| 고객 ( [고객번호], 이름, 최초보험가입일, 주소, 고객타입(회사원, 자유업자) ) 회사원( [고객번호], 회사명, 차량용도) 자유업자( [고객번호], 업종, 주당운행거리) 차량( [등록번호], 고객번호, 제작년도, 색상 ) 사고( [사고번호], 등록번호, 사고_발생_일시, 사고_장소 ) |
| 같은 보험사에 여러 차량이 같은 사고가 났는 경우. 각 차량별로 사고번호가 나와야 한다. |
Ver2.
| 사고( [사고번호, 등록번호], 사고_발생_일시, 사고_장소 ) |
| 이 테이블 구조라면 사고_발생일시, 사고_장소 데이터가 중복됨. |
Ver3.
| 사고( [사고번호], 사고_발생_일시, 사고_장소 ) 사고차량( [사고번호, 등록번호] ) |
| Ver1, 2의 문제점을 해결하는 구조 |
2. employees database
departments: 부서테이블
- dept_no: 부서번호
- dept_name: 부서명
dept_emp: 부서에 소속된 사원 테이블
- emp_no: 사원번호
- dept_no: 부서번호
- from_date: 소속 시작일
- to_date: 소속 종료일 (9999-01-01 값이면 종료 아님 )
dept_manager: 부서 매니저 테이블
- emp_no: 사원번호
- dept_no: 부서번호
- from_date: 매니저 시작일
- to_date: 매니저 종료일 (9999-01-01 값이면 종료 아님 )
employees: 사원 테이블
- emp_no: 사원번호
- birth_date: 생년월일
- first_name: 이름
- last_name: 성
- gender: 성별
- hire_date: 고용일
salaries: 사원 연봉 테이블
- emp_no: 사원번호
- salary: 연봉
- from_date: 연봉 시작일
- to_date: 연봉 종료일
titles: 사원 직급
- emp_no: 사원번호
- title: 직급명
- from_date: 직급 시작일
- to_date: 직급 종료일
/*
departments: 부서테이블
*/
EXPLAIN
SELECT E.emp_no, E.first_name, E.last_name, S.salary, ( SELECT MAX(dept_no) FROM dept_emp
WHERE emp_no =10001 )
FROM employees E
INNER JOIN salaries S
ON E.emp_no = S.emp_no
WHERE E.emp_no = 10001;
SELECT E.emp_no, E.first_name, E.last_name, S.salary, MAX(DE.dept_no) AS max_dept_no
FROM employees E
INNER JOIN salaries S
ON E.emp_no = S.emp_no
INNER JOIN dept_emp DE
ON DE.emp_no = E.emp_no
WHERE E.emp_no = 10001
GROUP BY E.emp_no, E.first_name, E.last_name, S.salary;
-- Q-02. 사원번호 1100으로 시작하면서 사원번호가 5자리인 사원의 정보를 모두 출력하시오.
SELECT @@profiling;
SET @@profiling = 1;
SELECT *
FROM employees
WHERE emp_no LIKE '1100_';
SELECT *
FROM employees
WHERE emp_no BETWEEN 11000 AND 11009;
SELECT *
FROM employees
WHERE emp_no >= 11000
AND emp_no <= 11009;
SHOW PROFILES;
-- Q-03. 사원의 성별 별 사원 수
SELECT gender, COUNT(emp_no)
FROM employees
GROUP BY gender;
-- Q-04. 사원 중 1989년도에 입사하였고 사원번호가 100,000를 초과하는 사원번호를 구하시오.
;
EXPLAIN
SELECT emp_no
FROM employees
WHERE YEAR(hire_date) = 1989
AND emp_no > 100000;
SELECT emp_no
FROM employees
WHERE hire_date BETWEEN '1989-01-01' AND '1989-12-31'
AND emp_no > 100000;
CREATE INDEX idx_employees_hiredate
ON employees (hire_date);
DROP INDEX idx_employees_hiredate
ON employees;