[SQL] IF, CASE, 함수 만들기

DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
number int,
name varchar(20),
type varchar(10),
attack int,
defense int
);
INSERT INTO mypokemon (number, name, type, attack, defense)
VALUES (10, 'caterpie', 'bug', 30, 35),
(25, 'pikachu', 'electric', 55, 40),
(26, 'raichu', 'electric', 90, 55),
(125, 'electabuzz', 'electric', 83, 57),
(133, 'eevee', 'normal', 55, 50),
(137, 'porygon', 'normal', 60, 70),
(152, 'chikoirita', 'grass', 49, 65),
(153, 'bayleef', 'grass', 62, 80),
(172, 'pichu', 'electric', 40, 15),
(470, 'leafeon', 'grass', 110, 130);


SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //
create function isStrong(attack int, defense int)
	returns varchar(20)
begin
	declare a int;
    declare b int;
    declare isStrong varchar(20);
    set a=attack;
    set b=defense;
    SELECT CASE
			WHEN a+b >= 120 THEN "very strong"
			WHEN a+b >= 90 THEN "strong"
			ELSE "not strong"
	END into isStrong;
	return isStrong;
end
//
DELIMITER ;


select * from mypokemon;
select name,
case
when number>150 then 'old'
when number<=150 then 'new'
end as age
from mypokemon;

select name,
case
when attack+defense<100 then 'weak'
when attack+defense>=100 then 'strong'
end as ability
from mypokemon;

select type, if(avg(attack)>60, 1,0) as is_strong_type
from mypokemon
group by type;

select name,
case
when attack>100 and defense>100 then 1
when attack<100 or defense<100 then 0
end as ace
from mypokemon;

SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //
create function number_bin(number int)
	returns varchar(20)
begin
	declare a int;
    declare number_bin varchar(20);
    set a=number;
    SELECT CASE
			WHEN a<100 THEN "<100"
			WHEN b<200 THEN "<200"
			ELSE "<500"
	END into number_bin;
	return number_bin;
end 
//
DELIMITER ;


SELECT name, 
		CASE
			WHEN number<100 THEN "<100"
			WHEN number<200 THEN "<200"
			ELSE "<500"
	END as number_bin
from mypokemon;

SELECT name, 
		CASE
			WHEN number>=150 and attack>=50 THEN "new_strong"
			WHEN number>=150 and attack<50 THEN "new_weak"
            WHEN number<150 and attack>=50 THEN "old_strong"
			ELSE "old_weak"
	END as age_attack
from mypokemon;


select type, 
		CASE
			WHEN count(1)=1 THEN "solo"
			WHEN count(1)<3 THEN "minor"
            WHEN count(1)>=3 THEN "major"
	END as count_by_type
from mypokemon
group by type;