sugenius

사전 준비 

java 17 이상, IDE (IntelliJ or Eclipse) 설치 

* Spring Boot 3 ~ 은 JDK 17 이상 사용해야한다.

프로젝트 생성

스프링 부트 스타터 사이트로 이동해서 스프링 프로젝트 생성

Spring Initializr

- Project : Gradle/Maven 

필요한 라이브러리를 가져오고 빌드 하는 라이플사이클 까지 도와주는 툴 

과거에는 Maven , 요즘 추세는 Gradle 많이 씀 

- Spring Boot 

SNAPSHOT : 아직 만들고 있는 버전 

M2 : 아직 정식 Release 된 버전이 아님 

따라서 뒤에 영어가 붙어 있지 않은 정식버전의 최신 버전을 사용하도록 함

- Project Metadata 

Group : 도메인 

Artifact : 빌드 결과물 (프로젝트명) 

- Dependencies 

Spring Web : 웹 프로젝트

Thymeleaf  : HTML 을 만들어주는 템플릿 엔진 (프리마커 등 다른 엔진을 사용하기도함) 

인텔리제이에서 다운받은 프로젝트의 build.gradle 파일을 선택하여 open

- 프로젝트 구조 잠깐 설명 

src
ㄴ main 
ㄴㄴ java 
ㄴㄴ resources
ㄴtest // 테스트 코드 관련 소스가 담김
build.gradle // 

-dependencies : 라이브러리 들
spring initializr 에서 받은 Spring Web ,Thymeleaf 확인 가능 
implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
implementation 'org.springframework.boot:spring-boot-starter-web'

디렉토리 구조가 이상하게 보인다면 보기 편하게 변경 하기

Compact Middle Packages 선택

main 함수 run 시킨 후 (스프링 애플리케이션 실행)

Tomcat started on port 8080 (http) with context path '' 로그 확인 

그리고 웹 브라우저 확인 localhost:8080 되면 최초 환경 설정 완료 (에러 페이지 화면 확인) 

- IntelliJ Gradle 대신에 자바 직접 실행

최근 Intellij 버전은 Gradle을 통해 실행하는 것이 기본 설정이고 이렇게 되면 실행속도가 느림

그럴 경우 두 가지 옵션 변경 해줌, 그러면 gradle 을 통하지 않고 java 를 띄워버려 빠르게 진행됨 

윈도우 : File Setting > Build Tools Gradle

Build and run using : IntelliJ IDEA

Run tests using : IntelliJ IDEA 

IntelliJ JDK 설치 확인

라이브러리 살펴보기 

Gradle은 의존관계가 있는 라이브러리를 함께 다운로드 함

스프링 부트 라이브러리

- spring-boot-starter-web 

ㄴspring-boot-starter-tomcat : 톰캣 (웹서버) 

ㄴspring-webmvc : 스프링 웹 MVC 

- spring-boot-starter-thymeleaf : 타임리프 템플릿 엔진(View) 

- spring-boot-starter(공통) : 스프링 부터 + 스프링 코어 + 로깅 

ㄴspring-boot

ㄴㄴspring-core 

ㄴspring-boot-starter-logging

ㄴㄴlogback, slf5j 

테스트 라이브러리

spring-boot-starter-test 

ㄴjunit : 테스트 프레임워크 

ㄴ mockito : 목 라이브러리

ㄴ assertj : 테스트 코드를 좀 더 편하게 작성하게 도와주는 라이브러리 

ㄴ spring-test : 스프링 통합 테스트 지원

View 환경설정

resources/static/index.html 등록하면 자동으로 웰컴 페이지로 등록됨

(경로에 맞춰 index.html 파일 생성) 

스프링 부트가 제공하는 Welcome Page 기능 설명 확인 

https://docs.spring.io/spring-boot/docs/current/reference/html/web.html#web

thymeleaf 템플릿 엔진 

공식 사이트 :   Thymeleaf 

스프링 공식 튜토리얼 : Getting Started | Serving Web Content with Spring MVC 

스프링 부트 메뉴얼 : Spring Boot Features

+ 외에도 사용가능한 템플릿 엔진 문서 확인 가능 

-controller/helloController 추가 

-resources/templates/hello.html 추가

* 참고: spring-boot-devtools 라이브러리를 추가하면, html 파일을 컴파일만 해주면 서버 재시작 없이 View 파일 변경이 가능하다 

빌드하고 실행하기 

윈도우 환경 : cmd 

나는 현재 인텔리제이 터미널 위치에서 함. 

./gradlw
cd build\libs
java -jar hello-spring-0.0.1-SNAPSHOT.jar

./gradlew clean
./gradlew clean build

프로젝트 위치에서 파일 목록 확인

"./gradlw" 명령으로 gradlew.bat 실행  >> gradlew Bduile 완료

빌드 완료 확인 후 

"cd build\libs" 으로 이동 후 ~.jar 파일 확인

"java -jar hello-spring-0.0.1-SNAPSHOT.jar" 명령 하면 스프링이 뜨고, 주소 이동시 화면 확인 가능 

이렇게 서버를 띄워두면 앱을 동시에 8080 포트를 열 수 없다. 
이와 같이 서버 배포를 할땐 java -jar ~.jar 로 실행을 시키게 되면 스프링이 동작하게 된다.

./gradlew clean

하면 build 폴더 자체가 없어짐 

잘 안된다면 

./gradlew clean build

https://school.programmers.co.kr/learn/challenges?tab=algorithm_practice_kit 

https://school.programmers.co.kr/learn/challenges?tab=algorithm_practice_kit 

<스택/큐>
Q.같은 숫자는 싫어 

def solution(arr):
    answer = []
    for a in arr : 
        if a not in answer or a!= answer[-1] : 
            answer.append(a)
    return answer

Q.기능개발 #***

'''
#틀림 --> x > dq[0] 에서 부등호 빠짐 ㅠ x>=q[0] 이여야함 
#반례 : [90, 90, 90, 90],[30, 1, 1, 1],[1, 3] 
from collections import deque 
def solution(progresses, speeds):
    answer = []
    dq=[]
    for p,s in zip(progresses,speeds) : 
        n=(100-p)//s 
        if n*s+p < 100 : 
            n+=1 
        dq.append(n)
        
    dq=deque(dq)
    cnt=0
    
    while True : 
        if len(dq)==0 : 
            break 
            
        x=dq.popleft()
        cnt=1
        while dq and x > dq[0] : 
            dq.popleft()
            cnt+=1
        answer.append(cnt)
    
    return answer

'''
from collections import deque 
def solution(progresses, speeds):
    answer = []
    dq=[]
    dq=deque(dq)
    
    for p,s in zip(progresses,speeds) : 
        n=(100-p)//s 
        while True : 
            if n*s+p < 100 : 
                n+=1
            else : 
                break
        
        dq.append(n)
    
    while True : 
        if len(dq)==0 : 
            break 
        if len(dq)==1 : 
            answer.append(1)
            break 
            
        x=dq.popleft()
        cnt=1
        while dq and x >= dq[0] : 
            dq.popleft()
            cnt+=1
            
        answer.append(cnt)
    
    return answer

Q.올바른 괄호 

def solution(s):
    answer = True
    stack=[] 
    
    if s[0] == ')' : 
        return False 
    
    for x in s : 
        if x=='(' : 
            stack.append(x) 
        else : #')'
            if len(stack)==0 : 
                return False 
            stack.pop()
            
    if len(stack) > 0 : 
        return False

    return True

Q.프로세스 #***

from collections import deque 
def solution(priorities, location):
    answer = 0
    dq = deque() 
    for i,p in enumerate(priorities) :  
        dq.append((i,p))
        
    while True : 
        x=dq.popleft()
        if any(x[1]<d[1] for d in dq) : 
            dq.append(x)
        else : 
            answer+=1
            if x[0] == location :
                break
    return answer
#queue =  [(i,p) for i,p in enumerate(priorities)]

Q.다리를 지나는 트럭 #***

'''
#틀림
from collections import deque 
def solution(bridge_length, weight, truck_weights):
    answer = 0
    dq=deque([0]*bridge_length)
    dq=deque(dq)
    truck_weights = deque(truck_weights)
    while True : 
        if len(truck_weights) == 0 and len(dq) == 0 : 
            break 
            
        if len(truck_weights) > 0 and (sum(dq)+truck_weights[0]) <= weight : 
            dq.append(truck_weights.popleft())
        else : 
            dq.popleft()
        answer+=1
        
    return answer

'''
from collections import deque 
def solution(bridge_length, weight, truck_weights):
    answer = 0
    
    #시간 초과 / 5번 테스트 케이스만 .. >> sum() 함수 : O(n) 이라 김  um보다는 변수를 가감하는 방식으로 변경해야 
    dq=deque([0]*bridge_length)
    truck_weights = deque(truck_weights)
    sum_truck= 0 
    
    while dq:
        answer+=1
        sum_truck-=dq.popleft()
        if truck_weights:
            #if sum(dq)+truck_weights[0]<=weight:
            if sum_truck+truck_weights[0]<=weight:
                x=truck_weights.popleft()
                sum_truck+=x
                dq.append(x)
            else:
                dq.append(0)
    
    return answer

Q.주식가격 #***

def solution(prices):
    answer = []
    
    '''
    #테스트 케이스 1번 제외 하고 틀림 
    #반례 [4,5,1,2,6,1,1], [2,1,4,2,1,1,0] 
    for i, p in enumerate (prices) : 
        cnt=0
        print(p, prices[i+1:])
        for j in prices[i+1:] : 
            if p <= j : 
                cnt+=1
        answer.append(cnt)
    
    '''

    '''
    #효율성 테스트 모두 실패 
    for i,p in enumerate (prices) : 
        cnt=0
        stack=[]
        for t in prices[i+1:] : 
            stack.append(t)
            if stack[-1] < p :
                cnt=len(stack)
                break
            else : 
                cnt+=1
        answer.append(cnt)
    '''

    return answer

from collections import deque 
def solution(prices):
    answer = []
    dq=deque(prices)
    
    while dq : 
        x=dq.popleft()
        cnt=0
        for i in dq : 
            cnt+=1 
            if i < x :
                break 
        answer.append(cnt)
    return answer

https://school.programmers.co.kr/learn/challenges?tab=algorithm_practice_kit 

<해시> 
Q.완주하지 못한 선수 #***

def solution(participant, completion):
    answer = ''
    '''
    #틀림. 효율성 테스트 모두 실패 
    for c in completion : 
        participant.remove(c)
    answer=participant[0]
    '''
    tmp=dict() 
    for p in participant : 
        tmp[p]=tmp.get(p,0) +1
    for c in completion :
        tmp[c]=tmp[c]-1
    
    for t in tmp.keys() :
        if tmp[t] > 0 :
            answer=t
            break
    
    return answer

'''
import collections
def solution(participant, completion):
    answer = collections.Counter(participant) - collections.Counter(completion)
    return list(answer.keys())[0]
'''
'''
    participant.sort()
    completion.sort()
    for i in range(len(completion)):
        if participant[i] != completion[i]:
            return participant[i]
    return participant[len(participant)-1]
'''

Q.폰켓몬

def solution(nums):
    answer = 0
    cnt=len(nums)
    nums=set(nums)
    if len(nums) > cnt/2 :
        answer = cnt/2 
    else : 
        answer=len(nums)
    return answer
    #return min(len(ls)/2, len(set(ls)))

Q.전화번호 목록 #***

def solution(phone_book):
    '''
    #틀림 ㅠ 
    answer = True
    x = min(phone_book) 
    for p in phone_book : 
        if x in p and p!=x: 
            answer=False
            break
    '''
    answer=True
    phone_book = sorted(phone_book)
    for p1, p2 in zip(phone_book, phone_book[1:]):
        if p2.startswith(p1): 
            answer=False
            break
    return answer

Q.의상 #*** 

'''
A 종류 N개,B 종류 M개 일 때 가능한 모든 경우의 수는 (N+1)(M+1)
(N+1)(M+1) = NM + N + M + 1
NM: N과 M을 모두 사용하는 경우
N: N만 사용하는 경우
M: M만 사용하는 경우
1: 모두 사용하지 않는 경우 
따라서, 결괏값 리턴시 -1을 통해 아무것도 입지 않는 경우를 제외하여 
최소 1개 이상의 의상을 입는 경우의 수만 리턴할 수 있도록 함 
(출처 구글링)
'''
def solution(clothes):
    answer = 0
    tmp=dict() 
    for c in clothes : 
        type=c[1]
        tmp[type] = tmp.get(type,0) + 1 
        
    answer=1
    for type,cnt in tmp.items() : 
        answer*=(cnt+1)
    answer-=1
    
    return answer

Q.베스트 앨범 #*** 

def solution(genres, plays):
    answer = []
    dic1={}
    dic2={}
    for i , (g,p) in enumerate(zip(genres, plays)) : #dic1 : 장르 인덱스 , 값 정리 (리스트로 저장)
        #print(i,g,p)
        if g not in dic1 : 
            dic1[g] = [(i,p)]
        else :
            dic1[g].append((i,p))
        #print(dic1) #{'classic': [(0, 500), (2, 150), (3, 800)], 'pop': [(1, 600), (4, 2500)]}
        
        if g not in dic2 : #dic2 : 장르별 합계
            dic2[g] = p 
        else : 
            dic2[g] += p 
        #print(dic2) #{'classic': 1450, 'pop': 3100} 
        
    for (k,v) in sorted(dic2.items(), key=lambda x:x[1], reverse=True) : #같은 장르 내에서는 최대 2곡까지 
        for (i,p) in sorted(dic1[k], key=lambda x:x[1], reverse=True)[:2]: 
            answer.append(i)
    return answer

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit 

<JOIN>

Q. 상품 별 오프라인 매출 구하기 | Level 2

select p.product_code, (sum(os.sales_amount) * p.price) as sales
from offline_sale os 
left join product p on os.product_id = p.product_id
group by os.product_id
order by sales desc, p.product_code asc

Q. 조건에 맞는 도서와 저자 리스트 출력하기 | Level 2

select b.book_id , a.author_name, DATE_FORMAT(b.published_date, "%Y-%m-%d") as published_date
from author a 
inner join book b on a.author_id = b.author_id and b.category = '경제'
order by b.published_date asc
-- DATE_FORMAT !!

Q. 없어진 기록 찾기 | Level 3

/* 오답!
select ao.animal_id, ao.name
from animal_outs ao 
inner join animal_ins ai on ao.animal_id != ai.animal_id
order by ao.animal_id;
*/

select ao.animal_id, ao.name
from animal_outs ao 
left join animal_ins ai on ao.animal_id = ai.animal_id
where ai.animal_id is null
order by ao.animal_id;
-- !! a left join b on a.key = b.key where b.key is null 조건 !

Q. 있었는데요 없었습니다 | Level 3

select ai.animal_id, ai.name
from animal_ins ai
inner join animal_outs ao on ai.animal_id = ao.animal_id
where ao.datetime < ai.datetime
order by ai.datetime

Q. 오랜 기간 보호한 동물(1) | Level 3

select ai.name, ai.datetime
from animal_ins ai 
left join animal_outs ao on ai.animal_id = ao.animal_id
where ao.animal_id is null
order by ai.datetime asc 
limit 3

Q. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 | Level 4 >> 하는 즁 ! 

/* 오답
select 
    A.car_id
    , A.car_type
    , round(daily_fee*30*(1-discount_rate/100),0) as fee
from (
    select *
    from car_rental_company_car crcc 
    where crcc.car_type in ('세단', 'SUV')
    and crcc.car_id not in (
        select crcrh.car_id -- 대여 불가 (기간 내 대여중)
        from car_rental_company_rental_history crcrh
        where crcrh.start_date between '2022-11-01' and '2022-12-01'
        or crcrh.end_date between '2022-11-01' and '2022-12-01'
        )
    ) A
left join (
    select * 
    from car_rental_company_discount_plan crcdp
    where crcdp.duration_type like "30%"
    order by discount_rate desc 
    ) B
on A.car_type = B.car_type
-- fee 조건 ... 
order by fee desc, car_type asc, car_id desc

*/
-- 대여 start_date, end_date 조건 주의 !
-- CAST(REPLACE(DISCOUNT_RATE, '%', '') AS DECIMAL) 안해도 되네 ? 

-- (1-DISCOUNT_RATE/100) 계산식

Q. 5월 식품들의 총매출 조회하기 | Level 4

Q. 주문량이 많은 아이스크림들 조회하기 | Level 4

Q. 그룹별 조건에 맞는 식당 목록 출력하기 | Level 4

Q. 보호소에서 중성화한 동물 | Level 4

Q. FrontEnd 개발자 찾기 | Level 4

Q. 상품을 구매한 회원 비율 구하기 | Level 5

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit 

<IS NULL>
Q.경기도에 위치한 식품창고 목록 출력하기

SELECT
    WAREHOUSE_ID
    , WAREHOUSE_NAME
    , ADDRESS
    , IFNULL(FREEZER_YN,'N') as FREEZER_YN
FROM FOOD_WAREHOUSE 
WHERE ADDRESS LIKE '경기도%'
ORDER BY WAREHOUSE_ID

Q.이름이 없는 동물의 아이디

SELECT ANIMAL_ID
FROM ANIMAL_INS 
WHERE NAME IS NULL
ORDER BY ANIMAL_ID

Q.이름이 있는 동물의 아이디

SELECT ANIMAL_ID 
FROM ANIMAL_INS 
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID

Q.NULL 처리하기

SELECT 
    ANIMAL_TYPE
    , IFNULL(NAME,"No name") as NAME
    , SEX_UPON_INTAKE
FROM ANIMAL_INS

Q.나이 정보가 없는 회원 수 구하기

SELECT COUNT(*)
FROM USER_INFO 
WHERE AGE IS NULL

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit 

<GROUP BY>
Q.즐겨찾기가 가장 많은 식당 정보 출력하기

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 FOOD_TYPE)
ORDER BY FOOD_TYPE DESC

Q.저자 별 카테고리 별 매출액 집계하기

SELECT 
    au.AUTHOR_ID
    , au.AUTHOR_NAME
    , bo.CATEGORY
    , SUM(SALES*PRICE) as TOTAL_SALES
FROM BOOK_SALES as bs
INNER JOIN BOOK as bo ON bs.BOOK_ID = bo.BOOK_ID 
INNER JOIN AUTHOR as au ON bo.AUTHOR_ID = au.AUTHOR_ID
WHERE YEAR(bs.SALES_DATE)=2022
AND MONTH(bs.SALES_DATE)=1
GROUP BY au.AUTHOR_NAME, bo.CATEGORY 
ORDER BY au.AUTHOR_ID , bo.CATEGORY DESC

Q.자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

SELECT CAR_TYPE, COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE (OPTIONS LIKE '%통풍시트%' 
      OR OPTIONS LIKE '%열선시트%'
      OR OPTIONS LIKE '%가죽시트%')
#WHERE OPTIONS LIKE '%시트%' 
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE

Q.조건에 맞는 사용자와 총 거래금액 조회하기

/*
SELECT WRITER_ID
FROM USED_GOODS_BOARD
WHERE STATUS='DONE'
GROUP BY WRITER_ID
HAVING SUM(PRICE)>=700000
*/
SELECT ugb.WRITER_ID, ugu.NICKNAME, SUM(ugb.PRICE) as TOTAL_SALES
FROM USED_GOODS_BOARD as ugb
JOIN USED_GOODS_USER  as ugu ON ugb.WRITER_ID = ugu.USER_ID
WHERE STATUS='DONE'
GROUP BY ugb.WRITER_ID
HAVING SUM(ugb.PRICE)>=700000
ORDER BY TOTAL_SALES

Q.성분으로 구분한 아이스크림 총 주문량

SELECT 
     ii.INGREDIENT_TYPE as INGREDIENT_TYPE
    ,SUM(TOTAL_ORDER) as TOTAL_ORDER 
FROM FIRST_HALF as fh
LEFT JOIN ICECREAM_INFO as ii ON fh.FLAVOR=ii.FLAVOR
GROUP BY ii.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER

Q.카테고리 별 도서 판매량 집계하기

SELECT
    b.CATEGORY as CATEGORY
    ,SUM(SALES) as TOTAL_SALES
FROM BOOK_SALES as bs
LEFT JOIN BOOK as b ON bs.BOOK_ID = b.BOOK_ID
WHERE YEAR(bs.SALES_DATE)=2022 and MONTH(bs.SALES_DATE)=1
GROUP BY b.CATEGORY
ORDER BY CATEGORY

Q.진료과별 총 예약 횟수 출력하기

SELECT 
    MCDP_CD as 진료과코드
    ,COUNT(*) as 5월예약건수
FROM APPOINTMENT
WHERE DATE_FORMAT(APNT_YMD,"%Y-%m-%d") LIKE "2022-05-%"
GROUP BY MCDP_CD
ORDER BY 5월예약건수,진료과코드

Q.자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

SELECT 
    CAR_ID
    ,MAX(
    #CASE WHEN ('2022-10-16' between DATE_FORMAT(START_DATE,"%Y-%m-%d") and DATE_FORMAT(END_DATE,"%Y-%m-%d") )
    CASE WHEN ('2022-10-16' between START_DATE and END_DATE )
    THEN '대여중'
    ELSE '대여 가능' END
    ) as AVAILABILITY
    #,MAX(if('2022-10-16' between START_DATE and END_DATE, '대여중','대여 가능'))
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY CAR_ID #CAR_ID별로 기록하기 위함
ORDER BY CAR_ID DESC
/*
MAX()함수는 GROUP BY와 함께 사용해야함
GROUP BY를 통해 CAR_ID 기준으로 묶어주고, 
위 조건을 MAX() 통해 반환하면
, 같은 CAR_ID 중에 가장 큰 값이 실행 결과로 나타난다.
*/

Q.대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

SELECT MONTH(START_DATE) as MONTH, CAR_ID, COUNT(HISTORY_ID) as RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY  
WHERE CAR_ID IN (
    SELECT CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY   
    WHERE MONTH(START_DATE) between 8 and 10
    GROUP BY CAR_ID 
    HAVING COUNT(HISTORY_ID)>=5
    )
AND MONTH(START_DATE) between 8 and 10
GROUP BY MONTH, CAR_ID
HAVING RECORDS>0
ORDER BY MONTH,CAR_ID DESC

Q.식품분류별 가장 비싼 식품의 정보 조회하기

/* 틀린 답 
SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
GROUP BY CATEGORY 
HAVING CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY PRICE DESC
*/
SELECT CATEGORY, PRICE AS MAX_PRICE ,PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY,PRICE) IN (
    SELECT CATEGORY,MAX(PRICE) AS MAX_PRICE
    FROM FOOD_PRODUCT
    GROUP BY CATEGORY 
    HAVING CATEGORY IN ('과자', '국', '김치', '식용유')
    )
ORDER BY MAX_PRICE DESC

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

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

Q.동명 동물 수 찾기

SELECT NAME, COUNT(*)
FROM ANIMAL_INS 
GROUP BY NAME 
HAVING COUNT(NAME)>=2
ORDER BY NAME

Q.년, 월, 성별 별 상품 구매 회원 수 구하기

SELECT YEAR(os.SALES_DATE) as YEAR
    ,MONTH(os.SALES_DATE) as MONTH
    ,ui.GENDER as GENDER
    ,COUNT(distinct ui.USER_ID) as USERS 
FROM ONLINE_SALE as os 
INNER JOIN USER_INFO as ui 
ON os.USER_ID = ui.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY YEAR , MONTH, GENDER
ORDER BY YEAR , MONTH, GENDER

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

SELECT HOUR(DATETIME) AS HOUR , COUNT(*) AS COUNT
FROM ANIMAL_OUTS 
WHERE HOUR(DATETIME) between 9 and 19 
GROUP BY HOUR
ORDER BY HOUR

Q.입양 시각 구하기(2)#***

/*
틀린 답
SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME) 
ORDER BY HOUR
*/

/*
SET @변수명 = 대입값; 
SELECT @변수명 := 대입값; # :=는 할당 연산자
*/

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

Q.가격대 별 상품 개수 구하기 #***

SELECT 
    TRUNCATE(PRICE,-4) as PRICE_GROUP #4자릿수를 버림. 
    , COUNT(*) as PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP
/*
TRUNCATE(PRICE,-4)  #4자릿수를 버림. 
TRUNC(수, [, 자릿수])
수를 지정된 소수점 자리까지 버림하여 반환해주는 함수이다. 
자릿수를 명시하지 않았을 경우 기본값은 0 . 즉, 정수로 반환
*/

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit 

<SUM, MAX, MIN>
Q.가격이 제일 비싼 식품의 정보 출력하기

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT 
ORDER BY PRICE DESC 
LIMIT 1

Q.가장 비싼 상품 구하기

SELECT MAX(PRICE) as MAX_PRICE
FROM PRODUCT

Q.최댓값 구하기

SELECT MAX(DATETIME) as 시간
FROM ANIMAL_INS 
/*
SELECT DATETIME as 시간
FROM ANIMAL_INS 
ORDER BY DATETIME DESC
LIMIT 1
*/

Q.최솟값 구하기

SELECT MIN(DATETIME) AS 시간
FROM ANIMAL_INS

Q.동물 수 구하기

SELECT count(*) as count
FROM ANIMAL_INS

Q.중복 제거하기

SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS 
WHERE NAME is not null

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit 

<SELECT>
Q.3월에 태어난 여성 회원 목록 출력하기

SELECT MEMBER_ID,MEMBER_NAME,GENDER,DATE_FORMAT(DATE_OF_BIRTH,"%Y-%m-%d")
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH)=3
AND GENDER='W'
AND TLNO is not null 
ORDER BY MEMBER_ID;
/*
DATE_FORMAT(DATE_OF_BIRTH, 원하는 형식) 
DATE_FORMAT(DATE_OF_BIRTH, "%Y-%m-%d") AS BIRTH, #1992-03-16
DATE_FORMAT(DATE_OF_BIRTH, "%Y") AS YEAR, 
DATE_FORMAT(DATE_OF_BIRTH, "%m") AS MONTH,
DATE_FORMAT(DATE_OF_BIRTH, "%d") AS DAY
*/

Q.12세 이하인 여자 환자 목록 출력하기

SELECT PT_NAME, PT_NO, GEND_CD, AGE, ifnull(TLNO,'NONE') as TLNO
FROM PATIENT
WHERE AGE<=12 
and GEND_CD='W'
ORDER BY AGE DESC, PT_NAME ASC
/*
IFNULL(Column명, "Null일 경우 대체 값")
*/

Q.평균 일일 대여 요금 구하기

SELECT ROUND(AVG(DAILY_FEE),0) AS AVERAGE_FEE 
FROM CAR_RENTAL_COMPANY_CAR 
WHERE CAR_TYPE='SUV';

Q.인기있는 아이스크림

SELECT FLAVOR
FROM FIRST_HALF 
ORDER BY TOTAL_ORDER DESC,SHIPMENT_ID ASC

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

SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD,"%Y-%m-%d") as HIRE_YMD
FROM DOCTOR 
WHERE MCDP_CD IN ('CS','GS')
ORDER BY HIRE_YMD DESC, DR_NAME

Q.조건에 맞는 도서 리스트 출력하기

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

Q.조건에 부합하는 중고거래 댓글 조회하기

SELECT
    ugb.TITLE
    , ugb.BOARD_ID
    , ugr.REPLY_ID
    , ugr.WRITER_ID
    , ugr.CONTENTS
    , DATE_FORMAT(ugr.CREATED_DATE,"%Y-%m-%d") as CREATED_DATE
FROM USED_GOODS_BOARD as ugb
INNER JOIN USED_GOODS_REPLY as ugr
ON ugb.BOARD_ID = ugr.BOARD_ID
WHERE YEAR(ugb.CREATED_DATE) = 2022 
AND MONTH(ugb.CREATED_DATE) = 10 
ORDER BY ugr.CREATED_DATE, ugb.TITLE

Q.과일로 만든 아이스크림 고르기

SELECT ii.FLAVOR 
FROM ICECREAM_INFO as ii
INNER JOIN FIRST_HALF as fh 
ON ii.FLAVOR = fh.FLAVOR
WHERE fh.TOTAL_ORDER>3000
and ii.INGREDIENT_TYPE = 'fruit_based'
ORDER BY fh.TOTAL_ORDER DESC

Q.서울에 위치한 식당 목록 출력하기

SELECT 
    ri.REST_ID
    ,ri.REST_NAME
    ,ri.FOOD_TYPE
    ,ri.FAVORITES
    ,ri.ADDRESS
    ,ROUND(AVG(rr.REVIEW_SCORE),2) as SCORE
FROM REST_INFO  as ri
INNER JOIN REST_REVIEW as rr
ON ri.REST_ID = rr.REST_ID
WHERE ri.ADDRESS LIKE '서울%'
GROUP BY ri.REST_ID #***
ORDER BY SCORE DESC, ri.FAVORITES DESC

Q.강원도에 위치한 생산공장 목록 출력하기

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY  
WHERE ADDRESS LIKE '강원도%'
ORDER BY FACTORY_ID

Q.재구매가 일어난 상품과 회원 리스트 구하기 

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

Q.모든 레코드 조회하기

SELECT ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS  
ORDER BY ANIMAL_ID

Q.오프라인/온라인 판매 데이터 통합하기 

SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") as SALES_DATE
    ,PRODUCT_ID
    ,USER_ID
    ,SALES_AMOUNT
FROM ONLINE_SALE 
WHERE MONTH(SALES_DATE)=3
#WHERE SALES_DATE LIKE '2022-03%'
#WHERE DATE_FORMAT(ons.SALES_DATE,"%Y-%m")='2022-03'
UNION
SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") as SALES_DATE
    ,PRODUCT_ID
    ,NULL as USER_ID
    ,SALES_AMOUNT
FROM OFFLINE_SALE 
WHERE MONTH(SALES_DATE)=3
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID
/*
UNION (DISTINCT) : 쿼리의 결과를 합친다. 중복된 ROW는 제거 
<-> UNION ALL
*/

Q.역순 정렬하기

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC

Q.아픈 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS  
WHERE INTAKE_CONDITION='Sick'
ORDER BY ANIMAL_ID

Q.어린 동물 찾기

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

Q.동물의 아이디와 이름

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID

Q.여러 기준으로 정렬하기

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

Q.상위 n개 레코드

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1

Q.조건에 맞는 회원수 구하기 

SELECT count(*) #count(USER_ID) as USERS
FROM USER_INFO
WHERE AGE between 20 and 29 #AGE >= 20 and AGE<=29
and YEAR(JOINED)=2021