SQL(2) JOIN, GROUP BY 사용하기

직접 해보기

LEFT JOIN, GROUP BY

USE learnmysql;

CREATE TABLE `user` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) not NULL,
  `email` varchar(255) not NULL
);

CREATE TABLE `content` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `title` varchar(255) not NULL,
  `body` varchar(255) not NULL,
  `created_at` timestamp not NULL DEFAULT CURRENT_TIMESTAMP,
  `userId` int,
  FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
);

CREATE TABLE `category` (
  `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) NOT NULL
);

CREATE TABLE `content_category` (
  `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `contentId` int NOT NULL,
  `categoryId` int NOT NULL,
  FOREIGN KEY (`contentId`) REFERENCES `content` (`id`),
  FOREIGN KEY (`categoryId`) REFERENCES `category` (`id`)
);

CREATE TABLE `role` (
  `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) NOT NULL
);

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

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에도 속하지 않는 user의 모든 컬럼 데이터를 찾기위한 SQL을 작성해주세요.

SELECT * from user
where user.roleId IS NULL;
//where user.roleId = Null 이라고 쓰지 않는다!

jiSungPark이 작성한 content의 title을 찾기위한 SQL을 작성해주세요.

SELECT content.title from content
JOIN user 
ON content.userId = user.userId
where user.name = 'jiSungPark';

JiSungPark이 작성한 content의 category name을 찾기위한 SQL을 작성해주세요.

SELECT category.name from category 
LEFT JOIN content_category 
ON content_category.categoryId = category.id 
LEFT JOIN content 
ON content.id=content_category.contentId
LEFT JOIN user 
ON user.id = content.userId 
WHERE user.name='JiSungPark';

category의 name이 soccer인 content의 title, body, created_at을 찾기위한 SQL을 작성해주세요.

SELECT content.title,content.body,content.created_at from content
LEFT JOIN content_category
ON content.id = content_category 
LEFT JOIN category
ON content_category.categoryId = category.id
WHERE category.name = 'soccer';

category의 name이 soccer인 content의 title, body, created_at, user의 name을 찾기위한 SQL을 작성해주세요.

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

duRiCha가 작성한 글의 개수 (컬럼명: ContentCount)를 출력하기 위한 SQL을 작성해주세요.

SELECT count(content.id) as ContentCount from content
LEFT JOIN user
ON content.userId = user.id
WHERE user.name = 'duRiCha';

각 user(컬럼명: name)가 작성한 글의 개수 (컬럼명: ContentCount)를 출력하기 위한 SQL을 작성해주세요.

SELECT user.name as name, count(content.id) as ContentCount from user
LEFT JOIN content
ON content.userId = user.id
GROUP BY user.name;