SQL ) LV2. 부모의 형질을 모두 가지는 대장균 찾기
관련 개념
- 조인(JOIN)이란?
- 정의 : 두 릴레이션으로부터 조인 조건에 맞는 튜플을 결합하여 출력한다.
- 카티션 프로덕트(CARTESIAN PRODUCT)
- 두 릴레이션 튜플의 모든 조합을 출력
- 만약 A릴레이션의 튜플이 3개, B릴레이션의 튜플이 8개라면 카티션 프로덕트 결과는 24개 튜플이 출력된다.
- SELECT * FROM A, B; 형태로 사용
- 내부 조인(INNER JOIN)
- 조인이 가능한 키 값을 기준으로 NULL을 제외한 교집합 출력
- 동등 조인 : 두 릴레이션의 카티션 프로덕트 한 결과 중 조건에 맞는 튜플 출력
- 자연 조인 : 두 릴레이션의 동일한 타입과 이름의 속성이 존재한다면, 이를 기준으로 출력
- 외부 조인(OUTER JOIN)
- 조인이 가능한 키 값을 기준으로 NULL을 포함한 기준 테이플 튜플 출력
- LEFT OUTER JOIN : 왼쪽 테이블을 기준으로 모든 튜플이 출력(오른쪽 테이블에 매칭되는 튜플이 없다면 NULL 값이 들어감)
- RIGHT OUTER JOIN : 오른쪽 테이블을 기준으로 모든 튜플이 출력(왼쪽 테이블에 매칭되는 튜플이 없다면 NULL 값이 들어감)
접근법
- 자기 테이블을 카티션 프로덕트하여 모든 조합 중에서
- 테이블 E1의 부모ID가 테이블 E2의 ID가 같은 경우와
- 테이블 E1의 형질과 테이블 E2의 형질이 테이블 E2의 형질과 같은 경우만 조회한다.
코드
SELECT E1.ID, E1.GENOTYPE, E2.GENOTYPE PARENT_GENOTYPE
FROM ECOLI_DATA E1, ECOLI_DATA E2
WHERE (E1.PARENT_ID = E2.ID) && (E1.GENOTYPE & E2.GENOTYPE = E2.GENOTYPE)
ORDER BY E1.ID;
SQL ) LV2. 조건에 맞는 개발자 찾기
접근법
- 두 테이블을 카티션 조인 후, 스킬 이름이 Python 또는 C#이면서, 해당 코드와 개발자의 스킬 코드를 비트 연산했을 때, 0보다 큰 경우만 조회했다.
- 이때, 주의할 점은 Python과 C#모두 갖는 개발자의 경우 두 번 출력 될 수 있으므로 DISTINCT 연산을 적용해야한다.
코드
SELECT DISTINCT D.ID, D.EMAIL, D.FIRST_NAME, D.LAST_NAME
FROM DEVELOPERS D, SKILLCODES S
WHERE S.NAME IN ('Python', 'C#') && (D.SKILL_CODE & S.CODE > 0)
ORDER BY D.ID;
SQL ) LV2. 업그레이드 된 아이템 구하기
접근법
- 업그레이드가 될 아이템들은 결국 누군가의 PARENT_ITEM_ID에 해당한다.
- 따라서, 서브쿼리로 먼저 RARITY가 RARE인 데이터의 ITEM_ID를 가져오고, PARENT_ITEM_ID가 해당 ID에 해당하는 경우의 튜플을 조회했다.
- 이때, 두 테이블의 칼럼이 모두 필요하므로 JOIN연산 후 조회했다.
코드
SELECT F.ITEM_ID, ITEM_NAME, RARITY
FROM ITEM_INFO F JOIN ITEM_TREE T ON F.ITEM_ID = T.ITEM_ID
WHERE PARENT_ITEM_ID IN (SELECT ITEM_ID FROM ITEM_INFO WHERE RARITY = 'RARE')
ORDER BY 1 DESC;
SQL ) LV2. ROOT 아이템 구하기
접근법
- 문제에서 ROOT 아이템은 PARENT_ITEM_ID가 NULL인 아이템을 의미하므로, 해당 조건을 충족하는 튜플만 조회
코드
SELECT F.ITEM_ID, ITEM_NAME
FROM ITEM_INFO F JOIN ITEM_TREE T ON F.ITEM_ID = T.ITEM_ID
WHERE PARENT_ITEM_ID IS NULL
ORDER BY 1;
SQL ) LV2. 조건에 맞는 아이템들의 가격의 총합 구하기
관련 개념
- 집계함수 SUM
- 칼럼의 자료형이 숫자형일때만 사용 가능
- 테이블 칼럼의 합을 조회하고 싶을 때 사용
- 만약 값이 없을 경우 NULL이 출력되므로, 미리 NULL인 경우 0으로 처리 후 SUM연산을 수행
코드
SELECT SUM(PRICE) AS TOTAL_PRICE FROM ITEM_INFO WHERE RARITY = 'LEGEND';
SQL ) LV2. 조건에 맞는 도서와 저자 리스트 출력하기
접근법
- 조인 연산후 CATEGORY가 경제인 튜플만 조회
코드
SELECT BOOK_ID, AUTHOR_NAME, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK B JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE CATEGORY = '경제' ORDER BY 3;
SQL ) LV2. 재구매가 일어난 상품과 회원 리스트 구하기
관련 개념
- GROUP BY
- 데이터 조회 시 그룹으로 묶어서 조회할 때 사용
- HAVING
- 그룹화한 결과에 조건을 주고 싶을 때 사용
- 예를 들어, 그룹화된 칼
- WHERE와의 차이점?
- WHERE절은 GROUP BY 이전에 실행 되지만, HAVING은 GROUP BY 이후에 실행되기 때문에, 그룹화 후 조건을 부여할 때 사용한다.
- 또한, HAVING절에는 집계 함수를 사용할 수 있지만, WHERE절에는 집계 함수를 사용하지 못 한다.
접근법
- USER_ID와 PRODUCT_ID를 기준으로 그룹화후, 고유 칼럼 값인 ONLINE_SALE_ID 기준 COUNT했을때 2이상인 튜플만 조회
코드
SELECT USER_ID, PRODUCT_ID FROM ONLINE_SALE
GROUP BY 1, 2 HAVING COUNT(ONLINE_SALE_ID) >= 2 ORDER BY 1, 2 DESC;
SQL ) LV2. 3월에 태어난 여성 회원 목록 출력하기
접근법
- 여러 조건을 기반으로 데이터 출력(생월, 성별, 전화번호 유무)
코드
SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 3 AND GENDER = 'W' AND TLNO IS NOT NULL ORDER BY 1;
SQL ) LV2. 가격이 제일 비싼 식품의 정보 출력하기
접근법
- 가격순 내림차순 정렬후 가장 첫번째 행만 조회 ⇒ 가격이 가장 비싼 데이터
코드
SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT ORDER BY PRICE DESC LIMIT 1;
SQL ) LV2. 최솟값 구하기
접근법
- 보호시작일 DATETIME 최소 시간만 출력하면 되므로 MIN(DATETIME)만 조회하는 방법 또는 DATETIME기준 오름차순 정렬 후 LIMIT 1로 가장 첫번째 튜플의 DATETIME만 조회
코드
#답1
SELECT DATETIME AS '시간' FROM ANIMAL_INS ORDER BY DATETIME ASC LIMIT 1;
#답2
SELECT MIN(DATETIME) FROM ANIMAL_INS;
