[Database] Database 전체 실습

실습

---

# 테이블 수정 삭제

1. department 테이블에서 location_id 열에 FOREIGN KEY 제약 조건 추가하시오.

ALTER TABLE department ADD CONSTRAINT FOREIGN KEY(location_id)
REFERENCES location(local_code);

 

2. location 테이블에서 national_code 열에 FOREIGN KEY 제약 조건 추가하시오.

ALTER TABLE location ADD CONSTRAINT FOREIGN KEY(national_code)
REFERENCES national(national_code);

 

# 조인

1. 테이블을 다중 JOIN 하여 사번, 직원명, 부서명, 지역명을 조회하시오.

SELECT e.emp_id AS '사번',
	e.emp_name AS '직원명',
	d.dept_title AS '부서명',
	l.local_name AS '지역명'

FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN location l ON d.location_id = l.local_code
ORDER BY emp_name
;

2. 테이블을 다중 JOIN 하여 사번, 직원명, 부서명, 지역명, 국가명 조회하시오.

-- 부서가 있는 사원만
SELECT e.emp_id AS '사번',
	e.emp_name AS '직원명',
	d.dept_title AS '부서명',
	l.local_name AS '지역명',
	n.national_name AS '국가명'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN location l ON d.location_id = l.local_code
INNER JOIN national n ON l.national_code = n.national_code
;


-- 부서가 없는 사원도
SELECT e.emp_id AS '사번',
	e.emp_name AS '직원명',
	d.dept_title AS '부서명',
	l.local_name AS '지역명',
	n.national_name AS '국가명'
FROM employee e
LEFT OUTER JOIN department d ON e.dept_code = d.dept_id
LEFT OUTER JOIN location l ON d.location_id = l.local_code
LEFT OUTER JOIN national n ON l.national_code = n.national_code
;

 

부서가 존재하는 사원만

부서가 존재하지 않는 사원

3. 테이블을 다중 JOIN 하여 사번, 직원명, 부서명, 지역명, 국가명, 급여 등급 조회하시오.

SELECT e.emp_id AS '사번',
	e.emp_name AS '직원명',
	d.dept_title AS '부서명',
	l.local_name AS '지역명',
	n.national_name AS '국가명',
	g.sal_level AS '급여 등급'
FROM employee e
LEFT OUTER JOIN department d ON e.dept_code = d.dept_id
LEFT OUTER JOIN location l ON d.location_id = l.local_code
LEFT OUTER JOIN national n ON l.national_code = n.national_code
INNER JOIN sal_grade g ON e.salary BETWEEN g.min_sal AND g.max_sal
;

4. 부서가 있는 직원들의 직원명, 직급명, 부서명, 근무 지역명을 조회하시오.

SELECT e.emp_name AS '직원명',
	j.job_name AS '직급명',
	d.dept_title AS '부서명',
	l.local_name AS '근무 지역'
FROM employee e
LEFT OUTER JOIN job j ON e.job_code = j.job_code
LEFT OUTER JOIN department d ON e.dept_code = d.dept_id
LEFT OUTER JOIN location l ON d.location_id = l.local_code
;

5. 직급이 대리이면서 ASIA 지역에서 근무하는 직원들의 사번, 직원명, 직급명, 부서명, 근무지역, 급여를 조회하시오.

SELECT e.emp_id AS '사번',
	e.emp_name AS '직원명',
	j.job_name AS '직급명',
	d.dept_title AS '부서명',
	l.local_name AS '근무 지역',
	e.salary AS '급여'
FROM employee e
INNER JOIN job j ON e.job_code = j.job_code
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN location l ON d.location_id = l.local_code
WHERE l.local_name LIKE 'ASIA%' AND
	j.job_name = '대리'
;

6. 보너스를 받는 직원들의 직원명, 보너스, 연봉, 부서명, 근무지역을 조회하시오. (단, 부서 코드가 없는 사원도 출력될 수 있게 OUTER JOIN 사용)

SELECT e.emp_name AS '직원명',
	e.bonus AS '보너스',
	e.salary * 12 AS '연봉',
	d.dept_title AS '부서명',
	l.local_name AS '근무 지역'
FROM employee e
LEFT OUTER JOIN department d ON e.dept_code = d.dept_id
LEFT OUTER JOIN location l ON d.location_id = l.local_code
WHERE bonus IS NOT NULL
;

7. 한국과 일본에서 근무하는 직원들의 직원명, 부서명, 근무지역, 근무 국가를 조회하시오.

SELECT e.emp_name AS '직원명',
	d.dept_title AS '부서명',
	l.local_name AS '근무 지역',
	n.national_name AS '근무 국가'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN location l ON d.location_id = l.local_code
INNER JOIN national n ON l.national_code = n.national_code
WHERE n.national_name IN ('한국', '일본')
;

 

# DDL

0. 데이터베이스 생성

$ sudo mariadb -u root -p
# password 입력
$ create database university;
$ show databases;

$ grant all privileges on university.* to 'beyond'@'%';
$ show grants for 'beyond'@'%';

 

1. 학과(tb_department) 테이블을 생성하시오.

CREATE TABLE tb_department(
	department_no VARCHAR(10) PRIMARY KEY,
	department_name VARCHAR(20) NOT NULL,
	category VARCHAR(20) NULL,
	open_yn CHAR(1) NULL,
	capacity INT NULL
);

2. 학생(tb_student) 테이블을 생성하시오.

CREATE TABLE tb_student(
	student_no VARCHAR(10) PRIMARY KEY,
	department_no VARCHAR(10) NOT NULL REFERENCES tb_department(department_no),
	student_noame VARCHAR(30) NOT NULL,
	student_ssn VARCHAR(14) NULL,
	student_address VARCHAR(100) NULL,
	entrance_date DATE NULL,
	absence_yn CHAR(1) NULL,
	coach_professor_no VARCHAR(10) NULL
);
ALTER TABLE tb_student ADD CONSTRAINT FOREIGN KEY(coach_professor_no)
REFERENCES tb_professor(professor_no);

3. 과목(tb_class) 테이블을 생성하시오.

-- 과목 테이블(tb_class)
CREATE TABLE tb_class(
	class_no VARCHAR(10) PRIMARY KEY,
	department_no VARCHAR(10) NOT NULL REFERENCES tb_department(department_no),
	preattending_class_no VARCHAR(10), -- 외부
	class_name VARCHAR(30) NOT NULL,
	class_type VARCHAR(10)
);
ALTER TABLE tb_class ADD CONSTRAINT FOREIGN KEY(preattending_class_no)
REFERENCES tb_class(class_no);

4. 교수(tb_professor) 테이블을 생성하시오.

CREATE TABLE tb_professor(
	professor_no VARCHAR(10) PRIMARY KEY,
	professor_name VARCHAR(30) NOT NULL,
	professor_ssn VARCHAR(14),
	professor_address VARCHAR(100),
	department_no VARCHAR(10) REFERENCES tb_department(department_no)
);

5. 과목 교수(tb_class_professor) 테이블을 생성하시오.

-- 과목 교수 테이블(tb_class_professor)
CREATE TABLE tb_class_professor(
	class_no VARCHAR(10) REFERENCES tb_class(class_no),
	professor_no VARCHAR(10) REFERENCES tb_professor(professor_no),
	PRIMARY KEY(class_no, professor_no)
);

6. 학점( tb_grade) 테이블을 생성하시오.

CREATE TABLE tb_grade(
	tern_no VARCHAR(10) NOT NULL,
	class_no VARCHAR(10) NOT NULL REFERENCES tb_class(class_no),
	student_no VARCHAR(10) NOT NULL REFERENCES tb_student(student_no),
	`point` DECIMAL(3,2),
	 PRIMARY KEY(tern_no, class_no, student_no)
);