[MySQL] 그룹/묶음 뒤에 조건: group by..having, 실습문제

Group by뒤의 조건: having

-- group by의 결과에 대해서 조건을 걸 때는 having을 사용한다. 그 뒤에 and 및 or도 사용 가능하다. 

group by 컬럼명 having 묶음 후의 조건

 

-- 각 고객별로 주문 금액 평균이 300 달러 이상인 데이터만 가져오시오.

select c.first_name, c.last_name, avg(o.amount) as AVG from customers c join orders o on c.id = o.customer_id group by c.id having avg(o.amount) >= 300;

 

-- 실습

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;

 

-- 첫번째 문제

select s.first_name, p.title, p.grade from students s join papers p 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;

 

-- 세번째 문제

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;

 

-- 네번째 문제

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

 

-- 다섯번째

select s.first_name, ifnull(avg(grade),0) as average , if(avg(p.grade)>70,'PASSING','FALING') as passing_status         -- select문의 같은 레벨에서는 as 뒤의 타이틀이 적용되지 않는다.  from students s left join papers p on s.id = p.student_id group by s.first_name  order by average desc;

ㄴ select문의 같은 레벨에서는 as 뒤의 타이틀이 적용되지 않는다