隨堂練習：SQL 常用述詞

請執行以下程式，觀察執行結果。

參考解答

CREATE table products (
    id INT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    price INT NOT NULL,
    weight DECIMAL(5, 2),
    category VARCHAR(20)
);

/* 建立資料，插入多筆資料 */
INSERT INTO products VALUES 
    (1, '華速 intel i3 筆電', 20000, 2.12, 'NB'),
    (2, 'Mac Pro 筆電', 62000, 1.4, 'NB'),
    (3, '微興電競筆電', 32000, 3.00, 'NB'),
    (4, '戈林冰箱', 22000, 13.78, '3C'),
    (5, '三力冰箱', 52000, 23.18, '3C'),
    (6, 'C 語言入門', 420, 0.31, 'Book'),
    (7, 'python3 實戰', 580, 0.28, 'Book'),
    (8, 'JavaScript 英雄', 1000, 0.12, 'Book'),
    /* 故意放入 category 有誤資料當作學習素材 */
    (9, 'Java 資料分析', 340, -1, NULL),
    (10, 'python 資料分析', 640, -0.43, NULL);

/* 開頭相同 */
SELECT name
FROM products
WHERE name LIKE 'py%';

/* 中間相同 */
SELECT name
FROM products
WHERE name LIKE '%thon%';

/* 結尾相同 */
SELECT name
FROM products
WHERE name LIKE '%分析';

/* IS NULL */
SELECT name, category
FROM products
WHERE category IS NULL;

SELECT name, category
FROM products
WHERE category IS NOT NULL;

/* BETWEEN */
SELECT name, category
FROM products
WHERE price BETWEEN 100 AND 1000;

/* IN、NOT IN */
SELECT name, category
FROM products
WHERE category IN ('NB', 'Book');

SELECT name, category
FROM products
WHERE category NOT IN ('NB', 'Book');