데이터입출력구현-(학사ERD, 스토어드프로시저)

min seung moon·2021년 3월 29일
1

Oracle

목록 보기
20/23

학사 정보를 관리하기






1. 요구 사항에서 명사에 주목하여 엔티티를 식별하고 엔티티의 특성을 나타내는 관련 속성을 구별한 엔티티 정의서를 작성하시오

2. 요구사항에서 동시에 주목하여 엔티티들을 연결하는 관계를 식별하고 존재성을 구별하여 관계 정의서를 작성하시오

3. 요구사항과 엔티티/관계 정의서를 기준으로 ERD를 작성하시오

4. 요구사항과 엔티티/관계 정의서 및 ERD를 기준으로 테이블 명세서를 작성하시오


5. 테이블 명세서에 따른 테이블 생성 SQL문을 작성하시오

CREATE TABLE 학과 (
    번호 NUMBER NOT NULL PRIMARY KEY,
    이름 NVARCHAR2(10) NOT NULL
);

CREATE TABLE 교수 (
    번호 NUMBER NOT NULL PRIMARY KEY,
    이름 NVARCHAR2(10) NOT NULL,
    학과번호 NUMBER NOT NULL,
    FOREIGN KEY(학과번호) REFERENCES 학과(번호)
);

CREATE TABLE 학생(
    번호 NUMBER NOT NULL PRIMARY KEY,
    이름 NVARCHAR2(10) NOT NULL,
    주소 NVARCHAR2(50),
    학년 NUMBER,
    키 NUMBER,
    몸무게 NUMBER,
    상태 NVARCHAR2(4),
    입력일자 DATE,
    학과번호 NUMBER NOT NULL,
    FOREIGN KEY(학과번호) REFERENCES 학과(번호)
);

CREATE TABLE 과목(
    번호 NUMBER NOT NULL PRIMARY KEY,
    이름 NVARCHAR2(11) NOT NULL,
    학점 NUMBER,
    교수번호 NUMBER NOT NULL,
    FOREIGN KEY(교수번호) REFERENCES 교수(번호)
);

CREATE TABLE 수강내역(
    학생번호 NUMBER NOT NULL,
    과목번호 NUMBER NOT NULL,
    점수 NUMBER(4,1),
    등급 NVARCHAR2(1),
     CONSTRAINT 수강내역PK  PRIMARY KEY(학생번호, 과목번호),
    FOREIGN KEY(학생번호) REFERENCES 학생(번호),
    FOREIGN KEY(과목번호) REFERENCES 과목(번호)
);

CREATE SEQUENCE SEQ;
INSERT INTO 학과 VALUES(SEQ.NEXTVAL, '소프트웨어공학과');
INSERT INTO 학과 VALUES(SEQ.NEXTVAL, '컴퓨터공학과');
INSERT INTO 학과 VALUES(SEQ.NEXTVAL, '경영정보학과');
INSERT INTO 학과 VALUES(SEQ.NEXTVAL, '경영학과');
INSERT INTO 학과 VALUES(SEQ.NEXTVAL, '연극영화학과');
INSERT INTO 학과 VALUES(SEQ.NEXTVAL, '철학과');
SELECT * FROM 학과;

DROP SEQUENCE SEQ;
CREATE SEQUENCE SEQ;
INSERT INTO 교수 VALUES(SEQ.nextval, '이해진', 1);
INSERT INTO 교수 VALUES(SEQ.nextval, '김정주', 1);
INSERT INTO 교수 VALUES(SEQ.nextval, '김이숙', 1);
INSERT INTO 교수 VALUES(SEQ.nextval, '이석호', 2);
INSERT INTO 교수 VALUES(SEQ.nextval, '김사부', 3);
INSERT INTO 교수 VALUES(SEQ.nextval, '장미희', 5);
INSERT INTO 교수 VALUES(SEQ.nextval, '백남영', 5);
INSERT INTO 교수 VALUES(SEQ.nextval, '김현이', 5);
INSERT INTO 교수 VALUES(SEQ.nextval, '이사부', 5);
SELECT * FROM 교수;

DROP SEQUENCE SEQ;
CREATE SEQUENCE SEQ;
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, '데이터베이스관리', 3, 3);
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, '데이터베이스프로그래밍', 3, 3);
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, 'SQL활용', 3, 4);
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, '데이터베이스', 4, 5);
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, '인터넷마케팅', 3, 5);
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, '연기실습', 2, 7);
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, '영화제작실습', 2, 8);
INSERT INTO 과목 VALUES(SEQ.NEXTVAL, '영화마케팅', 1, 9);
SELECT * FROM 과목;

DROP SEQUENCE SEQ;
CREATE SEQUENCE SEQ;
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(SEQ.nextval, '김이향', '인천남동구', 4, 166, 56, '재학', '2005-03-01',5);
INSERT INTO 학생(번호,이름,주소,학년,키,상태,입력일자,학과번호)
VALUES(SEQ.nextval, '박보검', '인천부평구', 4, 168, '재학', '2010-03-01',6);
INSERT INTO 학생(번호,이름,주소,학년,상태,입력일자,학과번호)
VALUES(SEQ.nextval, '이세영', '서울구로구', 4, '재학', '2013-03-01',4);
INSERT INTO 학생(번호,이름,주소,학년,상태,입력일자,학과번호)
VALUES(SEQ.nextval, '문주원', '경기부천시', 3, '재학', '2014-02-13',3);
INSERT INTO 학생(번호,이름,주소,학년,상태,입력일자,학과번호)
VALUES(SEQ.nextval, '성춘향', '서울영등포구', 3, '재학', '2014-02-13',5);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(SEQ.nextval, '박지은', '서울양천구', 2, 183, 65, '재학', '2015-02-14',3);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(SEQ.nextval, '안칠현', '서울금천구', 1, 178, 65, '재학', '2016-02-19',1);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(SEQ.nextval, '김태희', '서울구로구', 1, 165, 45, '재학', '2016-02-19',3);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(SEQ.nextval, '채영', '서울구로구', 1, 172, 47, '재학', '2016-02-19',2);
INSERT INTO 학생(번호,이름,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(SEQ.nextval, '박수애', 1, 168, 46, '재학', '2016-02-19',2);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(SEQ.nextval, '영란', '서울강남구', 1, 162, 43, '재학', '2016-02-19',5);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(SEQ.nextval, '윤호', '서울강남구', 1, 184, 66, '휴학', '2016-08-10',1);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(SEQ.nextval, '보아', '서울구로구', 1, 162, 45, '재학', '2016-02-19',1);
INSERT INTO 학생(번호,이름,주소,학년,키,몸무게,상태,입력일자,학과번호)
VALUES(SEQ.nextval, '문근영', '서울강남구', 1, 165, 45, '재학', '2016-02-19',3);
SELECT * FROM 학생;

INSERT INTO 수강내역 VALUES(7, 1, 84.5, 'B');
INSERT INTO 수강내역 VALUES(12, 1, 50, 'F');
INSERT INTO 수강내역 VALUES(13, 1, 90, 'A');
INSERT INTO 수강내역 VALUES(7, 2, 80, 'B');
INSERT INTO 수강내역 VALUES(13, 2, 94.5, 'A');
INSERT INTO 수강내역 VALUES(9, 3, 90, 'A');
INSERT INTO 수강내역 VALUES(10, 3, 70, 'C');
INSERT INTO 수강내역 VALUES(4, 4, 90, 'A');
INSERT INTO 수강내역 VALUES(6, 4, 55, 'F');
INSERT INTO 수강내역 VALUES(8, 4, 85, 'B');
INSERT INTO 수강내역 VALUES(14, 4, 95, 'A');
INSERT INTO 수강내역 VALUES(4, 5, 70, 'C');
INSERT INTO 수강내역 VALUES(6, 5, 95, 'A');
INSERT INTO 수강내역 VALUES(8, 5, 90, 'A');
INSERT INTO 수강내역 VALUES(14, 5, 95, 'A');
INSERT INTO 수강내역 VALUES(6, 8, 90, 'A');
INSERT INTO 수강내역 VALUES(8, 8, 80, 'B');
INSERT INTO 수강내역 VALUES(14, 8, 90, 'A');
INSERT INTO 수강내역 VALUES(1, 6, 85, 'B');
INSERT INTO 수강내역 VALUES(5, 6, 80, 'B');
INSERT INTO 수강내역 VALUES(11, 6, 78.5, 'C');
INSERT INTO 수강내역 VALUES(1, 7, 95, 'A');
INSERT INTO 수강내역 VALUES(5, 7, 85, 'B');
INSERT INTO 수강내역 VALUES(1, 8, 100, 'A');
SELECT * FROM 수강내역;

6. 프로시저 수행 시 학년을 입력받아 학생의 이름, 학년, 키, 몸무게 데이터를 검색하라, 단 키 내림차순으로 정렬하고 같은 키는 몸무게 오름차순으로 정렬한다(실행 예 : exec Q21 2)

SET SERVEROUTPUT ON;

create or replace procedure Q21(
    pi_studentID in 학생.학년%TYPE
)
as
begin
    for student in (
    select 이름, 학년, 키, 몸무게  from 학생
    where 학년=pi_studentID order by 키 desc, 몸무게 asc
    )
    Loop
        DBMS_OUTPUT.PUT_LINE('이름 ' || student.이름 || ' 학년 ' || student.학년 ||
        ' 키 ' || student.키 || ' 몸무게 ' || student.몸무게);
    end loop;
end;
execute Q21(2);

execute Q21(1);

7. 프로시저 수행 시 입력된 학생과 학년이 동일한 모든 학생의 이름과 키, 몸무게를 검색하라(실행 예 : exec Q23 '문주원')

create or replace procedure Q23(
    pi_studentname in 학생.이름%type
)
as
begin
    for student in (
    select 이름, 키, 몸무게 from 학생 where 학년 = (select 학년 from 학생 where 이름 = pi_studentname)
    )
    Loop
        DBMS_OUTPUT.PUT_LINE('이름 ' || student.이름 || ' 키 ' || student.키 ||
        ' 몸무게 ' || student.몸무게);
    end loop;
end; 
execute Q23('문주원');

execute Q23('윤호');

8. 프로시저 수행 시 학생의 이름을 입력받아 그 학생과 학년은 같고 키가 큰 학생의 이름, 학년, 키를 검색하라(실행 예 : exec Q25 '김태희')

create or replace procedure Q25(
    pi_studentname in 학생.이름%type
)
as
begin
    for student in (
    select 이름, 학년, 키 from 학생 where 학년 = (select 학년 from 학생
    where 이름 = pi_studentname) and 키 > (select 키 from 학생 where 이름 = pi_studentname)
    )
    Loop
        DBMS_OUTPUT.PUT_LINE('이름 ' || student.이름 || ' 학년 ' || student.학년 ||
        ' 키 ' || student.키);
    end loop;
end; 

execute Q25('김태희');

9. 프로시저 수행 시 입력된 학생의 학생번호, 이름, 소속학과명을 검색하라(실행 예 : exec Q36 '김이향')

create or replace procedure Q36(
    pi_studentname in 학생.이름%type
)
as
    학생번호 number;
    학생이름 nvarchar2(10);
    학과이름 nvarchar2(10);
begin
    select 학생.번호, 학생.이름, 학과.이름 into 학생번호, 학생이름, 학과이름 from 학생 
    inner join 학과
        on 학생.학과번호 = 학과.번호
    where 학생.이름 = pi_studentname;
    DBMS_OUTPUT.PUT_LINE('학생 번호 ' || 학생번호 || ' 학생 이름 ' || 학생이름 || ' 학과명 ' || 학과이름);
end;
    
execute Q36('김이향');

10. 프로시저 수행 시 학생의 키를 입력받아 그 이상의 키를 가진 학생의 번호, 이름, 키, 소속학과명을 검색하라(실행 예 : exec Q37 180)

create or replace procedure Q37(
    pi_studentheight in 학생.키%type
)
as
begin
    for student in (
    select 학생.번호, 학생.이름 as 학생이름, 학생.키, 학과.이름 as 학과이름 from 학생
    inner join 학과
        on 학생.학과번호 = 학과.번호
    where 학생.키 >= pi_studentheight
    )
    Loop
        DBMS_OUTPUT.PUT_LINE('학생 번호 ' || student.번호 || ' 학생 이름 ' || student.학생이름 ||
        ' 학생키 ' || student.키 || ' 학과명 ' || student.학과이름);
    end loop;
end; 

execute Q37(180);

profile
min seung moon
아직까지는 코린이!
이전 포스트

ORACLE (feat. PACKAGE)

다음 포스트

데이터베이스 구축 실습(음악정보)

0개의 댓글