SELECT: কলাম, এক্সপ্রেশন, অ্যালিয়াস আর DISTINCT

SELECT * FROM categories ORDER BY category_id;

+-------------+-------------+
| category_id | name        |
+-------------+-------------+
|           1 | Books       |
|           2 | Electronics |
|           3 | Courses     |
|           4 | Accessories |
|           5 | Furniture   |
+-------------+-------------+

SELECT * FROM products ORDER BY product_id LIMIT 3;

+------------+---------------------------+-------------+-------+-------+
| product_id | name                      | category_id | price | stock |
+------------+---------------------------+-------------+-------+-------+
|          1 | Python Crash Course       |           1 |  1200 |    40 |
|          2 | Hands-On Machine Learning |           1 |  2500 |    15 |
|          3 | Mechanical Keyboard       |           2 |  4500 |    12 |
+------------+---------------------------+-------------+-------+-------+

SELECT price, name
FROM products
ORDER BY product_id
LIMIT 5;

+-------+---------------------------+
| price | name                      |
+-------+---------------------------+
|  1200 | Python Crash Course       |
|  2500 | Hands-On Machine Learning |
|  4500 | Mechanical Keyboard       |
|   900 | Wireless Mouse            |
| 18500 | 27-inch Monitor           |
+-------+---------------------------+

SELECT product_id, name, price, stock, price * stock AS stock_value
FROM products
ORDER BY product_id;

+------------+-----------------------------+-------+-------+-------------+
| product_id | name                        | price | stock | stock_value |
+------------+-----------------------------+-------+-------+-------------+
|          1 | Python Crash Course         |  1200 |    40 |       48000 |
|          2 | Hands-On Machine Learning   |  2500 |    15 |       37500 |
|          3 | Mechanical Keyboard         |  4500 |    12 |       54000 |
|          4 | Wireless Mouse              |   900 |    60 |       54000 |
|          5 | 27-inch Monitor             | 18500 |     5 |       92500 |
|          6 | SQL Masterclass             |  3000 | NULL  | NULL        |
|          7 | AI Engineering Bootcamp     | 15000 | NULL  | NULL        |
|          8 | Laptop Stand                |  1500 |    25 |       37500 |
|          9 | USB-C Hub                   |  2200 |     0 |           0 |
|         10 | Noise-Cancelling Headphones |  7500 |     8 |       60000 |
|         11 | Gift Card                   |  1000 | NULL  | NULL        |
+------------+-----------------------------+-------+-------+-------------+

SELECT name,
       price,
       price / 1000            AS thousands_wrong,
       price / 1000.0          AS thousands,
       ROUND(price * 1.15, 2)  AS price_with_vat
FROM products
ORDER BY product_id
LIMIT 4;

+---------------------------+-------+-----------------+-----------+----------------+
| name                      | price | thousands_wrong | thousands | price_with_vat |
+---------------------------+-------+-----------------+-----------+----------------+
| Python Crash Course       |  1200 |               1 |       1.2 |         1380.0 |
| Hands-On Machine Learning |  2500 |               2 |       2.5 |         2875.0 |
| Mechanical Keyboard       |  4500 |               4 |       4.5 |         5175.0 |
| Wireless Mouse            |   900 |               0 |       0.9 |         1035.0 |
+---------------------------+-------+-----------------+-----------+----------------+

SELECT order_id, product_id, quantity, unit_price,
       quantity * unit_price AS line_total
FROM order_items
WHERE order_id IN (7, 9)
ORDER BY order_id, product_id;

+----------+------------+----------+------------+------------+
| order_id | product_id | quantity | unit_price | line_total |
+----------+------------+----------+------------+------------+
|        7 |          4 |        2 |        900 |       1800 |
|        7 |          9 |        1 |       2200 |       2200 |
|        9 |          3 |        1 |       4050 |       4050 |
|        9 |          4 |        1 |        900 |        900 |
+----------+------------+----------+------------+------------+

SELECT 2 + 3 * 4 AS no_brackets, (2 + 3) * 4 AS with_brackets, 17 % 5 AS remainder;

+-------------+---------------+-----------+
| no_brackets | with_brackets | remainder |
+-------------+---------------+-----------+
|          14 |            20 |         2 |
+-------------+---------------+-----------+

SELECT name AS product, price * stock AS "Stock value (taka)"
FROM products
WHERE stock > 0
ORDER BY product_id
LIMIT 3;

+---------------------------+--------------------+
| product                   | Stock value (taka) |
+---------------------------+--------------------+
| Python Crash Course       |              48000 |
| Hands-On Machine Learning |              37500 |
| Mechanical Keyboard       |              54000 |
+---------------------------+--------------------+

import pandas as pd
from sqlhelp import con

df = pd.read_sql("""
    SELECT product_id, price, stock, price * stock AS stock_value
    FROM products
    ORDER BY product_id
""", con)
print(list(df.columns))
print(df["stock_value"].sum())

['product_id', 'price', 'stock', 'stock_value']
383500.0

SELECT 'Order #' || order_id || ' (' || status || ')' AS label
FROM orders
WHERE customer_id = 2
ORDER BY order_id;

+----------------------+
| label                |
+----------------------+
| Order #2 (delivered) |
| Order #6 (delivered) |
| Order #12 (shipped)  |
+----------------------+

SELECT name || ' (' || city || ')'      AS piped,
       CONCAT(name, ' (', city, ')')    AS concat_fn
FROM customers
WHERE customer_id IN (1, 5)
ORDER BY customer_id;

+----------------------+----------------------+
| piped                | concat_fn            |
+----------------------+----------------------+
| Nadia Rahman (Dhaka) | Nadia Rahman (Dhaka) |
| NULL                 | Sadia Chowdhury ()   |
+----------------------+----------------------+

-- PostgreSQL
SELECT name || ' (' || city || ')'      AS piped,
       CONCAT(name, ' (', city, ')')    AS concat_fn
FROM customers
WHERE customer_id IN (1, 5)
ORDER BY customer_id;

+----------------------+----------------------+
| piped                | concat_fn            |
+----------------------+----------------------+
| Nadia Rahman (Dhaka) | Nadia Rahman (Dhaka) |
| NULL                 | Sadia Chowdhury ()   |
+----------------------+----------------------+

-- MySQL
SELECT name || ' (' || city || ')'      AS piped,
       CONCAT(name, ' (', city, ')')    AS concat_fn
FROM customers
WHERE customer_id IN (1, 5)
ORDER BY customer_id;

+-------+----------------------+
| piped | concat_fn            |
+-------+----------------------+
|     0 | Nadia Rahman (Dhaka) |
| NULL  | NULL                 |
+-------+----------------------+

SELECT DISTINCT city FROM customers ORDER BY city;

+------------+
| city       |
+------------+
| NULL       |
| Chattogram |
| Dhaka      |
| Khulna     |
| Rajshahi   |
| Sylhet     |
+------------+

SELECT DISTINCT customer_id, status
FROM orders
ORDER BY customer_id, status;

+-------------+-----------+
| customer_id | status    |
+-------------+-----------+
|           1 | delivered |
|           2 | delivered |
|           2 | shipped   |
|           3 | delivered |
|           4 | cancelled |
|           5 | delivered |
|           6 | delivered |
|           6 | pending   |
|           7 | delivered |
+-------------+-----------+

SELECT COUNT(*) AS orders, COUNT(DISTINCT customer_id) AS customers_who_ordered
FROM orders;

+--------+-----------------------+
| orders | customers_who_ordered |
+--------+-----------------------+
|     14 |                     7 |
+--------+-----------------------+

from sqlhelp import con

def product_cards():
    rows = con.execute("SELECT * FROM products WHERE product_id <= 2 ORDER BY product_id").fetchall()
    for product_id, name, category_id, price, stock in rows:
        print(f"{product_id}: {name} costs {price}")

product_cards()
con.execute("ALTER TABLE products ADD COLUMN description TEXT")   # নতুন একটা কলাম হাজির
con.commit()
try:
    product_cards()
except ValueError as error:
    print("ValueError:", error)

1: Python Crash Course costs 1200
2: Hands-On Machine Learning costs 2500
ValueError: too many values to unpack (expected 5)

def product_cards():
    rows = con.execute(
        "SELECT product_id, name, price FROM products WHERE product_id <= 2 ORDER BY product_id"
    ).fetchall()
    for product_id, name, price in rows:
        print(f"{product_id}: {name} costs {price}")

product_cards()

1: Python Crash Course costs 1200
2: Hands-On Machine Learning costs 2500

SELECT name, price, ROUND(price * 1.15, 2) AS price_with_vat
FROM products
ORDER BY product_id
LIMIT 4;

+---------------------------+-------+----------------+
| name                      | price | price_with_vat |
+---------------------------+-------+----------------+
| Python Crash Course       |  1200 |         1380.0 |
| Hands-On Machine Learning |  2500 |         2875.0 |
| Mechanical Keyboard       |  4500 |         5175.0 |
| Wireless Mouse            |   900 |         1035.0 |
+---------------------------+-------+----------------+

SELECT DISTINCT method FROM payments ORDER BY method;

SELECT DISTINCT method, paid_on
FROM payments
WHERE paid_on < '2026-03-01'
ORDER BY method, paid_on;

+--------+
| method |
+--------+
| bkash  |
| card   |
| cash   |
+--------+
+--------+------------+
| method | paid_on    |
+--------+------------+
| bkash  | 2026-01-12 |
| bkash  | 2026-02-03 |
| card   | 2026-01-20 |
| cash   | 2026-02-16 |
+--------+------------+

SELECT product_id,
       name || ' | price ' || price || ' taka | stock ' || COALESCE(stock, 'n/a') AS embedding_text
FROM products
WHERE product_id BETWEEN 5 AND 8
ORDER BY product_id;

+------------+--------------------------------------------------------+
| product_id | embedding_text                                         |
+------------+--------------------------------------------------------+
|          5 | 27-inch Monitor | price 18500 taka | stock 5           |
|          6 | SQL Masterclass | price 3000 taka | stock n/a          |
|          7 | AI Engineering Bootcamp | price 15000 taka | stock n/a |
|          8 | Laptop Stand | price 1500 taka | stock 25              |
+------------+--------------------------------------------------------+

-- PostgreSQL
SELECT product_id,
       name || ' | price ' || price || ' taka | stock ' || COALESCE(stock::text, 'n/a') AS embedding_text
FROM products
WHERE product_id BETWEEN 5 AND 8
ORDER BY product_id;

+------------+--------------------------------------------------------+
| product_id | embedding_text                                         |
+------------+--------------------------------------------------------+
|          5 | 27-inch Monitor | price 18500 taka | stock 5           |
|          6 | SQL Masterclass | price 3000 taka | stock n/a          |
|          7 | AI Engineering Bootcamp | price 15000 taka | stock n/a |
|          8 | Laptop Stand | price 1500 taka | stock 25              |
+------------+--------------------------------------------------------+