데이터베이스 문제

 

https://school.programmers.co.kr/learn/courses/30/lessons/59408

프로그래머스

DISTINCT: 중복 제거

SELECT count(DISTINCT NAME)
FROM ANIMAL_INS
WHERE NAME is not NULL

 

https://school.programmers.co.kr/learn/courses/30/lessons/59405

프로그래머스

LIMIT: 상위 1개의 데이터만 가져옴

• 오름차순 정렬 후 첫 번쨰 값

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1

 

https://school.programmers.co.kr/learn/courses/30/lessons/59037

프로그래머스

비교 연산

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION NOT IN ('Aged')

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NOT INTAKE_CONDITION = 'Aged'

 

https://school.programmers.co.kr/learn/courses/30/lessons/59404

프로그래머스

NAME 오름차순 정렬 후 같은 NAME의 경우 DATETIME 내림차순 정렬

SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME ASC, DATETIME DESC

 

https://school.programmers.co.kr/learn/courses/30/lessons/59047

프로그래머스

포함된 문자열 조건

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME like '%el%' and ANIMAL_TYPE='Dog'
ORDER BY NAME

 

https://school.programmers.co.kr/learn/courses/30/lessons/59410

프로그래머스

NULL 처리

1. case문을 이용해 처리 ➡️ NULL은 IS로 비교
2. IFNULL은 NULL인 경우 처리하는 함수

SELECT ANIMAL_TYPE, 
       case when NAME IS NULL then "No name"
       else NAME  end "NAME", 
       SEX_UPON_INTAKE
FROM ANIMAL_INS

SELECT ANIMAL_TYPE, IFNULL(NAME,'No name') AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS;

 

https://school.programmers.co.kr/learn/courses/30/lessons/59414

프로그래머스

DATE_FORMAT: 문자형 날짜에서 날짜 형식으로 변경

SELECT ANIMAL_ID, 
       NAME,
       DATE_FORMAT(DATETIME, '%Y-%m-%d') 날짜
FROM ANIMAL_INS

 

https://school.programmers.co.kr/learn/courses/30/lessons/131115

프로그래머스

함수 사용 x

SELECT *
FROM FOOD_PRODUCT
ORDER BY PRICE desc
LIMIT 1

함수 사용 o

select *
from food_product
where price = (select max(price)
              from food_product
              )

 

https://school.programmers.co.kr/learn/courses/30/lessons/131535

프로그래머스

between 사용

SELECT COUNT(*)
FROM USER_INFO
WHERE JOINED LIKE '%2021%' AND AGE BETWEEN 20 and 29

 

https://school.programmers.co.kr/learn/courses/30/lessons/131529

프로그래머스

substr 사용

SELECT substr(PRODUCT_CODE, 1, 2) CATEGORY,
       count(*) PRODUCTS
FROM PRODUCT
GROUP BY substr(PRODUCT_CODE, 1, 2)

 

https://school.programmers.co.kr/learn/courses/30/lessons/59044

JOIN 사용

프로그래머스

SELECT a.NAME, a.DATETIME
FROM ANIMAL_INS a LEFT JOIN ANIMAL_OUTS b ON a.ANIMAL_ID = b.ANIMAL_ID
WHERE b.ANIMAL_ID IS NULL
ORDER BY a.DATETIME
LIMIT 3

 

https://school.programmers.co.kr/learn/courses/30/lessons/144855

프로그래머스

SUM 사용

SELECT a.CATEGORY, SUM(b.SALES) TOTAL_SALES
FROM BOOK a LEFT JOIN BOOK_SALES b on a.BOOK_ID = b.BOOK_ID
WHERE b.SALES_DATE like '2022-01%'
GROUP BY 1
ORDER BY 1

 

https://school.programmers.co.kr/learn/courses/30/lessons/59411

프로그래머스

DATEDIFF: 두 날의 차이를 정수형으로 변경

SELECT I.ANIMAL_ID, I.NAME
FROM ANIMAL_INS I INNER JOIN ANIMAL_OUTS O ON I.ANIMAL_ID=O.ANIMAL_ID
ORDER BY DATEDIFF(O.DATETIME, I.DATETIME) desc
LIMIT 2

 

https://school.programmers.co.kr/learn/courses/30/lessons/151136

프로그래머스

ROUND: 소수점 반올림 함수

SELECT ROUND(AVG(DAILY_FEE), 0)
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = "SUV"

 

https://school.programmers.co.kr/learn/courses/30/lessons/131123

프로그래머스

서브쿼리

• GROUP BY와 MAX()를 동시에 사용할 때의 제약 GROUP BY와 함께 사용한 MAX(FAVORITES)는 각 그룹별 최대값을 계산하지만, 이 결과에서 다른 열(REST_ID, REST_NAME)을 바로 가져올 수는 없다

SELECT FOOD_TYPE, 
       REST_ID, 
       REST_NAME, 
       FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (SELECT FOOD_TYPE, MAX(FAVORITES)
                                 FROM REST_INFO
                                 GROUP BY 1
                                )
ORDER BY 1 DESC

 

https://school.programmers.co.kr/learn/courses/30/lessons/131536

프로그래머스

HAVING 절

SELECT USER_ID,
       PRODUCT_ID
FROM ONLINE_SALE
GROUP BY 1, 2
HAVING COUNT(PRODUCT_ID) > 1
ORDER BY 1, 2 DESC

 

https://school.programmers.co.kr/learn/courses/30/lessons/164670

프로그래머스

CONCAT: 문자열 합침

SELECT U.USER_ID,
       U.NICKNAME,
       CONCAT(U.CITY, ' ', U.STREET_ADDRESS1, ' ', U.STREET_ADDRESS2) "전체주소",
       CONCAT(SUBSTR(U.TLNO,1,3), '-', SUBSTR(U.TLNO,4,4), '-', SUBSTR(U.TLNO,8)) "전화번호"
FROM USED_GOODS_BOARD B
       INNER JOIN USED_GOODS_USER U
       ON B.WRITER_ID = U.USER_ID
GROUP BY 1
HAVING COUNT(B.WRITER_ID) >= 3
ORDER BY 1 DESC

 

https://school.programmers.co.kr/learn/courses/30/lessons/132204

프로그래머스

3개 테이블 JOIN

SELECT A.APNT_NO,
       P.PT_NAME,
       A.PT_NO,
       A.MCDP_CD,
       D.DR_NAME,
       A.APNT_YMD
FROM APPOINTMENT A
       INNER JOIN PATIENT P
       ON A.PT_NO = P.PT_NO
       INNER JOIN DOCTOR D
       ON A.MDDR_ID = D.DR_ID
WHERE A.MCDP_CD = 'CS'
       AND A.APNT_CNCL_YN = 'N'
       AND A.APNT_YMD LIKE '%2022-04-13%'
ORDER BY A.APNT_YMD

 

https://school.programmers.co.kr/learn/courses/30/lessons/157340

프로그래머스

방법 !

SELECT CAR_ID,
       CASE WHEN COUNT(
           CASE WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE THEN 1 END) > 0
           THEN '대여중'
           ELSE '대여 가능' END AVAILBALITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY 1
ORDER BY 1 DESC

 

https://school.programmers.co.kr/learn/courses/30/lessons/151138

프로그래머스

오늘 대여하고 오늘 반납해도 대여일이 하루라는 점 생각

SELECT HISTORY_ID,
       CAR_ID,
       DATE_FORMAT(START_DATE, '%Y-%m-%d') START_DATE,
       DATE_FORMAT(END_DATE, '%Y-%m-%d') END_DATE,
       CASE WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 30 THEN '장기 대여'
       ELSE '단기 대여' END RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE substr(START_DATE, 1, 7) = '2022-09'
ORDER BY HISTORY_ID DESC

 

https://school.programmers.co.kr/learn/courses/30/lessons/133027

프로그래머스

JOIN 전에 작업하고 JOIN하기

SELECT H.FLAVOR
FROM FIRST_HALF H
       INNER JOIN (SELECT SHIPMENT_ID, FLAVOR, SUM(TOTAL_ORDER) JULY_ORDER
                   FROM JULY
                   GROUP BY FLAVOR) J
       ON H.SHIPMENT_ID = J.SHIPMENT_ID
ORDER BY H.TOTAL_ORDER + J.JULY_ORDER DESC
LIMIT 3

 

https://school.programmers.co.kr/learn/courses/30/lessons/131537

프로그래머스

UNOIN

SELECT date_format(sales_date, '%Y-%m-%d') SALES_DATE,
       PRODUCT_ID,
       USER_ID,
       SALES_AMOUNT
FROM ONLINE_SALE
WHERE MONTH(SALES_DATE) = 3

UNION

SELECT date_format(sales_date, '%Y-%m-%d') SALES_DATE,
       PRODUCT_ID,
       NULL USER_ID,
       SALES_AMOUNT
FROM OFFLINE_SALE
WHERE MONTH(SALES_DATE) = 3

ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

 

https://school.programmers.co.kr/learn/courses/30/lessons/59413

프로그래머스

set

파이썬의 RANGE 처럼 변수로 만드는 방법

초기값을 -1로 두고 1씩 23번 더해가면 0에서 23까지 만들어짐

왼쪽 열을 그렇게 만들고 각각 묶어줌

SET @HOUR := -1;
SELECT (@HOUR := @HOUR + 1) AS HOUR, 
    (SELECT COUNT(*)
     FROM ANIMAL_OUTS
     WHERE HOUR(DATETIME) = @HOUR) AS COUNT
FROM ANIMAL_OUTS
WHERE @HOUR < 23;