문제 : 이동전화회사에서 가입자를 관리하기 위한 요구사항을 보고 테이블 생성 및 데이터 조회를 하라.
1. 각 가입자는 주민등록번호와 주소를 가지고 있다
2. 각 가입자는 하나 이상의 전화번호에 가입할 수 있다
3. 전화번호는 국과 번호가 있다(예 : 974-2610에서 974는 국, 2610은 번호 이다)
4. 가입자가 가입한 날짜를 가지고 있다
5. 이동전화회사는 여러종류의 부가서비스(ex: 문자메시지, 캐치콜 등)을 제공한다
6. 부가서비스는 부가서비스 이름, 한달 사용료가 있다
7. 각 가입자는 전화번호별로 부가서비스에 하나 이상 가입할 수 있다
8. 가입자가 부가서비스에 가입한 날짜를 가지고 있다.
여기까지가 문제입니다.
이 이후로는 제가 직접 작성한 쿼리입니다.
create table 회원
(가입번호 integer not null
,회원명 varchar(10)
,주민등록번호 varchar(20) not null
,주소 varchar(100)
,전화번호 varchar(20) not null
,전번가입 date
,서비스가입 date
,가입서비스종류 integer
,primary key(가입번호));
create table 부가서비스
(가입번호 integer not null
,가입서비스번호 integer not null
,서비스종류 varchar(20)
,사용료 integer
,primary key(가입번호)
,foreign key(가입번호) references 회원(가입번호));
insert into 회원 values(1, '김태희', '111111-2111111', '경기도 김포시', '987-8888', '2012-02-03', '2012-02-03', 1);
insert into 회원 values(2, '김태희', '111111-2111111', '경기도 김포시', '987-8888', '2012-02-03', '2012-02-03', 2);
insert into 회원 values(3, '김태희', '111111-2111111', '경기도 김포시', '987-9999', '2012-02-05', '2012-02-06', 1);
insert into 회원 values(4, '김태희', '111111-2111111', '경기도 김포시', '987-9999', '2012-02-05', '2012-02-06', 2);
insert into 회원 values(5, '김태희', '111111-2111111', '경기도 김포시', '987-9999', '2012-02-05', '2012-02-06', 3);
insert into 회원 values(6, '김태희', '111111-2111111', '경기도 김포시', '987-9999', '2012-02-05', '2012-02-06', 4);
insert into 회원 values(7, '전지현', '111222-2111111', '경기도 고양시', '988-8128', '2012-02-04', '2012-02-05', 3);
insert into 회원 values(8, '장동건', '121111-1122341', '서울특별시', '981-8288', '2012-02-07', '2012-02-08', 1);
insert into 회원 values(9, '원빈', '111111-1234511', '부산광역시', '911-8238', '2012-02-08', '2012-02-08', 2);
insert into 부가서비스 values(1, 1, '문자메시지', 29000);
insert into 부가서비스 values(2, 2, '발신자번호표시', 1000);
insert into 부가서비스 values(3, 3, '캐치콜 서비스', 500);
insert into 부가서비스 values(4, 4, '컬러링', 1200);
이렇게 테이블 작성 및 데이터를 삽입시켰구요.(제가 임의로 만든 데이터들입니다.)
모든 가입자의 가입 내역을 보기 위해 이렇게 쿼리를 작성하여 검색하였습니다.
select 회원.회원명 as 회원명, 회원.주민등록번호 as 주민등록번호, 회원.주소 as 주소, 회원.전화번호 as 전화번호
,회원.전번가입 as '전화번호 가입일', 부가서비스.서비스종류 as 부가서비스명, 부가서비스.사용료 as 서비스사용료, 회원.서비스가입 as '부가서비스 가입일'
from 회원, 부가서비스
where 회원.가입서비스종류 = 부가서비스.가입서비스번호;
이렇게 하면 1~9번의 조건을 전부 만족하는 쿼리가 출력이 되긴합니다.
그러나 예를 들어 김태희라는 사용자가 999-8888로 문자 메시지와 발신자표시 두개를 가입하였다면, 데이터 출력이 부가서비스 하나의 차이 때문에 두줄로 나오게 됩니다. 나머지 데이터는 전부 같은데 부가서비스만 다르게 해서 두개가 나오죠.
위에서 제가 입력했던 데이터를 위와 같은 쿼리검색으로 출력하게 되면 사용자는 4명뿐이지만 쿼리는 총 9줄로 출력이 됩니다(김태희가 부가서비스나 전화번호가 다르다는 이유로 6개로 출력이 됩니다.)
조건만 만족한다 뿐이지 너무 비효울적인거 같아서요
제가 작성한 테이블이나 검색하는 쿼리문의 내용을 수정하여 간소화 시킬수 있는 방법이 없을까요?
물론 1~9의 조건은 다 만족하면서요.
예를 들어 현재 인원은 4명인데 김태희가 전화번호를 두개 쓰고 첫번째 번호의 부가서비스가 3개, 두번째 부가서비스가 2개라면, 두줄로만 출력되면서 첫번째 번호에 부가서비스가 3개표시, 두번째 번호에는 2개 표시 이런식으로요.
혹시나 제가 작성한 쿼리문에서 불필요한게 있어 삭제해도 상관없는게 있다면 그것도 좀 알려주셨으면 합니다.