반응형
요구사항
개념 ERD
테이블 명세서
데이터 모델링
1. 권한을 부여한다.
https://forest-of-coding.tistory.com/6
[Database] 1.5장 Oracle user 접속 및 사용권한 부여
Oracle SQL Developer 사용 전 설정할 것 1. Win키 + R 를 누를 시 아래와 같은 실행창을 띄운다. 2. 실행창에 cmd를 입력한다. sqlplus sys/java@localhost:1521 as sysdba 3. 위의 코드를 입력하여 Oracle DB에 접속한다. 4
forest-of-coding.tistory.com
위의 객체 및 데이터 조작 권한 부여 참조하여 cmd에서 권한을 부여해야 한다.
2. Oracle SQL Developer에 학사관리시스템으로 접속한다.
테이블이 없음을 확인한다.
3. 테이블을 생성하여 Logical 로 모델링을 완성한다.
https://forest-of-coding.tistory.com/21
[Database] 6장 ERwin Data Modeler
ERwin Data Modeler (5장의 테이블 명세서를 기반으로 ER 다이어그램을 작성한다. 사용하는 프로그램은 ERwin Data Modeler 이다. 설정 정보공학 표기법을 사용하기 위해 아래와 같은 방식으로 ERwin을 설정
forest-of-coding.tistory.com
위의 ERwin Data Modeler 사용 방법을 토대로 아래와 같이 모델링을 완성한다.
+ zero인 경우가 없기에 전부 One or More로 변경한다.
4. 테이블을 생성하여 Physical 로 모델링을 완성한다.
5. 속성 타입과 길이, Null 여부를 기입한다.
6. Oracle SQL Developer 와 ERWin 설정과 연결을 참조하여 연동한다.
https://forest-of-coding.tistory.com/21
[Database] 6장 ERwin Data Modeler
ERwin Data Modeler (5장의 테이블 명세서를 기반으로 ER 다이어그램을 작성한다. 사용하는 프로그램은 ERwin Data Modeler 이다. 설정 정보공학 표기법을 사용하기 위해 아래와 같은 방식으로 ERwin을 설정
forest-of-coding.tistory.com
위의 페이지 Oracle SQL Developer 와 ERWin 설정과 연결을 참조하여 그대로 진행한다.
데이터 삽입
select * from student;
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
values ('23AA100001', '김철수', '951201-2014216', '010-0123-3456', '대전광역시 서구', '22A01');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
values ('23AB100301', '강서주', '940325-2123452', '010-1234-4567', '대전광역시 동구', '20B03');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
values ('23AA100034', '임석일', '991201-2031511', '010-6789-1011', '대전광역시 서구', '19A04');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
values ('23BB200099', '곽재우', '961201-2211001', '010-1011-1213', '서울특별시 동작구', '19D02');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
values ('23CR600024', '이미지', '970111-2405216', '010-1415-2425', '서울특별시 송파구', '18Z19');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
values ('23AC200010', '이성종', '951201-2014216', '010-4569-2013', '대구광역시 중구', '23D06');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
values ('21AA100001', '박무현', '971003-2204324', '010-4321-9876', '대구광역시 서구', '20F30');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
values ('22CA100012', '강하진', '960511-2042152', '010-5748-0133', '대전광역시 유성구', '20F32');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
values ('23CC300012', '김명수', '980220-2112012', '010-5422-0000', '대전광역시 서구', '10A11');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
values ('13BD600034', '이경민', '930630-2462021', '010-9999-1234', '대전광역시 서구', '20S62');
--
select * from subject;
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
values ('231AB101', 'A', '영어 스피치', 3);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
values ('231AB113', 'B', '영어의 역사', 2);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
values ('202AA206', 'A', '글쓰기', 4);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
values ('132AC303', 'B', '뇌과학', 3);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
values ('221CB302', 'B', '건강 스트레칭', 2);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
values ('192DC209', 'A', '전자회로', 4);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
values ('171DC205', 'A', 'C언어', 4);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
values ('172DC201', 'A', 'C언어 심화', 4);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
values ('201DD103', 'A', '컴퓨터 구조론', 4);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
values ('222AC204', 'B', '미술학', 3);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
values ('161CB303', 'B', '심리의 이해', 2);
--
select * from professor;
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
values ('22A01', '김승종', '영어영문', '영문', '010-1239-1123');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
values ('20B03', '박무진', '영어말하기', '영문', '010-7781-3320');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
values ('19A04', '홍길동', '국어국문', '국문', '010-0010-0011');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
values ('19D02', '성춘향', '뇌과학', '생명', '010-0000-1111');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
values ('18Z19', '장동우', '생활체육', null, '010-3210-3210');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
values ('23D06', '김말진', '전기전자', '전자', '010-9999-9150');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
values ('20F30', '서모아', '컴퓨터공학', '컴퓨터', '010-3459-0112');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
values ('20F32', '이진환', '컴퓨터공학', '컴퓨터', '010-4873-4156');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
values ('10A11', '허안현', '정보통신', null, '010-2157-1357');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
values ('20S62', '고아섭', '동양화', '예술', '010-3578-0479');
--
select * from class;
insert into class (std_no, sub_no, cls_score)
values ('23AA100001', '231AB101', 95);
insert into class (std_no, sub_no, cls_score)
values ('23AB100301', '231AB113', 88);
insert into class (std_no, sub_no, cls_score)
values ('23AA100001', '231AB113', 60);
insert into class (std_no, sub_no, cls_score)
values ('23AA100034', '202AA206', 77);
insert into class (std_no, sub_no, cls_score)
values ('23BB200099', '132AC303', 93);
insert into class (std_no, sub_no, cls_score)
values ('23CR600024', '221CB302', 5);
insert into class (std_no, sub_no, cls_score)
values ('23AC200010', '221CB302', 100);
insert into class (std_no, sub_no, cls_score)
values ('21AA100001', '192DC209', 78);
insert into class (std_no, sub_no, cls_score)
values ('22CA100012', '171DC205', 67);
insert into class (std_no, sub_no, cls_score)
values ('23CC300012', '201DD103', 82);
--
select * from counsel;
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
values ('23-12-14', '22A01', '23AA100001', '1', '성적 향상 관련');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
values ('23-12-13', '20B03', '23AB100301', '1', '성적 미달');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
values ('23-12-14', '19A04', '23AA100034', '2', '취업 자리 알선');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
values ('23-12-14', '19A04', '23AB100301', '2', '취업 알선');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
values ('23-11-10', '19D02', '23BB200099', '4', '집안 사정');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
values ('23-03-14', '18Z19', '23BB200099', '3', '전과 관련');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
values ('23-09-14', '23D06', '23AA100001', '4', '개인사');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
values ('20-12-14', '20F30', '23CR600024', '5', '퇴학');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
values ('18-10-04', '10A11', '13BD600034', '6', '교환학생');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
values ('19-12-14', '10A11', '23CC300012', '6', '교환 학생 정보');
--
select * from lecture;
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
values ('22A01', '231AB101', '2', '13:00', 'A건물101호', 35);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
values ('22A01', '231AB113', '4', '10:00', 'A건물401호', 30);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
values ('20B03', '231AB101', '2', '13:00', 'A건물102호', 35);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
values ('19A04', '202AA206', '3', '11:00', 'B건물301호', 50);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
values ('19D02', '132AC303', '6', '14:00', 'D건물321호', 25);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
values ('18Z19', '221CB302', '2', '15:00', 'D건물운동장', 60);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
values ('23D06', '192DC209', '5', '15:30', 'D건물431호', 45);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
values ('20F30', '171DC205', '3', '09:30', 'F건물131호', 45);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
values ('20F32', '171DC205', '2', '13:00', 'F건물135호', 45);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
values ('20F32', '172DC201', '5', '16:00', 'F건물211호', 35);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
values ('10A11', '201DD103', '3', '12:00', 'B건물213호', 35);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
values ('20S62', '222AC204', '4', '13:30', 'S건물101호', 25);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
values ('20S62', '161CB303', '6', '11:00', 'S건물101호', 35);
문제
1. 2023년도 1학기 수강생들의 학번, 과목코드, 성적을 내림차순 성적순으로 모두 조회하시오.
2. 과목코드가 231AB101 과목의 수강현황을 학번, 학생명, 과목코드, 과목명, 성적순으로 조회하시오.
3. 2022년도 2학기 총 수강생들은 몇 명인가?
4. 2023년도 1학기 수강생들의 학번, 과목코드, 학점을 조회하시오.
5. 2023년도에 발생한 상담건수를 조회하시오.
6. 수요일에 편성된 강의 목록 전체와 교수명, 교과명을 조회하시오.
7. 12월 중에 이루어진 상담내역과 학생명을 조회하시오.
문제의 해답
1번
-- 1. 2023년도 1학기 수강생들의 학번, 과목코드, 성적을 내림차순 성적순으로 모두 조회하시오.
select std_no 학번, sub_no 과목코드, cls_score 성적 from class
where substr(sub_no, 1, 3) = '231'
order by cls_score desc;
2번
-- 2. 과목코드가 231AB101 과목의 수강현황을 학번, 학생명, 과목코드, 과목명, 성적순으로 조회하시오.
select a.std_no 학번, a.std_name 학생명, c.sub_no 과목코드, c.sub_name 과목명, b.cls_score 성적
from student a, class b, subject c
where a.std_no = b.std_no
and b.sub_no = c.sub_no
and a.std_no = '23AA100001'
order by b.cls_score desc;
3번
-- 3. 2022년도 2학기 총 수강생들은 몇 명인가?
select count(*) from subject
where substr(sub_no, 1, 3) = 222;
4번
-- 4. 2023년도 1학기 수강생들의 학번, 과목코드, 학점을 조회하시오.
select std_no 학번, sub_no 과목코드,
case
when cls_score >= 95 then 'A+'
when cls_score >= 90 then 'A0'
when cls_score >= 85 then 'B+'
when cls_score >= 80 then 'B0'
when cls_score >= 75 then 'C+'
when cls_score >= 70 then 'C0'
when cls_score >= 65 then 'D+'
when cls_score >= 60 then 'D0'
else 'F'
end as 학점
from class
where substr(sub_no, 1, 3) = 231
order by cls_score desc;
5번
-- 5. 2023년도에 발생한 상담건수를 조회하시오.
select count(*) from counsel
where substr(cns_date, 1, 2) = 23;
-- where cns_date >= '2023-01-01' and cns_date <= '2023-12-23';
6번
-- 6. 수요일에 편성된 강의 목록 전체와 교수명, 교과명을 조회하시오.
select a.*, b.pro_name 교수명, c.sub_name 교과명
from lecture a, professor b, subject c
where b.pro_no = a.pro_no
and c.sub_no = a.sub_no
and a.lec_day = 4;
7번
-- 7. 12월 중에 이루어진 상담내역과 학생명을 조회하시오.
select a.*, b.std_name 학생명
from counsel a, student b
where a.std_no = b.std_no
and substr(cns_date, 4, 2) = '12';
-- substr(문자열, 시작위치, 길이)
반응형
'데이터베이스' 카테고리의 다른 글
| [Database] 8장 SQL 연습문제1 (0) | 2023.12.18 |
|---|---|
| [Database] 7장 SQL (0) | 2023.12.15 |
| [Database] 6.5.2장 모델링 테스트 (0) | 2023.12.11 |
| [Database] 6.5.1장 SQL 테스트 (0) | 2023.12.08 |
| [Database] 6장 ERwin Data Modeler (0) | 2023.12.07 |