Sprint 풀이

---

첫 번째로 해야할 일

위의 사진 처럼 연결되어 있는 schema를 살펴보자. 위처럼 테이블을 만들어주기 위해 migrations/schema.sql의 part 5의 주석을 내용을 채워보자.

CREATE TABLE `category` (
  -- TODO: id, name 추가하기
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255)
);

CREATE TABLE `content_category` (
  -- TODO: 조인테이블 만들기. id, contentId, categoryId 만들기
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `contentId` int NOT NULL, FOREIGN KEY (`contentId`) REFERENCES `content` (`id`),
  `categoryId` int NOT NULL, FOREIGN KEY (`categoryId`) REFERENCES `category`(`id`)
);

CREATE TABLE `role` (
  -- TODO: id, name 추가하기
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255)
);

ALTER TABLE `user` ADD roleId int;
ALTER TABLE `user` ADD FOREIGN KEY (`roleId`) REFERENCES `role` (`id`);

• category 테이블 만들기

CREATE TABLE `category` (
  -- TODO: id, name 추가하기
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255)
);

mysql> SELECT * FROM category;
+----+--------+
| id | name   |
+----+--------+
|  1 | soccer |
|  2 | family |
|  3 | health |
+----+--------+
3 rows in set (0.00 sec)

위의 코드를 이용해서 category라는 테이블을 만들어본다. 그 안의 데이터로는 Primary key를 가질 수 있도록 id를 만들고, 정수인 int로 선언한다. 이후 PRIMARY KEY AUTO_INCREMENT로 데이터가 추가 될 때, 자동적으로 Primary Key가 생성 될 수 있도록 한다.

• content_category 테이블 만들기

CREATE TABLE `content_category` (
  -- TODO: 조인테이블 만들기. id, contentId, categoryId 만들기
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `contentId` int NOT NULL, FOREIGN KEY (`contentId`) REFERENCES `content` (`id`),
  `categoryId` int NOT NULL, FOREIGN KEY (`categoryId`) REFERENCES `category`(`id`)
);

mysql> SELECT * From content_category;
+----+-----------+------------+
| id | contentId | categoryId |
+----+-----------+------------+
|  1 |         1 |          1 |
|  2 |         1 |          3 |
|  3 |         2 |          2 |
|  4 |         2 |          1 |
+----+-----------+------------+
4 rows in set (0.00 sec)

content와 category는 현재 N:N의 관계에 있다. 하여 1:N의 관계를 만들기 위해서, join table인 content_category를 만드는 것이다.

위와 같이 PRIMARY KEY AUTO_INCREMENT로 데이터가 추가 될 때, 자동적으로 Primary Key가 생성 될 수 있도록 한다. contentId와 categoryId는 Foreign Key로 들어가 있기 때문에, 정수인 int로 선언 해주며, Foreign Key로 설정해주기 위해서 FOREIGN KEY(’contentId’)로 선언한다. 또한 Foreign Key는 content 테이블의 Primary Key 참조 하기 때문에 REFERENCE content (id)로 선언해준다.

int NOT NULL을 설정해 준 이유는, id와 같은 민감한 데이터는 없으면 오류가 나야하기 때문이라고 한다. (설정해 주지 않으면, 따로 id값이 없다고 오류가 나지 않나보다.)

• role 테이블 만들기

CREATE TABLE `role` (
  -- TODO: id, name 추가하기
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255)
);

mysql> SELECT * FROM role;
+----+----------+
| id | name     |
+----+----------+
|  1 | attacker |
|  2 | defense  |
+----+----------+
2 rows in set (0.00 sec)

category 테이블에서 만들었던것과 같은 내용이다.

• user에 roleId 추가하기

ALTER TABLE `user` ADD roleId int;
ALTER TABLE `user` ADD FOREIGN KEY (`roleId`) REFERENCES `role` (`id`);

mysql> SELECT * FROM user;
+----+-------------+-------------------------------+--------+
| id | name        | email                         | roleId |
+----+-------------+-------------------------------+--------+
|  1 | jiSungPark  | jisung.park@manchester.united |      1 |
|  2 | woonJaeLee  | woonJae.Lee@goal.keeper       |   NULL |
|  3 | youngPyoLee | youngPyo.Lee@shadow.leg       |      2 |
|  4 | myungBoHong | myungBo.Hong@korea.joojang    |      2 |
|  5 | duRiCha     | duRi.Cha@run.fast             |      2 |
+----+-------------+-------------------------------+--------+
5 rows in set (0.00 sec)

ALTER TABLE을 이용해서 user 테이블에 roleId를 추가한다. user와 role 테이블은 1:N의 관계를 가지고 있으므로, user 테이블에서 roleId를 참조하여 Foreign Key를 갖고 있게 한다.

두 번째로 해야할 일

• category 테이블의 구조를 보기 위한 SQL

DESC category

• category 테이블에 존재하는 데이터에서 id, name을 찾는 SQL

SELECT category.id, category.name FROM category

• user의 name과 email 그리고 그 user가 속한 role name(컬럼명 : roleName)을 찾는 SQL
  • 속한 role이 없더라도 user의 name과 email, role name을 모두 찾아야 한다.

SELECT user.name, user.email role.name AS roleName FROM user left JOIN role ON user.roleId=role.id

위 문제를 다시 보면 속한 role이 없다는 것은 해당 값이 NULL일 경우에도 해당 값을 모두 찾아야한다는 의미이다. 그렇기 때문에 기준이되는 user를 기준으로 하는 left join으로 role이 NULL값인 것도 불러올 수 있도록 한다.

user와 role 테이블에는 name이라는 필드가 같이 존재한다. 그렇기때문에 해당 name이 어디에 속해있는 값인지 명확하게 해줄 필요가 있다. user에만 존재하는 email은 그냥 써도 무관하지만, 정확한 확인을 위하여 써주는 것이 좋겠다.

• 어느 role에도 속하지 않는 user의 모든 컬럼 데이터를 찾기위한 SQL

SELECT * FROM user WHERE roleId IS NULL

role에 속하지 않는 user의 모든 컬럼 데이터라는 말은 곧, user와 role이 JOIN이 될 PK나 FK가 존재하지 않는다는 말과 같다. user의 데이터를 보면 roleId가 NULL인 값이 있는데 이 부분을 출력할 수 있는 구문을 만들면 되겠다.

• content_category 테이블에 존재하는 모든 컬럼의 데이터를 찾기위한 SQL

SELECT * FROM content_category

• jiSungPark이 작성한 content의 title을 찾기위한 SQL

SELECT title FROM content JOIN user ON content.userId=user.id WHERE user.name='jiSungPark'

content에는 jiSungPark이라는 데이터가 저장되어 있는 name항목이 존재하지 않는다. 이 항목은 user 테이블에서 찾아와야하므로 content와 user가 JOIN이 되어야 한다. 이후 name의 값이 jiSungPark인 경우만 찾아와야 하므로 WHERE으로 조건을 붙여 결과를 출력하면 되겠다.

• JiSungPark이 작성한 content의 category name을 찾기위한 SQL
  1. JiSungPark이 user에 있는 name이므로 user와 content와의 JOIN이 필요하다.
  2. category name을 찾기 위해서 content와의 JOIN이 필요하지만 N:N의 관계이므로 중간에 있는content_category와의 JOIN을 먼저 수행하여 1:N의 관계로 만든다.
  3. 나머지 1:N의 관계를 만들기 위해 content_category와 category의 JOIN을 수행한다.
  4. user의 name이 JiSungPark인 경우로 값을 가져온다.

SELECT category.name FROM category 
JOIN content_category ON category.id=content_category.categoryId 
JOIN content ON content_category.contentId=content.id 
JOIN user ON content.userId=user.id 
WHERE user.name='jiSungPark'

• category의 name이 soccer인 content의 title, body, created_at을 찾기위한 SQL
  1. category name이 soccer인 경우를 찾아야 하므로 category와 content와의 JOIN이 필요하다.
  2. 위의 경우와 비슷하게 category와 content를 JOIN으로 연결하고 싶지만, N:N의 관계이기 때문에 content_category인 JOIN테이블을 이용하여 1:N의 관계로 만들자.
  3. content와 content_category의 JOIN으로 1:N의 관계를 만든다.
  4. 나머지 content_category와 category와의 JOIN으로 1:N을 만든다.
  5. category의 name이 soccer인 경우로 값을 가져온다.

SELECT content.title, content.body, content.created_at FROM content 
JOIN content_category ON content.id=content_category.contentId 
JOIN category ON content_category.categoryId=category.id 
WHERE category.name='soccer'

• category의 name이 soccer인 content의 title, body, created_at, user의 name을 찾기위한 SQL
  1. 위의 경우와 비슷하게 category name이 soccer인 content의 title, body, created_at 뿐만 아니라 user의 name의 데이터를 가지고 와야한다.
  2. schema를 보면 user에서 category까지의 JOIN이 필요하므로 user와 content의 JOIN이 필요하다.
  3. content와 category의 JOIN이 필요하며, 위의 경우와 마찬가지로 content_category JOIN 테이블을 이용하여 JOIN 한다.
  4. category의 name이 soccer인 경우로 값을 가져온다.

SELECT content.title, content.body, content.created_at, user.name FROM user 
JOIN content ON user.id=content.userId 
JOIN content_category ON content.id=content_category.contentId 
JOIN category ON content_category.categoryId=category.id 
WHERE category.name='soccer'

• duRiCha가 작성한 글의 개수 (컬럼명: ContentCount)를 출력하기 위한 SQL
  1. 글의 갯수는 content에 title과 body를 기준으로 COUNT를 사용하면 되겠다. 해당 테이블을 ContentCount로 보이보록 하기 위해 AS를 사용한다.
  2. duRiCha라는 name은 user에 있는 내용이므로 content와 user를 JOIN해준다.
  3. user의 name이 duRiCha인 경우로 값을 가져온다.

SELECT COUNT(title) AS ContentCount FROM content 
JOIN user ON content.userId=user.id 
WHERE user.name='duRiCha'

• 각 user(컬럼명: name)가 작성한 글의 개수 (컬럼명: ContentCount)를 출력하기 위한 SQL
  1. 위와 같은 방식으로 글의 갯수는 content에 title이나 body를 기준으로 COUNT하면 되겠다. 해당 테이블을 별칭으로 가지고 오기 위해 AS를 사용하여 user.name은 name, COUNT는 ContentCount로 가져온다.
  2. content와 user를 JOIN하기 전에 content에 title이 NULL값이어도 user는 출력이 되어야 하므로 Right나 Left JOIN을 사용한다.
  3. 허나 여기서, 위와 같이 하나의 조건이 아니기 때문에 user의 name이 갖는 COUNT가 전체적으로 필요하므로 GROUP을 지을 필요가 있다. 출력할 값은 user.name이므로 GROUP BY는 user.name으로 가져오면 되겠다.

SELECT user.name AS name, COUNT(title) AS ContentCount FROM content 
right JOIN user ON content.userId=user.id 
GROUP BY user.name