SQL 실습 및 점검
Getting Started
학습 사이트 W3Schools에서 제공하는 Quiz와 Practice를 진행합니다.
Bare minimum requirement
- 주어진 시간을 활용해 Quiz와 Practice를 모두 완료하세요. 시간 내에 최대한 할 수 있는 만큼만 진행해도 좋습니다. 시간이 부족한 경우, 정규시간 외의 시간을 활용하세요.
Quiz
Quiz 링크를 통해 학습 상태를 점검하세요.
Practice
실제 SQL문을 직접 작성하면서 이해도를 확인하세요.
반드시 스스로 풀어보고, 이해가 안 가는 부분은 다시 학습 내용으로 돌아와서 확인하세요.
과제 - Learn Schema / SQL
Part 1 - 데이터베이스 연결과 스키마
이번 연습문제의 목표는 데이터베이스 연결을 할 수 있고, 주어진 스키마를 구현할 수 있도록 SQL을 작성해야 합니다.
Bare minimum requirement
Part_1 테스트를 전부 통과하십시오.
Part1_Test.java 파일을 통해 테스트를 실행할 수 있습니다.
Getting Started
1. repository 주소에서 fork 및 clone 후 코드를 작성합니다.
- IntelliJ를 실행합니다.
- 열기를 클릭한 이후, 다운로드한 폴더를 클릭하고 Open버튼을 클릭합니다.
- 신뢰할 수 있는 프로젝트를 클릭합니다.
2. 본인의 MySQL 정보를 입력해 주세요.
script/Properties.java 파일을 확인하고 내 정보를 수정해 주세요.
- learnSQL이라는 이름으로 데이터베이스를 만드세요. 앞으로 이 데이터베이스를 사용합니다.
- lib/Mysql.java에서 Properties.java 변수가 어떻게 사용되는지 확인해 보세요.
- 아래 질문에 고민해 보시고, 페어와 토론해 보세요.
- 왜 .Properties.java 파일은 .gitignore에 존재할까요?
- Properties.java 파일이 왜 필요했을지 고민해 보세요.
[주의]
다행히도 여러분들이 직접 데이터베이스 연결 부분을 직접 구현할 필요는 없습니다. 현재 연습문제에서 작성해야 할 데이터베이스 연결 부분은 이미 다 구현되어 있으며, 이후 학습하시는 내용을 통해 직접 연결하는 부분을 작성하게 될 것입니다. 이 시간에는 해당 부분을 전부 이해하려 하기보다는, Properties.java 관련하여 MySQL 정보를 관리하는 방법에 초점을 두고 보시면 됩니다.
3. 연습문제 디렉토리
/lib
- /lib/FactoryService.java
- FactoryService는 여러분들이 데이터베이스에 연결하여, 원하는 SQL을 작성하여 결과를 확인하는 것과 별개로 TEST가 동작할 수 있도록 도와주는 Class입니다. 구체적으로 해당 연습문제의 동작이 궁금하신 분들만 참고해 주시면 됩니다.
- /lib/Mysql.java
- Mysql 서비스에 접속하고 쿼리를 보내는 작업과 자원을 종료하는 동작을 도와주는 Class입니다.
/migrations
- /schema.sql
- 이번 연습문제의 핵심이자, 꼭 작성해야 하는 부분입니다. 원하는 테이블과 열을 생성할 수 있어야 합니다.
/model
- FactoryService가 사용하는 별도의 객체입니다. 해당 객체를 사용하여, 테이블을 조작하게 됩니다.
- DTO, DAO에 대한 내용은 Section3에서 학습하게 됩니다. 해당 코드를 이해하지 않아도 됩니다.
/script
- 각 파트에 맞게 SQL STATEMENT를 채워 넣어주세요. 원하는 것을 확인하고, 그에 맞는 SQL을 작성해야 합니다.
그 외 디렉토리
.gitignore 혹은 build.gradle 와 같은 부분은 코드를 보거나 검색을 통해서 이해하실 수 있었으면 합니다.
4. migrations/schema.sql을 작성해 주세요.
user 테이블 구조
content 테이블 구조
- 위와 같은 테이블과 열이 필요합니다.
- PK는 auto increment 이여야 합니다.
- FK를 제외하고는 모두 NOT NULL 이여야 합니다.
- created_at과 같은 날짜/시간 정보는 timestamp이면서 default current_timestamp입니다.
[참고4-1] 모든 테스트케이스가 실행되기 전, schema.sql 파일이 실행되고, 끝난 후, database가 삭제된 후 새로 생성됩니다.
lib/FactoryService.java의 setup 메서드를 참고해 주세요.
[참고4-2] Schema Visualizer
스키마를 디자인하기 위해 다양한 외부 도구를 사용할 수 있습니다.
- 연필과 펜을 이용한 A4용지 및 화이트보드
- DBdiagram
- WWW SQL Designer
- MySQL Workbench
5. script/part1.java를 작성하고, Part1_Test의 모든 테스트를 통과하도록 노력하세요.
part1.java의 요구사항을 확인하고, FILL IN THIS를 채워주세요.
[참고5-1]
현재 테이블의 결과값을 List와 Map을 활용하여 터미널에 출력하고 있습니다. 실제 Mysql에서 보여주는 결과와 형식이 다를 수 있습니다.
[참고5-2]
part1의 경우 연습문제의 구조를 파악하고, schema.sql을 작성하는데 시간을 많이 보내시게 될 것입니다.
Part 2 - 스키마와 함께하는 기본 SQL
Part 2의 목표는 Part 1에서 구성한 스키마에서 데이터를 추가하거나, 불러올 수 있도록 SQL을 작성하는 것을 요구합니다. Learn SQL에서 연습했던 것을 Java 환경에서 다시 한번 확인해 볼 수 있습니다.
Bare minimum requirement
Part 2 테스트를 전부 통과하십시오.
Part2_Test.java 파일을 통해 테스트를 실행할 수 있습니다.
Getting Started
script/part2.java를 작성하고, Part 2의 모든 테스트를 통과하도록 노력하세요.
아래 사항을 참고하여 진행해 주세요.
- Part 1의 테스트케이스 역시 통과되어야 합니다.
- 이번 Part에서는 schema.sql 파일의 변경은 필요하지 않습니다. Learn SQL에서 연습하고 학습한 SQL을 충분히 활용해 보세요.
- Part 2의 테스트에서는 아래 표와 같이 데이터가 존재합니다. 이는 아래의 도표가 아닌, 테스트케이스를 살펴보아도 알 수 있습니다. (lib/FactoryService.java의 58번째 줄 part2_setup 메서드를 참고해 주세요.)
user 테이블 데이터
content 테이블 데이터
Part 3 - 추가 요구조건과 스키마
Part 3의 목표는 추가 요구조건에 따라, 스키마를 추가/변경하고 이를 확인해 보는 과정입니다. 이를 통해 1:N, N:N 관계와 더불어 JOIN에 대한 이해가 단단해 지기를 기대합니다.
Bare minimum requirement
Part 3 테스트를 전부 통과하십시오.
- Part3_Test.java 파일을 통해 테스트를 실행할 수 있습니다.
- Part 1, Part 2 테스트 또한 모두 통과해야 합니다.
Getting Started
1. migrations/schema.sql을 추가 작성해 주세요.
content 테이블
변경사항이 없습니다.
category 테이블 구조
content_category 테이블 구조
role 테이블 구조
user 테이블 구조
2. script/part3.java에 주석을 참고하여, SQL을 채워 넣어주세요.
- 참고: part3의 테스트에서는 테스트케이스를 살펴보고, 확인해 보세요. (lib/FactoryService.java의 112번째 줄 part3_setup 메서드를 참고해 주세요.)
Part 3의 목표는 추가 요구조건에 따라, 스키마를 추가/변경하고 이를 확인해 보는 과정입니다. 이를 통해 1:N, N:N 관계와 더불어 JOIN에 대한 이해가 단단해 지기를 기대합니다.
Properties
package com.codestates.script;
public class Properties {
public static String URL = "jdbc:mysql://127.0.0.1:3306"; // 기본 설정값
public static String DATABASE_USERNAME = "root"; // 기본값은 root입니다.
public static String DATABASE_PASSWORD = "****"; // 본인의 패스워드로 변경해주세요.
public static String getURL() {
return URL;
}
public static String getDatabaseUsername() {
return DATABASE_USERNAME;
}
public static String getDatabasePassword() {
return DATABASE_PASSWORD;
}
}
schema.sql
CREATE TABLE `role` (
`id` int PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) not NULL
);
CREATE TABLE `user` (
`id` int PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) not NULL,
`email` varchar(255) not NULL,
`roleId` int,
FOREIGN KEY (`roleId`) REFERENCES `role` (`id`)
);
CREATE TABLE `content` (
`id` int PRIMARY KEY AUTO_INCREMENT,
`title` varchar(255),
`body` varchar(255),
`created_at` timestamp not NULL DEFAULT CURRENT_TIMESTAMP,
`userId` int,
FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
);
CREATE TABLE `category` (
`id` int PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) not NULL
);
CREATE TABLE `content_category` (
`id` int PRIMARY KEY AUTO_INCREMENT,
`contentId` int not NULL,
`categoryId` int not NULL,
FOREIGN KEY (`contentId`) REFERENCES `content` (`id`),
FOREIGN KEY (`categoryId`) REFERENCES `category` (`id`)
);
part1
package com.codestates.script;
public class part1 {
/*
----------------------------------------------------------------------------------------------
TODO: 유어클래스의 requirement를 참조하여, migration/schema.sql에 알맞은 테이블을 구성해주세요.
*/
/*
----------------------------------------------------------------------------------------------
TODO: Q 1-1. 현재 있는 데이터베이스에 존재하는 모든 테이블 정보를 보기위한 SQL을 작성해주세요.
*/
public static final String PART1_1 = "SHOW TABLES";
/*
----------------------------------------------------------------------------------------------
TODO: Q 1-2. user 테이블의 구조를 보기위한 SQL을 작성해주세요.
- 요구사항에 맞는 user 테이블을 작성해야만, 테스트를 통과합니다.
*/
public static final String PART1_2 = "DESC user";
/*
----------------------------------------------------------------------------------------------
TODO: Q 1-3. content 테이블의 구조를 보기위한 SQL을 작성해주세요.
- 요구사항에 맞는 content 테이블을 작성해야만, 테스트를 통과합니다.
*/
public static final String PART1_3 = "DESC content";
}
part1 test code
package com.codestates;
import com.codestates.lib.FactoryService;
import com.codestates.lib.Mysql;
import com.codestates.script.Properties;
import com.codestates.script.part1;
import org.junit.jupiter.api.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import static org.assertj.core.api.Assertions.assertThat;
public class Part1_Test {
private static Mysql mysql = Mysql.getInstance();
private static FactoryService factoryService = FactoryService.getInstance();
private static Connection connection = null;
// 테스트 시작전 initializr
@BeforeAll
public static void init() throws SQLException {
connection = mysql.getConnection();
factoryService.init(connection);
factoryService.migration(connection);
}
// 테스트 종료후 데이터베이스 자원 종료
@AfterAll
public static void terminate() throws SQLException {
mysql.terminate(connection);
}
@Test
@DisplayName("Q 1-0. 데이터베이스 접속 확인")
public void Connect_Test() throws SQLException {
System.out.printf("<YOUR DATABASE CONFIG>%n");
System.out.printf("URL : %s%n", com.codestates.script.Properties.getURL());
System.out.printf("DATABASE_USERNAME : %s%n", Properties.getDatabaseUsername());
System.out.printf("DATABASE_PASSWORD : %s%n", com.codestates.script.Properties.getDatabasePassword());
}
@Test
@DisplayName("Q 1-1. 현재 있는 데이터베이스에 존재하는 모든 테이블 정보를 보기위한 SQL을 작성해주세요.")
public void Query_Test_1_1() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part1.PART1_1);
assertThat(response.size() >= 2).isTrue();
assertThat(response.toString().contains("user")).isTrue();
assertThat(response.toString().contains("content")).isTrue();
// 결과 출력
for(HashMap<String, Object> map: response) {
Set set = map.entrySet();
for (Object o : set) {
Map.Entry<String, Object> entry = (Map.Entry) o;
Object value = entry.getValue();
System.out.printf("TABLE : %s%n", value);
}
}
}
@Test
@DisplayName("Q 1-2. user 테이블의 구조를 보기위한 SQL을 작성해주세요.")
public void Query_Test_1_2() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part1.PART1_2);
assertThat(response.size() >= 3).isTrue();
assertThat(response.get(0).toString().contains("id")).isTrue();
assertThat(response.get(0).toString().contains("PRI")).isTrue();
assertThat(response.get(1).toString().contains("name")).isTrue();
assertThat(response.get(1).toString().contains("varchar")).isTrue();
assertThat(response.get(2).toString().contains("email")).isTrue();
// 결과 출력
for(HashMap<String, Object> map: response) {
System.out.println(map);
}
}
@Test
@DisplayName("Q 1-3. content 테이블의 구조를 보기위한 SQL을 작성해주세요.")
public void Query_Test_1_3() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part1.PART1_3);
assertThat(response.size()).isEqualTo(5);
assertThat(response.get(0).toString().contains("id")).isTrue();
assertThat(response.get(0).toString().contains("PRI")).isTrue();
assertThat(response.get(1).toString().contains("title")).isTrue();
assertThat(response.get(1).toString().contains("varchar")).isTrue();
assertThat(response.get(2).toString().contains("body")).isTrue();
assertThat(response.get(3).toString().contains("created_at")).isTrue();
assertThat(response.get(3).toString().contains("CURRENT_TIMESTAMP")).isTrue();
assertThat(response.get(4).toString().contains("userId")).isTrue();
assertThat(response.get(4).toString().contains("MUL")).isTrue();
// 결과 출력
for(HashMap<String, Object> map: response) {
System.out.println(map);
}
}
}
part2
package com.codestates.script;
public class part2 {
/*
----------------------------------------------------------------------------------------------
TODO: Q 2-1. user 테이블에 존재하는 모든 컬럼을 포함한 모든 데이터를 확인하기 위한 SQL을 작성해주세요.
*/
public static final String PART2_1 = "SELECT * FROM user";
/*
----------------------------------------------------------------------------------------------
TODO: Q 2-2. user 테이블에 존재하는 모든 데이터에서 name 컬럼만을 확인하기 위한 SQL을 작성해주세요.
*/
public static final String PART2_2 = "SELECT name FROM user";
/*
----------------------------------------------------------------------------------------------
TODO: Q 2-3. user 테이블에 데이터를 추가하기 위한 SQL을 작성해주세요.
- 원하는 name, email을 사용하시면 됩니다.
*/
public static final String PART2_3 = "INSERT INTO user(name, email) VALUES('midsummer','midsummer@naver.com')";
/*
----------------------------------------------------------------------------------------------
TODO: Q 2-4. user 테이블에서 특정 조건을 가진 데이터를 찾기위한 SQL을 작성해주세요.
- 조건 : name이 luckykim이여야 합니다.
*/
public static final String PART2_4 = "SELECT * FROM user WHERE name = 'luckykim'";
/*
----------------------------------------------------------------------------------------------
TODO: Q 2-5. user 테이블에서 특정 조건을 가진 데이터를 찾기위한 SQL을 작성해주세요.
- 조건 : name이 luckykim이 아니여야 합니다.
*/
public static final String PART2_5 = "SELECT name FROM user WHERE name <> 'luckykim'";
/*
----------------------------------------------------------------------------------------------
TODO: Q 2-6. content 테이블에 존재하는 모든 데이터에서 title 컬럼만을 찾기 위한 SQL을 작성해주세요.
*/
public static final String PART2_6 = "SELECT title FROM content";
/*
----------------------------------------------------------------------------------------------
TODO: Q 2-7. content의 title과 그 컨텐츠를 작성한 user의 name을 찾기 위한 SQL을 작성해주세요.
- 저자가 없더라도, 켄턴츠의 title을 모두 찾아야합니다.
*/
public static final String PART2_7 = "SELECT title, name FROM content LEFT JOIN user ON user.id = content.id";
/*
----------------------------------------------------------------------------------------------
TODO: Q 2-8. content의 title과 그 컨텐츠를 작성한 user의 name을 찾기 위한 SQL을 작성해주세요.
- 저자가 있는 컨텐츠의 title만 찾아야합니다.
*/
public static final String PART2_8 = "SELECT content.title, user.name FROM content JOIN user ON user.id = content.userId";
/*
----------------------------------------------------------------------------------------------
TODO: Q 2-9. content의 데이터를 수정하기 위한 SQL을 작성해주세요.
- title이 database homework인 content 데이터에서 body를 database is very easy로 수정해야합니다.
*/
public static final String PART2_9 = "UPDATE content SET content.body = 'database is very easy' WHERE content.title = 'database homework'";
/*
----------------------------------------------------------------------------------------------
TODO: Q 2-10. content의 데이터를 추가하기 위한 SQL을 작성해주세요.
- luckykim이 작성한 컨텐츠를 추가해주세요. 제목과 본문은 자유입니다. (참고: luckykim의 아이디는 1입니다.)
*/
public static final String PART2_10 = "INSERT INTO content(title, body, userId) VALUES('ab', 'cd', 1)";
}
part2 test code
package com.codestates;
import com.codestates.lib.FactoryService;
import com.codestates.lib.Mysql;
import com.codestates.script.part2;
import org.junit.jupiter.api.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import static org.assertj.core.api.Assertions.assertThat;
public class Part2_Test {
private static final Mysql mysql = Mysql.getInstance();
private static final FactoryService factoryService = FactoryService.getInstance();
private static Connection connection = null;
@BeforeAll
public static void init() throws SQLException {
connection = mysql.getConnection();
factoryService.init(connection);
factoryService.migration(connection);
factoryService.part2_setup();
}
@AfterAll
public static void terminate() throws SQLException {
mysql.terminate(connection);
}
@Test
@DisplayName("Q 2-1. user 테이블에 존재하는 모든 컬럼을 포함한 모든 데이터를 확인하기 위한 SQL을 작성해주세요.")
public void Query_Test_2_1() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part2.PART2_1);
ArrayList<HashMap<String,Object>> factoryResponse = factoryService.find(connection, "user", "*");
assertThat(response.size()).isEqualTo(4);
assertThat(response).usingRecursiveComparison().isEqualTo(factoryResponse);
}
@Test
@DisplayName("Q 2-2. user 테이블에 존재하는 모든 데이터에서 name 컬럼만을 확인하기 위한 SQL을 작성해주세요.")
public void Query_Test_2_2() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection,part2.PART2_2);
ArrayList<HashMap<String,Object>> factoryResponse = factoryService.find(connection, "user", "name");
assertThat(response.size()).isEqualTo(4);
assertThat(response).usingRecursiveComparison().isEqualTo(factoryResponse);
}
@Test
@DisplayName("Q 2-3. user 테이블에 데이터를 추가하기 위한 SQL을 작성해주세요.")
public void Query_Test_2_3() throws SQLException {
mysql.query(connection,part2.PART2_3);
ArrayList<HashMap<String,Object>> factoryResponse = factoryService.find(connection, "user", "*");
assertThat(factoryResponse.size()).isEqualTo(5);
}
@Test
@DisplayName("Q 2-4. user 테이블에서 특정 조건을 가진 데이터를 찾기위한 SQL을 작성해주세요.")
public void Query_Test_2_4() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection,part2.PART2_4);
System.out.println(response);
assertThat(response.size()).isEqualTo(1);
assertThat(response.get(0).get("name")).isEqualTo("luckykim");
}
@Test
@DisplayName("Q 2-5. user 테이블에서 특정 조건을 가진 데이터를 찾기위한 SQL을 작성해주세요.")
public void Query_Test_2_5() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection,part2.PART2_5);
assertThat(response.size()).isBetween(3, 4);
for(HashMap<String, Object> data : response) {
assertThat(data.get("name")).isNotEqualTo("luckykim");
}
}
@Test
@DisplayName("Q 2-6. content 테이블에 존재하는 모든 데이터에서 title 컬럼만을 찾기 위한 SQL을 작성해주세요.")
public void Query_Test_2_6() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection,part2.PART2_6);
ArrayList<HashMap<String,Object>> factoryResponse = factoryService.find(connection, "content", "title");
assertThat(factoryResponse.size()).isEqualTo(3);
assertThat(response).usingRecursiveComparison().isEqualTo(factoryResponse);
}
@Test
@DisplayName("Q 2-7. content의 title과 그 컨텐츠를 작성한 user의 name을 찾기 위한 SQL을 작성해주세요. - 저자가 없더라도, 켄턴츠의 title을 모두 찾아야합니다.")
public void Query_Test_2_7() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection,part2.PART2_7);
assertThat(response.size()).isEqualTo(3);
int count = 0;
for(HashMap<String, Object> data : response) {
if(data.get("name") == null) continue;
if(data.get("name").equals("luckykim")) count++;
}
assertThat(count).isEqualTo(1);
}
@Test
@DisplayName("Q 2-8. content의 title과 그 컨텐츠를 작성한 user의 name을 찾기 위한 SQL을 작성해주세요. - 저자가 있는 컨텐츠의 title만 찾아야합니다.")
public void Query_Test_2_8() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection,part2.PART2_8);
assertThat(response.size()).isEqualTo(1);
assertThat(response.get(0).get("title")).isEqualTo("database homework");
assertThat(response.get(0).get("name")).isEqualTo("luckykim");
}
@Test
@DisplayName("Q 2-9. content의 데이터를 수정하기 위한 SQL을 작성해주세요. - title이 database homework인 content 데이터에서 body를 database is very easy로 수정해야합니다.")
public void Query_Test_2_9() throws SQLException {
mysql.query(connection,part2.PART2_9);
ArrayList<HashMap<String,Object>> factoryResponse = factoryService.find(connection, "content", "*");
assertThat(factoryResponse.size()).isEqualTo(3);
for(HashMap<String, Object> data : factoryResponse) {
assertThat(data.get("body")).isNotEqualTo("database is easy");
}
}
@Test
@DisplayName("Q 2-10. content의 데이터를 추가하기 위한 SQL을 작성해주세요. - luckykim이 작성한 컨텐츠를 추가해주세요. 제목과 본문은 자유입니다. (참고: luckykim의 아이디는 1입니다.)")
public void Query_Test_2_10() throws SQLException {
mysql.query(connection,part2.PART2_10);
ArrayList<HashMap<String,Object>> factoryResponse = factoryService.find(connection, "content", "*");
assertThat(factoryResponse.size()).isEqualTo(4);
int count = 0;
for(HashMap<String, Object> data : factoryResponse) {
if(data.get("userId") == null) continue;
int userId = (int)data.get("userId");
if(userId == 1) count++;
}
assertThat(count).isEqualTo(2);
}
}
part3
package com.codestates.script;
public class part3 {
/*
----------------------------------------------------------------------------------------------
TODO: 유어클래스의 requirement를 참조하여, migration/schema.sql에 추가로 구성해주세요.
*/
/*
----------------------------------------------------------------------------------------------
TODO: Q 3-1-1. category 테이블의 구조를 보기위한 SQL을 작성해주세요.
- 요구사항에 맞는 category 테이블을 작성해야만, 테스트를 통과합니다.
*/
public static final String PART3_1_1 = "DESC category";
/*
----------------------------------------------------------------------------------------------
TODO: Q 3-1-2. content_category 테이블의 구조를 보기위한 SQL을 작성해주세요.
- 요구사항에 맞는 content_category 테이블을 작성해야만, 테스트를 통과합니다.
*/
public static final String PART3_1_2 = "DESC content_category";
/*
----------------------------------------------------------------------------------------------
TODO: Q 3-1-3. role 테이블의 구조를 보기위한 SQL을 작성해주세요.
- 요구사항에 맞는 role 테이블을 작성해야만, 테스트를 통과합니다.
*/
public static final String PART3_1_3 = "DESC role";
/*
----------------------------------------------------------------------------------------------
TODO: Q 3-1-4. user 테이블의 구조를 보기위한 SQL을 작성해주세요.
- 요구사항에 맞는 user 테이블을 작성해야만, 테스트를 통과합니다.
*/
public static final String PART3_1_4 = "DESC user";
/*
----------------------------------------------------------------------------------------------
TODO: Q 3-2-1. category 테이블에 존재하는 데이터에서 id, name을 찾는 SQL을 작성해주세요.
*/
public static final String PART3_2_1 = "SELECT id, name FROM category";
/*
----------------------------------------------------------------------------------------------
TODO: Q 3-2-2. user의 name과 email 그리고 그 user가 속한 role name(컬럼명: roleName)을 찾기 위한 SQL을 작성해주세요.
- 속한 role이 없더라도, user의 name과 email,role name을 모두 찾아야합니다.
*/
public static final String PART3_2_2 = "SELECT user.name, user.email, role.name AS roleName FROM user LEFT JOIN role ON user.roleId = role.id";
/*
----------------------------------------------------------------------------------------------
TODO: Q 3-2-3. 어느 role에도 속하지 않는 user의 모든 컬럼 데이터를 찾기위한 SQL을 작성해주세요.
*/
public static final String PART3_2_3 = "SELECT * FROM user WHERE user.roleId IS NULL";
/*
----------------------------------------------------------------------------------------------
TODO: Q 3-2-4. content_category 테이블에 존재하는 모든 칼럼의 데이터를 찾기위한 SQL을 작성해주세요.
*/
public static final String PART3_2_4 = "SELECT * FROM content_category";
/*
----------------------------------------------------------------------------------------------
TODO: Q 3-2-5. minsanggu이 작성한 content의 title을 찾기위한 SQL을 작성해주세요.
*/
public static final String PART3_2_5 = "SELECT content.title FROM content JOIN user ON content.userId = user.id WHERE user.name = 'minsanggu'";
/*
----------------------------------------------------------------------------------------------
TODO: Q 3-2-6. minsanggu이 작성한 content의 category name을 찾기위한 SQL을 작성해주세요.
*/
public static final String PART3_2_6 = "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 = 'minsanggu'";
/*
----------------------------------------------------------------------------------------------
TODO: Q 3-2-7. category의 name이 java인 content의 title, body, created_at을 찾기위한 SQL을 작성해주세요.
*/
public static final String PART3_2_7 = "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 = 'java'";
/*
----------------------------------------------------------------------------------------------
TODO: Q 3-2-8. category의 name이 java인 content의 title, body, created_at, user의 name을 찾기위한 SQL을 작성해주세요.
*/
public static final String PART3_2_8 = "SELECT content.title, content.body, content.created_at, user.name FROM content JOIN content_category ON content.id = content_category.contentId JOIN category ON content_category.categoryId = category.id JOIN user ON content.userId = user.id WHERE category.name = 'java'";
/*
----------------------------------------------------------------------------------------------
TODO: Q 3-2-9. teawoongna가 작성한 글의 개수 (컬럼명: ContentCount)를 출력하기 위한 SQL을 작성해주세요.
*/
public static final String PART3_2_9 = "SELECT COUNT(*) AS ContentCount FROM content JOIN user ON content.userId = user.id WHERE user.name = 'teawoongna'";
/*
----------------------------------------------------------------------------------------------
TODO: Q 3-2-10. 각 user(컬럼명: name)가 작성한 글의 개수 (컬럼명: ContentCount)를 출력하기 위한 SQL을 작성해주세요.
*/
public static final String PART3_2_10 = "SELECT user.name AS name, COUNT(content.id) AS ContentCount FROM content RIGHT JOIN user ON content.userId = user.id GROUP BY user.name";
}
part3 test code
package com.codestates;
import com.codestates.lib.FactoryService;
import com.codestates.lib.Mysql;
import com.codestates.script.part3;
import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import static org.assertj.core.api.Assertions.assertThat;
public class Part3_Test {
private static final Mysql mysql = Mysql.getInstance();
private static final FactoryService factoryService = FactoryService.getInstance();
private static Connection connection = null;
@BeforeAll
public static void init() throws SQLException {
connection = mysql.getConnection();
factoryService.init(connection);
factoryService.migration(connection);
factoryService.part3_setup();
}
@AfterAll
public static void terminate() throws SQLException {
mysql.terminate(connection);
}
@Test
@DisplayName("Q 3-1-1. category 테이블의 구조를 보기위한 SQL을 작성해주세요.")
public void Query_Test_3_1_1() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part3.PART3_1_1);
assertThat(response.size()).isEqualTo(2);
assertThat(response.get(0).toString().contains("id")).isTrue();
assertThat(response.get(0).toString().contains("PRI")).isTrue();
assertThat(response.get(0).toString().contains("auto_increment")).isTrue();
assertThat(response.get(1).toString().contains("name")).isTrue();
assertThat(response.get(1).toString().contains("varchar")).isTrue();
// 결과 출력
for(HashMap<String, Object> map: response) {
System.out.println(map);
}
}
@Test
@DisplayName("Q 3-1-2. content_category 테이블의 구조를 보기위한 SQL을 작성해주세요.")
public void Query_Test_3_1_2() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part3.PART3_1_2);
System.out.println(response);
assertThat(response.size()).isEqualTo(3);
assertThat(response.get(0).toString().contains("id")).isTrue();
assertThat(response.get(0).toString().contains("PRI")).isTrue();
assertThat(response.get(0).toString().contains("auto_increment")).isTrue();
assertThat(response.get(1).toString().contains("contentId")).isTrue();
assertThat(response.get(1).toString().contains("MUL")).isTrue();
assertThat(response.get(2).toString().contains("categoryId")).isTrue();
assertThat(response.get(2).toString().contains("MUL")).isTrue();
// 결과 출력
for(HashMap<String, Object> map: response) {
System.out.println(map);
}
}
@Test
@DisplayName("Q 3-1-3. role 테이블의 구조를 보기위한 SQL을 작성해주세요.")
public void Query_Test_3_1_3() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part3.PART3_1_3);
assertThat(response.size()).isEqualTo(2);
assertThat(response.get(0).toString().contains("id")).isTrue();
assertThat(response.get(0).toString().contains("PRI")).isTrue();
assertThat(response.get(0).toString().contains("auto_increment")).isTrue();
assertThat(response.get(1).toString().contains("name")).isTrue();
assertThat(response.get(1).toString().contains("varchar")).isTrue();
// 결과 출력
for(HashMap<String, Object> map: response) {
System.out.println(map);
}
}
@Test
@DisplayName("Q 3-1-4. user 테이블의 구조를 보기위한 SQL을 작성해주세요.")
public void Query_Test_3_1_4() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part3.PART3_1_4);
assertThat(response.size()).isEqualTo(4);
assertThat(response.get(0).toString().contains("id")).isTrue();
assertThat(response.get(0).toString().contains("PRI")).isTrue();
assertThat(response.get(0).toString().contains("auto_increment")).isTrue();
assertThat(response.get(1).toString().contains("name")).isTrue();
assertThat(response.get(1).toString().contains("varchar")).isTrue();
assertThat(response.get(2).toString().contains("email")).isTrue();
assertThat(response.get(2).toString().contains("varchar")).isTrue();
assertThat(response.get(3).toString().contains("roleId")).isTrue();
assertThat(response.get(3).toString().contains("MUL")).isTrue();
// 결과 출력
for(HashMap<String, Object> map: response) {
System.out.println(map);
}
}
@Test
@DisplayName("Q 3-2-1. category 테이블에 존재하는 데이터에서 id, name을 찾는 SQL을 작성해주세요.")
public void Query_Test_3_2_1() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part3.PART3_2_1);
assertThat(response.size()).isEqualTo(3);
for(HashMap<String, Object> map: response) {
assertThat(map.size()).isEqualTo(2);
System.out.println(map);
}
}
@Test
@DisplayName("Q 3-2-2. user의 name과 email 그리고 그 user가 속한 role name(컬럼명: roleName)을 찾기 위한 SQL을 작성해주세요. - 속한 role이 없더라도, user의 name과 email,role name을 모두 찾아야합니다.")
public void Query_Test_3_2_2() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part3.PART3_2_2);
assertThat(response.size()).isEqualTo(5);
int count = 0;
for(HashMap<String, Object> map: response) {
assertThat(map.size()).isEqualTo(3);
if(map.get("roleName") == null) count++;
}
assertThat(count).isEqualTo(1);
}
@Test
@DisplayName("Q 3-2-3. 어느 role에도 속하지 않는 user의 모든 컬럼 데이터를 찾기위한 SQL을 작성해주세요.")
public void Query_Test_3_2_3() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part3.PART3_2_3);
assertThat(response.size()).isEqualTo(1);
assertThat(response.get(0).get("roleId")).isNull();
assertThat(response.get(0).get("name")).isEqualTo("jungsikhwang");
}
@Test
@DisplayName("Q 3-2-4. content_category 테이블에 존재하는 모든 칼럼의 데이터를 찾기위한 SQL을 작성해주세요.")
public void Query_Test_3_2_4() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part3.PART3_2_4);
assertThat(response.size()).isEqualTo(4);
for(HashMap<String, Object> map: response) {
assertThat(map.size()).isEqualTo(3);
}
}
@Test
@DisplayName("Q 3-2-5. minsanggu이 작성한 content의 title을 찾기위한 SQL을 작성해주세요.")
public void Query_Test_3_2_5() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part3.PART3_2_5);
assertThat(response.size()).isEqualTo(1);
assertThat(response.get(0).get("title")).isEqualTo("developer proverb");
}
@Test
@DisplayName("Q 3-2-6. minsanggu이 작성한 content의 category name을 찾기위한 SQL을 작성해주세요.")
public void Query_Test_3_2_6() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part3.PART3_2_6);
System.out.println(response);
assertThat(response.size()).isEqualTo(2);
assertThat(response.get(0).get("name")).isEqualTo("java");
assertThat(response.get(1).get("name")).isEqualTo("query");
}
@Test
@DisplayName("Q 3-2-7. category의 name이 java인 content의 title, body, created_at을 찾기위한 SQL을 작성해주세요.")
public void Query_Test_3_2_7() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part3.PART3_2_7);
assertThat(response.size()).isEqualTo(2);
assertThat(response.get(0).get("title")).isEqualTo("developer proverb");
assertThat(response.get(1).get("title")).isEqualTo("common proverb");
}
@Test
@DisplayName("Q 3-2-8. category의 name이 java인 content의 title, body, created_at, user의 name을 찾기위한 SQL을 작성해주세요.")
public void Query_Test_3_2_8() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part3.PART3_2_8);
assertThat(response.size()).isEqualTo(2);
assertThat(response.get(0).get("title")).isEqualTo("developer proverb");
assertThat(response.get(0).get("name")).isEqualTo("minsanggu");
assertThat(response.get(0).size()).isEqualTo(4);
assertThat(response.get(1).get("title")).isEqualTo("common proverb");
assertThat(response.get(1).get("name")).isEqualTo("teawoongna");
assertThat(response.get(1).size()).isEqualTo(4);
}
@Test
@DisplayName("Q 3-2-9. teawoongna가 작성한 글의 개수 (컬럼명: ContentCount)를 출력하기 위한 SQL을 작성해주세요.")
public void Query_Test_3_2_9() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part3.PART3_2_9);
assertThat(response.size()).isEqualTo(1);
assertThat(response.get(0).get("ContentCount")).isEqualTo(1L);
}
@Test
@DisplayName("Q 3-2-10. 각 user(컬럼명: name)가 작성한 글의 개수 (컬럼명: ContentCount)를 출력하기 위한 SQL을 작성해주세요.")
public void Query_Test_3_2_10() throws SQLException {
ArrayList<HashMap<String,Object>> response = mysql.selectQuery(connection, part3.PART3_2_10);
assertThat(response.size()).isEqualTo(5);
int count = 0;
for(HashMap<String, Object> map: response) {
assertThat(map.size()).isEqualTo(2);
if(map.get("ContentCount").toString().equals("1")) count++;
}
assertThat(count).isEqualTo(2);
}
}
