[함수] 숫자 함수 관련 - 아직 미숙 (정리예정)

 

 

-- 각 작가별로 자신이 쓴 책의 페이지수를 다 더하면?

 

SELECT author_lname,SUM(pages) as author_lname_cut

FROM books b

group by author_lname

 

-- 평균을 구하는 함수 agv()

-- books 테이블의 페이지수 평균은?

 

SELECT AVG(pages)

FROM books b

 

-- 각 작가별로 쓴책의 평균 페이지수를 알고 싶다

SELECT author_lname ,AVG(pages) as page_avg

FROM books b

group by author_lname

order by page_avg;

 

use test6;

 

 

-- 최대값을 구하는 함수 : max:

-- 페이지 수가 가장 많은 책은, 몇페이지 인가?

 

SELECT max(pages)

FROM books b ;

 

-- 최소값을 구하는 함수 ㅣ min()

-- 페이지 컬럼에서, 가장 적은 페이지수는?

 

SELECT min(pages)

FROM books b ;

 

-- 페이지 컬럼의 최대값 최소값 토탈값 평균값을 보여주세요

 

SELECT *

FROM books b ;

 

SELECT MAX(pages) as max,MIN(pages) as min,AVG(pages) as avg,SUM(pages) as sum

FROM books b ;

 

SELECT MAX,MIN,AVG,SUM(pages)

FROM books b ;

 

 

-- 페이지수가 가장 긴 책의 , 제목, 작가이름, 페이지수를 보여주세요

 

-- 방법 1 페이지 내림차순 정렬 한 후 1번데이터만 보이도록

SELECT title, author_lname ,pages

FROM books b

order by pages DESC

limit 1;

 

-- 방법 2 : pages 의 max값을 구해서, Sub Query 해서 해결한다

-- 1 페이지값이 가장큰것을 구하고

SELECT MAX(pages)

from books b ;

 

-- 2 pages 컬럼이, 위의 맥스값은 634인 데이터를 가져오시오

SELECT *

FROM books b

where pages = 634;

 

-- 두개의 문장을 한개의 문장으로 만드는것 : sub Query

-- 쿼리의 쿼리가 셀ㄹ

SELECT *

FROM books b

where pages = (SELECT MAX(pages)

 

 

from books);

 

-- 재고(stock_quantity) 가 가장 적은 책의, 책 이름과 발행 년도를 보여주세요

 

SELECT min(stock_quantity)

FROM books b;

 

SELECT title , released_year,stock_quantity

FROM books b

WHERE stock_quantity = (SELECT min(stock_quantity)

FROM books b);

 

SELECT *

FROM books b

order by stock_quantity asc

limit 0,2;

 

-- 출간년도가 2017년도인 데이터를 가져오시오

SELECT *

FROM books b

WHERE b.released_year =2017;

 

-- 출간년도가 2017년이 아닌 데이터를 가져오시오

-- 00이 아닌것 != <<== 부호

SELECT *

FROM books b

WHERE b.released_year != 2017;

 

-- 작가의 lname이 Harris가 아닌 데이터를 가져오되,

-- 책 제목과 페이지수만 가져오시오

 

SELECT title ,pages , author_lname

FROM books b

WHERE author_lname != "Harris";

 

-- 책 제목에 W가 포함된 책을 가져오시오

SELECT *

FROM books b

WHERE title like'%W%';

 

-- 책 제목에 W가 포함되지 않은 책을 가져오시오

SELECT *

FROM books b

WHERE title not like'%W%';

 

-- 책의 재고가 100개 이상인 데이터를 가져오되.

-- 책 제목과 재고만 보여주세요

SELECT title , stock_quantity

FROM books b

WHERE stock_quantity >= 100;

 

-- 출간년도가 2000년 이상인 책에 대해서

-- 최신책으로 정렬해서

-- 제목과 년도를 보여주세요

 

SELECT title , released_year

FROM books b

WHERE released_year >= 2000

order by released_year desc;

 

-- 신규데이터 추가

INSERT INTO books

(title,author_fname,author_lname,released_year,pages)

values ("좋은책","길동","홍",2014,214);

 

SELECT *

FROM books b ;

 

-- 재고가 null인 데이터를 가져오시오

SELECT*

FROM books b

WHERE stock_quantity is NULL;

 

SELECT*

FROM books b

WHERE ISNULL(stock_quantity) ;

 

-- 재고가 null인 아닌 데이터를 가져오시오

SELECT*

FROM books b

WHERE stock_quantity is not NULL;

 

-- 출간년도가 1990년에서 2015년 사이의 책 데이터를 가져오시오

SELECT *

FROM books b

WHERE released_year >= 1990 AND released_year <= 2015;

 

SELECT *

FROM books b ;

WHERE released_year BETWEEN 1990 and 2015;

 

-- 책 재고가 100보다 크고나 30보다 작은 데이터만 가져오세요

 

SELECT *

FROM books b

WHERE stock_quantity < 30 or stock_quantity >100;

 

-- 년도별 stock_quantity의 평균값이 70보다 큰 책들의 데이터를 가져와서

-- 년도와 평균값을 보여주세요

-- having 원래 있는 데이터가 아닌 가공한 컬럼의 데이터의 값을 가져오는 것

SELECT released_year, avg(stock_quantity) as avg

FROM books b

group by released_year HAVING avg > 70;

 

-- 위의 결과에서 avg가 큰것부터 나오도록 정렬 하세요

SELECT released_year, avg(stock_quantity) as avg

FROM books b

group by released_year HAVING avg > 70

order by avg DESC;

 

-- 출간년도가 2000년 이상인 데이터에서,

-- 년도별 stock_quantity(재고)수량의 평균값이 70보다 큰 데이터의

-- 년도와 평균값을 보여주되, 평균 값이 높은 것부터 정렬해서 보여주세요

 

SELECT released_year ,avg(stock_quantity) as avg

FROM books b

WHERE released_year >= 2000

group by released_year HAVING avg > 70

order by avg desc;

 

 

-- 실습

SELECT *

FROM subscriptions s;

 

-- 플랜별로 구독자의 수를 조회하세요

SELECT plan_name ,COUNT(*) as pc

FROM subscriptions s

group by plan_name ;

 

-- 구독 상태별로 총 구독 요금을 계산하세요

SELECT status,SUM(price)

FROM subscriptions s

group by status ;

 

-- 플랜별 평균 구독 요금을 계산하세요

SELECT plan_name ,AVG(price) as avg

FROM subscriptions s

group by plan_name ;

 

-- 구독자가 2명 이상인 플랜의 이름과 구독자 수를 조회하세요???

SELECT plan_name ,COUNT(*) as cnt

FROM subscriptions s

group by plan_name HAVING cnt >= 2;

 

-- 플랜별 총 구독 요금이 13000원 이상인 구독 상태를 조회하세요

 

 

 

-- 플랜별로 2023년 6월 1일 이후에 시작된 구독자 수를 조회하되,

-- 구독자가 2명 이상인 플랜만 조회하고,

-- 구독자가 많은 순서로 정렬하세요

SELECT plan_name ,count(status) as cnt

FROM subscriptions s

where start_date >"2023-06-01"

group by plan_name HAVING cnt >=2

order by cnt ASC ;

 

SELECT *

FROM subscriptions s;

 

 

-- 상태가 Active인 구독자의

-- 플랜별 총 구독 요금을 계산하되,

-- 총 구독요금이 20000 이상인 플랜만 조회하고,

-- 총 구독 요금이 높은 순서대로 정렬 하세요

SELECT plan_name ,sum(price) as total

FROM subscriptions s

where status = 'Active'

group by plan_name HAVING total >=20000

order by total desc;

 

-- 데이터를 가공할때, 2가지 상황에 대해서 처리하는 함수 : if()

-- books 테이블에서, 300페이지 이상인 책은 '긴책' 이라고 하고,

-- 300페이지가 안되면 '짧은 책'이라고 하자

-- 컬럼 이름은 book_type 으로 한다

 

SELECT *,if(pages >=300,'긴책','짧은책') as book_type00000000000000000000000000000000020

FROM books b ;

 

-- 출간년도가 2000년 이상인 책들은 '최신책' 이라고 하고,

-- 그렇지 않으면 '예전책' 이라고 하고 새로운 컬럼을 만듭니다. type

 

SELECT *,IF(released_year >= 2000,'최신책','예전책') as type, CONCAT(title,released_year) as title_name

FROM books b

order by released_year ;

 

-- 데이터를 가공할때 3개 이상으로 분류 하는 경우

-- stock_quantity가 0 이상이고 50이하이면, *

-- stock_quantity가 50 이상이고 100이하이면, **

-- stock_quantity가 100 이상이면, ***

-- 새로운 컬럼 stock 을 만들어서 보여주세요

 

SELECT *

FROM books b ;

 

-- 1번 방법

SELECT * ,

CASE

when stock_quantity >=0 and stock_quantity<=50 THEN '*'

WHEN stock_quantity >=51 AND stock_quantity <= 100 THEN '**'

ELSE '***'

END as stock

FROM books b ;

 

-- 2번 방법

SELECT * ,

CASE

when stock_quantity BETWEEN 0 and 50 THEN '*'

WHEN stock_quantity BETWEEN 50 and 100 THEN '**'

ELSE '***'

END

FROM books b ;

 

-- stock_quantity에 null 이 있으면 0으로 나오게, 새로운 컬럼 stock_quantity2 를 만드시오

-- null 이 있으면, 다른 값으로 셋팅하는 함수 : ifnull()

-- 실제 사용하는 실무는

 

-- 표현 1

SELECT *, IFNULL(stock_quantity, 0) as stock_quantity2

from books b ;

 

-- 잘안쓰는 표현2

SELECT *, IFNULL(stock_quantity,"데이터 없음") as stock_quantity2

from books b ;

 

 

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

SELECT *

FROM books b

WHERE released_year <= 1980;

 

-- Eggers 나 Chabon 이 쓴 책을 가져오시오

 

SELECT *

FROM books b

WHERE author_lname = 'Eggers' or author_lname='Chabon';

 

SELECT *

FROM books b

WHERE author_lname IN('Eggers','Chabon');

 

-- Lahiri 가 썻고, 2000년 이후에 발행된 책을 가져오시오

SELECT *

FROM books b

WHERE author_lname ='Lahiri' AND released_year >=2000;

 

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

SELECT *

FROM books b

WHERE pages BETWEEN 100 AND 200;

 

SELECT *

FROM books b

WHERE pages >= 100 AND pages <= 200;

 

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

SELECT *

FROM books b

WHERE author_lname like 'C%' or author_lname LIKE 'S%';

 

-- 책 제목에 stories를 포함하고 있으면, short stories로

-- 제목이 Just Kide 와 일치하면 Memoir로 하고

-- 제목에 Heartbreaking 이 포함되어 있으면, Memoir로 하고

-- 그렇지 않으면 Novel 이라고 한다.

 

SELECT title , author_lname ,

CASE

when title like '%stories%' THEN 'short stories'

when title = 'Just Kide' then 'Memoir'

when title like '%Heartbreaking%' then 'Memoir'

ELSE 'Novel'

END as type

FROM books b ;

 

SELECT title , author_lname ,

CASE

when title like '%stories%' THEN 'short stories'

when title = 'Just Kide' or title like '%Heartbreaking%' then 'Memoir'

ELSE 'Novel'

END as TYPE

FROM books b ;

 

-- 작가의 full name 으로, 각 작가별로 몇권의 책을 썻는지 갯수를 나타내세요.

-- 대신, 1권이면 book 으로 하고 2권이상이면 books 를 붙여주세요.

SELECT title, author_lname ,

if(COUNT(*)=1,CONCAT(COUNT(*),'book'),CONCAT(COUNT(*),'books')) as count

FROM books b

group by author_fname, author_lname;

 

 

SELECT *

FROM books b

 

-- 년도별 책의 갯수와 평균 페이지수를 나타내세요

-- 단, 년도로 정렬합니다.

 

SELECT released_year as year, COUNT(title) as books,

avg(pages) as 'avg pages'

FROM books b

group by released_year

order by released_year ;

 

-- 값을 더해주는 함수 sum()

-- books 테이블의 모든 책의 페이지수를 다 더하면

 

SELECT SUM(pages)

FROM books b

 

-- 각 작가별로 자신이 쓴 책의 페이지수를 다 더하면?

 

SELECT author_lname,SUM(pages) as author_lname_cut

FROM books b

group by author_lname

 

 

 

-- 평균을 구하는 함수 agv()

-- books 테이블의 페이지수 평균은?

 

SELECT AVG(pages)

FROM books b

 

-- 각 작가별로 쓴책의 평균 페이지수를 알고 싶다

SELECT author_lname ,AVG(pages) as page_avg

FROM books b

group by author_lname

order by page_avg;