Chapter 2

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...

Chapter 3

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...

Chapter 5

SQL Joins SELECT * FROM product LEFT JOIN product_category ON product.product_category_id = product_category. product_category_id SELECT p.product_id, p.product_name, pc.product_category_id, pc.product_category_name FROM product AS p LEFT JOIN product_category AS pc...

Chapter 6

Aggregating Results for Analysis SELECT market_date, customer_id, COUNT(*) AS items_purchased FROM customer_purchases GROUP BY market_date, customer_id ORDER BY market_date, customer_id LIMIT 10 SELECT market_date, customer_id, SUM(quantity) AS items_purchased,...

Here, you will find example queries that correspond to each SQL for Data Scientists chapter, so you can copy and paste them into the online interactive SQL editor below and try modifying them yourself, for practice.

For complete chapter content, buy the book.

Note: The SQL provided in the book is formatted for MySQL databases. This online editor uses a SQLite database, so the queries on this website may differ slightly from the book. To download MySQL Workbench and try the queries as written in the book, download the support files from the publisher’s website (under Downloads) and follow the included instructions.

Chapter 4

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.