SELECT NOW() FROM dual;
SELECT * FROM USERS;

SELECT *
FROM USERS
WHERE username = 'ryan';

SELECT *
FROM POSTS
WHERE user_id = 1
  AND post_type = 'video'
;

-- 가입일(registration_date)이 2022년도인 사용자를 찾습니다.
SELECT username, registration_date
FROM USERS
WHERE registration_date BETWEEN '2022-01-01' AND '2022-12-31';


SELECT username, registration_date
FROM USERS
WHERE registration_date >= '2022-01-01'
    AND registration_date <= '2022-12-31';

-- user_id가 1, 9, 21 중 하나에 해당하는 사용자 정보를 찾습니다.
SELECT *
FROM USERS
WHERE user_id IN (1, 9, 21);

SELECT *
FROM USERS
WHERE user_id = 1
    OR user_id = 9
    OR user_id = 21;

-- 'p'로 시작하는 모든 사용자 검색
SELECT username
FROM USERS
WHERE username LIKE 'p%';

-- manager_id가 NULL인 사용자를 찾습니다.
SELECT *
FROM USERS
WHERE manager_id IS NULL
;

JOIN

DROP TABLE IF EXISTS EMPLOYEES;
DROP TABLE IF EXISTS DEPARTMENTS;

-- 1. 부서 테이블 (DEPARTMENTS) 생성
CREATE TABLE DEPARTMENTS (
                             id BIGINT PRIMARY KEY,
                             name VARCHAR(50) NOT NULL
);

-- 2. 사원 테이블 (EMPLOYEES) 생성
CREATE TABLE EMPLOYEES (
                           id BIGINT PRIMARY KEY,
                           name VARCHAR(50) NOT NULL,
                           dept_id BIGINT -- DEPARTMENTS 테이블의 id를 참조
);

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


-- 사원 테이블에는 4명의 사원을 넣어봅시다.
INSERT INTO EMPLOYEES (id, name, dept_id) VALUES (101, '김철수', 10);  -- 기획팀
INSERT INTO EMPLOYEES (id, name, dept_id) VALUES (102, '박영희', 20);  -- 기획팀
INSERT INTO EMPLOYEES (id, name, dept_id) VALUES (103, '이지은', 20);  -- 기획팀
INSERT INTO EMPLOYEES (id, name, dept_id) VALUES (104, '최민준', 30);  -- 기획팀

COMMIT;


SELECT E.id, E.name, D.name
FROM EMPLOYEES E
JOIN DEPARTMENTS D
ON E.dept_id = D.id
;


-- user_id가 1인 유저가 쓴 피드의
-- 작성자명, 피드내용, 피드작성일을 조회
SELECT U.username, P.content, P.creation_date
FROM POSTS P
JOIN USERS U
ON P.user_id = U.user_id
WHERE P.user_id = 1
;

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
;

-- ============== OUTER JOIN ============

SELECT U.user_id, U.username, UP.bio
FROM USERS U
LEFT JOIN USER_PROFILES UP
ON U.user_id = UP.user_id
ORDER BY U.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
WHERE UP.bio IS NULL
ORDER BY U.user_id
;


SELECT U.*, UP.*
FROM USERS U
         LEFT JOIN USER_PROFILES UP
                   ON U.user_id = UP.user_id
ORDER BY U.user_id
;

-- ======================================
-- ==   self join====================================

SELECT U.user_id, U.username, U.manager_id, M.username
FROM USERS U
JOIN USERS M
ON U.manager_id = M.user_id
;

--

SubQuery

-- ryan이 작성한 피드게시물을 조회
-- 비연관 서브쿼리: 서브쿼리 독자적 실행 가능, 안쪽먼저 실행 -> 바깥쪽 실행
SELECT *
FROM POSTS
WHERE user_id = (
    SELECT user_id
    FROM USERS
    WHERE username = 'ryan'
)
;

SELECT *
FROM POSTS
WHERE user_id = (
    SELECT user_id
    FROM USERS
    WHERE username LIKE 'p%'
)
;


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'
)
;


-- =========== 연관 서브쿼리 ===========
-- 연관 서브쿼리는 단독실행이 안됨,
-- 바깥쪽이 먼저 실행되고 그다음 서브쿼리가 실행됨
-- 메인쿼리 1번실행당 서브쿼리도 1번씩 반복실행됨

SELECT
    u.username,
    (
     SELECT COUNT(*)
     FROM POSTS p
     WHERE p.user_id = u.user_id
     ) 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 = 24
;

SELECT count(*)
FROM POSTS P
WHERE P.user_id = 24
;

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
;

SELECT
    p.post_id,
    p.content,
    (SELECT COUNT(*) FROM LIKES l WHERE l.post_id = p.post_id) AS "좋아요 수",
    (SELECT COUNT(*) FROM COMMENTS c WHERE c.post_id = p.post_id) AS "댓글 수"
FROM
    POSTS p
;

SELECT
    p.post_id,
    p.content,
    COALESCE(lc.like_count, 0) AS "좋아요 수",
    COALESCE(cc.comment_count, 0) AS "댓글 수"
FROM
    POSTS p
        LEFT JOIN
    (SELECT post_id, COUNT(*) AS like_count FROM LIKES GROUP BY post_id) lc
    ON p.post_id = lc.post_id -- '좋아요 수' 요약 테이블을 조인
        LEFT JOIN
    (SELECT post_id, COUNT(*) AS comment_count FROM COMMENTS GROUP BY post_id) cc
    ON p.post_id = cc.post_id -- '댓글 수' 요약 테이블을 조인
;