[Database] 14장 SQL 연습문제4

 

전에 실습하여 만든 학사관리시스템을 이용하여 문제를 풀이한다.

하단의 테이블 생성 후 데이터 삽입을 참고하여 세팅한다.

 

https://forest-of-coding.tistory.com/35

[Database] 6.5.3장 모델링 테스트2

 

 

 

추가 생성 테이블 & 데이터

 

-- 학사관리시스템에 아래 학점관리 테이블을 생성.
create table hakjum
    (grade char(2 byte) not null enable,
    min_point number(3) not null enable,
    max_point number(3) not null enable,
    constraint xpk_hakjum primary key (grade));

-- 학점관리 테이블에 데이터 입력
insert into hakjum(grade, min_point, max_point) values('A+', 96, 100);
insert into hakjum(grade, min_point, max_point) values('A0', 90, 95);
insert into hakjum(grade, min_point, max_point) values('B+', 85, 89);
insert into hakjum(grade, min_point, max_point) values('B0', 80, 84);
insert into hakjum(grade, min_point, max_point) values('C+', 75, 79);
insert into hakjum(grade, min_point, max_point) values('C0', 70, 74);
insert into hakjum(grade, min_point, max_point) values('D+', 65, 69);
insert into hakjum(grade, min_point, max_point) values('D0', 60, 64);
insert into hakjum(grade, min_point, max_point) values('F', 0, 59);

 

 

 

문제

 

1. 학사관리시스템에서 수강학생들의 성적을 학점으로 나타내시오.
학번, 학생명, 과목코드, 과목명, 성적, 학점

2. 학사관리시스템에서　과목별　평균성적을 구하시오.
과목코드, 과목명, 평균성적

 

 

 

1번 문제

 

-- 1. 학사관리시스템에서 수강학생들의 성적을 학점으로 나타내시오.
-- 학번, 학생명, 과목코드, 과목명, 성적, 학점
select * from student;
select * from class;
select * from subject;

select student.std_no 학번, student.std_name 학생명, class.sub_no 과목코드, subject.sub_name 과목명, class.cls_score 성적,   
    case when class.cls_score >= 95 then 'A+'
        when class.cls_score >= 90 then 'A0'
        when class.cls_score >= 85 then 'B+'
        when class.cls_score >= 80 then 'B0'
        when class.cls_score >= 75 then 'C+'
        when class.cls_score >= 70 then 'C0'
        when class.cls_score >= 65 then 'D+'
        when class.cls_score >= 60 then 'D0'
        else 'F'
    end 학점
    from student, class, subject
    where student.std_no = class.std_no
    and subject.sub_no = class.sub_no
    order by 6;

select student.std_no 학번, student.std_name 학생명, class.sub_no 과목코드, subject.sub_name 과목명, class.cls_score 성적, hakjum.grade 학점
    from student, class, subject, hakjum
    where student.std_no = class.std_no
    and subject.sub_no = class.sub_no
    and (class.cls_score between hakjum.min_point and hakjum.max_point)
    order by 6;

 

결과 화면1

 

 

 

2번 문제

 

-- 2. 학사관리시스템에서　과목별　평균성적을 구하시오.
-- 과목코드, 과목명, 평균성적
select * from class;
select * from subject;
select * from student;

select class.sub_no 과목코드, subject.sub_name 과목명, round(avg(class.cls_score),0) 평균성적
    from class, subject, student, hakjum
    where class.sub_no = subject.sub_no
    and student.std_no = class.std_no
    group by subject.sub_no, subject.sub_name, class.sub_no;

 

결과 화면2