সেটআপ: আপনার অনুশীলনের ডেটাবেস

import sqlite3

print("SQLite version:", sqlite3.sqlite_version)

SQLite version: 3.45.1

mkdir sql-for-ai
cd sql-for-ai

-- BitByte Shop: the practice database for the SQL & Data Handling tutorials.
-- Runs unchanged on SQLite, MySQL 8 and PostgreSQL. Foreign keys are written as table
-- constraints because MySQL silently ignores a REFERENCES written on the column itself.

DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(100) NOT NULL UNIQUE,
    city        VARCHAR(50),
    joined_on   DATE NOT NULL
);

CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY,
    name        VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE products (
    product_id  INTEGER PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    category_id INTEGER,
    price       INTEGER NOT NULL CHECK (price > 0),
    stock       INTEGER CHECK (stock >= 0),
    FOREIGN KEY (category_id) REFERENCES categories (category_id)
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date  DATE NOT NULL,
    status      VARCHAR(20) NOT NULL
                CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')),
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

CREATE TABLE order_items (
    order_id    INTEGER NOT NULL,
    product_id  INTEGER NOT NULL,
    quantity    INTEGER NOT NULL CHECK (quantity > 0),
    unit_price  INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders (order_id),
    FOREIGN KEY (product_id) REFERENCES products (product_id)
);

CREATE TABLE payments (
    payment_id  INTEGER PRIMARY KEY,
    order_id    INTEGER NOT NULL,
    amount      INTEGER NOT NULL CHECK (amount > 0),
    method      VARCHAR(20) NOT NULL,
    paid_on     DATE NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders (order_id)
);

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    department  VARCHAR(50) NOT NULL,
    manager_id  INTEGER,
    salary      INTEGER NOT NULL,
    hired_on    DATE NOT NULL,
    FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);

INSERT INTO customers (customer_id, name, email, city, joined_on) VALUES
    (1, 'Nadia Rahman',    'nadia@example.com',   'Dhaka',      '2025-11-03'),
    (2, 'Tanvir Ahmed',    'tanvir@example.com',  'Chattogram', '2025-12-14'),
    (3, 'Farhana Akter',   'farhana@example.com', 'Dhaka',      '2026-01-09'),
    (4, 'Rafiq Islam',     'rafiq@example.com',   'Sylhet',     '2026-01-22'),
    (5, 'Sadia Chowdhury', 'sadia@example.com',   NULL,         '2026-02-05'),
    (6, 'Imran Hossain',   'imran@example.com',   'Khulna',     '2026-02-18'),
    (7, 'Mitu Das',        'mitu@example.com',    'Dhaka',      '2026-03-30'),
    (8, 'Karim Uddin',     'karim@example.com',   'Rajshahi',   '2026-04-11');

INSERT INTO categories (category_id, name) VALUES
    (1, 'Books'),
    (2, 'Electronics'),
    (3, 'Courses'),
    (4, 'Accessories'),
    (5, 'Furniture');

INSERT INTO products (product_id, name, category_id, price, stock) VALUES
    (1,  'Python Crash Course',         1,    1200, 40),
    (2,  'Hands-On Machine Learning',   1,    2500, 15),
    (3,  'Mechanical Keyboard',         2,    4500, 12),
    (4,  'Wireless Mouse',              2,     900, 60),
    (5,  '27-inch Monitor',             2,   18500, 5),
    (6,  'SQL Masterclass',             3,    3000, NULL),
    (7,  'AI Engineering Bootcamp',     3,   15000, NULL),
    (8,  'Laptop Stand',                4,    1500, 25),
    (9,  'USB-C Hub',                   4,    2200, 0),
    (10, 'Noise-Cancelling Headphones', 2,    7500, 8),
    (11, 'Gift Card',                   NULL, 1000, NULL);

INSERT INTO orders (order_id, customer_id, order_date, status) VALUES
    (1,  1, '2026-01-12', 'delivered'),
    (2,  2, '2026-01-20', 'delivered'),
    (3,  1, '2026-02-03', 'delivered'),
    (4,  3, '2026-02-14', 'delivered'),
    (5,  4, '2026-02-25', 'cancelled'),
    (6,  2, '2026-03-08', 'delivered'),
    (7,  5, '2026-03-15', 'delivered'),
    (8,  3, '2026-03-28', 'delivered'),
    (9,  6, '2026-04-02', 'delivered'),
    (10, 1, '2026-04-19', 'delivered'),
    (11, 7, '2026-05-06', 'delivered'),
    (12, 2, '2026-05-21', 'shipped'),
    (13, 6, '2026-06-02', 'pending'),
    (14, 3, '2026-06-09', 'delivered');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (1,  1, 1, 1200),
    (1,  4, 1, 900),
    (2,  3, 1, 4500),
    (3,  6, 1, 3000),
    (4,  2, 1, 2500),
    (4,  8, 1, 1500),
    (5,  5, 1, 18500),
    (6,  7, 1, 15000),
    (7,  4, 2, 900),
    (7,  9, 1, 2200),
    (8,  1, 2, 1200),
    (9,  3, 1, 4050),
    (9,  4, 1, 900),
    (10, 5, 1, 18500),
    (11, 6, 1, 3000),
    (11, 2, 1, 2500),
    (12, 8, 2, 1500),
    (13, 7, 1, 15000),
    (14, 9, 1, 2200),
    (14, 4, 1, 900);

INSERT INTO payments (payment_id, order_id, amount, method, paid_on) VALUES
    (1,  1,  2100,  'bkash', '2026-01-12'),
    (2,  2,  4500,  'card',  '2026-01-20'),
    (3,  3,  3000,  'bkash', '2026-02-03'),
    (4,  4,  4000,  'cash',  '2026-02-16'),
    (5,  6,  5000,  'bkash', '2026-03-08'),
    (6,  6,  10000, 'card',  '2026-03-10'),
    (7,  7,  4000,  'card',  '2026-03-15'),
    (8,  8,  2400,  'bkash', '2026-03-28'),
    (9,  9,  4950,  'card',  '2026-04-02'),
    (10, 10, 18500, 'card',  '2026-04-19'),
    (11, 11, 5500,  'bkash', '2026-05-06'),
    (12, 12, 3000,  'card',  '2026-05-21'),
    (13, 14, 3100,  'cash',  '2026-06-11');

INSERT INTO employees (employee_id, name, department, manager_id, salary, hired_on) VALUES
    (1, 'Ayesha Siddiqua', 'Management',  NULL, 250000, '2022-01-10'),
    (2, 'Hasan Mahmud',    'Engineering', 1,    180000, '2022-03-01'),
    (3, 'Nusrat Jahan',    'Engineering', 2,    120000, '2023-06-15'),
    (4, 'Zahid Hasan',     'Engineering', 2,    120000, '2024-02-01'),
    (5, 'Lamia Karim',     'Data',        1,    160000, '2022-08-20'),
    (6, 'Shuvo Roy',       'Data',        5,     95000, '2025-01-05'),
    (7, 'Priya Sen',       'Data',        5,    110000, '2024-09-12');

"""Helpers for the BitByte SQL tutorials: a connection to shop.db, show() and reset()."""
import sqlite3

con = sqlite3.connect("shop.db")
con.execute("PRAGMA foreign_keys = ON")      # SQLite checks foreign keys only when asked


def show(sql, params=()):
    """Run one SELECT and print its rows as a table."""
    cur = con.execute(sql, params)
    cols = [d[0] for d in cur.description]
    rows = cur.fetchall()
    text = [["NULL" if v is None else str(v) for v in row] for row in rows]
    widths = [max([len(c)] + [len(r[i]) for r in text]) for i, c in enumerate(cols)]

    def line():
        return "+" + "+".join("-" * (w + 2) for w in widths) + "+"

    def fmt(cells, values=None):
        out = []
        for i, (cell, w) in enumerate(zip(cells, widths)):
            number = values is not None and isinstance(values[i], (int, float))
            out.append(cell.rjust(w) if number else cell.ljust(w))
        return "| " + " | ".join(out) + " |"

    print(line(), fmt(cols), line(), sep="\n")
    for cells, values in zip(text, rows):
        print(fmt(cells, values))
    print(line())


def reset():
    """Rebuild shop.db from shop.sql: every table back to its starting rows."""
    with open("shop.sql", encoding="utf-8") as f:
        script = f.read()
    con.commit()
    con.execute("PRAGMA foreign_keys = OFF")  # so tables you made that point at the shop do not block it
    try:
        con.executescript(script)
    finally:
        con.execute("PRAGMA foreign_keys = ON")

import sqlite3

# 1. কানেক্ট করুন। shop.db না থাকলে SQLite একটা খালি ফাইল বানিয়ে নেয়।
con = sqlite3.connect("shop.db")

# 2. এই কানেকশনে ফরেন কি মানতে SQLite-কে বলুন।
con.execute("PRAGMA foreign_keys = ON")

# 3. shop.sql পড়ে এর প্রতিটা স্টেটমেন্ট চালান।
with open("shop.sql", encoding="utf-8") as f:
    con.executescript(f.read())
con.commit()

# 4. যাচাই: প্রতিটা টেবিলে কয়টা সারি ঢুকল?
tables = ["customers", "categories", "products", "orders",
          "order_items", "payments", "employees"]
for table in tables:
    count = con.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
    print(f"{table:12} {count:3} rows")
con.close()

customers      8 rows
categories     5 rows
products      11 rows
orders        14 rows
order_items   20 rows
payments      13 rows
employees      7 rows

from sqlhelp import con, show

show("SELECT product_id, name, price FROM products ORDER BY price DESC LIMIT 3")

+------------+-----------------------------+-------+
| product_id | name                        | price |
+------------+-----------------------------+-------+
|          5 | 27-inch Monitor             | 18500 |
|          7 | AI Engineering Bootcamp     | 15000 |
|         10 | Noise-Cancelling Headphones |  7500 |
+------------+-----------------------------+-------+

city = "Dhaka"
show("SELECT customer_id, name, city FROM customers WHERE city = ? ORDER BY customer_id", (city,))

+-------------+---------------+-------+
| customer_id | name          | city  |
+-------------+---------------+-------+
|           1 | Nadia Rahman  | Dhaka |
|           3 | Farhana Akter | Dhaka |
|           7 | Mitu Das      | Dhaka |
+-------------+---------------+-------+

import pandas as pd

rows = con.execute("SELECT name, price FROM products ORDER BY price DESC LIMIT 2").fetchall()
print(rows)

df = pd.read_sql("SELECT product_id, name, price, stock FROM products ORDER BY product_id LIMIT 4", con)
print(df)

[('27-inch Monitor', 18500), ('AI Engineering Bootcamp', 15000)]
   product_id                       name  price  stock
0           1        Python Crash Course   1200     40
1           2  Hands-On Machine Learning   2500     15
2           3        Mechanical Keyboard   4500     12
3           4             Wireless Mouse    900     60

from sqlhelp import con, show, reset

con.execute("DELETE FROM payments")
con.commit()
show("SELECT COUNT(*) AS payments FROM payments")

reset()
show("SELECT COUNT(*) AS payments FROM payments")

+----------+
| payments |
+----------+
|        0 |
+----------+
+----------+
| payments |
+----------+
|       13 |
+----------+

sqlite3 shop.db

sqlite> .read shop.sql
sqlite> .tables
sqlite> .headers on
sqlite> .mode box
sqlite> PRAGMA foreign_keys = ON;
sqlite> SELECT name, price FROM products ORDER BY price DESC LIMIT 3;
sqlite> .quit

# পোর্ট 3306-এ MySQL 8, shop নামের একটা খালি ডেটাবেসসহ
docker run --name shop-mysql -e MYSQL_ROOT_PASSWORD=course -e MYSQL_DATABASE=shop \
  -p 3306:3306 -d mysql:8.4

# পোর্ট 5432-এ pgvector এক্সটেনশনসহ PostgreSQL, ডেটাবেস shop
docker run --name shop-postgres -e POSTGRES_PASSWORD=course -e POSTGRES_DB=shop \
  -p 5432:5432 -d pgvector/pgvector:pg17

# চালু হতে কয়েক সেকেন্ড অপেক্ষা করুন, তারপর দুটোতেই একই shop.sql লোড করুন
docker exec -i shop-mysql mysql -uroot -pcourse shop < shop.sql
docker exec -i shop-postgres psql -U postgres -d shop < shop.sql

# একটা ইন্টারঅ্যাকটিভ প্রম্পট খুলুন (বের হতে exit বা \q লিখুন)
docker exec -it shop-mysql mysql -uroot -pcourse shop
docker exec -it shop-postgres psql -U postgres -d shop

-- MySQL
SELECT COUNT(*) AS orders, MAX(order_date) AS last_order FROM orders;

+--------+------------+
| orders | last_order |
+--------+------------+
|     14 | 2026-06-09 |
+--------+------------+

-- PostgreSQL
SELECT COUNT(*) AS orders, MAX(order_date) AS last_order FROM orders;

+--------+------------+
| orders | last_order |
+--------+------------+
|     14 | 2026-06-09 |
+--------+------------+

from sqlhelp import show

show("SELECT sqlite_version() AS version")
show("SELECT COUNT(*) AS employees FROM employees")

show("SELECT product_id, name, price FROM products WHERE category_id = ? ORDER BY product_id", (2,))

from sqlhelp import con, reset

def table_sizes():
    names = [row[0] for row in con.execute(
        "SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name")]
    for name in names:
        count = con.execute(f"SELECT COUNT(*) FROM {name}").fetchone()[0]
        print(f"{name:12} {count}")

con.execute("DELETE FROM order_items")
con.commit()
table_sizes()
reset()
print("--- after reset")
table_sizes()

categories   5
customers    8
employees    7
order_items  0
orders       14
payments     13
products     11
--- after reset
categories   5
customers    8
employees    7
order_items  20
orders       14
payments     13
products     11