[데이터베이스] 4주차 강의 요약

부속질의

SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의

 

• 부속 질의문(서브 질의문): 다른 SELECT 문 안에 들어 있는 SELECT 문
  • 괄호로 묶어서 작성
  • ORDER BY 절 사용 불가
  • 단일 행 부속 질의문: 하나의 행을 결과로 반환
  • 다중 행 부속 질의문: 하나 이상의 행을 결과로 반환
• 부속 질의문을 먼저 수행하고, 그 결과를 이용해 상위 질의문 수행
  • 예시: 도서를 구매한 적이 있는 고객의 이름을 조회하려면?
    • Orders 테이블에서 주문 내역 확인
    • 주문한 고객의 고객번호 추출
    • 해당 고객번호로 Customer 테이블에서 고객 이름 조회
• 부속 질의문과 상위 질의문을 연결하는 연산자 필요
  • 단일 행 부속 질의문: 비교 연산자(=, <>, >, >=, <, <=) 사용 가능
  • 다중 행 부속 질의문: 일반 비교 연산자 사용 불가
    • 연산자 사용 관련 고려 필요

 

 

상관(correlated, 연결) 부속질의

 

• 부속질의 간에 상하 관계가 존재함, 상위 질의와 하위 질의가 독립적이지 않고 서로 관련을 맺고 있음
• 상위 쿼리와 하위 쿼리는 서로 의존적이고 상위 쿼리의 특정 행 값이 하위 쿼리의 조건에 사용됨
• 일반적인 부속질의와 달리 행 단위로 반복 실행됨

 

• 집합 연산 (UNION, MINUS, INTERSECT)
  • SQL 문의 결과는 테이블 형태로 나타남
  • 테이블 간의 집합 연산을 통해 합집합, 차집합, 교집합을 구할 수 있음
• UNION
  • 두 결과 집합의 합집합을 구하는 연산
  • 예시: Customer 테이블에서
    • 대한민국에 거주하는 고객 이름 집합
    • 도서를 주문한 고객 이름 집합
    • 두 집합의 합집합을 구함
  •  개념 표현
    • 고객 이름 = 대한민국 거주 고객 이름 ∪ 도서를 주문한 고객 이름
• 집합 연산 (MINUS, INTERSECT → NOT IN, IN)
  • 일부 DBMS에서는 MINUS, INTERSECT 연산자를 제공함
  • MySQL에서는 MINUS, INTERSECT 연산자를 지원하지 않음
  • 대신 다음과 같은 연산자를 사용함!
    • NOT IN: 차집합(MINUS)과 유사한 기능
    • IN: 교집합(INTERSECT)과 유사한 기능

 

• EXISTS와 NOT EXISTS
  • 상관 부속질의 형태로 사용되는 연산자, 부속질의 결과의 존재 여부를 확인함
  • EXISTS
    • 부속질의가 한 행이라도 반환하면 TRUE
  • NOT EXISTS
    • 부속질의가 아무 행도 반환하지 않으면 TRUE 

      

• 구식 JOIN (FROM ~ WHERE)
  • 논리적 의미: 카티션 프로덕트 후 WHERE 절로 필터링
  • 현대 DBMS 처리 방식: 옵티마이저가 내부적으로 JOIN으로 변환하여 최적화
  • 성능: JOIN과 동일, 차이 없음
  • 차이점: 가독성, 협업, 유지보수 측면에서 명시적인 JOIN 사용을 권장
  • 구식 문법은 호환성을 위해 유지됨
• 부속질의
  • 특징: 메인 쿼리 안에서 독립적으로 실행되는 쿼리
  • 최적화: 대부분 옵티마이저가 JOIN으로 변환 가능, 성능 차이 없음
  • 사용 이유: 특정 조건을 직관적으로 표현할 때 유용하나, JOIN 사용 권장
• 상관질의
  • 특징: 메인 쿼리의 각 행마다 서브쿼리가 반복 실행됨
  • 최적화: 경우에 따라 JOIN으로 변환되지만 항상 가능한 것은 아님 → 최적화가 안 되면 성능 차이 발생 가능
  • 성능: 최적화가 잘 되면 JOIN과 동일, 그렇지 않으면 반복 실행으로 성능 저하 가능
  • 사용 이유: EXISTS, NOT EXISTS 같은 조건을 직관적으로 표현할 때 자주 사용

 

---

4. 데이터 정의어

CREATE TABLE

• 테이블을 생성하는 명령문
• 속성과 속성에 대한 제약 조건 정의
  • NULL
  • NOT NULL
  • DEFAULT
• 기본키(Primary Key) 및 외래키(Foreign Key) 정의 가능
• 문법

• 외래키 지정 시
  • 참조 무결성 제약조건 유지를 위해 참조되는 테이블의 투플 삭제 시 처리 방법을 지정하는 옵션
  • ON DELETE CASCADE: 관련 투플을 함께 삭제
  • ON DELETE SET NULL: 관련 투플의 외래키 값을 NULL로 변경
  • ON DELETE RESTRICT: 부모 행이 참조되고 있으면 삭제/수정 불가 (기본값)
  • ON DELETE NO ACTION: SQL 표준 키워드로 RESTRICT와 동일

ALTER TABLE

• 생성된 테이블의 속성 변경 및 속성에 관한 제약을 변경하며 , 기본키 및 외래키를 변경함
• 문법

 

DROP TABLE

 

• 테이블을 삭제하는 명령어로, 테이블의 구조와 데이터를 모두 삭제하므로 사용 시 주의
• 데이터만 삭제하려면 DELETE 문 사용
• 삭제할 테이블을 참조하는 테이블이 있으면 삭제 수행 불가
• 해결 방법
  • 해당 테이블을 참조하는 테이블을 먼저 삭제
  • 또는 관련된 외래키 제약조건을 먼저 삭제

---

5. 데이터 조작어 - 삽입, 수정, 삭제

INSERT문

• 테이블에 새로운 튜플을 삽입하는 명령
• 문법

• INTO 키워드와 함께 투플을 삽입할 테이블 이름과 속성 이름을 나열 (생략 가능)
  • 속성 리스트를 생략하면 테이블 정의 시 지정된 속성 순서대로 값이 삽입됨
  • VALUES 키워드와 함께 삽입할 속성 값들을 나열
  • 속성의 순서는 변경 가능
  • 단, INTO 절의 속성 이름과 VALUES 절의 값은 순서대로 일대일 대응되어야 함
  • SELECT 문을 사용하여 삽입할 수도 있음

 

UPDATE문

• 특정 속성값을 수정하는 명령
• UPDATE 문에서 다른 테이블의 속성값을 이용할 수도 있음
• 문법

DELETE문

• 테이블에 있는 기존 투플을 삭제하는 명령
• 문법

 

---

문제 풀이

1. 고양이와 개는 몇 마리 있을까?

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요.

SELECT ANIMAL_TYPE, count(*) as count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC

• 고양이와 개의 수를 세어 고양이가 개보다 먼저 나오도록 조회해야 하는 문제이다.
• 먼저 SELECT로 ANIMAL_TYPE 컬럼과 데이터 전체 개수를 세는 COUNT(*)를 이용하여 수를 세자.
• 그리고 ANIMAL_TYPE에 있는 값별로 집계해야하므로 GROUP BY를 이용해보자.
• 마지막으로 고양이가 먼저 나와야 하므로(오름차순 A-Z) ORDER BY ASC로 정렬하면 된다!

 

2. 입양 시각 구하기(1)

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

SELECT HOUR(DATETIME) AS HOUR, COUNT(DATETIME) AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME)<=19
GROUP BY HOUR
ORDER BY HOUR

• 출력 예시를 보면 HOUR, COUNT로 되어있다는 것을 참고하자.
• DATETIME이 "2024-06-28 13:40:00"과 같은 형식으로 되어있으므로 DATETIME에서 시간만 가져오기 위해 HOUR 함수를 사용한다.
• 9:00부터 19:59까지의 값을 출력하기 위해 WHERE을 이용해 범위를 설정해준다. 참고로, 그냥 HOUR이라고 쓰면 에러가 난다!
• 시간대별 개수를 출력하기 위해 GROUP BY를 사용한다.
• 시간대순 정렬을 위해 ORDER BY를 사용한다.

 

3. NULL 처리하기

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

입양 게시판에 동물 정보를 게시하려 합니다. 동물의 생물 종, 이름, 성별 및 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 프로그래밍을 모르는 사람들은 NULL이라는 기호를 모르기 때문에, 이름이 없는 동물의 이름은 "No name"으로 표시해 주세요.

SELECT ANIMAL_TYPE, IFNULL(NAME, "No name") AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

• 이름이 없는 동물은 "No name"으로 표시해야하기 때문에 IFNULL(컬럼명, "NULL일 경우 쓸 값")을 사용해 No name을 표시해준다.
• ORDER BY를 통해 오름차순으로 정렬해주었다.

 

4. DATETIME에서 DATE로 형 변환

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

ANIMAL_INS 테이블에 등록된 모든 레코드에 대해, 각 동물의 아이디와 이름, 들어온 날짜1를 조회하는 SQL문을 작성해주세요. 이때 결과는 아이디 순으로 조회해야 합니다.

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, "%Y-%m-%d") AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

• DATETIME형을 DATE로 바꾸는 법은 DATE_FORMAT(컬럼명, 형식)을 사용하면 된다.

 

5. 중성화 여부 파악하기

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요.

SELECT ANIMAL_ID, NAME,
IF(SEX_UPON_INTAKE LIKE 'Neutered%' OR SEX_UPON_INTAKE LIKE 'Spayed%', 'O', 'X') as 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

• IF문을 이용해 중성화된 동물(Neutered 또는 Spayed가 포함된) 행은 O, 그렇지 않으면 X로 표시할 수 있다.