본문 바로가기
CS/데이터베이스

SQL 기본 문법 정리

by 위대한초밥V 2023. 9. 20.

프로그래머스 SQL 고득점 Kit으로 공부한 내용들을 정리합니다.

 

SUM, MAX, MIN

COUNT

데이터의 개수를 셀 때 사용한다.

SELECT COUNT(NAME) FROM table;

중복되지 않은 이름 데이터 개수를 센다면 DISTINCT를 사용한다.

SELECT COUNT(DISTINCT NAME) FROM table;

 

문제 풀이

가격이 제일 비싼 식품의 정보 출력하기

SELECT * FROM FOOD_PRODUCT WHERE PRICE = (SELECT MAX(PRICE) PRICE FROM FOOD_PRODUCT);

 

SELECT * FROM FOOD_PRODUCT WHERE PRICE = (SELECT MAX(PRICE) PRICE FROM FOOD_PRODUCT);

  • SELECT * FROM FOOD_PRODUCT : FOOD_PRODUCT 테이블에서 모든 컬럼(*)을 선택해라
  • WHERE PRICE = ( … ) : 가격이 ( … )인 레코드만
  • (SELECT MAX(PRICE) PRICE FROM FOOD_PRODUCT : FOOD_PRODUCT의 PRICE 컬럼 데이터 중에서 가장 큰 것으로

 

가장 비싼 상품 구하기

SELECT PRICE MAX_PRICE FROM PRODUCT WHERE PRICE = (SELECT MAX(PRICE) PRICE FROM PRODUCT);

SELECT PRICE MAX_PRICE FROM PRODUCT WHERE PRICE = (SELECT MAX(PRICE) PRICE FROM PRODUCT);

  • SELECT PRICE MAX_PRICE FROM PRODUCT : PRODUCT 테이블에서 선택한 PRICE 컬럼명을 MAX_PRICE라고 지정해라.
  • WHERE PRICE = (SELECT MAX(PRICE) PRICE FROM PRODUCT) : 가격이 PRODUCT 테이블에서 PRICE 컬럼 데이터 중에서 가장 큰 것으로 

 

동물 수 구하기

SELECT COUNT(*) from ANIMAL_INS;

SELECT COUNT(*) from ANIMAL_INS; : ANIMAL_INS 테이블에서 NULL 포함 여부에 관계없이 대상 테이블의 모든 행을 카운트해라.

 

중복 제거하기

SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
  • 동물의 이름 갯수를 조회하므로, NAME에 COUNT 함수를 적용한다.
  • 컬럼 내 같은 데이터가 존재하면, 중복제거하기 위해 DISTINCT를 사용한다.
  • NAME이 NULL 값이 아닌 것을 비교한다.

SELECT

LIKE, NOT LIKE

특정 문자를 포함하는지 데이터를 확인하고 있다면, 제외하고 출력한다.

  • LIKE: 특정 문자를 포함하는 데이터를 출력한다.
  • NOT LIKE: 특정 문자를 포함하지 않는 데이터를 출력한다.
  • 컬럼 LIKE '문자%': 특정 문자로 시작하는 데이터 확인
  • 컬럼 LIKE '%문자': 특정 문자로 종료하는 데이터 확인
  • 컬럼 LIKE '%문자%': 문자 시작 ~ 종료까지 특정 문자 포함 여부 확인

JOIN(INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN)

  • JOIN: 교집합
  • LEFT JOIN: 왼쪽 테이블을 중심으로 오른쪽의 테이블을 매치시킨다.
    • 왼쪽 테이블의 한 개의 레코드에 여러개의 오른쪽 테이블 레코드가 일치할 경우, 해당 왼쪽 레코드를 여러번 표시한다.
    • 왼쪽은 무조건 표시하고, 매치되는 레코드가 오른쪽에 없으면 NULL을 표시한다.
  • RIGHT JOIN: 오른쪽 테이블을 중심으로 왼쪽 테이블을 매치시킨다.(LEFT JOIN과 방향만 오른쪽으로 바뀐 것!)
    • 오른쪽 테이블의 한 개의 레코드에 여러 개의 왼쪽 테이블 레코드가 일치할 경우, 해당 오른쪽 레코드를 여러번 표시한다.
    • 오른쪽은 무조건 표시하고, 매치되는 레코드가 왼쪽에 없으면 NULL을 표시한다.
  • OUTER JOIN: 조건에 부합하지 않은 행까지 포함시켜 결합한다.
    • FULL JOIN -> 거의 사용할 일이 없으며, 지원하지 않은 경우도 있다.

 

DATE_FORMAT

DATE_FORMAT(날짜, 형식): 날짜를 지정한 형식으로 출력한다.

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') AS DATE

출력 결과: 2023-09-21

 

쿼리문 합치는 방법(UNION, UNION ALL)

UNION: 여러 개의 쿼리문을 사용하여 하나의 데이터로 출력한다.

UNION ALL: 각각에 쿼리에 나온 데이터를 하나로 합쳐준다. 이때 중복되는 갓도 그대로 출력된다.

 

문제 풀이

동명 동물 수 찾기

SELECT NAME, COUNT(NAME) AS 'COUNT'
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) > 1
ORDER BY NAME ASC
  • NAME 필드로 그룹화한다.
  • COUNT(NAME)으로 개수를 세었을 때, 1보다 큰 원소들을
  • NAME으로 오름차순 정렬한다. 

 

어린 동물 찾기

SELECT ANIMAL_ID, NAME FROM ANIMAL_INS
WHERE INTAKE_CONDITION NOT LIKE 'Aged'
ORDER BY ANIMAL_ID
  • NOT LIKE: INTAKE_CONDITION 필드가 'Aged' 라는 문자를 포함하지 않은 것들을 선택한다.

 

여러 기준으로 정렬하기

SELECT ANIMAL_ID, NAME, DATETIME FROM ANIMAL_INS
ORDER BY NAME, DATETIME DESC
  • NAME 기준으로 오름차순 정렬하고
  • DATETIME 기준으로 내림차순 정렬한다.

-> 여러 기준으로 정렬할 때는 `,`로 구분한다.

 

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

SELECT F.FLAVOR 
FROM FIRST_HALF F JOIN ICECREAM_INFO I ON F.FLAVOR = I.FLAVOR
WHERE (F.TOTAL_ORDER > 3000) AND (I.INGREDIENT_TYPE = "fruit_based")
ORDER BY F.TOTAL_ORDER DESC
  1.  각각의 컬럼에 해당하는 조건을 출력하기 위해 두 테이블을 연결한다.
    • (INNER) JOIN으로 조건에 해당하는 행을 추출한다.
  2. 상반기 아이스크림 총 주문량이 3000보다 높은 아이스크림과 아이스크림의 주 성분이 과일인 아이스크림을 찾는다.
    • WHERE 절을 이용하고 AND로 묶어준다.
  3. 총 주문량이 큰 순서대로 조회한다.
    • ORDER BY 총 주문량 DESC

 

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

WHERE 사용한 경우

SELECT A.REST_ID, B.REST_NAME, B.FOOD_TYPE, B.FAVORITES, B.ADDRESS, ROUND(AVG(A.REVIEW_SCORE),2) AS SCORE
FROM REST_REVIEW A
JOIN REST_INFO B ON A.REST_ID = B.REST_ID
WHERE B.ADDRESS LIKE '서울%'
GROUP BY A.REST_ID
ORDER BY SCORE DESC, B.FAVORITES DESC;

HAVING 사용한 경우

SELECT A.REST_ID, B.REST_NAME, B.FOOD_TYPE, B.FAVORITES, B.ADDRESS, ROUND(AVG(A.REVIEW_SCORE),2) AS SCORE
FROM REST_REVIEW A
JOIN REST_INFO B ON A.REST_ID = B.REST_ID
GROUP BY A.REST_ID
HAVING B.ADDRESS LIKE '서울%'
ORDER BY SCORE DESC, B.FAVORITES DESC;

👉 더 읽어보면 좋을 내용: SQL Having과 Where 차이

 

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

SELECT USER_ID, PRODUCT_ID FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) >= 2
ORDER BY USER_ID ASC, PRODUCT_ID DESC
  • USER_ID와 PRODUCT_ID로 그룹화한다.
    • GROUP BY 사용
  • 그룹화한 결과를 COUNT 함수로 합계를 구했을 때, 개수가 2 이상인 것들이 재구매 상품이 된다.
    • HAVING, COUNT 함수 사용

 

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

-- 온라인
(SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT 
FROM ONLINE_SALE
WHERE SALES_DATE LIKE '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 SALES_DATE LIKE '2022-03-%')
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

온라인 쿼리문 작성, 오프라인 쿼리문 작성, UNION으로 합친다.

NULL

문제 풀이

경기도에 위치한 식품창고 목록 출력하기

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IF(FREEZER_YN is NULL, 'N', FREEZER_YN) AS FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기도 %'

IF문을 사용하여, FREEZER_YN이 NULL이면, 'N'을 출력하도록 한다.

GROUP BY

GROUP BY

유형별로 갯수를 가져오고 싶을 때, 컬럼에 데이터를 그룹화하는 방법

  • GROUP BY: 특정 컬럼을 그룹화한다.
  • HAVING: 특정 컬럼을 그룹화한 결과에 조건을 건다.

=> WHERE랑 HAVING의 차이는 WHERE는 그룹화하기 전이고, HAVING은 그룹화 후에 조건이다.

 

컬럼 그룹화

SELECT 컬럼 FROM 테이블 GROUP BY 그룹화할 컬럼;

조건 처리 후에 컬럼 그룹화

SELECT 컬럼 FROM 테이블 WHERE 조건식 GROUP BY 그룹화할 컬럼;

컬럼 그룹화 후에 조건 처리

SELECT 컬럼 FROM 테이블 GROUP BY 그룹화할 컬럼 HAVING 조건식;

조건 처리 후에 컬럼 그룹화 후에 조건 처리

SELECT 컬럼 FROM 테이블 WHERE 조건식 GROUP BY 그룹화할 컬럼 HAVING 조건식;

 

SUM VS COUNT

  • SUM: 요소들의 value들의 합
  • COUNT: 요소들의 개수

 

BETWEEN VS IN VS LIKE

BETWEEN 연산자

select * from scores where num between 30 and 40;
  • scores 테이블의 num 컬럼 값이 30에서 40 사이인 데이터를 출력한다.
  • AND 연산자를 이용하여 쓸 수도 있다.
    • num >= 30 and num <= 40

IN 연산자

select * from scores where num in (30, 40, 50, 60);
  •  scores 테이블의 num 컬럼 값이 30 또는 40 또는 50 또는 60인 데이터를 출력한다.
  • in 연산자는 괄호 안의 여러 개의 값 중, 하나라도 일치하면 참을 반환한다.
  • OR 연산자를 이용하여 쓸 수도 있다.
    • where num = 30 or num = 40 or num = 50 or num = 60

LIKE 연산자

select * from scores where subject like '%수학%';

scores 테이블의 subject 컬럼의 값 중에 '수학'이 포함되는 데이터를 출력한다.

  • '%수학%': 수학이 포함되는 데이터를 출력한다.
  • '수학%': 수학으로 시작되는 데이터를 출력한다.
  • '%수학': 수학으로 끝나는 데이터를 출력한다.
select * from scores where subject = '수학';

like와 달리 = 을 사용할 경우, 정확히 일치되는 데이터만 출력한다.

 

CASE문 사용

CASE
	WHEN 조건1 THEN 결과값1
	WHEN 조건2 THEN 결과값2
	WHEN 조건N THEN 결과값N
	ELSE 결과값
END
  • 조건을 통과하고 첫 번째 조건이 충족되면 값을 반환한다.
  • 조건에 따라 True(참)이면 읽기를 중지하고 결과를 반환하고
  • 조건이 True(참)가 아니면 ELSE 절의 값을 반환한다.
  • ELSE 부분이 없고 조건이 참이 아니면 NULL을 반환한다.

TRUNCATE(숫자, 버릴 자릿수)

SELECT TRUNCATE(1234.56789, 1) // 1234.5
SELECT TRUNCATE(1234.56789, 4) // 1234.5678
SELECT TRUNCATE(1234.56789, -1) // 1230
SELECT TRUNCATE(1234.56789, -2) // 1200

 

문제 풀이

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

SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF F
INNER JOIN ICECREAM_INFO I
ON F.FLAVOR = I.FLAVOR
GROUP BY INGREDIENT_TYPE
ORDER BY TOTAL_ORDER;
  • 아이스크림의 주 성분, 성분별 총 주문량을 조회한다.
  • 아이스크림 맛을 기준으로 테이블을 병합한다.
  • 아이스크림의 주 성분으로 묶는다.
  • 총 주문량을 기준으로 오름차순 정렬한다.

 

즐겨찾기가 가장 많은 식당 정보 출력하기

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;

WHERE (FOOD_TYPE, FAVORITES) IN (SELECT FOOD_TYPE, MAX(FAVORITES)
FROM REST_INFO
GROUP BY FOOD_TYPE)
  • 서브쿼리문을 사용하여 FOOD_TYPE으로 그룹화한 것에서 가장 FAVORITES가 높은 값을 불러온다. 
  • 이 데이터를 원본 테이블과 매칭시킨다. 
  • 매칭 결과를 FOOD_TYPE으로 정렬하여 출력한다.

 

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

SELECT USER_ID, NICKNAME, SUM(PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD, USED_GOODS_USER
WHERE WRITER_ID = USER_ID AND STATUS = "DONE"
GROUP BY USER_ID
HAVING SUM(PRICE) >= 700000
ORDER BY TOTAL_SALES ASC
  1. USER_ID, NICKNAME, 가격 합계를 TOTAL_SALES라고 이름 붙여서 불려온다.
  2. USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 
  3. WRITER_ID와 USER_ID가 같고, STATUS가 "DONE"인 것을 
  4. USER_ID로 묶는다. 
  5. PRICE의 합이 700000 이상인 것들을
  6. TOTAL_SALES 기준으로 오름차순 정렬한다.

 

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

SELECT A.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORY, SUM(A.PRICE * C.SALES) AS TOTAL_SALES
FROM BOOK AS A
JOIN AUTHOR AS B ON A.AUTHOR_ID = B.AUTHOR_ID
JOIN BOOK_SALES AS C ON A.BOOK_ID = C.BOOK_ID
WHERE C.SALES_DATE LIKE '2022-01%'
GROUP BY A.AUTHOR_ID, A.CATEGORY
ORDER BY A.AUTHOR_ID, A.CATEGORY DESC
  1. AUTHOR_ID, AUTHOR_NAME, CATEGORY, PRICE * SALES들의 합을 불러온다.
  2. 여러개의 테이블을 조인한다.
    • BOOK 테이블과 AUTHOR 테이블을 각 테이블의 AUTHOR_ID로 조인한다.
    • 위에서 조인한 결과와 BOOK_SALES 테이블을 각 테이블의 BOOK_ID로 조인한다.
  3. SALES_DATE의 형식으로 2022년 1월에 해당하는 데이터인지 확인한다.
  4. AUTHOR_ID와 CATEGORY로 각각 그룹화한다.
  5. AUTHOR_ID와 CATEGORY로 내림차순 정렬한다.

 

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

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10'
AND CAR_ID IN (SELECT CAR_ID 
               FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
               WHERE DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10'
              GROUP BY CAR_ID
              HAVING COUNT(*) >= 5) 
GROUP BY CAR_ID, MONTH(START_DATE)
HAVING RECORDS >= 1
ORDER BY MONTH, CAR_ID DESC
  1.   대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차
    • 서브 쿼리로 구한다.
    • DATE_FORMAT 함수를 이용해 차종별 대여 시작일이 2022년 8월과 10월 사이에 있는 행들을 구한다.
    • HAVING 절을 사용해 차종별 총 대여 횟수가 5번 이상인지 확인한다.
  2. 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수 리스트를 출력한다.
    • 대여 시작일이 2022년 8월부터 2022년 10월인 자동차 중에서 구한 서브쿼리에 해당하는 행을 구한다.
    • GROUP BY를 CAR_ID별, 월별로 총 대여 횟수를 구한다.
    • 특정 월의 총 대여횟수가 0인 경우는 결과에서 제외하기 위해 HAVING 절에 대여 횟수가 1 이상인 경우를 조건으로 걸어준다.
  3. 정렬한다.
    • ORDER BY: MONTH 오름차순(ASC 생략가능) 정렬한다. CAR_ID는 내림차순(DESC)로 정렬한다.

 

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

SELECT A.CATEGORY, SUM(B.SALES) AS TOTAL_SALES
FROM BOOK AS A
JOIN BOOK_SALES AS B ON A.BOOK_ID = B.BOOK_ID
WHERE SALES_DATE LIKE '2022-01-%'
GROUP BY A.CATEGORY
ORDER BY A.CATEGORY

 

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

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE PRICE IN (
    SELECT MAX(PRICE) 
    FROM FOOD_PRODUCT 
    GROUP BY CATEGORY) 
    AND CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY MAX_PRICE DESC

MAX로 PRICE를 조회하면 해당 컬럼에 대한 값만 나오고, 행 전체는 나오지 않는다. 서브 쿼리로 걸러서 먼저 조회한다.

  • SELECT MAX(PRICE) FROM FOOD_PRODUCT GROUP BY CATEGORY
    => 서브 쿼리로 카테고리별 가격이 가장 비싼 식품을 조회한다.
  • WHERE PRICE IN (서브쿼리) AND CATEGORY IN ('과자', '국', '김치', '식용유')
    => PRICE가 서브쿼리에서 조회한 내역안에 있고, CATEGORY가 '과자', '국', '김치', '식용유' 중 하나라면 조회한다.
  • 결과는 MAX_PRICE로 정렬한다.

 

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

SELECT CAR_ID, 
    CASE
        WHEN CAR_ID IN (SELECT CAR_ID 
                       FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                       WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE) THEN '대여중'
        ELSE '대여 가능'
    END 'AVAILABILITY'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC
  1. 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중'이라고 표시하고, 대여중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가한다.
    • CASE문을 사용한다.
    • 서브 쿼리를 사용해 CAR_ID를 구한다.
    • GROUP BY를 통해 CAR_ID 별로 위에서 구한 조건에 해당하는 CAR_ID라면 '대여중'으로, 아니면 '대여 가능'으로 표시해준다. AVAILABILITY라는 컬럼에 값을 출력한다.
  2. 자동차 ID를 기준으로 내림차순 정렬한다.
    • ORDER BY 컬럼명 DESC

 

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

SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH, GENDER, COUNT(DISTINCT A.USER_ID) AS USERS
FROM USER_INFO A
JOIN ONLINE_SALE B ON A.USER_ID = B.USER_ID
GROUP BY YEAR, MONTH, GENDER
HAVING GENDER IS NOT NULL
ORDER BY YEAR, MONTH, GENDER

1. 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성한다.

  • 년 → YEAR(SALES_DATE)
  • 월 → MONTH(SALES_DATE)
  • 성별 → GENDER 
  • 상품을 구매한 회원 수 → COUNT(DISTINCT USER_ID)
    → 동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재한다.
    → 중복제거를 위해 DISTINCT 키워드를 사용한다.

2. 3.  USER_INFO 테이블을 A로 지정하고, ONLINE_SALE 테이블을 B로 지정해 조인한다.

4. 성별 정보가 없는 경우 결과에서 제외한다. IS NOT NULL 을 이용하여 제거한다.

5. 년, 월, 성별 별로 상품을 구매한 회원수를 집계한다.

6. 년, 월, 성별을 기준으로 오름차순 정렬한다.

 

입양 시각 구하기(2)

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
  1. SET 함수를 사용해 0~23까지의 테이블을 완성한다.
  2. @HOUR := -1 ➡️ HOUR 변수에 -1을 대입한다.
    • HOUR 변수에 HOUR + 1을 대입한다.
    • WHERE문을 이용해 @HOUR가 0부터 23까지로 테이블 범위를 정한다.
  3. SELECT문으로 HOUR 변수와 시간이 같을 때를 COUNT 하기 때문에 WHERE문으로 HOUR(DATETIME) = @HOUR와 같아질 때를 찾는다.

 

가격대 별 상품 개수 구하기

SELECT TRUNCATE(PRICE, -4) AS PRICE_GROUP, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP

TRUNCATE 함수를 사용해서, 만의 자리만 제외하고 남은 값들을 절사한다. 

반응형

티스토리툴바