[Database] 6.5.1장 SQL 테스트

 

문제

 

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 등등이 있음

 

문자열로 변환 안 했을 때 결과 화면1

 

select count(st_no) 공사건수, to_char(sum(st_amount), '999,999,999,999')

 

 

 

2번 문제

 

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

 

결과 화면2

 

 

 

3번 문제

 

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

 

결과 화면3

 

 

 

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로 작성하면 된다.

 

결과 화면4

 

 

 

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

 

결과 화면5