[SQL] 합치기 실습문제 (students/papers)

INSERT INTO students (first_name) VALUES 
('Caleb'), ('Samantha'), ('Raj'), ('Carlos'), ('Lisa');

INSERT INTO papers (student_id, title, grade ) VALUES
(1, 'My First Book Report', 60),
(1, 'My Second Book Report', 75),
(2, 'Russian Lit Through The Ages', 94),
(2, 'De Montaigne and The Art of The Essay', 98),
(4, 'Borges and Magical Realism', 89);

select * from students;
select * from papers;

 

 

-- 방법 1
select * 
from students, papers
where students.id = papers.student_id;

select * 
from papers p, students s -- 테이블명을 줄여서 바꿔 부를수 있다.
where s.id = p.student_id
order by grade desc;

-- 방법2
select students.first_name, papers.title, papers.grade 
from papers
join students
    on papers.student_id = students.id
order by grade desc;

select s.first_name, p.title, p.grade 
from papers p
join students s
    on p.student_id = s.id
order by p.grade desc;


-- 방법3
select s.first_name, p.title, p.grade
from papers p
left join students s
on p.student_id = s.id;

-- 실습문제 5번

-- missing 넣는 방법
select s.first_name,
case 
when p.title is null then 'MISSING'
    -- when p.title = null then 'MISSING'은 안된다. 
    else p.title
end as title
from students s
left join papers p
on s.id = p.student_id;

-- 더 간단한 방법 'ifnull'    
select s.first_name,
ifnull(p.title, 'MISSING') as title
from students s
left join papers p
on s.id = p.student_id;
    
select s.first_name,
ifnull(p.title, 'MISSING') as title
ifnull(p.grade, 0) as grade
from students s
left join papers p
on s.id = p.student_id;
    
-- 실습문제 6번
-- 사람별 점수 평균을 구하기

select s.first_name, ifnull(avg(p.grade),0) as average
from students s
left join papers p
on s.id = p.student_id
group by s.id
order by average desc;

-- 실습문제 7번

select s.first_name, ifnull(avg(p.grade),0) as average,
case 
when avg(p.grade) >= 80 then 'Passing'
    else 'Falling'
end as passing_status
from students s
left join papers p
on s.id = p.student_id
group by s.first_name
order by average desc;