📌 Oracle SQL 학습 정리 - 함수, 서브쿼리, GROUP BY, 조인까지 종합 정리

📅 날짜

2025-03-25

---

✅ 1. SQL 함수(Function) 개념

SQL에서 함수(Function)는 특정 작업을 수행하기 위해 미리 정의된 명령어 집합을 의미합니다.
입력값을 받아 처리한 후 결과를 반환하며, 데이터를 가공하거나 분석할 때 필수적으로 사용됩니다.

🔹 함수의 주요 분류

• 단일행 함수(Single-Row Function): 각 행마다 하나의 결과를 반환합니다.
  예: UPPER(), ROUND(), TO_CHAR() 등
• 다중행 함수(Multi-Row Function): 여러 행을 하나의 그룹으로 묶어 하나의 결과를 반환합니다.
  예: SUM(), AVG(), COUNT() 등
• 분석 함수(Window Function): 집계된 결과를 각 행에 다시 적용하여 누적 합계, 순위 등을 보여줍니다.
  예: RANK(), LAG(), SUM() OVER() 등

---

좋아! 아래는 벨로그 업로드용 마크다운 형식으로 정리한 2, 3, 4, 5번 항목이야.
각 항목은 함수 설명 + 코드 예제까지 포함했어. 이대로 복사해서 벨로그에 올리면 완벽해 ✅

⸻

✅ 2. 단일행 함수 정리

🔹 문자형 함수 (Character Functions)

SELECT name,
       LOWER(name) AS lower_name,
       UPPER(name) AS upper_name,
       INITCAP(name) AS initcap_name,
       LENGTH(name) AS name_len,
       SUBSTR(name, 1, 2) AS first_two_chars,
       INSTR(name, '김') AS position_kim
FROM userTbl;

🔹 숫자형 함수 (Number Functions)

SELECT price,
       ROUND(price / 1000, 2) AS rounded,
       TRUNC(price / 1000, 1) AS truncated,
       MOD(price, 100) AS remainder,
       CEIL(price / 100) AS ceiled,
       FLOOR(price / 100) AS floored
FROM buyTbl;

🔹 날짜형 함수 (Date Functions)

SELECT name, mDate,
       SYSDATE AS today,
       ADD_MONTHS(mDate, 6) AS six_months_later,
       MONTHS_BETWEEN(SYSDATE, mDate) AS months_diff,
       NEXT_DAY(mDate, '금요일') AS next_friday,
       LAST_DAY(mDate) AS last_day_of_month
FROM userTbl;

🔹 NULL 관련 함수

SELECT name,
       NVL(mobile1, '없음') AS nvl_result,
       NVL2(mobile1, '연락처 있음', '미입력') AS contact_check,
       COALESCE(mobile1, mobile2, '미기재') AS best_contact,
       NULLIF(price, 30) AS null_if_price
FROM userTbl u
JOIN buyTbl b ON u.userID = b.userID;

🔹 조건부 함수 (CASE, DECODE)

SELECT name, height,
       CASE
           WHEN height >= 180 THEN '키 큼'
           WHEN height >= 170 THEN '보통'
           ELSE '작음'
       END AS height_grade
FROM userTbl;

⸻

✅ 3. 다중행 함수 (집계 함수)

🔹 전체 통계 계산

SELECT COUNT(*) AS 전체인원,
       COUNT(mobile1) AS 연락처입력,
       AVG(height) AS 평균키,
       MAX(height) AS 최고키,
       MIN(height) AS 최저키,
       STDDEV(height) AS 키표준편차,
       VARIANCE(height) AS 키분산
FROM userTbl;

🔹 지역별 사용자 통계 (GROUP BY)

SELECT addr,
       COUNT(*) AS 인원수,
       AVG(height) AS 평균키
FROM userTbl
GROUP BY addr;

🔹 HAVING 절 사용 예제

SELECT addr, AVG(height) AS 평균키
FROM userTbl
GROUP BY addr
HAVING AVG(height) >= 170;

⸻

✅ 4. 서브쿼리 및 GROUP BY 고급

🔹 지역 평균보다 많이 구매한 사용자 (서브쿼리)

SELECT u.userID, u.addr, SUM(b.price * b.amount) AS 구매총액
FROM userTbl u
JOIN buyTbl b ON u.userID = b.userID
GROUP BY u.userID, u.addr
HAVING SUM(b.price * b.amount) > (
    SELECT AVG(total)
    FROM (
        SELECT u2.addr, SUM(b2.price * b2.amount) AS total
        FROM userTbl u2
        JOIN buyTbl b2 ON u2.userID = b2.userID
        GROUP BY u2.addr
        HAVING u2.addr = u.addr
    )
);

🔹 ROLLUP

SELECT groupName, SUM(price * amount) AS 판매금액
FROM buyTbl
WHERE groupName IS NOT NULL
GROUP BY ROLLUP(groupName);

🔹 CUBE

SELECT groupName, prodName, SUM(price * amount) AS 판매금액
FROM buyTbl
WHERE groupName IS NOT NULL
GROUP BY CUBE(groupName, prodName);

🔹 GROUPING SETS

SELECT groupName, prodName, SUM(price * amount) AS 판매금액
FROM buyTbl
WHERE groupName IS NOT NULL
GROUP BY GROUPING SETS((groupName), (prodName), ());

⸻

✅ 5. 분석 함수 (Window Function)

🔹 순위 함수

SELECT name, height,
       RANK() OVER (ORDER BY height DESC) AS rank_result,
       DENSE_RANK() OVER (ORDER BY height DESC) AS dense_rank_result,
       ROW_NUMBER() OVER (ORDER BY height DESC) AS row_num
FROM userTbl;

🔹 파티션별 순위

SELECT name, addr, height,
       RANK() OVER (PARTITION BY addr ORDER BY height DESC) AS local_rank
FROM userTbl;

🔹 이전/다음 행 참조 (LAG, LEAD)

SELECT name, birthYear, height,
       LAG(name, 1, '없음') OVER (ORDER BY birthYear) AS 이전사람,
       LEAD(name, 1, '없음') OVER (ORDER BY birthYear) AS 다음사람
FROM userTbl;

🔹 누적 합계

SELECT userID, prodName, price * amount AS 구매금액,
       SUM(price * amount) OVER (PARTITION BY userID ORDER BY num) AS 누적구매금액
FROM buyTbl
ORDER BY userID, num;

⸻

✅ 6. 조인(Join) 정리

🔹 조인이란?

조인(Join)은 두 개 이상의 테이블을 연결하여 관련 데이터를 하나의 결과로 조회할 수 있도록 하는 SQL 기능입니다.
관계형 데이터베이스에서 테이블 간 관계를 설정해 복합 정보를 얻을 수 있습니다.

---

🔹 INNER JOIN (내부 조인)

양쪽 테이블에서 조건이 일치하는 행만 반환합니다.

✅ 회원별 총 구매 금액

SELECT u.userID, u.name, SUM(b.price * b.amount) AS 총구매금액
FROM userTbl u
JOIN buyTbl b ON u.userID = b.userID
GROUP BY u.userID, u.name;

✅ 특정 상품(예: '책') 구매 회원

SELECT DISTINCT u.name
FROM userTbl u
JOIN buyTbl b ON u.userID = b.userID
WHERE b.prodName = '책';

✅ 가장 많이 구매한 회원 (수량 기준)

SELECT rownum AS RN, name, 총수량
FROM (
  SELECT u.name, SUM(b.amount) AS 총수량
  FROM userTbl u
  JOIN buyTbl b ON u.userID = b.userID
  GROUP BY u.name
  ORDER BY 총수량 DESC
)
WHERE rownum = 1;

---

🔹 OUTER JOIN (외부 조인)

한쪽 테이블의 데이터를 모두 포함하고, 연결되지 않은 쪽은 NULL로 표시됩니다.

✅ LEFT OUTER JOIN (왼쪽 테이블 기준)

SELECT *
FROM userTbl u
LEFT JOIN buyTbl b ON u.userID = b.userID;

✅ 구매하지 않은 회원 찾기 (LEFT JOIN + NULL 필터)

SELECT u.userID, u.name
FROM userTbl u
LEFT JOIN buyTbl b ON u.userID = b.userID
WHERE b.userID IS NULL;

✅ RIGHT OUTER JOIN (오른쪽 테이블 기준)

SELECT u.name, b.prodName, b.price
FROM userTbl u
RIGHT JOIN buyTbl b ON u.userID = b.userID;

---

🔹 FULL OUTER JOIN

양쪽 테이블의 모든 행을 조회하며, 일치하지 않는 데이터는 NULL로 채웁니다.

✅ 실습 테이블 예제 (studentTbl, examTbl)

-- 학생 테이블
CREATE TABLE studentTbl (
  studentID CHAR(5) PRIMARY KEY,
  name VARCHAR2(20)
);

-- 시험 응시 테이블
CREATE TABLE examTbl (
  examID CHAR(5) PRIMARY KEY,
  studentID CHAR(5),
  subject VARCHAR2(20),
  score NUMBER
);

-- 데이터 삽입
INSERT INTO studentTbl VALUES ('S001', '홍길동');
INSERT INTO studentTbl VALUES ('S002', '김철수');
INSERT INTO studentTbl VALUES ('S003', '이영희');
INSERT INTO studentTbl VALUES ('S004', '박지민');

INSERT INTO examTbl VALUES ('E101', 'S001', '수학', 85);
INSERT INTO examTbl VALUES ('E102', 'S002', '영어', 90);
INSERT INTO examTbl VALUES ('E103', 'S005', '국어', 88);

✅ FULL OUTER JOIN 실행

SELECT s.name AS 학생이름, e.subject AS 과목, e.score
FROM studentTbl s
FULL OUTER JOIN examTbl e ON s.studentID = e.studentID;

---

🔹 조인 정리 요약표

조인 유형	설명
INNER JOIN	양쪽 테이블에서 조건에 맞는 데이터만 반환
LEFT OUTER JOIN	왼쪽 테이블 전체 + 오른쪽 일치하는 행만
RIGHT OUTER JOIN	오른쪽 테이블 전체 + 왼쪽 일치하는 행만
FULL OUTER JOIN	양쪽 테이블 전체 + 일치하지 않으면 NULL 반환

---