Chapters

The SELECT Statement

SELECT *
FROM product
LIMIT 5

---

SELECT market_date, vendor_id, booth_number
FROM vendor_booth_assignments
ORDER BY market_date, vendor_id
LIMIT 5

---

SELECT
    market_date,
    customer_id,
    vendor_id,
    quantity * cost_to_customer_per_qty AS price
FROM customer_purchases
LIMIT 10

---

SELECT
    market_date,
    customer_id,
    vendor_id,
    ROUND(quantity * cost_to_customer_per_qty, 2) AS price
FROM customer_purchases
LIMIT 10

---

The MySQL CONCAT() function seen in Chapter 2 in the book is not available in SQLite. Instead, place two pipe characters between each field or string to combine them.

SELECT
  customer_id,
  customer_first_name || ' ' || customer_last_name AS customer_name
FROM customer

INTERACTIVE SQL EDITOR

Enter SQL in the textbox below and click "Execute" to run your query. Results will be displayed below.

SELECT * FROM customer ORDER BY customer_last_name
Execute

The WHERE Clause

SELECT
    product_id,
    product_name,
    product_category_id
FROM product
WHERE
    product_category_id = 1
LIMIT 5

---

SELECT
    market_date,
    customer_id,
    vendor_id,
    product_id,
    quantity,
    quantity * cost_to_customer_per_qty AS price
FROM customer_purchases
WHERE customer_id = 4
ORDER BY market_date, vendor_id, product_id
LIMIT 5

---

SELECT
    market_date,
    customer_id,
    vendor_id,
    product_id,
    quantity,
    quantity * cost_to_customer_per_qty AS price
FROM customer_purchases
WHERE customer_id = 3
    OR customer_id = 4
ORDER BY market_date, customer_id, vendor_id, product_id

---

SELECT
    product_id,
    product_name
FROM product
WHERE
    product_id = 10
    OR (product_id > 3
    AND product_id < 8)

---

SELECT
    product_id,
    product_name
FROM product
WHERE
    (product_id = 10
    OR product_id > 3)
    AND product_id < 8

---

SELECT *
FROM vendor_booth_assignments
WHERE
    vendor_id = 4
    AND market_date <= '2019-05-11'
ORDER BY market_date

---

SELECT
    customer_id,
    customer_first_name,
    customer_last_name
FROM customer
WHERE
    customer_last_name IN ('Diaz' , 'Edwards', 'Wilson')
ORDER BY customer_last_name, customer_first_name

---

SELECT
    customer_id,
    customer_first_name,
    customer_last_name
FROM customer
WHERE
    customer_first_name LIKE 'Jer%'

---

SELECT *
FROM product
WHERE product_size IS NULL

---

SELECT
    market_date,
    customer_id,
    vendor_id,
    quantity * cost_to_customer_per_qty AS price
FROM customer_purchases
WHERE
    market_date IN
        (
        SELECT market_date
        FROM market_date_info
        WHERE market_rain_flag = 1
        )
LIMIT 5

INTERACTIVE SQL EDITOR

Enter SQL in the textbox below and click "Execute" to run your query. Results will be displayed below.

SELECT * FROM customer ORDER BY customer_last_name
Execute

CASE Statements

SELECT
    vendor_id,
    vendor_name,
    vendor_type,
    CASE
        WHEN LOWER(vendor_type) LIKE '%fresh%'
            THEN 'Fresh Produce'
        ELSE 'Other'
    END AS vendor_type_condensed
FROM vendor

---

SELECT
    market_date,
    CASE
        WHEN market_day = 'Saturday' OR market_day = 'Sunday'
            THEN 1
        ELSE 0
    END AS weekend_flag
FROM market_date_info
LIMIT 5

---

SELECT
    market_date,
    customer_id,
    vendor_id,
    ROUND(quantity * cost_to_customer_per_qty, 2) AS price,
    CASE
        WHEN quantity * cost_to_customer_per_qty > 50
            THEN 1
        ELSE 0
    END AS price_over_50
FROM customer_purchases
LIMIT 10

---

SELECT
    market_date,
    customer_id,
    vendor_id,
    ROUND(quantity * cost_to_customer_per_qty, 2) AS price,
    CASE
        WHEN quantity * cost_to_customer_per_qty < 5.00
            THEN 'Under $5'
        WHEN quantity * cost_to_customer_per_qty < 10.00
            THEN '$5-$9.99'
        WHEN quantity * cost_to_customer_per_qty < 20.00
            THEN '$10-$19.99'
        WHEN quantity * cost_to_customer_per_qty >= 20.00
            THEN '$20 and Up'
    END AS price_bin
FROM customer_purchases
LIMIT 10

---

SELECT
    market_date,
    customer_id,
    vendor_id,
    ROUND(quantity * cost_to_customer_per_qty, 2) AS price,
    CASE
        WHEN quantity * cost_to_customer_per_qty < 5.00
            THEN 0
        WHEN quantity * cost_to_customer_per_qty < 10.00
            THEN 5
        WHEN quantity * cost_to_customer_per_qty < 20.00
            THEN 10
        WHEN quantity * cost_to_customer_per_qty >= 20.00
            THEN 20
    END AS price_bin_lower_end
FROM customer_purchases
LIMIT 10

---

SELECT
    vendor_id,
    vendor_name,
    vendor_type,
    CASE WHEN vendor_type = 'Arts & Jewelry'
        THEN 1
        ELSE 0
    END AS vendor_type_arts_jewelry,
    CASE WHEN vendor_type = 'Eggs & Meats'
        THEN 1
        ELSE 0
    END AS vendor_type_eggs_meats,
    CASE WHEN vendor_type = 'Fresh Focused'
        THEN 1
        ELSE 0
    END AS vendor_type_fresh_focused,
    CASE WHEN vendor_type = 'Fresh Variety: Veggies & More'
        THEN 1
        ELSE 0
    END AS vendor_type_fresh_variety,
    CASE WHEN vendor_type = 'Prepared Foods'
        THEN 1
        ELSE 0
    END AS vendor_type_prepared
FROM vendor

INTERACTIVE SQL EDITOR

Enter SQL in the textbox below and click "Execute" to run your query. Results will be displayed below.

SELECT * FROM customer ORDER BY customer_last_name
Execute