요구사항

개념 ERD

테이블 명세서

데이터 모델링

1. 권한을 부여한다.

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

위의 객체 및 데이터 조작 권한 부여 참조하여 cmd에서 권한을 부여해야 한다.

2. Oracle SQL Developer에 학사관리시스템으로 접속한다.

테이블이 없음을 확인한다.

3. 테이블을 생성하여 Logical 로 모델링을 완성한다.

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

위의 ERwin Data Modeler 사용 방법을 토대로 아래와 같이 모델링을 완성한다.

+ zero인 경우가 없기에 전부 One or More로 변경한다.

4. 테이블을 생성하여 Physical 로 모델링을 완성한다.

5. 속성 타입과 길이, Null 여부를 기입한다.

6. Oracle SQL Developer 와 ERWin 설정과 연결을 참조하여 연동한다.

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

위의 페이지 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(문자열, 시작위치, 길이)

요구사항

개념 ERD

테이블 명세서

데이터 모델링

1. 권한을 부여한다.

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

위의 객체 및 데이터 조작 권한 부여 참조하여 cmd에서 권한을 부여해야 한다.

2. Oracle SQL Developer에 다수리시스템으로 접속한다.

테이블이 없음을 확인한다.

3. 테이블을 생성한다.

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

위의 ERwin Data Modeler 사용 방법을 토대로 아래와 같은 테이블을 우선적으로 생성한다.

4. Logical을 완성한다.

5. 위와 같은 방법으로 Physical을 완성한다.

6. 각자의 타입과 Not NULL 여부를 기입한다.

7. 임의로 다대다 관계로 연결하여 CST_NO을 FK로 가진다.

7번의 경우 위의 개념 ERD와는 다르게 진행한다.

8. 전부 문제가 없다면 3번의 링크를 참조하여 테이블을 Oracle SQL Developer 로 연결하여 확인한다.

데이터 삽입

-- employee 테이블
select * from employee;

insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A101', '김철수', '대전광역시 서구', '010-0123-3456', '120000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A102', '강서주', '대전광역시 동구', '010-1234-4567', '210000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A103', '임석일', '대전광역시 서구', '010-6789-1011', '220000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A104', '곽재우', '서울특별시 동작구', '010-1011-1213', '100000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A105', '이미지', '서울특별시 송파구', '010-1415-2425', '310000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A106', '이성종', '대구광역시 중구', '010-4569-2013', '300000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A107', '박무현', '대구광역시 서구', '010-4321-9876', '205000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A108', '강하진', '대전광역시 유성구', '010-5748-0133', '90000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A109', '김명수', '대전광역시 서구', '010-5422-0000', '410000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A110', '이경민', '대전광역시 서구', '010-9999-1234', '200900');


-- customer 테이블
select * from customer;

insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C101', '김승종', '대전광역시 중구', '010-1239-1123');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C102', '박무진', '대전광역시 서구', '010-7781-3320');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C103', '홍길동', '대전광역시 유성구', '010-0010-0011');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C104', '성춘향', '대전광역시 동구', '010-0000-1111');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C105', '장동우', '대전광역시 대덕구', '010-3210-3210');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C106', '김말진', '부산광역시 중구', '010-9999-9150');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C107', '서모아', '서울특별시 동작구', '010-3459-0112');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C108', '이진환', '대전광역시 중구', '010-4873-4156');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C109', '허안현', '대전광역시 중구', '010-2157-1357');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C110', '고아섭', '대전광역시 동구', '010-3578-0479');


-- car 테이블
select * from car;

insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('68오8269', '스즈키', '2014', 62051, 'C101');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('113서9441', '그랜저820', '2016', 20135, 'C101');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('222나9651', '모닝', '2016', 182051, 'C102');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('123두2341', '스파크', '2021', 32105, 'C103');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('134누9963', '모닝', '2019', 2113, 'C104');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('123가4568', '쏘나타', '2010', 354021, 'C105');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('210가1234', '폭스바겐', '2013', 547311, 'C106');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('152가3018', '볼보', '2004', 543210, 'C107');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('158가4832', '벌칸', '2013', 210, 'C107');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('380우4104', '레인지', '2019', 213483, 'C108');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('201수1474', '오펠 록스', '2010', 123087, 'C109');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('105가4203', '벌칸', '2020', 456210, 'C110');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('105가4209', '벤츠', '2023', 10967, 'C110');


-- 서비스 테이블
select * from service;

insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('201011S001', 10, null, 'A101', '68오8269', 'C101');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('221230S002', 7, null, 'A102', '222나9651', 'C102');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('220101S003', 13, 1000000, 'A103', '123두2341', 'C103');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('201115S004', 1, 50000, 'A101', '134누9963', 'C104');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('230135S005', 3, 100000, 'A104', '123가4568', 'C105');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('230303S006', 50, null, 'A105', '210가1234', 'C106');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('230412S007', 100, 3420000, 'A106', '152가3018', 'C107');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('230501S008', 2160, null, 'A107', '380우4104', 'C108');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('210526S009', 21, 240000, 'A108', '201수1474', 'C109');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('231111S010', 10000, 250000, 'A109', '105가4203', 'C110');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('231213S011', 10, 20000, 'A106', '158가4832', 'C107');

문제

1. 월급이 20만원과 30만원 사이인 직원의 이름, 주소, 연락처, 급여를 구하시오.

2. 고객 'C107'이 수리를 요청한 목록을 수리비의 내림차순으로 출력하시오.

3. 카센터의 직원수와 직원의 평균급여를 출력하시오.

4. 50만원 이상 수리한 자동차 현황을 서비스번호, 자동차번호, 모델, 수리금액, 고객번호, 고객명, 연락처 순으로 조회하시오.

5. 2023년도 수리된 자동차현황을 서비스번호, 자동차번호, 수리시간, 수리금액을 금액이 큰 것부터 조회하시오.

6. 금액기준으로 가장 큰 금액수리를 요청한 고객의 고객번호, 금액을 조회하시오.

문제의 해답

• 1번 문제

-- 1. 월급이 20만원과 30만원 사이인 직원의 이름, 주소, 연락처, 급여를 구하시오.
select emp_name 이름, emp_address 주소, emp_telno 연락처, emp_salary 급여 
    from employee 
    where emp_salary between 200000 and 300000;
    -- where emp_sqlary > 200000 and emp_sqlary < 1300000;

• 2번 문제

-- 2. 고객 'C107'이 수리를 요청한 목록을 수리비의 내림차순으로 출력하시오.
select b.cst_no, b.cst_name, a.svc_cost
    from service a, customer b
    where b.cst_no = 'C107'
        and a.cst_no = b.cst_no
        order by a.svc_cost desc;

• 3번 문제

-- 3. 카센터의 직원수와 직원의 평균급여를 출력하시오.
select count(emp_no) 직원수, round(avg(emp_salary)) 평균급여 from employee;

• 4번 문제

-- 4. 5만원 이상 수리한 자동차 현황을 서비스번호, 자동차번호, 모델, 수리금액, 고객번호, 고객명, 연락처 순으로 조회하시오.
select s.svc_no 서비스번호, c.car_no 자동차번호, c.car_model 모델, s.svc_cost 수리금액, u.cst_no 고객번호, u.cst_name 고객명, u.cst_telno 연락처
    from service s, car c, customer u
    where s.svc_cost >= 50000
        and u.cst_no = c.cst_no
        and c.car_no = s.car_no;

• 5번 문제

-- 5. 2023년도 수리된 자동차현황을 서비스번호, 자동차번호, 수리시간, 수리금액을 금액이 큰 것부터 조회하시오.
select svc_no 서비스번호, car_no 자동차번호, svc_time 수리시간, svc_cost 수리금액
    from service
    where substr(svc_no, 1, 2) = '23'
    order by nvl(svc_cost, 0) desc;

• 6번 문제

-- 6. 금액기준으로 가장 큰 금액수리를 요청한 고객의 고객번호, 금액을 조회하시오.
select asd 고객번호, mpr 금액 
    from 
        (select cst_no asd, max(svc_cost) mpr
            from service
            group by cst_no
            order by nvl(max(svc_cost), 0) desc)
    where rownum = 1;
-- rownum : 가장 첫번째 것을 가져옴.

문제

1. 진행되고 있는 공사의 공사건수와 총공사 비용을 구하시오.

2. 공사별 자재별 수량을 구하시오.

3. 총자재 현황을 금액이 큰 것 순서대로 자재번호, 자재명, 수량, 금액을 구하시오.

4. 공사가 끝난 사업장 중 공사금액이 큰 순서로 사업장번호, 사업장명, 공사금액을 구하시오.

5. 2023년도에 공사현장에 파견된 직원의 사번, 이름, 파견일자, 공사명을 구하시오.

제공되는 테이블과 데이터

6장에서 연결된 테이블을 기반으로 데이터를 삽입한다.

삭제를 할 시 데이터를 삭제 후 테이블을 삭제해야 한다.

테이블이나 데이터 삭제 시에는 관계 되어 있는 자식 테이블을 우선적으로 삭제 하여야 오류 없이 삭제된다.

-- EMPLOYEE 테이블 데이터 입력
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_position, emp_dept)
    values('2101', '강서준', '세종시 다정남로 22', '010-8317-9503', '사원', '관리부');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_position, emp_dept)
    values('2102', '김민준', '대전시 둔산로 16', '010-5126-1320', '사원', '행정부');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_position, emp_dept)
    values('2103', '도진아', '부산시 해운로 07', '010-0154-2315', '대리', '관리부');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_position, emp_dept)
    values('2104', '문임환', '대구시 푸르지로 73', '010-4832-1305', '과장', '건설부');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_position, emp_dept)
    values('2105', '서유민', '대전시 은행로 01', '010-2167-0218', '사원', '건설부');

-- SITE 데이터 입력
insert into site (st_no, st_name, st_address, st_telno, st_amount, st_manqty, st_strdate, st_predate)
    values('A001', '천세대관평아파트공사', '대전시 관평동 300번길', '070-2658-1001', 15200000, 502, '2020-01-08', '2023-10-10');
insert into site (st_no, st_name, st_address, st_telno, st_amount, st_manqty, st_strdate, st_predate)
    values('A002', '대관평아파트공사', '세종시 평동 420번길', '070-2486-2015', 18100000, 805, '2020-01-08', '2025-01-08');
insert into site (st_no, st_name, st_address, st_telno, st_amount, st_manqty, st_strdate, st_predate, st_enddate)
    values('A003', '인재아파트공사', '부산시 해운동 042번길', '070-3877-2544', 25900000, 1202, '2022-04-14', '2024-12-20', '2024-10-16');
insert into site (st_no, st_name, st_address, st_telno, st_amount, st_manqty, st_strdate, st_predate)
    values('A004', '대전아파트공사', '서울시 대전동 300번길', '070-4867-9942', 3900000, 427, '2021-03-27', '2023-11-04');
insert into site (st_no, st_name, st_address, st_telno, st_amount, st_manqty, st_strdate, st_predate)
    values('A005', '재구대구아파트공사', '대구시 푸르동 051번길', '070-2489-3004', 57800000, 902, '2023-09-18', '2026-09-10');
insert into site (st_no, st_name, st_address, st_telno, st_amount, st_manqty, st_strdate, st_predate, st_enddate)
    values('A006', '인재아파트공사', '부산시 해운리 202번길', '070-3837-2884', 1000000, 1203, '2022-04-14', '2024-10-25', '2024-08-16');

-- ITEM 데이터 입력
insert into item (itm_no, itm_name, itm_qty, itm_price, itm_buydate, st_no)
    values('STL1000001', '나사못10*15', 500, 500000, '2023/08/21', 'A001');
insert into item (itm_no, itm_name, itm_qty, itm_price, itm_buydate, st_no)
    values('STL1000002', '판자100*10', 7000, 13000000, '2020/04/11', 'A002');
insert into item (itm_no, itm_name, itm_qty, itm_price, itm_buydate, st_no)
    values('STL1000003', '벽돌100*150', 2000, 600000, '2023/02/01', 'A001');
insert into item (itm_no, itm_name, itm_qty, itm_price, itm_buydate, st_no)
    values('STL1000004', '시멘트1000*105', 10000, 9000000, '2024/09/17', 'A003');
insert into item (itm_no, itm_name, itm_qty, itm_price, itm_buydate, st_no)
    values('STL1000005', '삽1*15', 300, 8000000, '2026/01/27', 'A004');

-- WORK 데이터 입력
insert into work(emp_no, st_no, wrk_inpdate)
    values('2101', 'A001', '2021.05.12');
insert into work(emp_no, st_no, wrk_inpdate)
    values('2102', 'A002', '2026.09.30');
insert into work(emp_no, st_no, wrk_inpdate)
    values('2103', 'A003', '2022.05.07');
insert into work(emp_no, st_no, wrk_inpdate)
    values('2104', 'A004', '2022.06.02');
insert into work(emp_no, st_no, wrk_inpdate)
    values('2105', 'A005', '2023.07.25');

1번 문제

-- 1. 진행되고 있는 공사의 공사건수와 총공사 비용을 구하시오.
select count(st_no), sum(st_amount) from site;
select count(*) 공사건수, sum(st_amount) 총공사비용 from site;
select count(*) as 공사건수, sum(st_amount) as 총공사비용 from site;
select count(*) "공사건수", sum(st_amount) "총공사비용(&)" from site; -- 특수 기호 인식을 위해서 큰따옴표를 사용한다.
select count(st_no) 공사건수, to_char(sum(st_amount), '999,999,999,999') 총공사비용 from site; -- 숫자 사용 시 세 자리씩 끊고 싶다면 char로 변환한 후 추가하여야 한다.
-- to_number, to_date 등등이 있음

2번 문제

-- 2. 공사별 자재별 수량을 구하시오.
select st_no 공사번호, itm_no 자재번호, sum(itm_qty) 자재수량 
    from item
    group by st_no, itm_no
    order by st_no;

3번 문제

-- 3. 총자재 현황을 금액이 큰 것 순서대로 자재번호, 자재명, 수량, 금액을 구하시오.
-- order by 속성 desc; 속성을 기준으로 큰 것부터 순서대로 나열한다.
select itm_no 자재번호, itm_name 자재명, itm_qty 자재수량, itm_price 자재금액 
    from item 
    order by itm_price desc;

4번 문제

-- 4. 공사가 끝난 사업장 중 공사금액이 큰 순서로 사업장번호, 사업장명, 공사금액을 구하시오.
select st_no 사업장번호, st_name 사업장명, st_amount 공사금액, st_enddate 완료일자
    from site 
    where st_enddate is not null 
    order by st_amount desc;
-- st_enddate에 값이 있을 시 공사가 끝난 것으로 확인된다.
-- 공사 중인 것만 보고 싶다면 is null로 작성하면 된다.

5번 문제

-- 5. 2023년도에 공사현장에 파견된 직원의 사번, 이름, 파견일자, 공사명을 구하시오.
select a.emp_no 사번, a.emp_name 이름, b.wrk_inpdate 파견일자, c.st_name 공사명 
    from employee a, work b, site c 
    where a.emp_no = b.emp_no
    and c.st_no = b.st_no
    and substr(b.wrk_inpdate, 1, 2) = '23';