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

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

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 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,
    COUNT(DISTINCT product_id) AS different_products_purchased
FROM customer_purchases
GROUP BY market_date, customer_id
ORDER BY market_date, customer_id
LIMIT 10
SELECT
    market_date,
    customer_id,
    vendor_id,
    quantity * cost_to_customer_per_qty AS price
FROM customer_purchases
WHERE customer_id = 3
ORDER BY market_date, vendor_id

SELECT
    customer_id,
    market_date,
    SUM(quantity * cost_to_customer_per_qty) AS total_spent
FROM customer_purchases
WHERE customer_id = 3
GROUP BY market_date
ORDER BY market_date

SELECT
    c.customer_first_name,
    c.customer_last_name,
    cp.customer_id,
    v.vendor_name,
    cp.vendor_id,
    cp.quantity * cp.cost_to_customer_per_qty AS price
FROM customer AS c
    LEFT JOIN customer_purchases AS cp
        ON c.customer_id = cp.customer_id
    LEFT JOIN vendor v
        ON cp.vendor_id = v.vendor_id
WHERE
    cp.customer_id = 3
ORDER BY cp.customer_id, cp.vendor_id
SELECT
    c.customer_first_name,
    c.customer_last_name,
    cp.customer_id,
    v.vendor_name,
    cp.vendor_id,
    ROUND(SUM(quantity * cost_to_customer_per_qty), 2) AS total_spent
FROM customer AS c
    LEFT JOIN customer_purchases AS cp
        ON c.customer_id = cp.customer_id
    LEFT JOIN vendor v
        ON cp.vendor_id = v.vendor_id
WHERE
    cp.customer_id = 3
GROUP BY
    c.customer_first_name,
    c.customer_last_name,
    cp.customer_id,
    v.vendor_name,
    cp.vendor_id
ORDER BY cp.customer_id, cp.vendor_id
SELECT
    pc.product_category_name,
    p.product_category_id,
    MIN(vi.original_price) AS minimum_price,
    MAX(vi.original_price) AS maximum_price
FROM vendor_inventory AS vi
    INNER JOIN product AS p
        ON vi.product_id = p.product_id
    INNER JOIN product_category AS pc
        ON p.product_category_id = pc.product_category_id
GROUP BY pc.product_category_name, p.product_category_id

SELECT
    vendor_id,
    COUNT(DISTINCT product_id) AS different_products_offered,
    AVG(original_price) AS average_product_price
FROM vendor_inventory
WHERE market_date BETWEEN '2020-07-01' AND '2020-07-15'
GROUP BY vendor_id
ORDER BY vendor_id
SELECT
    vendor_id,
    COUNT(DISTINCT product_id) AS different_products_offered,
    SUM(quantity * original_price) AS value_of_inventory,
    SUM(quantity) AS inventory_item_count,
    SUM(quantity * original_price) / SUM(quantity) AS average_item_price
FROM vendor_inventory
WHERE market_date BETWEEN '2020-07-01' AND '2020-07-15'
GROUP BY vendor_id
HAVING inventory_item_count >= 100
ORDER BY vendor_id
SELECT
    cp.market_date,
    cp.customer_id,
    SUM(CASE WHEN product_qty_type = "unit" THEN quantity ELSE 0 END) AS qty_units_purchased,
    SUM(CASE WHEN product_qty_type = "lbs" THEN quantity ELSE 0 END) AS qty_lbs_purchased,
    SUM(CASE WHEN product_qty_type NOT IN ("unit","lbs") THEN quantity ELSE 0 END) AS qty_other_purchased
FROM customer_purchases cp
    INNER JOIN product p
        ON cp.product_id = p.product_id
WHERE market_date = '2020-07-11'
GROUP BY market_date, customer_id
ORDER BY market_date, customer_id

INTERACTIVE SQL EDITOR

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