DB 관리 및 쿼리 실습(1)

배운것들을 활용한 쿼리 실습.

college라는 DB를 root에서 생성하고 mysqlUser@localhost에게 권한을 부여한다.

테이블 구조는 다음과 같이 만들 것.

 

 

먼저 ERDCloud 사이트에서 ERD를 그렸다.

 

그 후, 테이블을 생성(외래키 생성)하고, insert 데이터를 넣음.

create table professor(
pr_num int not null,
pr_name varchar(20) not null,
pr_age int not null,
pr_room varchar(45) not null,
pr_state varchar(40) default '재직',
pr_position varchar(40) default '조교수',
primary key(pr_num)
);

create table subject(
su_num int not null,
su_code varchar(20) not null,
su_title varchar(45) not null,
su_point int not null default 0,
su_time int not null default 0,
primary key(su_num)
);

create table student(
st_num int not null,
st_name varchar(20) not null,
st_term int not null default 1,
st_point int not null default 0,
primary key(st_num)
);

create table course(
co_num int not null,
co_pr_num int not null,  
co_su_num int not null,   
co_term int not null default 1,
co_year int not null,
co_timetable varchar(100),
primary key(co_num),
foreign key(co_pr_num) references professor(pr_num), --
foreign key(co_su_num) references subject(su_num) -- 여기서 이 두개는 위에서 미리 만들어 놔야 함.
);

create table guide(
gu_num int not null,
gu_pr_num int not null,
gu_st_num int not null,
gu_year int,
primary key(gu_num),
foreign key(gu_pr_num) references professor(pr_num),
foreign key(gu_st_num) references student(st_num)
);

create table attend(
at_num int not null auto_increment,
at_st_num int not null,
at_co_num int not null,
at_mid int not null default 0,
at_final int not null default 0,
at_hw int not null default 0,
at_attend int not null default 0,
at_score varchar(10),
at_pass varchar(1) default 'f',
at_repetition varchar(1) default 'n',
primary key(at_num),
foreign key(at_st_num) references student(st_num),
foreign key(at_co_num) references course(co_num)
);

insert into student(st_num, st_name,st_term) values
(2020123001,'김영철',1),
(2020123002,'나영희',1),
(2020160001,'강철수',2),
(2020160002,'박철수',2),
(2020456001,'강군',2);

insert into subject values
(1,'msc001','대학수학',3,3),
(2,'com001','컴퓨터개론',2,2),
(3,'com002','운영체제',3,3),
(4,'abc001','디자인이론',2,2),
(5,'abc002','생활영어',2,3);

insert into professor values
(2005789001, '홍길동',60,'B동302호','재직','정교수'),
(2006456001, '박영실',65,'B동301호','안식년','정교수'),
(2010160001, '강길동',55,'A동202호','재직','조교수'),
(2010160002, '김길순',50,'A동301호','재직','조교수'),
(2011123001, '이순신',55,'A동203호','재직','정교수');

insert into course(co_num, co_pr_num, co_su_num, co_term, co_year, co_timetable) values
(1, 2005789001,1,1,2002,'월2a/2b/3a/3b/4a/4b'),
(2, 2010160002,1,2,2002,'월2a/2b/3a/3b/4a/4b'),
(3, 2010160001,2,1,2002,'화1a/1b/2a/2b'),
(4, 2010160001,3,1,2002,'목2a/2b/3a/3b/4a/4b'),
(5, 2011123001,4,2,2002,'화1a/1b/2a/2b'),
(6, 2011123001,5,2,2002,'수1a/1b/2a/2b');

insert into guide(gu_num, gu_pr_num, gu_st_num, gu_year) values
(1, 2010160001,2020160001,2023),
(2, 2010160001,2020160002,2023),
(3, 2011123001,2020123001,2023),
(4, 2011123001,2020123002,2023),
(5, 2010160002,2020456001,2023);

insert into attend(at_st_num, at_co_num) values
(2020123001,1),
(2020123001,2),
(2020123001,3),
(2020123001,4),
(2020123001,5),
(2020123001,6),
(2020123002,1),
(2020123002,2),
(2020123002,3),
(2020123002,4),
(2020123002,5),
(2020160001,1),
(2020160001,2),
(2020160001,3),
(2020160001,4),
(2020160001,5),
(2020160001,6),
(2020160002,1),
(2020160002,2),
(2020160002,3),
(2020160002,4),
(2020160002,5),
(2020160002,6),
(2020456001,1),
(2020456001,2),
(2020456001,3),
(2020456001,4),
(2020456001,5);