퀴즈 문제

7회 퀴즈 문제

[월별 급여집계 테이블]

부서	성 명	년월	급여액
총무부	홍길동	200101	200
총무부	임꺽정	200101	250
총무부	장길산	200101	100
총무부	최고봉	200101	200
총무부	황비홍	200101	120
영업부	궁해	200101	300
영업부	왕곤	200101	310
영업부	견온	200101	220
생산부	박임자	200101	350
생산부	전본인	200101	300
생산부	노친구	200101	300
생산부	김갱제	200101	230

[결과 집합]

부  서	성명	급여액
총무부	최고봉	200
영업부	궁해	300
생산부	노친구	300

●문제 : 2001년 1월의 각 부서별 급여액순으로
        상위 2위에 해당 하는 사원 1명의 이름,
        급여액을 구하는 SQL을 작성하시오.
        단, 급여액이 동 일할 경우 성명 순으로 함.

create table quiz7
(
 부서 varchar(10),
 성명 varchar(10),
 년월 varchar(6),
 급여액 bigint
)

insert into quiz7 values('총무부', '홍길동', '200101', 200)
insert into quiz7 values('총무부', '임꺽정', '200101', 250)
insert into quiz7 values('총무부', '장길산', '200101', 100)
insert into quiz7 values('총무부', '최고봉', '200101', 200)
insert into quiz7 values('총무부', '황비홍', '200101', 120)

insert into quiz7 values('영업부', '궁해', '200101', 300)
insert into quiz7 values('영업부', '왕곤', '200101', 310)
insert into quiz7 values('영업부', '견온', '200101', 220)

insert into quiz7 values('생산부', '박임자', '200101', 350)
insert into quiz7 values('생산부', '전본인', '200101', 300)
insert into quiz7 values('생산부', '노친구', '200101', 300)
insert into quiz7 values('생산부', '김갱제', '200101', 230)

6회 퀴즈 문제

대리점별 월 상품매출 테이블의 테이터 구조

대리점	월	대분류	중분류	상품코드	매출액
마포	200011	남성의류	골프웨어	A0001	100
마포	200011	남성의류	골프웨어	A0002	200
마포	200011	남성의류	골프웨어	A0003	300
마포	200011	남성의류	골프웨어	A0004	400
마포	200011	남성의류	골프웨어	A0005	500
마포	200011	남성의류	골프웨어	A0006	600
마포	200011	남성의류	신사정장	B0001	700
마포	200011	남성의류	신사정장	B0002	800
마포	200011	남성의류	신사정장	B0003	900
마포	200011	남성의류	신사정장	B0004	1000
마포	200011	남성의류	신사정장	B0005	1100
마포	200011	남성의류	신사정장	B0006	1200
마포	200011	남성의류	신사정장	B0007	1300
마포	200011	남성의류	신사정장	B0008	2000
마포	200011	남성의류	신사정장	B0009	1400
마포	200011	남성의류	신사정장	B0010	2300

∴ 결과 집합

대리점	대분류	중분류	상품코드	매출액	그룹	기여도
마포	남성의류	골프웨어	A0006	600	B	50
마포	남성의류	골프웨어	A0005	500	B	50
마포	남성의류	골프웨어	A0004	400	C	30
마포	남성의류	골프웨어	A0003	300	C	30
마포	남성의류	골프웨어	A0002	200	D	10
마포	남성의류	골프웨어	A0001	100	D	10
마포	남성의류	신사정장	B0010	2300	A	10
마포	남성의류	신사정장	B0008	2000	B	30
마포	남성의류	신사정장	B0009	1400	B	30
마포	남성의류	신사정장	B0007	1300	B	30
마포	남성의류	신사정장	B0006	1200	C	30
마포	남성의류	신사정장	B0005	1100	C	30
마포	남성의류	신사정장	B0004	1000	C	30
마포	남성의류	신사정장	B0003	900	C	30
마포	남성의류	신사정장	B0002	800	D	10
마포	남성의류	신사정장	B0001	700	D	10

∴ 문제위의 테이블의 데이터를 참조하여 결과 집합을 구하는 SQL을  작성하시오. 
위의 테이블은 ABC회사의 대리점별 상품매출을 월별로 집계한 것으로 상품이 중분류에서 어느 정도의 매출 기여를 하고 있는지 알고자 하며, 매출 기여도를 구하는 방식은 다음과 같다.

중분류의 전체 매출을 100%라고 했을 때

• 그룹 : 상위 10%를 A 그룹, 그 다음 상위 30%를 B 그룹, 그 다음 상위 40% C 그룹 그리고 나머지 20%는 D 그룹으로 표시를 한다.그런데 일부 중분류는 상품이 아주 적어 A 그룹,B 그룹, C 그룹 등에 속하지 않을 수도 있다. 이럴 경우에는 처음으로 속하는 그룹에 귀속시키도록 한다.
• 기여도 : 그룹 매출이 전체 중분류 매출에서 차지하는 비중을 구할 때에는 10의 배수가 되도록 해야 한다. 예를 들어 1% ~ 9% : 0, 10% ~ 19% : 10, 20% ~ 29% : 20 ,................,90% ~ 99% : 90, 100% : 100으로 나타내고자 한다.

create table quiz6(
 STORE varchar(6),
 YYMM varchar(6),
 GCODE varchar(10),
 MCODE varchar(10),
 ITEM varchar(6),
 AMT bigint
)

insert into quiz6 values('마포', '200011', '남성의류', '골프웨어', 'A0001', 100)
insert into quiz6 values('마포', '200011', '남성의류', '골프웨어', 'A0002', 200)
insert into quiz6 values('마포', '200011', '남성의류', '골프웨어', 'A0003', 300)
insert into quiz6 values('마포', '200011', '남성의류', '골프웨어', 'A0004', 400)
insert into quiz6 values('마포', '200011', '남성의류', '골프웨어', 'A0005', 500)
insert into quiz6 values('마포', '200011', '남성의류', '골프웨어', 'A0006', 600)
insert into quiz6 values('마포', '200011', '남성의류', '신사정장', 'B0001', 700)
insert into quiz6 values('마포', '200011', '남성의류', '신사정장', 'B0002', 800)
insert into quiz6 values('마포', '200011', '남성의류', '신사정장', 'B0003', 900)
insert into quiz6 values('마포', '200011', '남성의류', '신사정장', 'B0004', 1000)
insert into quiz6 values('마포', '200011', '남성의류', '신사정장', 'B0005', 1100)
insert into quiz6 values('마포', '200011', '남성의류', '신사정장', 'B0006', 1200)
insert into quiz6 values('마포', '200011', '남성의류', '신사정장', 'B0007', 1300)
insert into quiz6 values('마포', '200011', '남성의류', '신사정장', 'B0008', 2000)
insert into quiz6 values('마포', '200011', '남성의류', '신사정장', 'B0009', 1400)
insert into quiz6 values('마포', '200011', '남성의류', '신사정장', 'B0010', 2300)

5회 퀴즈 문제

● 퀴즈의 핵심 :  집합의 다양한 가공

[SAMPLE 테이블]

성 명	특기사항	구분
김영민	대표이사	A
김영민	고문	A
김영민	회장	B
김영민	HALF SUB 2	C
김영민	FULL SUB 3	C
문성구	교수	A
문성구	홍보	B
문성구	대회준비장	B
문성구	FULL SUB 4	C
문성구	HALF SUB 3	C
문성구	10KM SUB 1	C
서승환	주임	A
서승환	총무	B
서승환	섭외	B
서승환	대회준비위원	B
서승환	10KM SUB 1	C

• 구분 : A - 직위, B - 동호회 직위, C - 기록 

[결과 집합]

성명	직위	동호회 직위	기록
김영민	대표이사	회장	HALF SUB 2
	고문		FULL SUB 3
문성구	교수	홍보	FULL SUB 4
		대회준비장	HALF SUB 3
			10KM SUB 1
서승환	주임	총무	10KM SUB 1
		섭외
		대회준비위원

●문제 : SAMPLE 테이블의 데이터를 가공하여 결과 집합으로
         추출할 수 있는 SQL문을 작성하시오

 

 

 

create Table SampleT
(
 성명 varchar(10),
 특기사항 varchar(20),
 구분 char(1)
)

insert into SampleT values('김영민', '대표이사', 'A')
insert into SampleT values('김영민', '고문', 'A')
insert into SampleT values('김영민', '회장', 'B')
insert into SampleT values('김영민', 'HALF SUB 2', 'C')
insert into SampleT values('김영민', 'FULL SUB 3', 'C')
insert into SampleT values('문성구', '교수', 'A')
insert into SampleT values('문성구', '홍보', 'B')
insert into SampleT values('문성구', '대회준비장', 'B')
insert into SampleT values('문성구', 'FULL SUB 4', 'C')
insert into SampleT values('문성구', 'FULL SUB 3', 'C')
insert into SampleT values('문성구', '10KM SUB 1', 'C')
insert into SampleT values('서승환', '주임', 'A')
insert into SampleT values('서승환', '총무', 'B')
insert into SampleT values('서승환', '섭외', 'B')
insert into SampleT values('서승환', '대회준비위원', 'B')
insert into SampleT values('서승환', '10KM SUB 1', 'C')

select identity(int, 1, 1) id, * into #temp_quiz5 from samplet

select case seq when 1 then 성명 end,
       max(case 구분 when 'A' then 특기사항 end) 특기사항1,
       max(case 구분 when 'B' then 특기사항 end) 특기사항2,
       max(case 구분 when 'C' then 특기사항 end) 특기사항3
from
(

 select a.성명,a.구분,a.특기사항, a.id-b.minnum+1 as seq
 from #temp_quiz5 a, -- 원집합
 (
  select 성명,구분, min(id) as minnum
              from #temp_quiz5
              group by 성명,구분

 ) b -- 1차 중간집합
 where a.성명 = b.성명
 and a.구분 = b.구분
) x

group by 성명,seq

order by 성명

4회 퀴즈 문제

● 퀴즈의 핵심 : 1 : M 관계를 1:1 관계로 변경하는 방법

∴ ERD

∴ 테이블별 테이타 구조

- 표준단위

표준단위코드	표준단위명	사용구분
SDU001	기판	Y
SDU002	O2	Y
.	.	.

- 항목그룹

항목그룹코드	대분류명	중분류	소분류명
IG001	외관	회로	접속부
IG002	치수	회로	입자
.	.	.	.

- 검사항목

검사항목코드	검사명	항목그룹코드
IT001	역삽입	IG001
IT002	오염	IG001
IT003	변색	IG001
IT004	탈색	IG001
IT005	길이	IG002
IT006	각도	IG002
IT007	지름	IG002
.	.	.

- 표준단위별 검사항목

표준단위코드	검사항목코드	검사 TOOL	검사일자
SDU001	IT001	육안	20000821
SDU001	IT003	육안	20000821
SDU001	IT004	현미경	20000821
SDU001	IT005	각도기	20000821
SDU001	IT007	자	20000821
.	.	.	.

- 검사결과이력

표준단위	검사항목코드	검사수준	검사순번	시작일	종료일
SDU001	IT001	보통	3	20000821	20000823
SDU001	IT001	보통	4	20000822	20000824
SDU001	IT001	보통	5	20000821	20000822
SDU001	IT003	보통	2	20000821	20000826
SDU001	IT003	보통	3	20000821	20000823
SDU001	IT003	보통	4	20000821	20000827
SDU001	IT003	특별	1	20000821	20000827
SDU001	IT003	특별	2	20000821	20000822
SDU001	IT005	보통	1	20000821	20000828
SDU001	IT005	보통	2	20000821	20000821
SDU001	IT007	보통	1	20000821	20000823
.	.	.	.	.	.

∴ 결과 집합

표준단위명	대분류명	중분류명	소분류명	항목명	항목수	검사수준	종료일
기판	외관	회로	접속부	역삽입	3	보통	20000822
기판	치수	회로	입자	길이	2	보통	20000821
.	.	.	.	.	.	.

∴ 문제위의 ERD와 테이블의 데이터를 참조하여 결과 집합을 구하는 SQL을 작성하시오.
(단 검색 조건은 표준단위코드='SDU001' 이고, 검사수준='보통' 이다. 검사항목과 표준단위별검사항목 테이블만 GROUP BY에 참여하고 나머지 테이블의 집합들은 GROUP BY에 참여하지 않도록 한다. ) 표준단위별 검사항목에서 항목그룹별로 등록되어 있는 전체 항목의 수를 보여주고 항목은 아무거나 하나만 보여준다. 그리고 검사결과이력 테이블에서는 해당 표준단위와 항목에 대해 종료일이 가장 빠른 한건만 추출한다.

주의 사항 : DB의 프로시저나 저장형 함수를 사용하지 않고 해결해야함.

create table 표준단위
(
 표준단위코드 char(6),
 표준단위명 varchar(10),
 사용구분 char(1)
)

insert into 표준단위 values('SDU001', '기판', 'Y')
insert into 표준단위 values('SDU002', 'O2', 'Y')

create table 항목그룹
(
 항목그룹코드 char(5),
 대분류명 varchar(10),
 중분류명 varchar(10),
 소분류명 varchar(10)
)

insert into 항목그룹 values('IG001', '외관', '회로', '접속부')
insert into 항목그룹 values('IG002', '치수', '회로', '입자')

create table 검사항목
(
 검사항목코드 char(5),
 검사명 varchar(10),
 항목그룹코드 char(5)
)

insert into 검사항목 values('IT001', '역삽입', 'IG001')  
insert into 검사항목 values('IT002', '오염', 'IG001')
insert into 검사항목 values('IT003', '변색', 'IG001')
insert into 검사항목 values('IT004', '탈색', 'IG001')
insert into 검사항목 values('IT005', '길이', 'IG002')
insert into 검사항목 values('IT006', '각도', 'IG002')
insert into 검사항목 values('IT007', '지름', 'IG002')

create table 표준단위별검사항목
(
 표준단위코드 char(6),
 검사항목코드 char(5),
 검사Tood varchar(10),
 검사일자 char(8)
)

insert into 표준단위별검사항목 values('SDU001', 'IT001', '육안', '20000821')
insert into 표준단위별검사항목 values('SDU001', 'IT003', '육안', '20000821')
insert into 표준단위별검사항목 values('SDU001', 'IT004', '현미경', '20000821')
insert into 표준단위별검사항목 values('SDU001', 'IT005', '각도기', '20000821')
insert into 표준단위별검사항목 values('SDU001', 'IT007', '자', '20000821')

create table 검사결과이력
(
 표준단위 char(6),
 검사항목코드 char(5),
 검사수준 varchar(10),
 검사순번 int,
 시작일 char(8),
 종료일 char(8)
)

insert into 검사결과이력 values('SDU001', 'IT001', '보통', 3, '20000821', '20000823')
insert into 검사결과이력 values('SDU001', 'IT001', '보통', 4, '20000822', '20000824')
insert into 검사결과이력 values('SDU001', 'IT001', '보통', 5, '20000821', '20000822')

insert into 검사결과이력 values('SDU001', 'IT003', '보통', 2, '20000821', '20000826')
insert into 검사결과이력 values('SDU001', 'IT003', '보통', 3, '20000821', '20000823')
insert into 검사결과이력 values('SDU001', 'IT003', '보통', 4, '20000821', '20000827')
insert into 검사결과이력 values('SDU001', 'IT003', '보통', 1, '20000821', '20000827')
insert into 검사결과이력 values('SDU001', 'IT003', '보통', 2, '20000821', '20000822')

insert into 검사결과이력 values('SDU001', 'IT005', '보통', 1, '20000821', '20000828')
insert into 검사결과이력 values('SDU001', 'IT005', '보통', 2, '20000821', '20000821')
insert into 검사결과이력 values('SDU001', 'IT007', '보통', 1, '20000821', '20000823')

select d.표준단위명, e.대분류명, e.중분류명, e.소분류명,
             substring(c.검사항목,6,30) 항목명, c.항목수,
              c.표준단위코드, substring(c.검사항목,1,5) 검사항목코드
          from (

select  a.표준단위코드, b.항목그룹코드,
 MIN(b.검사항목코드+b.검사명) 검사항목,
            count(*) 항목수
from 표준단위별검사항목 a, 검사항목 b
 where a.표준단위코드 ='SDU001'
 and a.검사항목코드 = b.검사항목코드
             group by a.표준단위코드, b.항목그룹코드

 )  c,
               표준단위 d, 항목그룹 e
          where d.표준단위코드 = c.표준단위코드
            and  e.항목그룹코드 = c.항목그룹코드

 

제 3회 퀴즈 문제

●퀴즈의 핵심 : BETWEEN 조인의 기본 개념

[SAMPLE 테이블]

성명	구분	시작일	종료일
홍길동	A10	20000801	20000807
홍길동	G20	20000803	20000815
홍길동	A20	20000807	20000822
홍길동	G30	20000817	20000825
홍길동	A30	20000822	99991231
.	.	.	.
.	.	.	.

[결과 집합 : 2000년 8월 기준]

성명	구분1	구분2	시작일	종료일
홍길동	A10		20000801	20000803
홍길동	A10	G20	20000803	20000807
홍길동	A20	G20	20000807	20000815
홍길동	A20		20000815	20000817
홍길동	A20	G30	20000817	20000822
홍길동	A30	G30	20000822	20000825
홍길동	A30		20000825	20000831
마동탁	.	.	.	.

[그림으로 표현하면]

●문제 : SAMPLE 테이블의 데이터를 가공하여
결과 집합으로추출할 수 있는 SQL문을
작성하시오

●퀴즈에 대한 문의 사항 혹은 힌트등의
질의응답은 열린기술광장을 통해 진행
하겠습니다. 많은 참여 부탁드립니다.

create table quiz3
(
 성명 varchar(10),
 구분 char(3),
 시작일 char(8),
 종료일 char(8)
)

insert into quiz3 values('홍길동', 'A10', '20000801', '20000807')
insert into quiz3 values('홍길동', 'G20', '20000803', '20000815')
insert into quiz3 values('홍길동', 'A20', '20000807', '20000822')
insert into quiz3 values('홍길동', 'G30', '20000817', '20000825')
insert into quiz3 values('홍길동', 'A30', '20000822', '99991231')

 

제 2회 퀴즈 문제

●퀴즈의 핵심 : 집합에 대해 약분의 개념을 활용한다.

성명	시작일	종료일
홍길동	19961001	19981230
홍길동	19981230	19990605
홍길동	19990605	19991002
홍길동	20000201	20000301
홍길동	20000501	99991231
김동훈	19910106	19910731
김동훈	19910731	19940201
김동훈	19940201	19941021
김동훈	19941021	19961031
김동훈	19990501	20000331
김동훈	20000331	99991231
마동탁	19980103	19980727
마동탁	19980727	20000103
마동탁	20000103	20000601
마동탁	20000601	99991231
.	.	.
.	.	.

[SAMPLE 테이블]

위와 같은 데이터가 있는 SAMPLE 테이블이 있다고 가정하여 보자.
어떤 특정 사원은 이력이 중간에 끊어진 경우가 있는데 이러한 끊어진 시작일과 종료일을 추출하고자 한다.
위의 SAMPLE 테이블과 같이 이력발생 데이터에 대한 시작일과 종료일을 함께 관리하는 방법이 선분으로 이력을 관리하는 경우이고
시작일만 가지고 관리하는 방법은 점으로 이력을 관리하는 경우이다.
(이력관리에 대한 자세한 내용은 Solution Warehous->Application Development-> 이력관리를 참조) SAMPLE 테이블의 데이터들을 선분으로 표시하면 아래의 그림과 같이 표현할 수 있다.

위의 그림에서 사원원별로 끊어진 시작일과 종료일을 추출하면 아래와 같은 결과가 나타난다.
(빨간 점선부분의 선분을 추출한 결과)

성명	시작일	종료일
홍길동	19991002	20000201
홍길동	20000301	20000501
김동훈	19961031	19990501

●문제 : 위와 같은 결과를 추출할 수 있는 한개의 SQL문을 작성하시오

 

 

 

출처 : 엔코아

create table quiz2 ( name varchar(20), start_dt varchar(8), end_dt varchar(8));
insert into quiz2 values ('홍길동','19961001','19981230');
insert into quiz2 values ('홍길동','19981230','19990605');
insert into quiz2 values ('홍길동','19990605','19991002');
insert into quiz2 values ('홍길동','20000201','20000301');
insert into quiz2 values ('홍길동','20000501','99991231');
insert into quiz2 values ('김동훈','19910106','19910731');
insert into quiz2 values ('김동훈','19910731','19940201');
insert into quiz2 values ('김동훈','19940201','19961031');
insert into quiz2 values ('김동훈','19990501','20000331');
insert into quiz2 values ('김동훈','20000331','99991231');
insert into quiz2 values ('마동탁','19980103','19980727');
insert into quiz2 values ('마동탁','19980727','20000103');
insert into quiz2 values ('마동탁','20000103','20000601');
insert into quiz2 values ('마동탁','20000601','99991231');

create table copy_t (no int);

insert into copy_t values(1);
insert into copy_t values(2);

select identity(int, 1, 1) id, * into #temp_quiz2 from quiz2 x, copy_t y where y.no <= 2  order by name, start_dt, end_dt, no

select name, end_r as start_date, start_l as end_date from
(

 select min(id) as id, min(no) as no, name, sum(case no when 1 then convert(bigint, start_dt) end) start_l ,
  sum(case no when 1 then convert(bigint, end_dt) end) end_l,
              sum(case no when 2 then convert(bigint, start_dt) end) start_r,
  sum(case no when 2 then convert(bigint, end_dt) end) end_r
 from
 (
  select id, name, start_dt, end_dt, no from #temp_quiz2  
 ) a
 
 group by name, case no when 1 then id else id + 1 end

) b
where end_r <> start_l

다음과 같은 4개의 로우를 가진 테이블이 있다.

COL1	COL2
A	10
C	20
D	10
B	30

하나의 SQL을 사용하여 다음과 같은 결과를 추출하는 SQL을 작성해보세요.

COL1	COL2	누계
A	10	10
C	20	30
D	10	40
B	30	70

위와 같은 예를 3GL로 만든다면 쉽게 해결된다.
하지만, 하나의 SQL에서 해결하기 위해서는 조인의
원리를 이해해야 한다.
여기에서 주의할 점은 COL1의 정렬순서이다.
즉, 테이블에 저장되어진 순서대로 결과를 보고자 한다.

 

출처 : 엔코아

 

 

create table #table1(

 col1 char(1),
 col2 int
)

insert into #table1 values('A', 10)

insert into #table1 values('C', 20)

insert into #table1 values('D', 10)

insert into #table1 values('B', 30)

 

 

 

답)

select min(y.col1) col1, min(y.col2) col2, sum(x.col2) as 'tot'
from
#table1 x, #table1 y
where x.col1 <= y.col1
group by y.col1

[출처] 선분찾기|작성자 우리자나