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 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 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
        ON p.product_category_id = pc.product_category_id
ORDER BY pc.product_category_name, p.product_name
SELECT *
FROM customer AS c
    LEFT JOIN customer_purchases AS cp
        ON c.customer_id = cp.customer_id

Run this query in the interactive editor below, scroll to the bottom of the output, and look for customer number 27


SELECT *
FROM customer AS c
    INNER JOIN customer_purchases AS cp
        ON c.customer_id = cp.customer_id

Run this query, scroll to the bottom of the output, and see if customer number 27 is returned

SELECT c.*
FROM customer AS c
    LEFT JOIN customer_purchases AS cp
        ON c.customer_id = cp.customer_id
WHERE cp.customer_id IS NULL

Note that RIGHT JOINs are not supported in SQLite

SELECT DISTINCT c.*
FROM customer AS c
    LEFT JOIN customer_purchases AS cp
        ON c.customer_id = cp.customer_id
WHERE cp.market_date = '2020-07-01'
SELECT DISTINCT c.*
FROM customer AS c
    LEFT JOIN customer_purchases AS cp
        ON c.customer_id = cp.customer_id
WHERE (cp.market_date <> '2020-07-01' OR cp.market_date IS NULL)
SELECT
    b.booth_number,
    b.booth_type,
    vba.market_date,
    v.vendor_id,
    v.vendor_name,
    v.vendor_type
FROM booth AS b
    LEFT JOIN vendor_booth_assignments AS vba 
        ON b.booth_number = vba.booth_number
    LEFT JOIN vendor AS v 
        ON v.vendor_id = vba.vendor_id
ORDER BY b.booth_number, vba.market_date

INTERACTIVE SQL EDITOR

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