[EXERCISES] 9. 실습예제(4)

 

반갑습니다!

실습예제3까지 끝내고 이제 실습예제4를 한 번 풀어보겠습니다

실습예제4는 앞선 문제들보다 간단합니다 ^^

아래는 실습에 사용할 데이터에 대한 SQL 파일입니다!

student_paper.sql

0.00MB

 

 

 

 

이번 실습에서는 두 개의 테이블이 필요합니다

하나는 학생들의 이름 정보가 들어있는 students 테이블,

나머지 하나는 학생의 과제 점수 정보가 들어있는 papers 테이블입니다 ^^

 

실습에 사용할 데이터셋인 students 테이블에 대한 정보입니다

 

 

실습에 사용할 데이터셋인 papers 테이블에 대한 정보입니다

 

위 두 테이블은 students 테이블의 id 값을 중심으로 관계성이 설정되어 있습니다!

papers 테이블을 보시면 student_id 컬럼이 보이죠?

해당 컬럼은 students 컬럼의 id 값을 외부 참조하는 Foreign Key가 지정되어 있습니다 

 

이 관계성을 바탕으로

위 두 테이블에 대하여 JOIN 연산 및 SQL문 키워드들을

이용하여 아래의 실습 문제를 해결해보겠습니다 ^^!

 

 

 

Q1.

 

각 학생에 대해서 제출된 과제 제목과 점수를 표기하는 문제입니다.

과제 제출 정보가 없는 학생은 제외합니다.

 

 

 

A1.

 

SELECT students.first_name, papers.title, papers.grade
FROM students
JOIN papers ON students.id = papers.student_id
ORDER BY grade DESC;

 

먼저는 두 테이블을 JOIN 연산으로 연결해야 합니다

students 테이블의 id 컬럼의 값을 외부 참조하고 있으므로

해당 컬럼을 기준으로 연결해주시면 되겠습니다 ^^

 

여기서 중요한 부분은, 과제 제출 정보가 없는 학생은 제외해야 하므로

그냥 JOIN 연산을 해주시면 되겠습니다 ^^

 

 

여기서 잠깐,

바로 다음 문제에서 이어지는 부분인데요

 

만약 제출 정보가 없는 학생을 제외시키지 않고

모든 학생들에 대한 제출 정보가 출력되도록 하려면,

LEFT JOIN 연산을 수행해주시면 되겠습니다

 

JOIN연산은 붙이고자 하는 테이블에 데이터가 Null이라면,

연결의 기준이 되는 테이블의 데이터도 제외가 됩니다

 

LEFT JOIN연산은 붙이고자 하는 테이블에 데이터가 Null이라도,

연결의 기준이 되는 테이블의 데이터가 제외되지 않습니다

붙인 테이블의 Null이 그대로 남아서 조회되죠 ^^

 

 

두 개 이상의 테이블을 연결하는 JOIN 연산시

내가 조회하고자 하는 데이터의 범위가 어떻게 되는지 명확하게 알고

구분할 줄 알아야 JOIN연산인지, LEFT 혹은 RIGHT JOIN연산인지

정확하게 가늠할 수 있습니다 ^^!

 

 

 

 

 

Q2.

 

과제 제출 정보가 없는 학생들도 포함되게끔 조회하면 되겠습니다.

 

 

 

A2.

 

SELECT students.first_name, papers.title, papers.grade
FROM students
LEFT JOIN papers ON students.id = papers.student_id
ORDER BY students.id ASC, papers.grade ASC;

 

1번 문제와 가장 큰 차이점은 LEFT JOIN 부분입니다 ^^

1번 정답 하단에 첨언하여 설명드렸습니다

 

 

 

 

 

Q3.

 

각 학생마다 과제 점수가 몇 점인지 평균값을 구하는 문제입니다.

미제출자는 과제 점수가 0점으로 표기됩니다.

 

 

 

 

 

A3.

 

SELECT students.first_name, IFNULL(AVG(grade), 0) AS average

FROM students

LEFT JOIN papers ON students.id = papers.student_id

GROUP BY students.first_name

ORDER BY papers.grade DESC;

 

LEFT JOIN 연산을 통해 테이블을 합친 다음 GROUP BY로 그룹화하여

AVG() 함수로 평균을 구하고, 만약 값이 Null이라면 0값을 삽입해주면 되겠습니다 ^^

 

 

 

 

 

Q4.

 

3번 문제에서 구한 average 값에 대해

75점 이상이면 PASSING,

75점 미만이면 FALLING 표시를 해주면 되겠습니다

 

 

 

A4.

 

SELECT students.first_name,
   IFNULL(AVG(grade), 0) AS average,
       IF(AVG(grade) >= 75, 'PASSING', 'FALLING') AS passing_status
FROM students
LEFT JOIN papers ON students.id = papers.student_id
GROUP BY students.first_name
ORDER BY papers.grade DESC;

 

3번 문제에 대해 IF조건문만 추가하여

평균값이 75점 이상이면 PASSING,

그 이외이면 FALLING값이 되도록 하면 되겠습니다 ^^

 

 

 

 

이번 실습예제 4는 JOIN 및 LEFT JOIN과 관련된 내용이 제일 핵심이었습니다 ^^

그리고 일전에 살펴본 IFNULL()을 통해 Null 존재 여부에 따라 값을 삽입해서 Null을 처리하고,

IF() 도 사용하여 분기점을 나누는 부분이 있었습니다 ^^

 

다음 포스트에서는 새로운 실습예제를 가지고 함께 또 연습해보겠습니다!