MySQL LogicalOperators2

[주인장]

-- 8. IN : 여러 값 중 하나라도 일치하는 행을 반환

SELECT title, author_lname

FROM books

WHERE

    author_lname = 'Carver'

    OR author_lname = 'Lahiri'

    OR author_lname = 'Smith';

SELECT title, author_lname

FROM books

WHERE

    author_lname IN ('Carver', 'Lahiri', 'Smith');

-- Carver, Lahiri, Smith 중 하나라도 일치하는 행을 반환

SELECT title, author_lname

FROM books

WHERE

    author_lname NOT IN('Carver', 'Lahiri', 'Smith');

-- Carver, Lahiri, Smith가 아닌 행을 반환

SELECT title, released_year

FROM books

WHERE

    released_year >= 2000 -- 2000년 이후

    AND released_year % 2 = 1;

-- 홀수 년도

-- 9. CASE : 조건에 따라 다른 결과를 반환

SELECT

    title,

    released_year,

    CASE

        WHEN released_year >= 2000 THEN 'Modern Lit'

        ELSE '20th Century Lit'

    END AS 'Genre'

FROM books;

SELECT

    title,

    stock_quantity,

    CASE

        WHEN stock_quantity BETWEEN 0 AND 50  THEN '*'

        WHEN stock_quantity BETWEEN 51 AND 100  THEN '**'

        WHEN stock_quantity BETWEEN 101 AND 150  THEN '***'

        ELSE '****'

    END AS STOCK

FROM books;

SELECT

    title,

    stock_quantity,

    CASE

        WHEN stock_quantity <= 40 THEN '*'

        WHEN stock_quantity <= 70 THEN '**'

        WHEN stock_quantity <= 100 THEN '***'

        WHEN stock_quantity <= 140 THEN '****'

        ELSE '*****'

    END AS stock

FROM books;

-- 10. IS NULL : NULL인 행을 반환

SELECT title, released_year FROM books WHERE author_lname IS NULL;