-- STUDENT_INFO 테이블 생성 및 데이터 삽입
CREATE TABLE SCHOOL.STUDENT_INFO (
student_id VARCHAR2(10) PRIMARY KEY,
student_name VARCHAR2(100),
birthdate DATE,
gender CHAR(1)
);
INSERT INTO SCHOOL.STUDENT_INFO (student_id, student_name, birthdate, gender)
VALUES ('1', '한승은', DATE '2001-01-01', 'F');
INSERT INTO SCHOOL.STUDENT_INFO (student_id, student_name, birthdate, gender)
VALUES ('2', '한우진', DATE '2002-02-02', 'F');
INSERT INTO SCHOOL.STUDENT_INFO (student_id, student_name, birthdate, gender)
VALUES ('3', '박민재', DATE '2001-03-03', 'M');
INSERT INTO SCHOOL.STUDENT_INFO (student_id, student_name, birthdate, gender)
VALUES ('4', '이채원', DATE '2002-04-04', 'F');
INSERT INTO SCHOOL.STUDENT_INFO (student_id, student_name, birthdate, gender)
VALUES ('5', '박정현', DATE '2000-05-05', 'M');
INSERT INTO SCHOOL.STUDENT_INFO (student_id, student_name, birthdate, gender)
VALUES ('6', '여동한', DATE '2003-06-06', 'F');
INSERT INTO SCHOOL.STUDENT_INFO (student_id, student_name, birthdate, gender)
VALUES ('7', '박승섭', DATE '2001-07-07', 'M');
INSERT INTO SCHOOL.STUDENT_INFO (student_id, student_name, birthdate, gender)
VALUES ('8', '전현규', DATE '2002-08-08', 'F');
INSERT INTO SCHOOL.STUDENT_INFO (student_id, student_name, birthdate, gender)
VALUES ('9', '박우진', DATE '2000-09-09', 'M');
INSERT INTO SCHOOL.STUDENT_INFO (student_id, student_name, birthdate, gender)
VALUES ('10', '신택훈', DATE '2003-10-10', 'F');
-- COURSE_INFO 테이블 생성 및 데이터 삽입
CREATE TABLE SCHOOL.COURSE_INFO (
lecture_code VARCHAR2(100) PRIMARY KEY,
lecture_name VARCHAR2(100),
lecture_year NUMBER(35),
lecture_semester NUMBER(35)
);
INSERT INTO SCHOOL.COURSE_INFO (lecture_code, lecture_name, lecture_year, lecture_semester)
VALUES ('데이터베이스', '데이터베이스', 2022, 2);
INSERT INTO SCHOOL.COURSE_INFO (lecture_code, lecture_name, lecture_year, lecture_semester)
VALUES ('HTML&CSS', 'HTML&CSS', 2022, 2);
INSERT INTO SCHOOL.COURSE_INFO (lecture_code, lecture_name, lecture_year, lecture_semester)
VALUES ('Javascript', 'Javascript', 2023, 1);
INSERT INTO SCHOOL.COURSE_INFO (lecture_code, lecture_name, lecture_year, lecture_semester)
VALUES ('JAVA Spring', 'JAVA Spring', 2023, 1);
INSERT INTO SCHOOL.COURSE_INFO (lecture_code, lecture_name, lecture_year, lecture_semester)
VALUES ('JSP&Servlet', 'JSP&Servlet', 2023, 2);
INSERT INTO SCHOOL.COURSE_INFO (lecture_code, lecture_name, lecture_year, lecture_semester)
VALUES ('Flutter', 'Flutter', 2024, 1);
-- GRADE_CODE 테이블 생성 및 데이터 삽입
CREATE TABLE SCHOOL.GRADE_CODE (
code CHAR(4) PRIMARY KEY,
code_name VARCHAR2(50),
percentage NUMBER(3) CHECK (percentage >= 0 AND percentage <= 100)
);
INSERT INTO SCHOOL.GRADE_CODE (code, code_name, percentage)
VALUES ('MT', '중간고사', 40);
INSERT INTO SCHOOL.GRADE_CODE (code, code_name, percentage)
VALUES ('LT', '기말고사', 40);
INSERT INTO SCHOOL.GRADE_CODE (code, code_name, percentage)
VALUES ('RP', '과제', 20);
CREATE TABLE SCHOOL.GRADES (
course_code VARCHAR2(100),
student_id VARCHAR2(10),
grade_code CHAR(4),
grade NUMBER(3) CHECK (grade >= 0 AND grade <= 100),
grade_timestamp TIMESTAMP,
PRIMARY KEY (course_code, student_id, grade_code),
FOREIGN KEY (course_code) REFERENCES SCHOOL.COURSE_INFO(lecture_code),
FOREIGN KEY (student_id) REFERENCES SCHOOL.STUDENT_INFO(student_id),
FOREIGN KEY (grade_code) REFERENCES SCHOOL.GRADE_CODE(code)
);
//////////////////////////////////////////////////////////////////////////////////////////////////
INSERT INTO SCHOOL.GRADES (course_code, student_id, grade_code, grade, grade_timestamp)
SELECT
'데이터베이스' AS course_code, -- 강의코드
student_id, -- 학번
'MT' AS grade_code, -- 평가코드 (중간고사)
ROUND(DBMS_RANDOM.VALUE() * 100, 2) AS grade, -- 임의의 성적
SYSTIMESTAMP AS grade_timestamp -- 성적입력일시
FROM
SCHOOL.STUDENT_INFO;
INSERT INTO SCHOOL.GRADES (course_code, student_id, grade_code, grade, grade_timestamp)
SELECT
'HTML&CSS' AS course_code, -- 강의코드
student_id, -- 학번
'MT' AS grade_code, -- 평가코드 (중간고사)
ROUND(DBMS_RANDOM.VALUE() * 100, 2) AS grade, -- 임의의 성적
SYSTIMESTAMP AS grade_timestamp -- 성적입력일시
FROM
SCHOOL.STUDENT_INFO;
INSERT INTO SCHOOL.GRADES (course_code, student_id, grade_code, grade, grade_timestamp)
SELECT
'Javascript' AS course_code, -- 강의코드
student_id, -- 학번
'MT' AS grade_code, -- 평가코드 (중간고사)
ROUND(DBMS_RANDOM.VALUE() * 100, 2) AS grade, -- 임의의 성적
SYSTIMESTAMP AS grade_timestamp -- 성적입력일시
FROM
SCHOOL.STUDENT_INFO;
INSERT INTO SCHOOL.GRADES (course_code, student_id, grade_code, grade, grade_timestamp)
SELECT
'JAVA Spring' AS course_code, -- 강의코드
student_id, -- 학번
'MT' AS grade_code, -- 평가코드 (중간고사)
ROUND(DBMS_RANDOM.VALUE() * 100, 2) AS grade, -- 임의의 성적
SYSTIMESTAMP AS grade_timestamp -- 성적입력일시
FROM
SCHOOL.STUDENT_INFO;
INSERT INTO SCHOOL.GRADES (course_code, student_id, grade_code, grade, grade_timestamp)
SELECT
'JSP&Servlet' AS course_code, -- 강의코드
student_id, -- 학번
'MT' AS grade_code, -- 평가코드 (중간고사)
ROUND(DBMS_RANDOM.VALUE() * 100, 2) AS grade, -- 임의의 성적
SYSTIMESTAMP AS grade_timestamp -- 성적입력일시
FROM
SCHOOL.STUDENT_INFO;
INSERT INTO SCHOOL.GRADES (course_code, student_id, grade_code, grade, grade_timestamp)
SELECT
'Flutter' AS course_code, -- 강의코드
student_id, -- 학번
'MT' AS grade_code, -- 평가코드 (중간고사)
ROUND(DBMS_RANDOM.VALUE() * 100, 2) AS grade, -- 임의의 성적
SYSTIMESTAMP AS grade_timestamp -- 성적입력일시
FROM
SCHOOL.STUDENT_INFO;
//////////////////////////////////////////////////////////////////////////////////////////////////
INSERT INTO SCHOOL.GRADES (course_code, student_id, grade_code, grade, grade_timestamp)
SELECT
'데이터베이스' AS course_code, -- 강의코드
student_id, -- 학번
'RP' AS grade_code, -- 평가코드 (중간고사)
ROUND(DBMS_RANDOM.VALUE() * 100, 2) AS grade, -- 임의의 성적
SYSTIMESTAMP AS grade_timestamp -- 성적입력일시
FROM
SCHOOL.STUDENT_INFO;
INSERT INTO SCHOOL.GRADES (course_code, student_id, grade_code, grade, grade_timestamp)
SELECT
'HTML&CSS' AS course_code, -- 강의코드
student_id, -- 학번
'RP' AS grade_code, -- 평가코드 (중간고사)
ROUND(DBMS_RANDOM.VALUE() * 100, 2) AS grade, -- 임의의 성적
SYSTIMESTAMP AS grade_timestamp -- 성적입력일시
FROM
SCHOOL.STUDENT_INFO;
INSERT INTO SCHOOL.GRADES (course_code, student_id, grade_code, grade, grade_timestamp)
SELECT
'Javascript' AS course_code, -- 강의코드
student_id, -- 학번
'RP' AS grade_code, -- 평가코드 (중간고사)
ROUND(DBMS_RANDOM.VALUE() * 100, 2) AS grade, -- 임의의 성적
SYSTIMESTAMP AS grade_timestamp -- 성적입력일시
FROM
SCHOOL.STUDENT_INFO;
INSERT INTO SCHOOL.GRADES (course_code, student_id, grade_code, grade, grade_timestamp)
SELECT
'JAVA Spring' AS course_code, -- 강의코드
student_id, -- 학번
'RP' AS grade_code, -- 평가코드 (중간고사)
ROUND(DBMS_RANDOM.VALUE() * 100, 2) AS grade, -- 임의의 성적
SYSTIMESTAMP AS grade_timestamp -- 성적입력일시
FROM
SCHOOL.STUDENT_INFO;
INSERT INTO SCHOOL.GRADES (course_code, student_id, grade_code, grade, grade_timestamp)
SELECT
'JSP&Servlet' AS course_code, -- 강의코드
student_id, -- 학번
'RP ' AS grade_code, -- 평가코드 (중간고사)
ROUND(DBMS_RANDOM.VALUE() * 100, 2) AS grade, -- 임의의 성적
SYSTIMESTAMP AS grade_timestamp -- 성적입력일시
FROM
SCHOOL.STUDENT_INFO;
INSERT INTO SCHOOL.GRADES (course_code, student_id, grade_code, grade, grade_timestamp)
SELECT
'Flutter' AS course_code, -- 강의코드
student_id, -- 학번
'RP' AS grade_code, -- 평가코드 (중간고사)
ROUND(DBMS_RANDOM.VALUE() * 100, 2) AS grade, -- 임의의 성적
SYSTIMESTAMP AS grade_timestamp -- 성적입력일시
FROM
SCHOOL.STUDENT_INFO;
CREATE OR REPLACE VIEW SCHOOL.GRADE_CALCULATION_VIEW AS
SELECT
S.student_id,
S.student_name,
G.course_code,
C.lecture_name,
M.grade AS 중간고사,
F.grade AS 기말고사,
R.grade AS 과제,
ROUND((COALESCE(M.grade, 0) * (SELECT percentage FROM SCHOOL.GRADE_CODE WHERE code = 'MT') / 100
+ COALESCE(F.grade, 0) * (SELECT percentage FROM SCHOOL.GRADE_CODE WHERE code = 'LT') / 100
+ COALESCE(R.grade, 0) * (SELECT percentage FROM SCHOOL.GRADE_CODE WHERE code = 'RP') / 100), 2) AS 평점,
ROUND((COALESCE(M.grade, 0) + COALESCE(F.grade, 0) + COALESCE(R.grade, 0)), 2) AS 총점,
CASE
WHEN ROUND((COALESCE(M.grade, 0) * (SELECT percentage FROM SCHOOL.GRADE_CODE WHERE code = 'MT') / 100
+ COALESCE(F.grade, 0) * (SELECT percentage FROM SCHOOL.GRADE_CODE WHERE code = 'LT') / 100
+ COALESCE(R.grade, 0) * (SELECT percentage FROM SCHOOL.GRADE_CODE WHERE code = 'RP') / 100), 2) >= 90 THEN 'A'
WHEN ROUND((COALESCE(M.grade, 0) * (SELECT percentage FROM SCHOOL.GRADE_CODE WHERE code = 'MT') / 100
+ COALESCE(F.grade, 0) * (SELECT percentage FROM SCHOOL.GRADE_CODE WHERE code = 'LT') / 100
+ COALESCE(R.grade, 0) * (SELECT percentage FROM SCHOOL.GRADE_CODE WHERE code = 'RP') / 100), 2) >= 80 THEN 'B'
WHEN ROUND((COALESCE(M.grade, 0) * (SELECT percentage FROM SCHOOL.GRADE_CODE WHERE code = 'MT') / 100
+ COALESCE(F.grade, 0) * (SELECT percentage FROM SCHOOL.GRADE_CODE WHERE code = 'LT') / 100
+ COALESCE(R.grade, 0) * (SELECT percentage FROM SCHOOL.GRADE_CODE WHERE code = 'RP') / 100), 2) >= 70 THEN 'C'
WHEN ROUND((COALESCE(M.grade, 0) * (SELECT percentage FROM SCHOOL.GRADE_CODE WHERE code = 'MT') / 100
+ COALESCE(F.grade, 0) * (SELECT percentage FROM SCHOOL.GRADE_CODE WHERE code = 'LT') / 100
+ COALESCE(R.grade, 0) * (SELECT percentage FROM SCHOOL.GRADE_CODE WHERE code = 'RP') / 100), 2) >= 60 THEN 'D'
ELSE 'F'
END AS 등급
FROM
SCHOOL.STUDENT_INFO S
JOIN
SCHOOL.GRADES G ON S.student_id = G.student_id
JOIN
SCHOOL.COURSE_INFO C ON G.course_code = C.lecture_code
LEFT JOIN
SCHOOL.GRADES M ON G.student_id = M.student_id AND G.course_code = M.course_code AND M.grade_code = 'MT'
LEFT JOIN
SCHOOL.GRADES F ON G.student_id = F.student_id AND G.course_code = F.course_code AND F.grade_code = 'LT'
LEFT JOIN
SCHOOL.GRADES R ON G.student_id = R.student_id AND G.course_code = R.course_code AND R.grade_code = 'RP';
SELECT * FROM SCHOOL.GRADE_CALCULATION_VIEW;