14. INNER JOIN 실습

테이블 삭제 DROP

-- 기존 테이블 삭제
DROP TABLE likes;
DROP TABLE settings;
DROP TABLE comments;
DROP TABLE photos;
DROP TABLE users;

테이블 새로 CREATE

-- users 테이블
CREATE TABLE users (
  id       SERIAL       PRIMARY KEY,
  nickname VARCHAR(50),
  email    VARCHAR(100)
);
-- photos 테이블
CREATE TABLE photos (
  id       SERIAL        PRIMARY KEY,
  filename VARCHAR(255),
  user_id  INTEGER       REFERENCES users(id)
);
-- comments 테이블
CREATE TABLE comments (
  id       SERIAL         PRIMARY KEY,
  body     VARCHAR(1000),
  user_id  INTEGER        REFERENCES users(id),
  photo_id INTEGER        REFERENCES photos(id)
);
-- settings 테이블
CREATE TABLE settings (
  id            SERIAL       PRIMARY KEY,
  private       BOOLEAN,
  adding_photos VARCHAR(15),
  user_id       INTEGER      UNIQUE REFERENCES users(id)
);
-- likes 테이블 생성(사용자와 사진을 다대다 연결)
CREATE TABLE likes (
  id       SERIAL  PRIMARY KEY,
  user_id  INTEGER REFERENCES users(id),
  photo_id INTEGER REFERENCES photos(id)
);

테이블 레코드 추가 INSERT

-- users 레코드
INSERT INTO
  users(nickname, email)
VALUES
  ('cloudstudying_kr', 'mail@cloudstudying.kr'),
  ('hongpark_cs',      'sehongpark@cloudstudying.kr'),
  ('haesamq',          'haesamq@naver.com')
;
-- photos 레코드
INSERT INTO
  photos(filename, user_id)
VALUES
  ('cat-on-road.jpg',           1),
  ('sunset-over-the-ocean.jpg', 1),
  ('andromeda-galaxy.jpg',      1),
  ('white-tiger.jpg',           2),
  ('nero-the-black-cat.jpg',    2),
  ('deleted-image.jpg',         NULL),
  ('blocked-image.jpg',         NULL)
;
-- comments 레코드
INSERT INTO
  comments(body, user_id, photo_id)
VALUES
  ('meow',             1, 1),
  ('nyaong',           2, 1),
  ('냐옹',              3, 1),
  ('sunset',           1, 2),
  ('falling slowly',   2, 2),
  ('Andromeda galaxy', 1, 3),
  ('mysteriouse..!',   3, 3),
  ('wow..!',           3, NULL),
  ('oh..!',            3, NULL)
;
-- settings 레코드
INSERT INTO
  settings(private, adding_photos, user_id)
VALUES
  (FALSE, 'MANUAL', 1),
  (FALSE, 'AUTO',   2),
  (TRUE,  'AUTO',   3)
;
-- likes 레코드
INSERT INTO
  likes(user_id, photo_id)
VALUES
  (1,    1),
  (2,    1),
  (1,    2),
  (2,    2),
  (3,    2),
  (1,    3),
  (3,    3),
  (NULL, 6),
  (NULL, 7)
;

---

INNER JOIN 문제1

-- 사진1에 달린 모든 댓글내용과 작성자의 닉네임을 조회하시오
SELECT body, users.nickname
	FROM comments
JOIN users ON
	users.id = comments.user_id
WHERE photo_id = 1;

---

INNER JOIN 문제2

-- 사진5번 파일의 파일명과 사진 게시자 닉네임을 조회하시오
SELECT filename, users.nickname
	FROM photos 
JOIN users ON
	photos.user_id = users.id
WHERE
	photos.id = 5;

---

INNER JOIN 문제3

-- 사진2를 좋아요한 사용자의 닉네임과 대상 사진의 파일명
SELECT 
	users.nickname, photos.filename
FROM likes
JOIN photos ON
	likes.photo_id = photos.id
JOIN users ON
	likes.user_id = users.id
WHERE likes.photo_id = 2;