✍🏻 programmers Lv1. MySQL -21 문제(24.10.16)

programmers Lv1. MySQL -21 문제(24.10.16)

9) 흉부외과 또는 일반외과 의사 목록 출력하기

• DOCTOR 테이블에서 진료과가 흉부외과(CS)이거나 일반외과(GS)인 의사의 이름, 의사ID, 진료과, 고용일자를 조회하는 SQL문을 작성하라. 이때 결과는 고용일자를 기준으로 내림차순 정렬하고, 고용일자가 같다면 이름을 기준으로 오름차순 정렬.

SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, "%Y-%m-%d") AS HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD = "CS" OR MCDP_CD = "GS"
ORDER BY HIRE_YMD DESC, DR_NAME ASC;

• DATE_FORMAT(date, format)

  • date : 포맷을 적용할 날짜 또는 datetime 값

  • format : 원하는 출력 형식

• 포맷 예시

  • %Y: 4자리 연도 (예: 2024)

  • %y: 2자리 연도 (예: 24)
  • %m: 2자리 월 (01에서 12까지, 예: 10)
  • %b: 약어로 된 월 이름 (예: Jan, Feb)
  • %M: 전체 월 이름 (예: January, February)
  • %d: 2자리 일 (01에서 31까지)
  • %H: 24시간 형식의 시 (00에서 23까지)
  • %i: 분 (00에서 59까지)
  • %s: 초 (00에서 59까지)

---

10) 역순 정렬하기

• 동물 보호소에 들어온 모든 동물의 이름과 보호 시작일을 조회하는 SQL문을 작성하라. 이때 결과는 ANIMAL_ID 역순으로 출력하라.

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC;

---

11) 조건에 맞는 회원수 구하기

• USER_INFO 테이블에서 2021년에 가입한 회원 중 나이가 20세 이상 29세 이하인 회원이 몇 명인지 출력

SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE AGE >=20 AND AGE <=29 AND JOINED LIKE '2021%'

• 문자열을 사용할 때는 항상 작은따옴표(')를 사용하는 것이 좋다. 이는 가독성을 높이고, SQL 표준에 맞추는 데 도움이 됨.

• ❗ 다른 사람 풀이

SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE AGE >=20 AND AGE <=29 AND YEAR(JOINED) = '2021%'

---

12) 이름이 없는 동물의 아이디

• 동물 보호소에 들어온 동물 중, 이름이 없는 채로 들어온 동물의 ID를 조회하는 sql문 작성

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL;

---

13) 인기있는 아이스크림

• 상반기에 판매된 아이스크림의 맛을 총주문량을 기준으로 내림차순 정렬하고 총주문량이 같다면 출하 번호를 기준으로 오름차순 정렬하여 조회하는 SQL 문을 작성

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC;

---

14) 12세 이하인 여자 환자 목록 출력하기

• PATIENT 테이블에서 12세 이하인 여자환자의 환자이름, 환자번호, 성별코드, 나이, 전화번호를 조회하는 SQL문을 작성. 이때 전화번호가 없는 경우, ‘NONE’으로 출력시켜 주시고 결과는 나이를 기준으로 내림차순 정렬하고, 나이 같다면 환자이름을 기준으로 오름차순 정렬

SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, 'NONE')
FROM PATIENT
WHERE GEND_CD LIKE 'W' AND AGE <=12 
ORDER BY AGE DESC, PT_NAME ASC;

• ❗ 처음 틀렸던 부분

SELECT PT_NAME, PT_NO, GEND_CD, AGE, TLNO
FROM PATIENT
WHERE GEND_CD LIKE 'W' AND AGE <= 12 AND IFNULL(TLNO, 'NONE')
ORDER BY AGE DESC, PT_NAME ASC;

• 다음과 같이 where절에 사용했는데 where 절에서의 조건은 반드시 true 또는 false여야 하는데 IFNULL(TLNO, 'NONE')는 TLNO가 NULL이 아닐 경우 그 값을 반환하고, NULL일 경우 'NONE'을 반환한다.

• 이것은 항상 true 또는 false로 반환되지 않으므로 오류가 발생한다.

---

15) 조건에 맞는 도서 리스트 출력하기

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

SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE,"%Y-%m-%d") AS PUBLISHED_DATE
FROM BOOK
WHERE CATEGORY LIKE '인문' AND PUBLISHED_DATE LIKE '2021%'

---

16) 과일로 만든 아이스크림 고르기

• 상반기 아이스크림 총주문량이 3,000보다 높으면서 아이스크림의 주 성분이 과일인 아이스크림의 맛을 총주문량이 큰 순서대로 조회하는 SQL 문을 작성

SELECT A.FLAVOR
FROM FIRST_HALF A
JOIN ICECREAM_INFO B ON A.FLAVOR = B.FLAVOR
WHERE A.TOTAL_ORDER >3000 AND B.INGREDIENT_TYPE LIKE 'fruit_based'
ORDER BY A.TOTAL_ORDER DESC;

• 두 개의 테이블을 연결해야 되니 join을 해야 됨. FIRST_HALF와 ICECREAM_INFO 테이블을 join으로 결합함.

• 연결 기준은 어떻게 될까?

  • ON A.FLAVOR = B.FLAVOR는 FIRST_HALF 테이블의 FLAVOR 열과 ICECREAM_INFO 테이블의 FLAVOR 열이 같은 행을 서로 연결하겠다는 의미임.

---

17) 평균 일일 대여 요금 구하기

• CAR_RENTAL_COMPANY_CAR 테이블에서 자동차 종류가 ‘SUV’인 자동차들의 평균 일일 대여 요금을 출력하는 SQL문을 작성. 이때 평균 일일 대여 요금은 소수 첫 번째 자리에서 반올림하고, 컬럼명은 AVERAGE_FEE 로 지정

SELECT ROUND(AVG(DAILY_FEE)) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE LIKE 'SUV';

• 소수점 반올림 : ROUND()

  • SELECT ROUND(1234.56789 ,-1) FROM DUAL // 1230

• 숫자의 특정 자리수 이하 버림 : TRUNCATE()

---

18) 모든 레코드 조회하기

• 동물 보호소에 들어온 모든 동물의 정보를 ANIMAL_ID순으로 조회하는 SQL문을 작성

SELECT *
FROM ANIMAL_INS

---

19) 특정 옵션이 포함된 자동차 리스트 구하기

• CAR_RENTAL_COMPANY_CAR 테이블에서 ‘네비게이션’ 옵션이 포함된 자동차 리스트를 출력하는 SQL문을 작성. 자동차 ID를 기준으로 내림차순 정렬

SELECT *
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC;

---

20) 잡은 물고기 중 가장 큰 물고기의 길이 구하기

• FISH_INFO 테이블에서 잡은 물고기 중 가장 큰 물고기의 길이를 ‘cm’ 를 붙여 출력하는 SQL 문을 작성. 컬럼명은 MAX_LENGTH로 지정

SELECT CONCAT(MAX(LENGTH), 'cm') AS MAX_LENGTH
FROM FISH_INFO

• CONCAT() : 여러 문자열을 하나의 문자열로 합칠 때 사용하는 함수

  • CONnect (연결) + cat = 고양이가 귀엽게 연결되어있는 거 생각 😸

---

21) 잔챙이 잡은 수 구하기

• 잡은 물고기 중 길이가 10cm 이하인 물고기의 수를 출력하는 SQL 문을 작성. 컬렴명 FISH_COUNT로 지정

SELECT COUNT(ID) AS FISH_COUNT
FROM FISH_INFO
WHERE LENGTH IS NULL;

---

22) Python 개발자 찾기

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

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

---

23) 최대값 구하기

• 가장 최근에 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성

SELECT DATETIME AS '시간' 
FROM ANIMAL_INS
ORDER BY DATETIME DESC LIMIT 1;

---

24) 한 해에 잡은 물고기 수 구하기

• FISH_INFO 테이블에서 2021년도에 잡은 물고기 수를 출력하는 SQL 문을 작성. 컬럼명은 FISH_COUNT로 지정

SELECT COUNT(ID) AS FISH_COUNT
FROM FISH_INFO
WHERE YEAR(TIME) LIKE '2021';

---

25) 잡은 물고기의 평균 길이 구하기

• 잡은 물고기의 평균 길이를 출력하는 SQL문을 작성. 평균 길이를 나타내는 컬럼 명은 AVERAGE_LENGTH로, 평균 길이는 소수점 3째자리에서 반올림하며, 10cm 이하의 물고기들은 10cm 로 취급하여 평균 길이 구하기

SELECT ROUND(AVG(IFNULL(LENGTH,10)),2) AS AVERAGE_LENGTH
FROM FISH_INFO

---

26) 자동차 대여 기록에서 장기/단기 대여 구분하기

• CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 ‘장기 대여’ 그렇지 않으면 ‘단기 대여’ 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성. 대여 기록 ID를 기준으로 내림차순 정렬

SELECT HISTORY_ID, 
        CAR_ID, 
        DATE_FORMAT(START_DATE,'%Y-%m-%d') AS START_DATE, 
        DATE_FORMAT(END_DATE,'%Y-%m-%d') AS END_DATE, 
CASE 
    WHEN DATEDIFF(END_DATE, START_DATE) < 29 THEN '단기 대여'       ELSE '장기 대여'
END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09%'
ORDER BY HISTORY_ID DESC;

• CASE : 조건문을 시작하는 키워드. 여러 조건을 평가하고, 조건이 맞는 경우에 해당하는 값을 반환

• WHEN : 조건을 지정하는 부분

  • DATEDIFF(END_DATE, START_DATE)는 두 날짜 사이의 일수 차이를 계산하는 함수

• THEN : 조건이 참일 경우 반환할 값을 지정

• ELSE : 모든 조건이 거짓일 경우 반환할 값을 지정

• END : CASE문이 끝났음을 나타냄

---

27) 가장 큰 물고기 10마리 구하기

• FISH_INFO 테이블에서 가장 큰 물고기 10마리의 ID와 길이를 출력하는 SQL 문을 작성. 결과는 길이를 기준으로 내림차순 정렬하고, 길이가 같다면 물고기의 ID에 대해 오름차순 정렬.

SELECT ID, LENGTH
FROM FISH_INFO
ORDER BY LENGTH DESC, ID LIMIT 10;

---

28) 조건에 부합하는 중고거래 댓글 조회하기

• USED_GOODS_BOARD와 USED_GOODS_REPLY 테이블에서 2022년 10월에 작성된 게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일을 조회하는 SQL문을 작성. 결과는 댓글 작성일을 기준으로 오름차순 정렬해주시고, 댓글 작성일이 같다면 게시글 제목을 기준으로 오름차순 정렬.

• 틀렸던 답안

SELECT A.TITLE, A.BOARD_ID, B.REPLY_ID, B.WRITER_ID, B.CONTENTS, DATE_FORMAT(B.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD A JOIN USED_GOODS_REPLY B ON A.BOARD_ID = B.BOARD_ID
WHERE B.CREATED_DATE LIKE '2022-10%'
ORDER BY B.CREATED_DATE ASC, A.TITLE ASC;

• 문제를 잘 읽어보면 2022년 10월에 작성된 게시글의 제목 ~ 이라는 말이 있기 때문에 B.CREATED_DATE가 아니라 A.CREATED_DATE이다. 그리고 정렬은 댓글 작성일을 기준이므로 B.CREATED_DATE가 맞다. 저거를 못봐서 한참을 틀린 문제..

• 수정 후 답안

SELECT A.TITLE, A.BOARD_ID, B.REPLY_ID, B.WRITER_ID, B.CONTENTS, DATE_FORMAT(B.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD A 
JOIN USED_GOODS_REPLY B ON A.BOARD_ID = B.BOARD_ID
WHERE A.CREATED_DATE LIKE '2022-10%'
ORDER BY B.CREATED_DATE ASC, A.TITLE ASC;

---

29) 특정 형질을 가지는 대장균 찾기

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

SELECT COUNT(ID) AS 'COUNT'
FROM ECOLI_DATA
WHERE GENOTYPE & 2 = 0
    AND (GENOTYPE & 4 = 4 OR GENOTYPE & 1 = 1);
    
# 2: 0010 - ID : 2탈락
# 4: 0100 - ID 4,  1: 0001 - ID 3

• 2를 비트로 표현하면 : 0010임.

• GENOTYPE 과 &(and연산)을 하면 비교하는 것과 일치하는 경우에만 허용된다는 의미. GENOTYPE & 2 = 0 는 0010과 비교해서 0이되는 것만 허용한다는 의미. 따라서 ID 2는 1111이므로 1111 & 0010 = 1이므로 제외된다.

• GENOTYPE & 4에서 4는 0100이므로 GENOTYPE과 0100을 비교해서 0100의 값이 허용되는 것을 살펴보면 ID 4 = 1101 & 0100 = 0100(4) 므로 ID 4만 허용된다.

• GENOTYPE & 1 =1 에서 1은 0001이므로 GENOTYPE과 0001을 비교해서 0001의 값이 허용되는 것을 살펴보면 ID 3 = 0001 & 0001 = 0001(1) 이므로 ID 1만 허용된다.