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

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