Join 예제

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);

 

-- grade로 정렬하세요

-- first_name, title, grade

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

 

 

-- 페이퍼 작성 안한 사람도 나오게.

SELECT s.first_name ,p.title ,p.grade 
FROM students s 
left join papers p on s.id=p.student_id ;

 

 

-- null을 제목은 MISSING, 점수는 0으로 세팅

SELECT s.first_name , IFNULL(p.title,'Missing') title, IFNULL(p.grade,0) grade 
FROM students s 
left join papers p on s.id=p.student_id ;

 

 

-- first_name과 average(소수점 4자리가 뒤에붙게)가 출력되게 하세요.

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;

 

 

-- 75점 이상이면 PASSING 아니면 FAILING

-- first_name, average, passing_status

SELECT s.first_name , ifnull(avg(p.grade),0)as average, 
		if(avg(p.grade)>=75,'PASSING','FAILING')as passing_status
FROM students s 
left join papers p on s.id =p.student_id 
group by s.id 
order by average desc;