#12-2. 12일차 퀴즈에 대한 문제풀이

1. student, exam_01 테이블을 조인하여
  학번, 이름, 학년, 주민번호, 시험성적 정보를 갖는 student_exam 테이블 생성

[내 답안]

select *
  from student;

select *
  from exam_01;

desc student;
desc exam_01;

-- 테이블 조인  
select s.studno, s.name, s.grade, s.jumin, e.total
  from student s, exam_01 e
 where s.STUDNO = e.STUDNO;

-- 테이블 생성
create table student_exam(
studno number       primary key,
name   varchar2(10) not null,
grade  number,
jumin char(13) not null,
total number);


-- 적재 진행 후 확인
select *
  from student_exam;

 

 

 

[문제풀이]

 

create table student_exam
as
select s.studno, s.name, s.grade, s.jumin, e.total
  from student s, exam_01 e
 where s.STUDNO = e.STUDNO;

select * from student_exam;

desc student_exam;

 

 

2. 위 테이블에 다음의 제약조건 추가
    컬럼     제약조건       제약조건명                설명
   studno   pk          std_exam_studno_pk
   name     not null    std_exam_name_nn
   grade    check       std_exam_grade_ck     1,2,3,4학년 중 하나만 입력가능
   jumin    uk          std_exam_jumin_uk

[내 답안]

 

alter table student_exam add constraint std_exam_studno_pk primary key(studno);
alter table student_exam add constraint std_exam_name_nn not null;
alter table student_exam add constraint std_exam_grade_ck check(grade in (1,2,3,4));
alter table student_exam add constraint std_exam_jumin_uk unique(jumin);



select C1.table_name,       -- 테이블명
       C1.constraint_name,  -- 제약조건명
       C2.column_name,      -- 컬럼명
       C1.constraint_type,  -- 제약조건타입(P:PK, U:UK, R:FOREIGN KEY, C:CHECK OR NOT NULL)
       C1.search_condition, -- CHECK 제약의 형태 또는 NOT NULL 여부
       C1.r_constraint_name -- 참조키(부모)의 제약조건명***
  from user_constraints C1,
       user_cons_columns C2
 where C1.constraint_name = C2.constraint_name
   and C1.table_name in ('STUDENT_EXAM')
 order by C1.table_name;

select *
  from user_cons_columns;

 

 

[문제풀이]

 

alter table student_exam add constraint std_exam_studno_pk primary key(studno);
alter table student_exam add constraint std_exam_grade_ck check(grade in (1,2,3,4));
alter table student_exam add constraint std_exam_jumin_uk unique(jumin);

-- ** 제약조건 확인
select c1.table_name,
       c1.constraint_name,
       c1.constraint_type,
       c1.search_condition,
       c1.r_constraint_name,
       c2.column_name
  from user_constraints c1,
       user_cons_columns c2
 where c1.constraint_name = C2.constraint_name
   and C1.table_name = 'STUDENT_EXAM';
  
-- ** not null 제약조건 삭제 후 재생성

alter table STUDENT_EXAM drop constraint SYS_C0011226;
alter table STUDENT_EXAM modify name constraint std_exam_name_nn not null;

3. 위 테이블에 새로운 컬럼 생성
컬럼명      타입       기본값
edate    date      sysdate

[내 답안]

 

alter table student_exam add (edate date default sysdate);

 

[문제풀이]

 

alter table student_exam add (edate date default sysdate);  -- 컬럼 추가 시 기본값 선언하는 경우
select *
  from student_exam;                                        -- 기존 데이터의 새로운 컬럼값이 모두 기본값으로 설정됨

4. 위 테이블에 새로운 학생 데이터 입력(단, edate 입력 생략)
  9999, 홍길동, 4학년, 9812121111111, 90

[내 답안]

 

desc student_exam;

insert into student_exam(studno, name, grade, jumin, total) values(9999, '홍길동', 4, '9812121111111', 90);
select * 

  from student_exam;

 

 

[문제풀이]

 

insert into student_exam(studno, name, grade, jumin, total) values(9999, '홍길동', 4, '9812121111111', 90);
select * 
  from student_exam;

5. 위 테이블에 새로운 학생 데이터 입력(단, edate null 입력)
  9998, 김길동, 3학년, 9012122111111, 80

[내 답안]

 

insert into student_exam values(9998, '김길동', 3, '9012122111111', 80, NULL);
select * 
  from student_exam;
  
commit;

 

[문제풀이]

 

insert into student_exam values(9998, '김길동', 3, '9012122111111', 80, NULL);
select * 
  from student_exam;

6. 모든 학생의 주민번호를 아래와 같은 형태로 변경(에러가 발생하는 경우 원인파악 후 해결)
   9812121111111 -> 981212-1111111

[내 답안]

 

alter table student_exam modify (jumin char(15));  

select *
  from student_exam;
  
select substr(jumin,1,7) || '-' || substr(jumin, 8, 7)
  from student_exam;

update student_exam s1
   set jumin = (select substr(jumin,1,7) || '-' || substr(jumin, 8, 7)
                  from student_exam
                 where jumin = s1.jumin);
                 
commit;

 

 

[문제풀이]

 

update student_exam
   set jumin = substr(jumin, 1, 6)||'-'||substr(jumin, 7);
   
select jumin, substr(jumin, 1, 6)||'-'||substr(jumin, 7)
  from student_exam;

 

-- 길이조정
-- 방법1) char(14)로 변경

update student_exam
   set jumin = substr(jumin, 1, 6)||'-'||substr(jumin, 7); -- error
   
update student_exam
   set jumin = substr(jumin, 1, 6)||'-'||substr(jumin, 7,7); -- 정상
   
-- 방법2) varchar2(14)로 변경

 

 

* char 변환시 공백이 생기기 때문에 내 답안의 경우 한 자릿수씩 밀린다

7. 각 학년별로 평균성적보다 낮은 학생 데이터 삭제

[내 답안]

 

select *
  from student_exam;
  
select grade, avg(total)
  from student_exam
 group by grade;
 
delete student_exam e1
 where e1.total < (select avg(total)
                     from student_exam
                    where grade = e1.grade);
  

 

[문제풀이]

 

delete student_exam s1
 where e1.total < (select avg(total)
                     from student_exam
                    where grade = s1.grade);
  

8. 모든 학생의 성적을 전체 평균으로 수정

[내 답안]

 

select *
  from student_exam;
    
select avg(total)
  from student_exam;

desc student_exam;

alter table student_exam drop constraint ;

update student_exam
  set grade = (select avg(total)
                 from student_exam);
                 
commit;

 

 

[문제풀이]

 

update student_exam
  set grade = (select avg(total)
                 from student_exam);
                               

 

 

9. 저장

[내 답안]

 

commit;

 

[문제풀이]

 

commit;