SQL 특강

홍순구 튜터님의 SQL 뿌셔뿌셔

부서지는 건 나였다....

1) 현재 시간 조회 및 USERS 테이블 기본 조회 예시
- 데이터베이스 연결 확인용으로 현재 시간을 조회하고, USERS 테이블 전체를 간단히 조회합니다.
SELECT NOW() FROM dual;
SELECT * FROM USERS;


2) (참조 링크) Spring Data JPA Query Method 문서
- JPA 리포지토리 메서드 이름으로 쿼리를 만드는 규칙을 정리한 공식 문서 링크입니다.
- https://docs.spring.io/spring-data/jpa/reference/jpa/query-methods.html

3) 초기화: 기존 테이블이 있으면 삭제 (실습 중 이상 현상 방지)
- 재실행해도 에러가 나지 않도록 EMPLOYEES, DEPARTMENTS 테이블을 먼저 제거합니다.
DROP TABLE IF EXISTS EMPLOYEES;
DROP TABLE IF EXISTS DEPARTMENTS;

4) DEPARTMENTS(부서) 테이블 생성
- 부서 기본키(id)와 부서명(name)을 저장하는 테이블을 생성합니다.
CREATE TABLE DEPARTMENTS (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

5) EMPLOYEES(사원) 테이블 생성
- 사원 기본키(id), 사원명(name), 소속부서(dept_id)를 저장합니다.
- dept_id는 DEPARTMENTS.id를 참조하는 역할(논리적 FK)입니다.
CREATE TABLE EMPLOYEES (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    dept_id BIGINT
);


6) 예시 데이터 삽입 (부서 3개)
- 부서 테이블에 실습용 부서 데이터를 넣습니다.
INSERT INTO DEPARTMENTS (id, name) VALUES (10, '기획팀');
INSERT INTO DEPARTMENTS (id, name) VALUES (20, '개발팀');
INSERT INTO DEPARTMENTS (id, name) VALUES (30, '디자인팀');


7) 예시 데이터 삽입 (사원 4명) - 실명 제거
--사원 테이블에 실습용 사원 데이터를 넣습니다. (name은 익명 처리)
INSERT INTO EMPLOYEES (id, name, dept_id) VALUES (101, '사원A', 10);  -- 기획팀
INSERT INTO EMPLOYEES (id, name, dept_id) VALUES (102, '사원B', 20);  -- 개발팀
INSERT INTO EMPLOYEES (id, name, dept_id) VALUES (103, '사원C', 20);  -- 개발팀
INSERT INTO EMPLOYEES (id, name, dept_id) VALUES (104, '사원D', 30);  -- 디자인팀

COMMIT;


8) CROSS JOIN 예시
- 두 테이블의 모든 조합(카테시안 곱)을 반환합니다. (조인 조건이 없어서 행이 크게 늘어날 수 있음)
SELECT *
FROM EMPLOYEES
CROSS JOIN DEPARTMENTS;


9) INNER JOIN 예시 (부서 소속으로 정확히 매칭)
- EMPLOYEES.dept_id와 DEPARTMENTS.id가 같은 행만 결합해 사원-부서를 연결합니다.
SELECT EMPLOYEES.id, EMPLOYEES.name, DEPARTMENTS.name
FROM EMPLOYEES
JOIN DEPARTMENTS
ON EMPLOYEES.dept_id = DEPARTMENTS.id;

----

정리 하다가 포기

넘나 어렵다. 밑에는 정리 못한 나머지 내용 입니다....

홍순구(스프링_튜터) 5:02 PM
SELECT U.username, C.comment_text, C.post_id
FROM COMMENTS C
JOIN USERS U
ON C.user_id = U.user_id
;
SELECT U.username, C.comment_text, P.content
FROM COMMENTS C
JOIN USERS U
ON C.user_id = U.user_id
JOIN POSTS P
ON P.post_id = C.post_id
;

홍순구(스프링_튜터) 5:20 PM
SELECT *
FROM USERS U
JOIN USER_PROFILES UP
ON U.user_id = UP.user_id
;
SELECT U.user_id, U.username, UP.bio
FROM USERS U
JOIN USER_PROFILES UP
ON U.user_id = UP.user_id
;
SELECT U.user_id, U.username, UP.bio
FROM USERS U
LEFT OUTER JOIN USER_PROFILES UP
ON U.user_id = UP.user_id
ORDER BY U.user_id
;

홍순구(스프링_튜터) 5:26 PM
SELECT U.*, UP.*
FROM USERS U
         LEFT JOIN USER_PROFILES UP
                   ON U.user_id = UP.user_id
ORDER BY U.user_id
;

홍순구(스프링_튜터) 5:32 PM
SELECT U.user_id, U.username, U.manager_id
FROM USERS U
;
SELECT U.user_id, U.username, U.manager_id, M.username
FROM USERS U
JOIN USERS M
ON U.manager_id = M.user_id
;

이재민(Spring_3기) 5:37 PM
서브쿼리

홍순구(스프링_튜터) 5:40 PM
-- ryan이 작성한 피드게시물을 조회
-- 비연관 서브쿼리: 서브쿼리 독자적 실행 가능
SELECT *
FROM POSTS
WHERE user_id = (
    SELECT user_id
    FROM USERS
    WHERE username = 'ryan'
)
;

홍순구(스프링_튜터) 5:47 PM
SELECT P.content, U.username, U.email
FROM POSTS P
JOIN USERS U
ON P.user_id = U.user_id
WHERE P.user_id = (
    SELECT user_id
    FROM USERS
    WHERE username = 'ryan'
)
;
SELECT u.username, (SELECT COUNT(*) FROM POSTS p WHERE p.user_id = u.user_id) AS "작성 게시물 수" -- 1. 바깥 쿼리의 u.user_id를 참조 FROM USERS u ;

홍순구(스프링_튜터) 5:54 PM
-- =========== 연관 서브쿼리 ===========
-- 연관 서브쿼리는 단독실행이 안됨,
-- 바깥쪽이 먼저 실행되고 그다음 서브쿼리가 실행됨
-- 메인쿼리 1번실행당 서브쿼리도 1번씩 반복실행됨

SELECT
    u.username,
    (
     SELECT COUNT(*)
     FROM POSTS p
     WHERE p.user_id = 3
     ) AS "작성 게시물 수" -- 1. 바깥 쿼리의 u.user_id를 참조
FROM
    USERS u
;

SELECT U.username, U.user_id
FROM USERS U
;

SELECT P.user_id, P.content
FROM POSTS P
WHERE P.user_id = 3
;

SELECT count(*)
FROM POSTS P
WHERE P.user_id = 3
;
SELECT u.username, COUNT(P.user_id)
FROM USERS U
LEFT JOIN POSTS P
ON U.user_id = P.user_id
GROUP BY P.user_id