1. SQL 내장함수, NULL, 비교문
- SQL에도 내장함수가 있다. 상수나 속성 이름을 입력값으로 받아 단일 값을 결과로 반환한다는 특징이 있음.
1.1 숫자 함수
- 질의 : +78과 -78의 절댓값 구하기
- 절댓값을 구하는 ABS() 함수를 이용한다
- 질의 : 4.875를 소수 첫째 자리까지 반올림한 값을 구하시오.
- ROUND() 함수를 통해 반올림한 값을 구할 수 있다.
- 아래처럼 ROUND를 이용해서 소수아래자릿수와 소수점 윗자리수를 다 분해해볼 수도 있다.
- 질의 : 도서 제목에 야구가 포함된 도서를 농구로 변경한 후 도서 목록을 나타내시오.
- REPLACE() 함수를 이용해 문자열을 치환할 수 있다.
- 질의 : 굿스포츠에서 출판한 도서의 제목과 문자 수, 바이트 수를 나타내시오.
- LENGTH() 는 바이트 수를 가져오는 함수이고, CHAR_LENGTH() 함수는 문자의 수를 가져오는 함수다.
- 이름가리기 실습 : 문자함수를 이용해 이름의 첫 글자와 마지막 글자만 남기고 가운데는 *로 표시하는 쿼리를 작성하세요
- repeat() 함수를 이용해서 강다니엘처럼 이름이 세 글자 이상인 경우 중간 글자 수만큼 *를 붙일 수 있다.
- 한 글자인 경우에는 CASE WHEN을 사용한다.
- 날짜, 시간 함수는 아래와 같은 종류가 있음.
- 날짜와 시간 부분을 나타내는 인수는 'format'으로 표기하고, format은 날짜 형식 지정자로 특별한 규칙이 있다!
- 주의할 것
- DBMS별로 서로 이름도 동작도 의미도 달라서 잘 보고 써야 함.
- NOW() 등은 DB서버의 타임존을 기준으로 하고 있어서 서버와 사용자가 다른 지역에 있으면 시간이 다르게 나옴..
- 날짜 컬럼이 NULL이면 함수 적용 시 에러가 발생하므로 기본값을 꼭 지정해야함!!
- SELECT에서 조회용으로 사용만 하고, 웬만해선 WHERE, JOIN, ORDER BY 같은 데서는 함수로 속성을 변환하지 말아야 한다!
- 질의 : 마당서점은 주문일로부터 10일 후에 매출을 확정한다. 각 주문의 확정일자를 구하시오.
- ADDDATE : 지정한 날짜에 day 또는 interval을 더해 새로운 날짜를 반환하는 함수
- format의 주요 지정자도 신경쓸 필요가 있음. 아래 표를 참고할 것...
- 질의 : 마당서점이 2024년 7월 7일에 주문받은 도서의 주문번호, 주문일, 고객번호, 도서번호를 모두 나타내시오. 단, 주문일은 %Y-%m-%d 형태로 표시한다.
- SRT_TO_DATE :: char형으로 저장된 날짜를 date 형으로 변환
- DATE_FORMAT :: 날짜형을 문자형으로 변환
- 질의 : DBMS 서버에 설정된 현재 날짜와 시간, 요일을 확인하시오.
- SYSDATE() :: MySQL DB에 설정된 현재 날짜와 시간을 반환
1.2. NULL 값 처리
- NULL 값은 비교 연산자로 비교 불가하며 아직 지정되지 않은 값을 뜻한다. (값도 없고 적용도 안되고)
- 모든 NULL 연산에 대한 결과는 NULL이다.
- 질의 : 이름, 전화번호가 포함된 고객 목록을 나타내시오. 단, 전화번호가 없는 고객은 '연락처없음'으로 표시하시오.
- IFNULL 함수를 이용 -> NULL 값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력
- 질의 : 고객 목록에서 고객번호, 이름, 전화번호를 앞의 2명만 나타내시오.
- 참고로 MySQL에서는 변수는 @ 기호를 붙여서 표기하고 치환문에는 SET 과 := 기호를 사용함.
- CASE WHEN 실습
2. 부속질의
- 부속질의란 : 하나의 SQL 문 안에 다른 SQL문이 중첩된 질의
- 주로 메인 쿼리의 조건에 따라 서브쿼리의 결과를 가져와 메인 쿼리에서 사용하는 용도로 활용됨
- 클린코드나 가독성은 join이 좋다고는 하지만 쿼리가 좀 더러워도 실무적으론 속도가 빠르면 부속질의를 쓰는 게 낫다고 함..
- 질의 : 평균 주문금액 이하의 주문에 대해 주문번호와 금액을 나타내시오.
- WHERE 부속질의 이용 (비교 연산자나 IN, NOT IN, 등의 연산자를 사용)
- 질의 : 대한민국에 거주하는 고객에게 판매한 도서의 총판매액을 구하시오.
- IN, NOT IN 등의 집합 연산자를 이용하자.
- IN : 주질의의 속성값이 부속질의에서 제공한 결과 집합에 있는지 확인하는 역할을 함
- 질의 : 3번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한 주문의 주문번호와 판매금액을 보이시오.
- ALL, SOME/ANY 한정 연산자를 사용
- 질의 : EXISTS 연산자를 사용하여 대한민국에 거주하는 고객에게 판매한 도서의 총판매액을 구하시오.
- EXISTS : 데이터의 존재 여부를 확인하는 연산자
- 질의 : 마당서점의 고객별 판매액을 나타내시오(고객이름과 고객별 판매액 출력)
- 스칼라 부속질의 활용 -> 부속질의의 결과값을 단일행, 단일열의 스칼라 값으로 반환 (다중행/열이라면 에러, 없으면 NULL)
- 질의 : orders 테이블에 각 주문에 맞는 도서이름을 입력하시오
- SELECT 부속질의 :: 스칼라 부속질의를 사용해 orders 테이블에 새로운 속성인 도서이름 bname을 추가
- 질의 : 고객번호가 2 이하인 고객의 판매액을 나타내시오.
- FROM 부속질의 :: 뷰(기존 테이블로부터 일시적으로 만들어진 가상테이블) 이용
2.1. CTE (Common Table Expression, WITH)
- WITH CTE명 AS (SELECT ...)
- 복잡한 SQL 쿼리 내에서 일시적인 결과 집합(임시 테이블)을 정의하여 가독성을 높이고 쿼리를 구조화함
- 쿼리 실행 시에만 존재하며 DB에 영구적으로 저장되지 않음
- SQL 윈도우 함수 :: 테이블의 행과 행 간의 관계를 정의하여 데이터를 윈도우로 그룹화해 사용하는 함수
3. SQL 문제풀이
1. 특정 날짜의 게시글을 조회하고, 영문 거래 상태를 한글로 변환하는 문제였음.
- CASE WHEN을 사용해서 세 상태를 각각 판매중 예약중 거래완료 이렇게 바꾸어 출력한다.
- date_format으로 날짜를 원하는 형태로 만들어서 비교한다!
SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
CASE STATUS
WHEN 'SALE' THEN '판매중' WHEN 'RESERVED' THEN '예약중' WHEN 'DONE' THEN '거래완료'
END AS STATUS
FROM USED_GOODS_BOARD
WHERE DATE_FORMAT(CREATED_DATE, '%Y-%m-%d') = '2022-10-05'
ORDER BY BOARD_ID DESC;
2. 2022/10에 작성된 게시글에 달린 댓글 정보 조회
- INNER JOIN을 사용해서 공통 키인 board_id를 기준으로 두 테이블을 연결해 하나의 결과로 합치기
- order by로 댓글 작성일과 게시글 제목을 기준으로 정렬한다!
SELECT b.title, b.board_id, r.reply_id, r.writer_id, r.contents, date_format(r.created_date, '%Y-%m-%d') as created_date
from used_goods_board b
join used_goods_reply r on b.board_id = r.board_id
where date_format(b.created_date, '%Y-%m') = '2022-10'
order by r.created_date asc, b.title asc;3. 경제 카테고리 도서의 정보와 저자 이름 함께 출력
- JOIN을 이용해서 두 테이블을 author_id로 연결
- where 절을 이용해서 문자열이 일치하는 것만 걸러냈다!
select b.book_id, a.author_name, date_format(b.published_date, '%Y-%m-%d') as published_date
from book b
join author a on b.author_id = a.author_id
where b.category = '경제'
order by b.published_date asc;4.상품 카테고리 코드별 상품 개수 구하기
- left() 함수를 이용해서 product_code에서 왼쪽 두글자만 추출하고, count()를 이용해서 id의 개수를 센다
- group by로 데이터를 그룹화해서 상품 개수를 모두 구하도록 묶어준다.
- order by 로 정렬해주면 끝!
SELECT left(product_code, 2) as category, count(product_id) as products
from product
group by left(product_code, 2)
order by category asc;- 부속질의로는 아래와 같이 해결할 수도 있다!
SELECT B.BOOK_ID,
(SELECT A.AUTHOR_NAME FROM AUTHOR A WHERE A.AUTHOR_ID = B.AUTHOR_ID) AS AUTHOR_NAME,
DATE_FORMAT(B.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK B
WHERE B.CATEGORY = '경제'
ORDER BY B.PUBLISHED_DATE ASC;5. 5/1을 기준으로 출고완료/출고대기/출고미정 상태 나누기
- date_format으로 out_date를 인식하도록 만들었다.
- case when을 사용해서 여기에서 날짜 범위는 부등호로 나눴다!
- 그리고 order by 로 정렬하면 됨.
SELECT order_id, product_id, date_format(out_date, '%Y-%m-%d') as out_date,
case when out_date <= '2022-05-01' then '출고완료'
when out_date > '2022-05-01' then '출고대기' else '출고미정'
end as '출고여부'
from food_order
order by order_id asc;
6. 음식 종류별로 가장 즐겨찾기 수가 많은 식당을 찾기
- 서브쿼리를 이용해서 group by로 음식 종류별로 그룹화 후 max(favorites)로 해당 그룹들에서 가장 높은 즐겨찾기 수를 찾는다!
- 메인 쿼리에서는 섭쿼리에서 찾은 음식 종류와 최대 즐겨찾기 수 조합과 일치하는 식당만 조회해서 id와 이름을 출력한다! (order by로 정렬도 해준다.)
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;
7.여러 대여 기록 중 하나라도 2022/10/16이 포함되어 있다면 대여중으로 출력하기
SELECT car_id, case
when sum(case
when '2022-10-16' between start_date and end_date then 1 else 0 end
) > 0 then '대여중'
else '대여 가능'
end as availability
from car_rental_company_rental_history
group by car_id
order by car_id desc;
8. 2022/01 기준 저자 및 카테고리별로 총매출액 계산
- join으로 도서/저자/판매량 테이블을 세 개 조인해서 데이터를 통합시켰다.
- group by로 저자 id, 저자명, 카테고리로 데이터를 묶어줬다 -> 집계 함수를 쓰기 위해서 했음
- sum() 함수를 이용해서 수량 * 단가를 계산하고 이걸 total_sales로 표시했다.
SELECT a.author_id, a.author_name, b.category, sum(s.sales*b.price) as total_sales
from book b
join author a on b.author_id = a.author_id
join book_sales s on b.book_id = s.book_id
where date_format(s.sales_date, '%Y-%m')='2022-01'
group by a.author_id, a.author_name, b.category
order by a.author_id asc, b.category desc;
9. 중고 거래 게시물을 3건 이상 등록한 사용자의 id, 닉네임, 전번을 조회하기
SELECT u.user_id, u.nickname,
concat(u.city, ' ', u.street_address1, ' ', u.street_address2) as 전체주소,
concat(substr(u.tlno, 1, 3), '-', substr(u.tlno, 4, 4), '-', substr(u.tlno, 8, 4)) as 전화번호
from used_goods_user u
join used_goods_board b on u.user_id = b.writer_id
group by u.user_id
having count(b.board_id) >= 3
order by u.user_id desc;'Develop > DataBase' 카테고리의 다른 글
| [DataBase] "EZPC" 개발 프로젝트 후기 (1) | 2026.06.06 |
|---|---|
| [6주차] 뷰, 함수, 프로시져 (1) | 2026.04.11 |
| [4주차] SQL 기초 (JOIN, CREATE, ALTER, DROP) (0) | 2026.03.28 |
| [3주차] SQL 기초 (SELECT문) (0) | 2026.03.21 |
| [2주차] 관계 데이터 모델 (DBMS) (0) | 2026.03.14 |