[2월][4주차][SQL] 데이터베이스 구축 : 테이블 설계부터 ERD까지

2025.02.25

실습 : 주어진 데이터를 활용하여 DB 구축 및 추출

---

1. 주어진 데이터를 가지고 우선, 대충 DB 구조를 그려보기 ! 

 

글씨는 흐린눈..^^

구조도 설계하기 재밌다 ㅎㅎ

---

2. MYSQL 활용하여 테이블 생성

[코드]

-- 데이터는 생략 --

# (b)department 테이블 생성
create database school;
create table department(dept_id varchar(50) primary key, 
                            dept_name varchar(50), 
                            office varchar(50)); 
                            
-- insert into 데이터 넣어주시면 됩니다~ --
#======================================================================================
# (a)student 테이블 생성
create table student(stu_id varchar(50) primary key, 
                            resident_id varchar(50), 
                            name varchar(50),
                            year int,
                            region varchar(50),
                            dept_id varchar(50)); 
 
-- insert into 데이터 넣어주시면 됩니다~ --

# (a) dept_id fk 만들기                 
alter table student 
add foreign key (dept_id)
references department(dept_id);
show create table student;
#======================================================================================
# (c) professor 테이블 생성
create table professor(prof_id varchar(50) primary key, 
                            resident_id varchar(50), 
                            name varchar(50),
                            dept_id varchar(50),
                            position varchar(50),
                            year_emp year); 
                            
-- insert into 데이터 넣어주시면 됩니다~ --

# (c) dept_id fk 만들기                 
alter table professor 
add foreign key (dept_id)
references department(dept_id);
show create table professor;
#======================================================================================
# (d) course 테이블 생성
create table course(course_id varchar(50) primary key, 
                            title varchar(50), 
                            credit int); 
                            
-- insert into 데이터 넣어주시면 됩니다~ --
  
#======================================================================================
# (e) class 테이블 생성
create table class(class_id varchar(50) primary key, 
                            course_id varchar(50), 
                            year year,
                            semester int,
                            division varchar(50),
                            prof_id varchar(50),
                            classroom varchar(50),
                            enroll int); 
                            
-- insert into 데이터 넣어주시면 됩니다~ --
  
# (e) course_id fk 만들기                 
alter table class 
add foreign key (course_id)
references course(course_id);
# (e) prof_id fk 만들기          
alter table class 
add foreign key (prof_id)
references professor(prof_id);
#======================================================================================
# (f) takes 테이블 생성
create table takes(stu_id varchar(50), 
                            class_id varchar(50), 
                            grade varchar(50)); 
                            
-- insert into 데이터 넣어주시면 됩니다~ --
  
# (f) stu_id fk 만들기                 
alter table takes 
add foreign key (stu_id)
references student(stu_id);
# (f) class_id fk 만들기          
alter table takes 
add foreign key (class_id)
references class(class_id);
select * from takes;

총 6개의 테이블 생성 완료 !! - 데이터 삽입까지 완료 ! - FK설정까지 완료 !

---

3. ERD 구조도로 테이블 시각화 

ERD 구조도 그릴때 사용하면 굉장히 좋은 사이트 추천 !!!!

 

dbdiagram.io - Database Relationship Diagrams Design Tool

위에 사이트 참고해서 그린 내가 구축한 데이터베이스 ERD 구조도 

ERD 그리기

이제 구조도를 이용해서 더 편하게 데이터를 추출합시다 ~ 

---

4. 데이터 추출 

예제 20문제 풀이

# 1) student 테이블에서 모든 학생들의 주소를 추출
select region
from student;

# 2) student 테이블에서 모든 학생들의 주소를 추출(중복제거)
select distinct(region) as address
from student;

# 3) professor 테이블에서 교수의 이름과 현재까지의 재직연수를 검색
select name, 2025-year_emp
from professor;

# 4) select문은 학생들의 이름, 학번, 그리고 소속 학과의 이름을 검색
select s.name, s.stu_id, d.dept_name
from student as s
join department as d on s.dept_id = d.dept_id;

# 4-1 다른 버전
select student.name, student.stu_id, department.dept_name
from student , department
where student.dept_id = department.dept_id;

# 5) 컴퓨터공학과 3학년 학생들의 학번을 검색
select stu_id
from student 
where dept_id = '920'
	and year = 3;
    
# 6) student 테이블에서 3, 4학년 학생들의 이름과 학번 오름차순으로 검색
select name, stu_id
from student
where year = 3 
	or year = 4
order by name asc, stu_id asc;

# 7) student 테이블에서 3, 4학년 학생들의 이름(내림차순)과 학번으로 검색
select name, stu_id
from student
where year = 3 
	or year = 4
order by name desc;

# 8) student 테이블과 department 테이블을 이용하여 학생들의 이름과 소속 학과 이름을 검색
select s.name, s.stu_id, d.dept_name
from student as s
join department as d on s.dept_id = d.dept_id;

# 9) student 테이블에서 ‘김광식’ 학생과 주소가 같은 학생들의 이름과 주소를 검색
select name
from student
where region = '서울';

# 9-1 다른버전
select s2.name
from student s1, student s2
where s1.address = s2.address 
	and s1.name = '김광식';

# 10) 교수들의 이름과 직위, 재직연수를 출력
select name as '이름', position as '학위', 2025-year_emp as '재직연수'
from professor;

# 11) student 테이블에서 김씨 성을 가진 학생들을 출력
select *
from student
where name like '김%';

# 12) student 테이블에서 여학생들만을 검색
select *
from student
where right(resident_id,7) like '2%'
	or right(resident_id,7) like '4%';
    
# 13) 컴퓨터공학과 학생들 중에서 교과목에 상관없이 학점을 'A+' 받은 학생들의 학번을 검색
select t.stu_id
from takes as t
join student as s on t.stu_id = s.stu_id
where s.dept_id = '920'
	and t.grade = 'A+';
    
# 14) 모든 교과목들에 대해 교과목명, 학점수, 개설 년도, 개설 학기를 검색
select c.title, c.credit, cl.year, cl.semester
from course as c
join class as cl on c.course_id = cl.course_id;

# 15) student 테이블에서 3학년 학생이 몇 명인지 출력
select count(*)
from student
where year = 3;

# 16) student 테이블에서 dept_id 필드에 값이 몇 개인지를 출력
select count(dept_id)
from student;

# 17) student 테이블에서 dept_id 필드에 값이 몇 개인지를 출력(중복재거)
select count(distinct(dept_id))
from student;

# 18) 컴퓨터공학과의 학생 수를 출력
select count(*)
from student
where dept_id = '920';

# 19) 전체 교수들의 재직연수 합
select sum(2025-year_emp)
from professor;

# 20) 전체 교수의 평균 재직연수를 출력
select avg(2025-year_emp)
from professor;

# 21) student 테이블에서 학과번호(dept_id 필드)별로 레코드의 개수를 출력
select dept_id, count(*)
from student
group by dept_id;

# 22) 학과번호 대신 department 테이블과 연결하여 학과 이름이 출력되도록 위의 문제를 수정
select d.dept_name, count(*)
from student as s 
join department as d on s.dept_id = d.dept_id
group by dept_name;

# 23) 학사 데이터베이스에서 학과별 교수 숫자와 평균 재직연수, 최대 재직연수를 출력
select dept_name, count(*), avg(2025-p.year_emp), max(2025-p.year_emp)
from professor as p
join department as d on p.dept_id = d.dept_id
group by dept_name;

# 24) 평균 재직연수가 10년 이상인 학과에 대해서만 교수 숫자와 평균 재직연수, 최대 재직연수를 출력
select  dept_name, count(*), avg(2012-p.year_emp), max(2012-p.year_emp)
from professor as p
join department as d on p.dept_id = d.dept_id
group by dept_name
having avg(2012-p.year_emp) >= 10;

# 25) '301호' 강의실에서 개설된 강좌의 과목명을 출력
select co.title
from course as co
join class as c on co.course_id = c.course_id
where c.classroom = '301호'

총 25문제 : 40분 / ㅎㅎ 재밌다