SQL_Basic_06_Day 07. 규칙 만들기

Day 07. 규칙 만들기

---

1. 조건 만들기

---

조건만들기

“구매 금액 10억 이상 고객은 VVIP로 1억 이상이면 VIP로 고객 등급 뽑아주세요”
“상품 매출이 1억 이상이면 o로 미만이면 x로 데이터 뽑아주세요”

 

조건을 만들때 사용하는 함수

IF

IF 특징

• IF(조건식, 참일때 값, 거짓일때 값) 형식으로 사용.
• 주로 SELECT절에 사용하는 함수로, 결과값을 새로운컬럼으로 반환.

 

IF 사용예제

SELECT name, IF(attack >= 60, ‘strong’, ‘weak’) AS attack_class
FROM pokemon.mypokemon;

 

 

 

 

IFNULL

데이터가 NULL인지 아닌지를 확인해 NULL이라면 새로운 값을 반환하는 함수

※ IS NULL : 데이터가 NULL인지 아닌지를 확인하는 연산자

 

IFNULL 특징

• IFNULL([컬럼이름], NULL일때 값) 형식으로 사용.
• 해당 컬럼의 값이 NULL인 로우에서 NULL일때 값을 반환.
• 주로 SELECT절에 사용하는 함수로, 결과 값을 새로운 컬럼으로 반환.

 

IFNULL 사용예제

 

SELECT name, IFNULL(name, ‘unknown’) AS full_name
FROM pokemon.mypokemon;

 

 

 

2. 여러 조건 한번에 만들기

---

 

조건을 하나 만들때 사용하는 문법 IF

조건을 여러개 만들때 사용하는 문법 CASE

CASE

CASE 특징

• 주로 SELECT절에 사용하는 함수로, 결과값을 새로운 컬럼으로 반환한다
• ELSE 문장을 생략시 NULL 값을 반환한다.

 

형식 1 

 

CASE
    WHEN 조건식1 THEN결과값1
    WHEN 조건식2 THEN결과값2
    ELSE 결과값3
END

 

형식2

 

CASE [컬럼이름]
    WHEN 조건값1 THEN결과값1
    WHEN 조건값2 THEN결과값2
    ELSE 결과값3
END

 

 

CASE 사용예제(1)

 

ELECT name,
CASE
    WHENattack >= 100 THEN ‘very strong’
    WHENattack >= 60 THEN ‘strong’
    ELSE ‘weak’
END AS attack_class
FROM pokemon.mypokemon;

 

 

 

CASE 사용예제(2)

 

SELECT name, type
CASE type
    WHEN ‘bug’ THEN ‘grass’
    WHEN ‘electric’ THEN ‘water’
    WHEN ‘grass’ THEN ‘bug’
END AS rival_type
FROM pokemon.mypokemon;

 

 

 

 

3. 함수 만들기

---

함수 만들기 쿼리 문법

 

CREATE FUNCTION [함수이름] ( [입력값이름] [데이터타입], …)
        RETURNS [결과값데이터타입]
BEGIN
        DECLARE [임시값이름] [데이터타입];
        SET [임시값이름] = [입력값이름];
        쿼리;
        RETURN결과값
END

 

함수 지우기 쿼리 문법

 

DROP FUNCTION [함수이름];

 

함수 만들기 예시

 

공격력과 방어력의 합을 가져오는함수

 

CREATE FUNCTION getAbility(attack INT, defense INT)
    RETURNS INT
BEGIN
        DECLARE a INT;
        DECLARE b INT;
        DECLARE ability INT;
        SET a = attack;
        SET b = defense;
        SELECT a + b INTO ability;
        RETURN ability;
END

 

 

 

 

 

4. [실습] 함수를 만들고 사용해보자

---

[포켓몬 데이터 입력 쿼리]

 

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);

 

MISSION
공격력과 방어력의 합이 120보다크면 ‘very strong’, 90보다 크면 ‘strong’,
모두 해당되지 않으면 ‘not strong’를 반환하는 함수 ‘isStrong’을 만들고 사용해주세요.
조건1: attack과 defense를 입력값으로 사용하세요.
조건2: 결과값 데이터타입은 VARCHAR(20)로 해주세요.

 

 

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 name, isStrong(attack, defense) AS isStrong
FROM mypokemon;

 

 

5. [실습] 조건문을 만들어 보자

---

※모든 문제 결과값은 결과 테이블을 보고 이해할 수 있게, 포켓몬이름 또는 타입 중 하나의 값을 반드시 포함해주세요.

 

MISSION (1)
포켓몬의 번호가 150보다 작으면 값을 ‘old’로 반환하고, 번호가 150보다 크거나 같으면 값을 ‘new’로 반환해서 ‘age’라는별명으로 가져와주세요. 

 

SELECT number, IF( number < 150,'old', 'new' ) As age
FROM mypokemon;

 

MISSION (2)
포켓몬의 공격력과 방어력의 합이 100보다 작으면 값을‘weak’로 반환하고, 100보다 크거나 같으면 값을‘strong’로반환해서 ‘ability’라는 별명으로 가져와주세요.

 

SELECT name , IF( attack + defense < 100 ,'weak', 'strong' ) As ability
FROM mypokemon;

 

MISSION (3)
포켓몬의 타입별 공격력의 평균이60 이상이면 값을True(1)로 반환하고, 60보다 작으면False(0)를
반환해 ‘is_strong_type’이라는 별명으로 가져와주세요.

 

SELECT type, CASE
				WHEN AVG(attack) >= 60 THEN TRUE
                WHEN AVG(attack) < 60 THEN FALSE
                END
                AS is_strong_type
FROM mypokemon
GROUP BY type;

SELECT type, IF(AVG(attack) >= 60 ,TRUE,FALSE) AS is_strong_type
FROM mypokemon
GROUP BY type;

 

MISSION (4)
포켓몬의 공격력이 100보다 크고, 방어력도 100보다 크면 값을True(1)로 반환하고, 둘 중 하나라도
100보다 작으면 False(0)를 반환해 ‘ace’라는 별명으로 가져와주세요.

 

SELECT name, CASE
            	WHEN attack > 100 AND defense > 100  THEN TRUE
                WHEN attack > 100 OR defense < 100 THEN FALSE
                END
                AS ace
FROM mypokemon;

 

 

MISSION (5)
포켓몬의 번호가 100보다 작으면 값을 ‘<100’을 반환하고, 200보다 작으면 값을‘<200’을 반환하고,
500보다 작으면 값을‘<500’을 반환하는 규칙을만들고, 각 포켓몬별 규칙 적용값 을‘number_bin’이라는
별명으로 가져와주세요.

 

SELECT name, CASE
                WHEN number < 100  THEN '<100 '
                WHEN number < 200  THEN '<200'
                WHEN number < 500  THEN '<500 '
                END
                AS number_bin
FROM mypokemon;

 

 

MISSION (6)
아래 표에 따른 값을 반환하는 규칙을 만들고, 각 포켓몬별 규칙 적용값을 ‘age_attack’ 이라는 별명으로
가져와주세요.

 

SELECT name, CASE
            	WHEN number>=150 AND attack>=50 THEN 'new_strong '
                WHEN number<150 AND attack>=50  THEN 'old_strong'
                WHEN number>=150 AND attack<50 THEN 'new_weak '
                WHEN number<150 AND attack<50 THEN 'old_weak '
                END
                AS age_attack
FROM mypokemon;

 

 

MISSION (7)
타입별 포켓몬수가 1개면 ‘solo’, 3개 미만이면 ‘minor’ , 3개이상이면 ‘major’를 반환하고,
‘count_by_type’이라는 별명으로 가져와주세요.

 

SELECT type ,CASE 
			WHEN COUNT(type) = 1 THEN 'solo'
            WHEN COUNT(type) < 3 THEN 'minor'
            WHEN COUNT(type) >= 3 THEN 'major'
            END
            AS count_by_type  
FROM mypokemon
GROUP BY type;