Lab Tutorial 6: IN & BETWEEN

Sample Database:

Assume you have the same sample database as before:

1. books:
   • Columns: book_id, title, author, price, quantity.
2. customers:
   • Columns: customer_id, name, email, phone.
3. orders:
   • Columns: order_id, customer_id, order_date.
4. order_items:
   • Columns: order_item_id, order_id, book_id, quantity, unit_price.

Sample Queries for IN and BETWEEN:

Find all books with prices either $10, $15, or $20.

IN (10, 15, 20) checks if the price of each book is either $10, $15, or $20, and returns the rows that match.

SELECT * FROM books WHERE price IN (10, 15, 20);

Find all books with prices between $10 and $20 (inclusive).

BETWEEN 10 AND 20 checks if the price of each book falls within the range of $10 to $20, inclusive, and returns the rows that match.

SELECT * FROM books WHERE price BETWEEN 10 AND 20;

Find all orders placed in January 2022.

BETWEEN '2022-01-01' AND '2022-01-31' checks if the order_date of each order falls within the range of January 2022 and returns the rows that match.

SELECT * FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2022-01-31';

Find all customers who have placed orders in the last quarter (October to December) of 2023.

This query first selects the customer_id of customers who have placed orders in the last quarter of 2023 using a subquery with BETWEEN '2023-10-01' AND '2023-12-31'. Then, it selects all columns from the customers table for those customer_ids.

SELECT * FROM customers WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders WHERE order_date BETWEEN '2023-10-01' AND '2023-12-31');