[개미의 걸음 3과목 데이터베이스 일반] 처리① 쿼리 작성

쿼리 작성

특수 연산자를 이용한 질의

IN	* 필드의 값이 IN 연산자의 인수로 지정된 값과 같은 레코드만 검색하는 것 * OR연산자를 수행한 결과와 동일 * WHERE 필드/식 IN(값1, 값2)
BETWEEN	* 필드의 값이 Between 연산자의 범위로 지정된 값 이내에 포함되는 레코드만 검색하는 것 * AND연산자를 수행한 결과와 동일 * WHERE 필드/식 BETWEEN 값1 AND 값2
LIKE	* 대표 문자를 이용해 필드의 값의 일부와 일치하는 레코드만 검색 * WHERE 필드/식 LIKE '문자 패턴'
NOT	* 필드의 값이 NOT 다음에 기술한 연산자의 결과에 포함되지 않는 레코드만 검색 * WHERE 필드/식 NOT 다른 연산자

# 대표 문자

* : 모든 문자를 대표
% : 모든 문자를 대표
? : 한 자리 문자를 대표
_ : 한 자리 문자를 대표
# : 한 자리 숫자를 대표

 

조인

2개 이상의 테이블에 나눠 저장된 정보를 1개의 테이블처럼 사용하기 위해 열결하는 방법을 정의하는 것

• 조인에 사용되는 기준 필드의 데이터 형식은 동일하거나 호환되야 함
• 여러개 테이블 조인시, 필드 이름 앞에 테이블이름도 함께 기재하면 접근 속도가 향상됨
      ex> 테이블이름.필드이름
• 일반적으로는 테이블간의 관계가 설정되야하지만 관계가 설정되지 않아도 조인 수행 가능

조인의 종류

1. 내부 조인    [INNER JOIN]	가장 일반적인 조인 형태 관계가 설정된 두 테이블에서 조인될 필드가 일치하는 행만 포함
	SELECT 필드명 FROM 테이블명1 INNER JOIN 테이블명2                    ON 테이블명1.필드명 = 테이블명2.필드명
2. 왼쪽 외부 조인  [LEFT OUTER JOIN]	JOIN문의 왼쪽에 있는 칼럼을 기준으로 오른쪽 칼럼의 데이터를 데이터 존재여부와 관계없이 출력    * 오른쪽 칼럼에 데이터가 없는 경우 NULL로 출력    * 왼쪽 칼럼과 오른쪽 칼럼의 동일한 데이터는 데이터 값 표시    * 왼쪽 칼럼과 오른쪽 칼럼의 동일하지 않은 데이터는 오른쪽 칼럼에 NULL로 표시
	SELECT 필드명 FROM 테이블명1 LEFT JOIN 테이블명2                     ON 테이블명1.필드명 = 테이블명2.필드명
3. 오른쪽 외부 조인  [RIGHT OUTER JOIN]	JOIN문의 오른쪽에 있는 칼럼을 기준으로 왼쪽 칼럼의 데이터를 데이터 존재여부와 관계없이 출력    * 왼쪽 칼럼에 데이터가 없는 경우 NULL로 출력    * 오른쪽 칼럼과 왼쪽 칼럼의 동일한 데이터는 데이터 값 표시    * 오른쪽 칼럼과 왼쪽 칼럼의 동일하지 않은 데이터는 왼쪽 칼럼에 NULL로 표시
	SELECT 필드명 FROM 테이블명1 LEFT JOIN 테이블명2                     ON 테이블명1.필드명 = 테이블명2.필드명

질의 종류

크로스탭 질의	* 테이블의 특정 필드의 요약값을 표시하고 그 값들을 그룹별로 나열 * 열과 행 방향의 표 형태로 숫자 데이터의 집계를 구함 * 스프레드시트의 피벗 테이블과 유사 * 행머리글로 사용될 필드는 여러 개 지정 가능    열 머리글로 사용될 필드는 한 개만 지정 가능
매개변수 질의	* 쿼리를 실행하면 매개 변수를 입력 받을 수 잇는 대화상자가 나타나는 질의 * 매개변수 입력 대화상자에 검색조건으로 사용할 정보를 입력받아 질의 수행 * 2가지 이상의 정보를 물어보는 질의 디자인 가능 * 매개변수 대화상자에 표시할 텍스트는 매개변수를 적용할 필드의 조건 행에 대괄호로 묶어 입력
통합 질의	* 성격이 유사한 2개의 테이블이나 질의의 내용을 합쳐서 하나의 테이블을 만들기 위한 질의 * 같은 레코드는 한 번만 기록 * 두 테이블의 필드 수가 다르면 통합되지 않음

 

 

예제 문제 1

 

1. 쿼리 디자인 켜기

• [만들기 → 쿼리 → 쿼리 디자인] 클릭
• 지역정보와 통행요금 테이블 선택 

 

2. 조인된 필드가 일치하는 행만 포함

• <지역정보>테이블의 지역코드를 <통행요금>테이블의 출발지코드로 드래그
• 연결된 선을 더블 클릭
• 일치하는 행만 포함되야하므로 "두 테이블의 조인된 필드가 일치하는 행만 포함" 선택

※ 등록인원은 성명필드를 이용하고 등록인원이 없는 프로그램명도 출력하시오.

조인속성을 통해 '등록인원'필드의 테이블이 아닌 다른 테이블을 기준으로 조인한다!

 

 

3. 조건 작성

• 문제의 그림에 주어진 대로 영업소주소, 차종, 요금, 요금을 더블클릭해서 추가
• 영업소주소에 조건 입력
      → Like "*" & [주소의 일부를 입력하세요] & "*"
      → 해당 문자를 포함하면 되므로 Like와 *사용
      →문자열은 " "안에 작성하고 &로 연결
• 요금 필드 앞에 출퇴근할인요금 입력후 : 입력
      → 출력될 필드명 설정
      → 요금의 20%할인율을 적용했으므로 "[요금]*0.8"입력

 

3. 데이터 형식 변경

• 요금과 출퇴근할인요금 필드의 데이터 형식을 통화 형식으로 변경
• 데이터가 없을 때 0으로 표시할 경우, 데이터가 없는 테이블이 아닌 다른 테이블을 기준으로 조인한다는 의미!!

 

4. 실행

• 디자인 → 실행을 눌려서 해당 쿼리가 제대로 실행되는지 확인

 

5. 쿼리저장

• 좌측 상단에 닫기 버튼 눌리고 쿼리 이름을 "통행요금조회"로 설정한 뒤 확인 버튼 클릭

 

 

 

예제 문제 2

 

1. 쿼리 디자인 켜기

• 만들기 → 쿼리 → 쿼리 디자인 클릭
• 지역정보와 통행목록 테이블 선택

 

2. 조건 설정

• 디자인 → 표시/숨기기에서 요약 클릭
• 통행료 필드의 요약 정보를 합계로 변경
• 통행료 필드는 통행료합계로 표시되야하므로 "통행료합계:"을 앞에 입력
• 지역명은 정렬순서를 내립차순으로 선택

※ 입사요일별 직무역량 평균과 행동역량 평균을 구하여 정렬[월,화,수...]하시오.

• <직원관리>테이블과 <직원평가>테이블을 이용할 것[사번 필드로 쿼리 내에서 관계설정하시오.]
      Weekday,Choose 함수를 이용해 입사일에서 입사요일을 추출하시오.
      평균값은 소수1자리까지만 표시하시오.
• 디자인 → 표시/숨기기에서 요약 클릭
• 직무역량, 행동역량 필드명 설정
     직무역량 평균 : [직무역량]
     행동역량 평균 : [행동역량]
  두 필드의 요약 정보를 평균으로 변경
  두 필드의 속성시트에서 형식 및 소수점 자리수 설정
         형     식      : 고정
      소수점 자리수 : 1    
• 입사요일 필드명 설정
     입사요일: Choose(Weekday([입사일],2),"월요일","화요일","수요일","목요일","금요일","토요일","일요일")
• 입사요일 정렬을 위해 새로운 필드 생성
     필드 설정  →  Weekday([입사일],2)
       정   렬    → 오름차순
       표   시    → 체크 해제

 

 

3. 실행

• 디자인 → 실행을 눌려서 해당 쿼리가 제대로 실행되는지 확인

 

4. 쿼리저장

• 좌측 상단에 닫기 버튼 눌리고 쿼리 이름을 "지역별 합계"로 설정한 뒤 확인 버튼 클릭

 

쿼리 작성시 조건 예

• 학과가 '경영학과'이거나  '기계공학과'인 레코드만 표시
       → '학과' 필드의 조건에 조건 값 입력
       → 조건 : "경영학과" OR "기계공학과"
• 점수가 77점 이상인 레코드만 표시
       → '점수' 필드의 조건에 조건 값 입력
       → 조건 : >=77
• 상위 10개의 자료만 나오도록 설정하시오.

방법1. [디자인 → 쿼리설정]에서 반환을 10으로 설정

방법2. 쿼리의 속성시트에서 상위 값을 10으로 설정[쿼리 빈공간 마우스 오른쪽 클릭 → 속성]

 

 

크로스탭쿼리

• 테이블의 특정 필드의 요약값을 표시하고 그 값들을 그룹별로 나열
• 열과 행 방향의 표 형태로 숫자 데이터의 집계를 구함
  
• 스프레드시트의 피벗 테이블과 유사
• 행머리글, 열머리글, 값,조건에 해당하는 필드 지정
• 행 머리글로 사용될 필드는 여러 개 지정 가능
  열 머리글로 사용될 필드는 한 개만 지정 가능
• 조건에 해당하는 필드는 요약을 조건으로 설정
  값에 해당하는 필드는 요약을 구하려는 값(합계, 평균 등)으로 설정

※ 출력되는 평균 값은 정수 부분만 표시하시오.

• 해당 필드의 속성시트에서 설정
        형     식    :  표준
      소수 자릿수 : 0

 

매개변수쿼리

• 쿼리를 실행하면 매개 변수를 입력 받을 수 잇는 대화상자가 나타나는 질의
• 매개변수 입력 대화상자에 검색조건으로 사용할 정보를 입력받아 질의 수행
• 2가지 이상의 정보를 물어보는 질의 디자인 가능
• 매개변수 대화상자에 표시할 텍스트는 매개변수를 적용할 필드의 조건 행에 대괄호로 묶어 입력

※ 매개변수 값과 '구분'필드의 값이 일치하는 '구분','기자재명'필드만을 포함하는 질의 작성
    ["구분을 입력하시오" 메시지가 출력되도록 하시오]

• 구분 필드의 조건에 매개변수 값 입력
      → 조건에 [구분을 입력하시오]만 넣어주면 매개변수쿼리가 생성됨

※ 매개변수 값의 일부라도 일치하는 자료를 모두 검색하는 질의 작성
    ["부서명을 입력하시오" 메시지가 출력되도록 하시오]

• 부서명 필드의 값을 매개변수로 입력받으시오
      교번,이름,연락처,사무실위치필드만 출력하시오
• 부서명 필드 추가
      → 조건에 Like "*" & [부서명을 입력하시오] & "*"를 넣어주면 매개변수쿼리가 생성됨
      → 출력되는 필드가 아니므로 표시 체크를 해제

 

※ 8월에 해당하는 정보를 테이블로 넘기고 생성되는 테이블 이름을 '8월납품'으로 지정하시오.

• [디자인 → 쿼리 유형]에서 테이블 만들기 클릭
      테이블 이름을 '8월납품'으로 지정
• [디자인 → 결과]에서 실행 클릭
      조회할 값 : 8 입력

 

 

추가쿼리

※ '추가할 테이블' 내용을 '추가될 테이블'에 추가하시오. 
    [단, 추가될 테이블에 존재하지 않는 레코드만 추가도록 하시오.]
    [필드1을 이용해 중복여부를 판단하는 추가 쿼리를 만드시오.]

• [만들기 → 쿼리 → 쿼리 디자인] 선택
• [테이블 추가]에서 '추가할 테이블' 추가  ☆유의☆
• '추가할 테이블'의 모든 필드를 더블클릭하여 추가
• [디자인 → 쿼리유형 → 추가] 선택
• 테이블 이름에는 '추가될 테이블명' 입력  ☆유의☆
• 필드1의 조건에 not in (select 필드1명 from 추가될 테이블명)
• [디자인 → 결과 → 실행] 선택
• 추가 쿼리는 반드시 실행버튼을 눌러서 실행해 줘야함!!!