SQL 7W

use univdb;

create table 학생21
(학번 char(4) not null,
이름 varchar(20) not null,
주소 varchar(50) null default '미정',
학년 int not null,
나이 int null,
성별 char(1) not null,
휴대폰번호 char(13) null,
소속학과 varchar(20) null,
primary key (학번),
unique (휴대폰번호));

create table 과목21
(과목번호 char(4) not null primary key,
이름 varchar(20) not null,
강의실 char(5) not null,
개설학과 varchar(20) not null,
시수 int not null);

create table 수강21
(학번 char(6) not null,
과목번호 char(4) not null,
신청날짜 date not null,
중간성적 int null default 0,
기말성적 int null default 0,
평가학점 char(1) null,
primary key(학번, 과목번호),
foreign key(학번) references 학생21(학번),
foreign key(과목번호) references 과목21(과목번호));

select * from 학생21;
select * from 수강21;
insert into 학생21 select * from 학생;
insert into 수강21 select * from 수강;

alter table 학생21
add 등록날짜 datetime not null default '2019-12-30';

-- 취미열을 입력 null 기본값 로봇조립으로 지정
alter table 학생21
add 취미 varchar(50) null default '로봇조립';

alter table 학생21
drop column 등록날짜;

alter table 학생21
drop column 취미;

-- 봉사활동 점수 50을 적용시키시오.
alter table 학생21
add 봉사활동점수 int not null default 50;
-- 소속학과가 컴퓨터인 학생들은 10점 감점하시오.
update 학생21 set 봉사활동점수 = 봉사활동점수 - 10 where 소속학과 = "컴퓨터";
-- 성씨가 '이'씨 사람들에게 학년을 1씩 증가시키시오.
update 학생21 set 학년 = 학년 + 1 where 이름 like "이%";

create user 'osy2'@'%' identified by '1234';
grant update, select on *.* to 'osy2'@'%' with grant option;

show grants;
show grants for 'osy2'@'%';

drop user 'osy2'@'%';

create view v1_고학년학생(학생이름, 나이, 성, 학년)
as select 이름, 나이, 성별, 학년 from 학생 where 학년 >= 3 and 학년 <=4;

select * from v1_고학년학생;

create view v2_과목수강현황(과목번호, 강의실, 수강인원수)
as select 과목.과목번호, 강의실, count(과목.과목번호)
from 과목 join 수강 on 과목.과목번호 = 수강.과목번호 group by 과목.과목번호;

select * from v2_과목수강현황;

create view v3_고학년여학생
as select * from v1_고학년학생 where 성 = '여';

select * from v3_고학년여학생;

create index idx_수강21 on 수강21(학번, 과목번호);
show index from 수강21;

insert into 과목21(과목번호, 이름, 강의실 ,개설학과, 시수)
values ('c111','database','A-123','산업공학',3);

create unique index idx_과목21 on 과목(이름 asc);
show index from 과목;

drop index idx_수강21 on 수강21;
alter table 과목 drop index idx_과목21;