[데이터베이스] 컴활 실기 문제 풀자 (크로스탭 쿼리, 쿼리 마법사, 불일치)

 

(해당 문제 출처 : https://m.cafe.daum.net/hermingway/ZYwR/39?listURI=%2Fhermingway%2FZYwR )

 

데이터베이스 실기문제를 계속해서 풀어보자.

 

 

쿼리 작성 문제

조회할 테이블 : <도서>

조건 : 2021년 1월 1일 이후

쿼리이름 : <신간도서정보>

 

[만들기]->[쿼리 마법사] 실행

 

 

테이블 표시 창에서 <도서>만 추가

 

문제에서 요구하는 필드들을 차례대로 선택

 

출간일자에 요구되는 조건을 다음과 같이 입력

실제로 결과가 일치하는지 확인 후,

 

신간도서정보로 저장

 

 

===

 

"매개 변수 값 입력"을 조건으로 넣기

대여 횟수의 합계, 대여일자의 최대값 구해서 나타내는 문제

 

테이블 표시 에서 갖고올 대상이 "쿼리"이므로

[쿼리] 탭 선택 후,

<대여내역관리> 선택

 

문제에서 요구하는 필드 차례대로 나열

 

GROUP BY (그룹화할 필드)와

통계 MAX(최대), COUNT(개수)에 대한

쿼리를 [SQL보기]에서 만든다.

SELECT 

                대여내역관리.고객명, 대여내역관리.전화번호, 
                COUNT(대여내역관리.일련번호) AS `대여횟수`, 
                MAX(대여내역관리.대여일자) AS `최근대여일자`

FROM 
                대여내역관리
GROUP BY 
                대여내역관리.고객명, 대여내역관리.전화번호

 

 

쿼리가 의도한대로 잘나오고 있다.

 

그리고 매개변수를 입력하게끔 하려면

해당 필드의 [조건]을

[메세지박스의 내용]

의 형태로 입력

 

테스트 해보니 잘 되고 있다.

바로 저장

 

===

쿼리 마법사 (불일치) 이용하기

 

[만들기]->[쿼리 마법사] 사용

 

새 쿼리 창에서 "불일치 검색 쿼리 마법사" 선택

 

나타낼 결과 필드는 "도서"이므로

테이블 : 도서 선택

 

관계있는 테이블은 "대여내역"이므로

테이블: 대여내역 선택

 

비교할 필드는 "도서코드"이므로 각 테이블의 "도서코드"를 선택

 

 

나타낼 필드 선택 후

쿼리 이름 지정 후 완료

 

테스트 결과 잘 나오고 있다.

 

 

===

 

크로스텝 쿼리 만들기

 

테이블 표시에 넣을 것 선택

 

 

현재 쿼리는 다음과 같이 돼있는데,

SELECT 
              *
FROM 
              도서 
              INNER JOIN 
                            (고객 
                            INNER JOIN 
                                          대여내역 
                                          ON 고객.고객번호 = 대여내역.고객번호
                            ) 
                            ON 도서.도서코드 = 대여내역.도서코드

 

 

이를 필드별로 설정하고

그룹화 해서 다음과 같이 나타냈다.

SELECT 
              도서.도서코드,
              Avg(고객.나이) As 평균나이,
              Sum(
                            Switch(
                                          고객.성별 = '남' , 1,
                                          고객.성별 = '여' , 0
                            )
              ) as 남,
              Sum(
                            Switch(
                                          고객.성별 = '남' , 0,
                                          고객.성별 = '여' , 1,
                            )
              ) as 여
FROM 
              도서 
              INNER JOIN 
                            (고객 
                            INNER JOIN 
                                          대여내역 
                                          ON 고객.고객번호 = 대여내역.고객번호
                            ) 
                            ON 도서.도서코드 = 대여내역.도서코드
GROUP BY
              도서.도서코드

 

Sum와 Switch를 사용해서

고객.성별의 값을 남과 여로 구분지어

나타낸 것을 참고한다.

 

위의 방법을 써서

나타내면

이렇게 되는데 감점요인이 많다.

(첫번째는 0건일 때엔 나타나지 말아야하는 점

두번째는 일련번호 필드를 이용해 [남], [여]의 건수를 나타낼 것)

그리고 가장 중요한 게 크로스쿼리를 나타내야하는 것

 

 

 

===============

다시 풀어야겠다.

 

남과 여는 [성별]필드로 [크로스탭]->[열 머리글]로

지정해주고 [일련번호] 필드를 통해 개수로 통계

한편 [도서코드] 필드는 [크로스탭]->[행 머리글]로 설정해주고

나이평균 역시 [행 머리글]로 설정해준다.

 

 

그리고 나이 평균을 [평균나이]로 캡션 지정해주고

각 열의 서식을 바꿔준다.

 

테스트 화면이 이상없으면 저장해준다.

 

 

* 추가 * 

 

RIGHT(도서.도서코드, 1) BETWEEN '1' AND '5'

위 조건도 넣어줘야 한다.