[함수] 숫자 함수 관련 - 아직 미숙 (정리예정)
-- 각 작가별로 자신이 쓴 책의 페이지수를 다 더하면?
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;
