[프로그래머스] MySQL 코딩테스트(2) - SELECT편

1. 코딩테스트 연습 > SELECT > 조건에 맞는 도서 리스트 출력하기

문제 설명
다음은 어느 한 서점에서 판매중인 도서들의 도서 정보(BOOK) 테이블입니다.
BOOK 테이블은 각 도서의 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다.

문제
BOOK 테이블에서 2021년에 출판된 '인문' 카테고리에 속하는 도서 리스트를 찾아서 도서 ID(BOOK_ID), 출판일 (PUBLISHED_DATE)을 출력하는 SQL문을 작성해주세요.
결과는 출판일을 기준으로 오름차순 정렬해주세요.

예시
예를 들어 BOOK 테이블이 다음과 같다면

조건에 속하는 도서는 도서 ID 가 3, 4인 도서이므로 다음과 같습니다.

그리고 출판일 오름차순으로 정렬하여야 하므로 다음과 같은 결과가 나와야 합니다.

주의사항
PUBLISHED_DATE의 데이트 포맷이 예시와 동일해야 정답처리 됩니다.

 

내가 쓴 답

SELECT BOOK_ID, DATE_FORMAT(published_date,'%Y-%m-%d') AS PUBLISHED_DATE 
    FROM book WHERE YEAR(published_date) = '2021' AND category = '인문'
    ORDER BY published_date;

 

2. 코딩테스트 연습 > SELECT > 조건에 맞는 회원수 구하기

문제 설명
다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블입니다. USER_INFO 테이블은 아래와 같은 구조로 되어있으며 USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.

GENDER 컬럼은 비어있거나 0 또는 1의 값을 가지며 0인 경우 남자를, 1인 경우는 여자를 나타냅니다.

문제
USER_INFO 테이블에서 2021년에 가입한 회원 중 나이가 20세 이상 29세 이하인 회원이 몇 명인지 출력하는 SQL문을 작성해주세요.

예시
예를 들어 USER_INFO 테이블이 다음과 같다면

2021년에 가입한 회원 중 나이가 20세 이상 29세 이하인 회원은 USER_ID 가 1, 3, 5 인 회원들 이므로, 다음과 같이 결과가 나와야 합니다.

 

내가 쓴 답

SELECT COUNT(*) AS USERS FROM user_info
    WHERE YEAR(joined) = '2021' AND age between 20 and 29;

 

3. 코딩테스트 연습 > SELECT > 3월에 태어난 여성 회원 목록 출력하기

문제 설명
다음은 식당 리뷰 사이트의 회원 정보를 담은 MEMBER_PROFILE 테이블입니다. MEMBER_PROFILE 테이블은 다음과 같으며 MEMBER_ID, MEMBER_NAME, TLNO, GENDER, DATE_OF_BIRTH는 회원 ID, 회원 이름, 회원 연락처, 성별, 생년월일을 의미합니다.

문제
MEMBER_PROFILE 테이블에서 생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL문을 작성해주세요. 이때 전화번호가 NULL인 경우는 출력대상에서 제외시켜 주시고, 결과는 회원ID를 기준으로 오름차순 정렬해주세요.

예시
MEMBER_PROFILE 테이블이 다음과 같을 때

SQL을 실행하면 다음과 같이 출력되어야 합니다.

주의사항
DATE_OF_BIRTH의 데이트 포맷이 예시와 동일해야 정답처리 됩니다.

 

내가 쓴 답

SELECT MEMBER_ID, MEMBER_NAME, GENDER, 
       DATE_FORMAT(DATE_OF_BIRTH,'%Y-%m-%d') AS DATE_OF_BIRTH
    FROM MEMBER_PROFILE 
    WHERE SUBSTR(DATE_OF_BIRTH, INSTR(DATE_OF_BIRTH, '-')+1, 2) = '03' 
    AND GENDER = 'W' AND TLNO IS NOT NULL ORDER BY MEMBER_ID;

 

4. 코딩테스트 연습 > SELECT > Python 개발자 찾기

문제 설명
DEVELOPER_INFOS 테이블은 개발자들의 프로그래밍 스킬 정보를 담은 테이블입니다. DEVELOPER_INFOS 테이블 구조는 다음과 같으며, ID, FIRST_NAME, LAST_NAME, EMAIL, SKILL_1, SKILL_2, SKILL_3는 각각 ID, 이름, 성, 이메일, 첫 번째 스킬, 두 번째 스킬, 세 번째 스킬을 의미합니다.

문제
DEVELOPER_INFOS 테이블에서 Python 스킬을 가진 개발자의 정보를 조회하려 합니다. Python 스킬을 가진 개발자의 ID, 이메일, 이름, 성을 조회하는 SQL 문을 작성해 주세요.
결과는 ID를 기준으로 오름차순 정렬해 주세요.

예시
예를 들어 DEVELOPER_INFOS 테이블이 다음과 같다면

다음과 같이 Python 스킬을 가진 개발자의 정보가 결과에 나와야 합니다.

 

내가 쓴 답

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
    FROM DEVELOPER_INFOS
    WHERE SKILL_1 = 'Python' OR SKILL_2 = 'Python'OR SKILL_3 = 'Python'
    ORDER BY ID;

 

5. 코딩테스트 연습 > SELECT > 특정 형질을 가지는 대장균 찾기

문제 설명
대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.
다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다. ECOLI_DATA 테이블의 구조는 다음과 같으며, ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE 은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다.

최초의 대장균 개체의 PARENT_ID 는 NULL 값입니다.

문제
2번 형질이 보유하지 않으면서 1번이나 3번 형질을 보유하고 있는 대장균 개체의 수(COUNT)를 출력하는 SQL 문을 작성해주세요. 1번과 3번 형질을 모두 보유하고 있는 경우도 1번이나 3번 형질을 보유하고 있는 경우에 포함합니다.

예시
예를 들어 ECOLI_DATA 테이블이 다음과 같다면

각 대장균 별 형질을 2진수로 나타내면 다음과 같습니다.
ID 1 : 1000₍₂₎
ID 2 : 1111₍₂₎
ID 3 : 1₍₂₎
ID 4 : 1101₍₂₎
각 대장균 별 보유한 형질을 다음과 같습니다.
ID 1 : 4
ID 2 : 1, 2, 3, 4
ID 3 : 1
ID 4 : 1, 3, 4
따라서 2번 형질이 없는 대장균 개체는 ID 1, ID 3, ID 4 이며 이 중 1번이나 3번 형질을 보유한 대장균 개체는 ID 3, ID 4 입니다.
따라서 결과는 다음과 같아야 합니다.

 

내가 쓴 답

SELECT COUNT(*) AS COUNT
FROM (SELECT 
        GENOTYPE,
        IF(GENOTYPE IN (8, 12, 10, 9, 14, 13, 11, 15), 1, 0) AS genotype4,             
        IF(GENOTYPE IN (4, 6, 5, 12, 7, 13, 14, 15), 1, 0) AS genotype3, 
        IF(GENOTYPE IN (2, 3, 6, 10, 7, 11, 14, 15), 1, 0) AS genotype2,       
        IF(GENOTYPE IN (1, 3, 5, 9, 7, 11, 13, 15), 1, 0) AS genotype1
    FROM ECOLI_DATA) as subview
WHERE genotype2 = 'False' and ((genotype1 = 'True') or (genotype3 = 'True'));

코드 설명 : 해당 형질을 갖기위한 모든 경우의 수에 들어갈 경우 1, 그렇지 않을 경우 0으로 조건절을 사용