MySQL 실습 문제(where, like, case, order by, group by ...)

 

반응형

 

 

 

 

 

주어진 데이터는 아래와 같습니다.

 

select *
from books;

 

 

 

 

---

실습문제

 

1. 1980년 이전에 발행된 책을 가져오시오

 

select *
from books
where released_year < 1980;

 

 

 

 

 

2. Eggers나 Chabon이 쓴 책을 가져오시오

 

select *
from books
where author_lname = 'Eggers' or author_lname = 'Chabon';

 

select *
from books
where author_lname in ('Eggers' , 'Chabon');

 

 

 

 

 

3. Lahiri가 썼고, 2000년 이후에 발행된 책을 가져오시오

 

select *
from books
where author_lname = 'Lahiri' and released_year >= 2000;

 

 

 

 

 

4. 페이지수가 100부터 200까지인 책들을 가져오시오

 

select *
from books
where pages between 100 and 200;

 

 

 

 

 

5. lname이 C나 S로 시작하는 작가가 쓴 책을 가져오시오

 

select *
from books
where author_lname like 'C%' or author_lname like 'S%';

 

 

 

 

 

6. 만약에 책 제목에 'stories'가 있으면 -> Type 을 Short Stories로 책 제목에 just kids, A Heartbreaking Work 가 있으면 -> Memoir 다른 것들은 모두 Novel로 타입을 지정합니다.

 

select title, author_lname,
		case 
        when title like '%stories%' then 'Short Stories'
        when title = 'Just kids' or title like 'A Heartbreaking Work' then 'Memoir'
        else 'novel'
        end as TYPE
from books;

 

 

 

 

 

7. 작가의 full name 으로 각 작가별로 몇권의 책을 썼는지 갯수를 나타내세요. 대신 1권이면 book으로하고 2권이상이면 book를 붙여주세요.

 

select title, author_lname, if(count(*) = 1, concat(count(*), ' book'), 
	concat(count(*), ' books')) as COUNT
from books
group by author_lname, author_fname
order by author_lname;

 

 

 

 

 

 

8. 연도별 책의 갯수와 평균 페이지수를 나타내세오, 단 연도로 정렬합니다.

 

select released_year as year,
		count(*) as '# books',
		avg(pages) as 'avg pages'
from books
group by released_year
order by year;

 

 

 

 

 

 

728x90