MYSQL 공부 1일차

DROP DATABASE IF EXISTS EXAMPLES1;
CREATE DATABASE EXAMPLES1;

USE EXAMPLES1;

CREATE TABLE TEST_SCORE -- 학생 테이블
(
  STU_NAME    	VARCHAR(10) NOT NULL, -- 이름
  STU_MAJOR    VARCHAR(10) NOT NULL,  -- 인원수
  STU_REGION	VARCHAR(10) NOT NULL, -- 소속 지역
  STU_AGE	INT NOT NULL,             -- 나이
  TEST_NO	INT,					  -- 몇 번째 시험인지
  TEST_RES	INT			  			  -- 점수
);

INSERT INTO TEST_SCORE VALUES('진이', '컴공', '구로구', 23, 1, 90);
INSERT INTO TEST_SCORE VALUES('진이', '컴공', '구로구', 23, 2, 99);
INSERT INTO TEST_SCORE VALUES('철수', '컴공', '강남구', 21, 1, 70); 
INSERT INTO TEST_SCORE VALUES('철수', '컴공', '강남구', 21, NULL, NULL);
INSERT INTO TEST_SCORE VALUES('영희', '컴공', '구로구', 23, NULL, NULL);
INSERT INTO TEST_SCORE VALUES('영희', '컴공', '구로구', 23, 2, 50);
INSERT INTO TEST_SCORE VALUES('희순', '전자', '강남구', 20, 1, 70);
INSERT INTO TEST_SCORE VALUES('희순', '전자', '강남구', 20, 2, 65);
INSERT INTO TEST_SCORE VALUES('희수', '전자', '서초구', 20, 1, 80);
INSERT INTO TEST_SCORE VALUES('희수', '전자', '서초구', 20, 2, 80);

이런 데이터가 주어졌을 때

문제1 -> 각 전공별로 제 1시험 1등과 제 2시험 1등의 정보를 추출하라! (단 동명이인은 없다는 것을 전제로 한다.)

각 전공별로, 각 시험 별로 묶어주어야 한다.
묶어줄 땐 GROUP BY를 활용할 수 있겠다.
GROUP BY STU_MAJOR,TEST_NO까지 생각해낼 수 있겠다.
시험을 치지 않아서 NULL인 것들은 걸러내야 하기에 WHERE문을 통해 걸러주도록 한다.
그러나 이러면 다른 정보들을 가져올 수 (SELECT)할 수 없게 된다.
일단 STU_MAJOR, TEST_NO, MAX(TEST_RES)까지 뽑아올 수 있겠다.
동명이인이 없으므로 STU_MAJOR, TEST_NO, MAX(TEST_RES)를 통해 역으로 다시 그 학생을 특정해야 한다.

이를 코드로 나타내어 보면

SELECT * FROM TEST_SCORE WHERE (STU_MAJOR, TEST_NO, TEST_RES) IN (SELECT STU_MAJOR, TEST_NO, MAX(TEST_RES) FROM TEST_SCORE WHERE TEST_NO > 0 GROUP BY STU_MAJOR, TEST_NO);

가 되겠다.