[Database] 6.5.2장 모델링 테스트

 

요구사항

 

 

 

 

개념 ERD

 

다수리 ERD

 

 

 

테이블 명세서

 

사원 테이블

 

고객 테이블

 

자동차 테이블

 

서비스 테이블

 

 

 

데이터 모델링

 

1. 권한을 부여한다.

 

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

[Database] 1.5장 Oracle user 접속 및 사용권한 부여

 

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

 

 

2

 

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

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

 

 

3. 테이블을 생성한다.

 

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

[Database] 6장 ERwin Data Modeler

 

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

 

3

 

 

4

 

4. Logical을 완성한다.

 

 

5

 

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

 

 

6

 

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

 

 

7

 

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

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

 

 

8

 

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;

 

1번 문제 결과 화면

 

 

• 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;

 

2번 문제 결과 화면

 

 

• 3번 문제

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

 

3번 문제 결과 화면

 

 

• 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;

 

4번 문제 결과 화면

 

 

• 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;

 

5번 문제 결과 화면

 

 

• 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 : 가장 첫번째 것을 가져옴.

 

6번 문제 결과 화면