[MySQL] 과거 강의자료 정리 -1

Intro

SQL문을 사용하는 것에 익숙해지기 위해서 별도의 정리를 하려고 한다.
(최근에 어떤 사건으로 인해서 SQL을 단단하게 다져놔야겠다는 생각도 있고)
따라서 이전에 DB관련 강의를 들으면서 풀었던 문제를 조금 변형해서 그에 따른 코드를 작성하려 한다.

공통 조건

다음의 테이블 구조를 참고해서 1~6까지를 구하기 위한 쿼리를 작성하여라.

• 테이블 구조 & 관계

• 테이블마다 주어진 데이터

문제 1

1004번 과목을 들은 학생 중 성적이 B인 학생의 학번을 뽑아내어라.

나만의 풀이 1

일단 성적 정보는 score 테이블에 있음을 알 수 있다.
여길 보면 과목 번호도 같이 나와있으니, 여기만 탐색해서 원하는 정보를 추출할 수 있다.
단순히 SELECT와 WHERE 조건을 활용해서 다음과 같이 코드를 짜면 결과를 얻을 수 있다.

SELECT std_stdno
FROM score
WHERE cls_clsno = 1004 AND grade = 'B';

문제 2

1003번 과목을 수강한 학생들의 이름과 학년을 추출해라.

나만의 풀이 2

이 문제에선 학생의 정보와 과목 수강 여부 두 가지를 고려해야 한다.
따라서 학생 정보를 담은 std 테이블과 score정보를 JOIN해야 한다.
단순히 주 키 & 외래 키를 고려해서 조인하고 조건만 맞추면 금방 해결된다.
따라서 다음과 같이 코드를 짤 수 있다.

SELECT st.stdname, st.year
FROM std st
JOIN score sc ON st.stdno = sc.std_stdno
WHERE sc.cls_clsno = 1003;

문제 3

컴퓨터학과의 과목을 수강한 학생들의 성함을 모두 추출해서 내림차순으로 정렬해라.

나만의 풀이 3

이 문제는 조금 까다롭다.
제공된 3개의 테이블을 모두 조인해야 정보를 얻을 수 있기 때문이다.
여러 개의 테이블을 조인할 때는 JOIN ~ ON ~절을 반복해서 수행할 수 있다.
이때 키만 안 꼬이게 설정하면 테이블을 적절히 엮을 수 있다.

모든 테이블을 조인한 후에는 수강 과목이 컴퓨터인 경우만 뽑아서 정렬하면 된다.
과목 정보는 cls 테이블을 참고해야 하며, 정렬은 학생 이름 stdname을 기준으로 역순 정렬하면 된다.
역순 정렬이므로, ORDER BY ~ DESC의 형태로 명령을 주면 된다.

최종 코드는 다음과 같다.

SELECT DISTINCT st.stdname
FROM std st
JOIN score sc ON st.stdno = sc.std_stdno
JOIN cls c ON c.clsno = sc.cls_clsno
WHERE c.dept = '컴퓨터'
ORDER BY st.stdname DESC;

문제 4

1001번 과목을 듣지 않는 학생을 찾아내어라.

나만의 풀이 4

이처럼 만일 없는 것, 반대의 것 등의 정보를 찾는 경우면 쿼리문이 조금 길어진다.
먼저 주어진 문제에서 1001번 과목을 듣는 학생의 정보를 추출해야 한다.
따라서 다음과 같은 쿼리문을 먼저 작성하게 된다.

SELECT st.stdno
FROM std st
JOIN score sc ON st.stdno = sc.std_stdno
WHERE sc.cls_clsno = 1001

이후 이를 하나로 감싸서 활용하게 된다.
위 정보에 없는 데이터만 추출할 수 있도록 겉(?) 코드를 감싸면 된다.

따라서 다음과 같이 전체 구문을 짤 수 있다.

SELECT stdname
FROM std
WHERE stdno NOT IN (
    SELECT st.stdno
    FROM std st
    JOIN score sc ON st.stdno = sc.std_stdno
    WHERE sc.cls_clsno = 1001);

** 주어진 사진에선 st.stdno 대신 st.stdname이 있는데, 중복 데이터가 없어서 저것도 동일 결과를 주기는 한다.
다만, 동명이인이 있을 수 있으므로, 앞선 코드대로 짜는 것이 더 정확하다.

문제 5

전기자기학에서 최고 성적을 받은 수강생의 성함을 출력하여라.

나만의 풀이 5

이 문제가 많이 (귀찮다)까다롭다.
일단, max값이 무엇인지 알아내야 한다.
그리고 이를 비교해서 정보를 추출해야 한다.

먼저 최고 성적을 추출하는 부분은 다음과 같이 짤 수 있다.

SELECT MAX(midscore+finscore)
FROM score
WHERE cls_clsno = 1001

이제 위 코드를 감싸는 겉 부분의 코드를 짜야 한다.
해당 정보는 말 그대로 최댓값만 담고 있기 때문에, 비교 구문을 넣어서 다시 조건을 짜야 한다.
즉, WHERE (점수합계) = (아까 코드) ..의 형태로 조건절을 만들어야 한다.

기타 조인연산까지 고려한 전체 코드는 다음과 같다.

SELECT st.stdname
FROM std st
JOIN score sc ON st.stdno = sc.std_stdno
WHERE (sc.midscore + sc.finscore) = (
    SELECT MAX(midscore+finscore)
    FROM score
    WHERE cls_clsno = 1001
) AND cls_clsno = 1001;

어차피 전기자기학 과목의 코드를 아니까 굳이 ='전기자기학'을 쓰지 않고 바로 과목 코드 1001을 이용했다.

문제 6 (+a)

학생들에게 지도교수를 매칭하려 한다. 이때, 학생의 성이 교수 성함에 들어가는 경우만 매칭하라.

나만의 풀이 6

여기선 앞선 조인이 아닌 카티션 곱을 활용해야 한다.
카티션 곱은 간단하게 말하면 일대일 매칭을 한다 생각하면 된다.
가령 [1, 2, 3], [a, b]가 있으면, [(1, a), (1, b), ..., (3, b)] 이런 식으로 묶어내는 것 말이다.

카티션곱은 그냥 카티션 곱을 하려는 속성과 테이블을 병렬로 기입하면 된다.
위 문제에선 SELECT stdname, prof FROM std, cls가 그 역할을 담당한다.
물론 문제 조건에 따라서 매칭을 해야하기 때문에 WHERE절에서 한 번 걸러내야 한다.

학생의 성이 교수 성함에 들어가는 경우만 찾아내라 했다.
따라서 먼저 학생의 성을 뽑아내야 하는데, 이는 SUBSTR()를 활용해서 얻을 수 있다.
구조는 SUBSTR(속성, 시작위치, 몇개)의 꼴이며, 위 문제에선 SUBSTR(stdname, 1, 1)로 하면 된다.
참고로, 여기선 0이 아닌 1이 시작 index라 생각하면 된다.

이제 남은 과정은 prof 속성와 LIKE 연산을 써서 매칭하는 경우만 뽑으면 된다.
그러나, 저 메소드를 그대로 "%"문자열과 붙이면 오류가 난다.
따라서 별도로 CONCAT()를 써서 문자열을 붙여주는 작업을 해야 한다.

고로 전체 코드는 다음과 같이 짤 수 있다.

SELECT stdname, prof
FROM std, cls
WHERE prof LIKE CONCAT("%", SUBSTR(stdname, 1, 1), "%")
ORDER BY stdname;

마치면서

사실 원래 이 문제는 관계 대수를 쓰는 문제였지만 여기선 SQL문을 써서 푸는 형태로 바꿨다.
관계 대수로 푸는 방법은 사실 5번 문제 때문에 기술하기가 좀 그렇다.

아마 위 형태처럼 기술해야 원하는 동작을 얻는 걸로 알고 있는데 보면 귀찮다 어렵다.
그래도 혹시 필요한 다른 분들을 위해서 살짝만 해석해놓도록 하겠다.
배정 연산(←)을 활용해서 임시 릴레이션 변수에 정보를 넣고, 이를 조인하는 형태라 보면 되겠다.
저것도 기존에 적었던거 살짝 수정한 거라서 정답이 아닐 수 있다.

이 과목 풀면서 그때 클로즈북 손코딩 (!!) 을 한다 해서 많이 당황했는데, 나름 꾸준히 공부해서 어찌저찌 성적을 얻어간 전례가 있었다.
따라서 시간이 나면 다른 문제들도 계속 정리하려 한다.

참고로 6번 문제는 string과 카티션 곱을 활용하기 위해서 내가 임의로 만든 문제이다.