오늘도 데이터분석

the report 문제 바로가기 

문제 

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.

문제정리
1.  Students 테이블에서 점수와 Grades테이블의 등급표(?)를 가지고 학생들의 등급 매기기 
2. 결과값의 컬럼은 Name, Grade, Mark
3. 8등급 미만인 학생의 이름은 NULL로 지정
4. 결과값은 높은 grade부터 정렬, 같은 grade면 이름 알파벳 순 또는 이름이 NULL이면 mark를 오름차순 정렬

풀이 

SELECT CASE WHEN g.grade >= 8 THEN s.name ELSE NULL END AS Name,
               g.grade AS Grade,
               s.marks AS Mark
FROM students s
           INNER JOIN grades g ON s.marks BETWEEN g.min_mark AND g.max_mark
ORDER BY Grade DESC, Name, Mark

Expected Output

Britney 10 95
Heraldo 10 94
Julia 10 96
Kristeen 10 100
Stuart 10 99
Amina 9 89
Christene 9 88
Salma 9 81
Samantha 9 87
Scarlet 9 80
Vivek 9 84
Aamina 8 77
Belvet 8 78
Paige 8 74
Priya 8 76
Priyanka 8 77
NULL 7 64
NULL 7 66
NULL 6 55
NULL 4 34
NULL 3 24

Employees Earning More Than Their Managers 문제 바로가기 

문제

Write an SQL query to find the employees who earn more than their managers.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Employee table:
+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | Null      |
| 4  | Max   | 90000  | Null      |
+----+-------+--------+-----------+
Output: 
+----------+
| Employee |
+----------+
| Joe      |
+----------+
Explanation: Joe is the only employee who earns more than his manager.

풀이

-- employee 테이블에 managerID를 기준으로 SELF JOIN -> manager 테이블을 새로 만들기 

-- manager보다 salary가 employee 조건문 WHERE로 필터링

SELECT em.name AS Employee
FROM employee AS em
         INNER JOIN employee AS ma ON em.managerID = ma.id
WHERE em.salary > ma.salary

OUTPUT

{"headers": ["Employee"], "values": [["Joe"]]}