The Report (IF, INNER JOIN, BETWEEN /AND, ORDER BY)

[문제]

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks.

 

Grades contains the following data:

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

Write a query to help Eve.

 

Sample Input

Sample Output

Maria 10 99
Jane 9 81
Julia 9 88 
Scarlet 8 78
NULL 7 63
NULL 7 68

Note

Print "NULL"  as the name if the grade is less than 8.

 

Explanation

Consider the following table with the grades assigned to the students:

So, the following students got 8, 9 or 10 grades:

• Maria (grade 10)
• Jane (grade 9)
• Julia (grade 9)
• Scarlet (grade 8)

 

---

[문제 설명]

> name, grade, mark를 구하고 grade가 8 미만인 경우 이름을 null로 출력하기

• 구해야 하는 것
  name, grade, marks
• 필터 조건
  grade가 8 미만인 경우 이름을 null로 출력
• 정렬
  grade 내림차순, name 오름차순, marks 오름차순

 

[나의 풀이]

SELECT IF(g.grade >= 8, name, null)
	, g.grade
    	, s.marks
FROM students s
    INNER JOIN grades g ON s.marks BETWEEN g.min_mark AND g.max_mark
ORDER BY g.grade DESC, s.name, s.marks

 

출력결과

name	grade	marks
Britney	10	95
Heraldo	10	94
Julia	10	96
: :	: :	: :

 

---

 

[설명]

1. INNER JOIN문

SELECT *
FROM students s
    INNER JOIN grades g ON s.marks BETWEEN g.min_mark AND g.max_mark

> ON절에 BETWEEN min_mark AND max_mark를 사용하여

   Grades 테이블의 min_mark 와 max_mark 사이에 존재하는

   students 테이블의 marks의 값을 출력

 

 

2. IF문

SELECT IF(g.grade >= 8, name, null)
	, g.grade
    	, s.marks
FROM students s
    INNER JOIN grades g ON s.marks BETWEEN g.min_mark AND g.max_mark

> grade가 8미만인 경우 이름을 null로 출력함

> 따옴표를 사용하여 'null'이라 쓰면 문자열로 출력되기 때문에

   따옴표 없이 null이라 써야 함

   

IF(조건,  참일 때 값, 거짓일 때 값)

 

 

3. 정렬

SELECT IF(g.grade >= 8, name, null)
	, g.grade
    	, s.marks
FROM students s
    INNER JOIN grades g ON s.marks BETWEEN g.min_mark AND g.max_mark
ORDER BY g.grade DESC, s.name, s.marks

> grade를 내림차순으로 정렬한 후 

   grade가 같으면 name을 오름차순,

   marks를 오름차순으로 정렬함