1. SQL 실습 세팅
CREATE TABLE user (
user_id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL,
email TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE post (
post_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
title TEXT NOT NULL,
content TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES user(user_id)
);
CREATE TABLE comment (
comment_id INTEGER PRIMARY KEY AUTOINCREMENT,
post_id INTEGER,
user_id INTEGER,
comment TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(post_id) REFERENCES post(post_id),
FOREIGN KEY(user_id) REFERENCES user(user_id)
);-- 사용자
INSERT INTO user (username, email, created_at) VALUES
('Alice', 'alice@example.com', '2023-07-22'),
('Bob', 'bob@example.com', '2023-03-09'),
('Charlie', 'charlie@example.com', '2023-09-08'),
('Dave', 'dave@example.com', '2023-08-02'),
('Eve', 'eve@example.com', '2023-10-30'),
('Frank', 'frank@example.com', '2023-06-15'),
('Grace', 'grace@example.com', '2023-05-20'),
('Hannah', 'hannah@example.com', '2023-12-01');
-- 글
INSERT INTO post (user_id, title, content, created_at) VALUES
(1, 'Alice의 독서록', 'Alice의 독서록에 대한 내용입니다.', '2023-11-01'),
(1, 'Alice의 프로그래밍 팁', 'Alice의 프로그래밍 팁에 대한 내용입니다.', '2023-12-19'),
(1, 'Alice의 첫 게시글', 'Alice의 첫 게시글에 대한 내용입니다.', '2023-02-24'),
(2, 'Bob의 독서록', 'Bob의 독서록에 대한 내용입니다.', '2023-01-08'),
(2, 'Bob의 첫 게시글', 'Bob의 첫 게시글에 대한 내용입니다.', '2023-03-30'),
(3, 'Charlie의 요리 레시피', 'Charlie의 요리 레시피에 대한 내용입니다.', '2023-04-15'),
(4, 'Dave의 일상', 'Dave의 일상에 대한 내용입니다.', '2023-07-03'),
(5, 'Eve의 독서록', 'Eve의 독서록에 대한 내용입니다.', '2023-09-21'),
(6, 'Frank의 영화 리뷰', 'Frank의 영화 리뷰에 대한 내용입니다.', '2023-02-11'),
(7, 'Grace의 운동 일지', 'Grace의 운동 일지에 대한 내용입니다.', '2023-05-29'),
(8, 'Hannah의 프로그래밍 팁', 'Hannah의 프로그래밍 팁에 대한 내용입니다.', '2023-08-19');
-- 댓글
INSERT INTO comment (post_id, user_id, comment, created_at) VALUES
(1, 2, '잘 보고 갑니다.', '2023-08-14'),
(1, 7, '정말 재미있어 보여요!', '2023-04-12'),
(2, 7, '재미있게 읽었습니다.', '2023-11-05'),
(3, 5, '잘 보고 갑니다.', '2023-02-22'),
(4, 3, '응원합니다!', '2023-03-17'),
(5, 4, 'Eve, 이 책 저도 읽어봤어요. 정말 좋더라고요!', '2023-09-25'),
(6, 1, 'Frank, 영화 리뷰 잘 보았습니다. 추천해주실만한가요?', '2023-02-13'),
(7, 2, 'Grace의 운동 일지 정말 도움이 많이 되네요. 감사합니다!', '2023-06-01'),
(8, 3, 'Hannah, 프로그래밍 팁 잘 읽었습니다. 다음 팁도 기대할게요!', '2023-08-21'),
(3, 6, 'Charlie, 요리 레시피 너무 맛있어 보여요. 시도해볼게요!', '2023-04-17'),
(4, 5, 'Dave의 일상이 재미있네요. 더 많은 이야기 기대합니다.', '2023-07-05');2. SQL 실습
-
user 1번은 아마 “alice” 일 것이다. 이 정보를 활용해 1번 user_id가 작성한 게시글의 모든 속성을 포함한 모든 데이터 조회하기
SELECT * FROM post WHERE user_id = 1; -
이메일이
alice@example.com인 사용자의 모든 정보를 조회SELECT * FROM user WHERE email = 'alice@example.com'; -
가장 최근에 작성된 게시글 5개의 제목과 내용을 조회
SELECT title, content FROM post ORDER BY created_at DESC LIMIT 5; -
게시글 내용에 “독서” 라는 text가 “포함” 된 게시글의 모든 내용을 조회
SELECT * FROM post WHERE content LIKE '%독서%'; -
3번 게시글에 달린 댓글 모든 내용 조회
SELECT * FROM comment WHERE post_id = 3; -- 개수는? SELECT count(*) as '개수' FROM comment WHERE post_id = 3; -
Alice 가 작성한 게시글을 찾고 → 해당 게시글에 달린 모든 댓글 모든 내용 조회
SELECT * FROM user WHERE username = 'Alice'; -- 위에서 user_id 값 확인하고 SELECT * FROM post WHERE user_id = 1; -- 해당 user_id = 1 의 모든 게시글 확인하고 SELECT * FROM comment WHERE post_id in (1, 2, 3); -- in 이라는 연산자 사용해보기 -
Alice 가 작성한 게시글의 수를 계산 (“서브 쿼리 사용”)
SELECT COUNT(*) FROM post WHERE user_id = ( SELECT user_id FROM user WHERE username = 'Alice' );
3. SQL Join 실습
-
Alice 가 작성한 모든 게시글의 제목과 내용 조회하기
SELECT title, content FROM post JOIN user ON post.user_id = user.user_id WHERE username = 'Alice'; -
각 게시글에 달린 댓글 수 조회하기
SELECT post.post_id, COUNT(comment.comment_id) AS comment_count FROM post LEFT JOIN comment ON post.post_id = comment.post_id GROUP BY post.post_id; -
특정 게시글에 달린 댓글의 내용과 댓글을 단 사용자의 이름 조회하기 (예시: post_id = 1)
SELECT comment.comment, user.username FROM comment JOIN user ON comment.user_id = user.user_id WHERE comment.post_id = 1; -
모든 게시글에 대한 평균 댓글 수를 계산
SELECT AVG(comment_count) FROM ( SELECT post_id, COUNT(comment_id) AS comment_count FROM comment GROUP BY post_id );
황세민