Operators & Expressions — Arithmetic, Logical, Pattern Matching

অপারেটর — গণিত, যুক্তি, pattern matching

Read: ~30 min Easy 16 practice problems Live SQLite runner

1. Why Operators Matter

SELECT picks columns; WHERE chooses rows. But the moment you ask anything more sophisticated than "show me everything," you reach for an operator — a symbol or keyword that combines values into a new value. By the end of this module you will be able to build any boolean condition the business throws at you: "give me orders above 1,000 BDT placed by Dhaka customers whose phone starts with 017, except cancelled ones."

SELECT দিয়ে column বাছাই হয়, WHERE দিয়ে row। কিন্তু একটু জটিল কাজ মানেই operator — যা মান বা শর্তকে যুক্ত করে নতুন মান তৈরি করে। এই module শেষে আপনি যেকোনো business condition লিখতে পারবেন — "যেসব order ১০০০ টাকার বেশি, ঢাকার গ্রাহকদের, যাদের ফোন 017 দিয়ে শুরু, কিন্তু cancelled নয়।"

Categories we will cover: arithmetic (+ - * / %), comparison (= < > …), logical (AND OR NOT), range / set (BETWEEN, IN), pattern (LIKE, GLOB), conditional (CASE WHEN), and NULL-safe (COALESCE, NULLIF).

আজকের ৭টি গোষ্ঠী — গণিত, তুলনা, যুক্তি, range/set, pattern, conditional এবং NULL-safe।

2. Arithmetic Operators

SQL supports the standard five: + add, - subtract, * multiply, / divide, % modulus (remainder). They follow normal precedence — multiplication and division before addition and subtraction — and you can use parentheses to make order explicit.

পাঁচটি প্রধান গাণিতিক operator — +, -, *, /, %। গণিতের নিয়ম মেনে * ও / আগে চলে, + ও - পরে। বন্ধনী দিয়ে ক্রম স্পষ্ট করা যায়।

arithmetic.sql

SELECT
    item,
    qty,
    unit_price,
    qty * unit_price             AS subtotal,
    qty * unit_price * 0.05      AS vat,
    qty * unit_price * 1.05      AS total_with_vat
FROM orders;

Integer-divide trap In SQLite, 5 / 2 evaluates to 2 — integer division truncates. Use a decimal literal (5.0 / 2) or cast (CAST(5 AS REAL) / 2) to force real division.

SQLite-এ 5 / 2 = 2 — integer division। দশমিকসহ ফলাফল চাইলে 5.0 / 2 বা CAST(5 AS REAL) / 2 লিখুন।

divide_trap.sql

SELECT
    5 / 2                AS int_div,     -- 2
    5.0 / 2              AS real_div,    -- 2.5
    10 % 3               AS mod;         -- 1

3. Logical Operators — `AND`, `OR`, `NOT`

The three boolean operators combine conditions. Precedence is fixed: NOT first, then AND, then OR. Use parentheses whenever the intent is not obvious — readers (and your future self) will thank you.

তিনটি boolean operator — AND (এবং), OR (অথবা), NOT (নয়)। ক্রম — NOT > AND > OR। স্পষ্টতার জন্য বন্ধনী দিন।

logical.sql

-- Dhaka customers with balance over 1000, OR anyone in Khulna.
SELECT name, city, balance
FROM customers
WHERE (city = 'Dhaka' AND balance > 1000)
   OR city = 'Khulna';

3.1 — Short-circuit evaluation

SQL engines may, but are not required to, short-circuit boolean expressions. In SQLite, an expression like x <> 0 AND 100 / x > 5 usually does not divide by zero when x = 0, because the first half is FALSE. But you should not rely on this for correctness — use CASE WHEN or a guard inside NULLIF for safety.

SQL engine চাইলে boolean expression-এ short-circuit করতে পারে — যেমন x <> 0 AND 100 / x > 5 এ, x শূন্য হলে দ্বিতীয় অংশ চালানো নাও হতে পারে। তবে এটার উপর নির্ভর না করে CASE WHEN বা NULLIF দিয়ে সুরক্ষা দিন।

4. Range & Set — `BETWEEN`, `IN`, `IS NULL`

These three convenience operators replace longer combinations of comparisons. BETWEEN a AND b is shorthand for x >= a AND x <= b (inclusive on both sides). IN (…) is shorthand for a chain of ORs. IS NULL is the only correct way to test for NULL, as we saw in Module 13.

BETWEEN a AND b = x >= a AND x <= b। IN (...) = অনেকগুলো OR। IS NULL দিয়েই কেবল NULL পরীক্ষা হয়।

range_set.sql

-- Items priced 100..2000, in either grocery or fashion
SELECT title, price, category
FROM products
WHERE price BETWEEN 100 AND 2000
  AND category IN ('grocery', 'fashion');

NOT IN + NULL = silent zero rows If the right-hand list of NOT IN contains a NULL, the entire condition becomes NULL — and zero rows survive. Always make sure the list cannot contain NULL, or rewrite using NOT EXISTS.

NOT IN (...) এর তালিকায় NULL থাকলে পুরো শর্তটাই NULL হয়ে যায় — এবং কোনো row আসে না। তাই তালিকায় NULL আছে কিনা সাবধানে দেখুন।

5. Pattern Matching — `LIKE`, `GLOB`

LIKE is the standard SQL pattern operator. It uses two wildcards: % matches any number of characters (including zero), and _ matches exactly one character. LIKE is case-insensitive for ASCII letters in SQLite by default.

LIKE দিয়ে pattern মেলানো হয়। দুটি wildcard আছে — % মানে যেকোনো সংখ্যক character (০-ও হতে পারে), _ মানে ঠিক একটি character। SQLite-এ LIKE ASCII অক্ষরের জন্য case-insensitive।

Pattern	Matches	Doesn't match
`'A%'`	Arif, Anita, Akash	Bani
`'%han'`	Khan, Zaman, Ehsan…wait — only Khan ends in 'han'	Khanam
`'_im'`	Mim, Tim, Sim (3-letter)	Karim (5-letter)
`'017%'`	017... phone numbers	019..., 015...
`'%@gmail.com'`	Gmail addresses	name@yahoo.com

like_demo.sql

-- Customers on Grameenphone (017xxx) using Gmail
SELECT name, phone, email
FROM customers
WHERE phone LIKE '017%'
  AND email LIKE '%@gmail.com';

5.1 — `GLOB` (case-sensitive Unix-style pattern)

SQLite also offers GLOB, which is case-sensitive and uses Unix glob syntax: * and ? are the wildcards, plus [abc] for character classes. Use it when you need precision, e.g. distinguishing 'iPhone' from 'iphone'.

glob_demo.sql

-- Only titles starting with capital "iP"
SELECT title FROM products
WHERE title GLOB 'iP*';

Regex briefly Pure SQLite has no built-in regular expression engine — calling col REGEXP 'pattern' requires loading an extension. PostgreSQL has ~ and ~*; MySQL has REGEXP. For most lecture-level work, LIKE and GLOB are enough.

SQLite-এ regex built-in নেই — extension load করা লাগে। PostgreSQL-এ ~, MySQL-এ REGEXP আছে। আমাদের কোর্সের জন্য LIKE/GLOB যথেষ্ট।

6. `CASE WHEN` — Inline Conditional Expression

CASE is SQL's if/else. It returns a value chosen by a chain of conditions. There are two forms: searched (each branch has its own boolean) and simple (compare one expression to several values).

CASE WHEN হলো SQL-এর if/else। দুটি রূপ আছে — searched (প্রতিটি branch-এ আলাদা শর্ত) এবং simple (একটি expression-কে কয়েকটি value-র সাথে তুলনা)।

case_searched.sql

SELECT name, cgpa,
    CASE
        WHEN cgpa IS NULL      THEN 'Not yet'
        WHEN cgpa >= 3.75      THEN 'Distinction'
        WHEN cgpa >= 3.25      THEN 'First Class'
        WHEN cgpa >= 2.75      THEN 'Second Class'
        ELSE                       'Need help'
    END AS grade_label
FROM students;

case_simple.sql

SELECT id, status,
    CASE status
        WHEN 'placed'    THEN 'অর্ডার গ্রহণ'
        WHEN 'shipped'   THEN 'পথে আছে'
        WHEN 'cancelled' THEN 'বাতিল'
        WHEN 'returned'  THEN 'ফেরত'
        ELSE                  'অজানা'
    END AS status_bn
FROM orders;

7. NULL Helpers — `COALESCE` and `NULLIF`

Two small functions handle the most common NULL chores. COALESCE(a, b, c, …) returns the first non-NULL value in its list. NULLIF(a, b) returns NULL if a = b and a otherwise — handy for "treat empty string as NULL" or "avoid divide-by-zero."

COALESCE(a, b, c, ...) তালিকার প্রথম non-NULL value ফেরত দেয়। NULLIF(a, b): a = b হলে NULL, না হলে a। "ফাঁকা string-কে NULL ধরা" বা "শূন্য দিয়ে ভাগ এড়ানো" — এই কাজগুলোর জন্য কাজে আসে।

coalesce.sql

SELECT name,
    COALESCE(mobile, landline, email, 'No contact') AS best_contact
FROM customers;

nullif.sql

-- Avoid divide-by-zero: NULLIF(target, 0) becomes NULL and the whole division becomes NULL.
SELECT name, sales, target,
    sales * 100.0 / NULLIF(target, 0) AS pct_of_target
FROM perf;

8. Operator Precedence — A Mental Chart

Tier	Operators	Direction
1 (highest)	Unary `-`, `+`, `NOT`	Right → Left
2	`*`, `/`, `%`	Left → Right
3	Binary `+`, `-`, `\|\|` (string concat)	Left → Right
4	`=`, `<>`, `<`, `>`, `<=`, `>=`	Left → Right
5	`BETWEEN`, `IN`, `LIKE`, `GLOB`, `IS`	—
6	`AND`	Left → Right
7 (lowest)	`OR`	Left → Right

When in doubt, parenthesise Parentheses are free; reading mistakes are not. If a colleague has to count tiers to read your WHERE clause, add parens.

সন্দেহ হলে বন্ধনী দিন। বন্ধনী পড়তে সহজ, ভুল দামি।

9. Practice Problems

প্রথমে নিজে চেষ্টা করুন; পরে Show Answer চাপুন।

Compute qty * unit_price as subtotal for each order.

প্রতিটি order-এর qty * unit_price দেখান।
✨ Show Answer
ans1.sql
```
SELECT id, qty, unit_price, qty * unit_price AS subtotal
FROM orders;
```
Find customers in Dhaka or Chattogram with balance over 500.

ঢাকা বা চট্টগ্রামে balance ৫০০-এর বেশি customer।
✨ Show Answer
ans2.sql
```
SELECT * FROM customers
WHERE city IN ('Dhaka', 'Chattogram') AND balance > 500;
```
Use BETWEEN to find orders with amount 1000 to 5000 inclusive.

BETWEEN দিয়ে amount ১০০০–৫০০০ এর order বের করুন।
✨ Show Answer
ans3.sql
```
SELECT * FROM orders
WHERE amount BETWEEN 1000 AND 5000;
```
Find phone numbers starting with 017.

017 দিয়ে শুরু — সেরকম phone বের করুন।
✨ Show Answer
ans4.sql
```
SELECT * FROM c WHERE phone LIKE '017%';
```
Find products with exactly 3 letters in the title (use LIKE).

Title-এ ঠিক ৩ অক্ষর — সেইসব product।
✨ Show Answer
ans5.sql
```
SELECT * FROM p WHERE title LIKE '___';
```

Label cgpa: ≥3.5 'A', ≥3.0 'B', ≥2.5 'C', else 'D'.

CGPA-কে A/B/C/D হিসেবে label করুন।

✨ Show Answer

ans6.sql

SELECT name, cgpa,
    CASE
        WHEN cgpa >= 3.5 THEN 'A'
        WHEN cgpa >= 3.0 THEN 'B'
        WHEN cgpa >= 2.5 THEN 'C'
        ELSE 'D'
    END AS grade
FROM s;

Use COALESCE to display 'N/A' instead of NULL email.

NULL email-এর জায়গায় 'N/A' দেখান।
✨ Show Answer
ans7.sql
```
SELECT name, COALESCE(email, 'N/A') AS email FROM u;
```
Use NULLIF to convert empty-string emails to NULL.

খালি string email-কে NULL বানান।
✨ Show Answer
ans8.sql
```
SELECT name, NULLIF(email, '') AS email FROM u;
```
Find books whose title ends with 'Mystery'.

যেসব বইয়ের title 'Mystery' দিয়ে শেষ — তাদের বের করুন।
✨ Show Answer
ans9.sql
```
SELECT * FROM books WHERE title LIKE '%Mystery';
```
Compute the percentage discount given an old and new price; protect against zero with NULLIF.

পুরাতন ও নতুন price থেকে discount % বের করুন; শূন্য থেকে রক্ষায় NULLIF।
✨ Show Answer
ans10.sql
```
SELECT item, old_price, new_price,
    (old_price - new_price) * 100.0 / NULLIF(old_price, 0) AS discount_pct
FROM deal;
```
Find users whose name has 'mim' (case-sensitive — use GLOB).

Name-এ ছোট হাতের 'mim' — case-sensitive খুঁজুন।
✨ Show Answer
ans11.sql
```
SELECT * FROM u WHERE name GLOB '*mim*';
```
Mark each order as 'Big' (≥10000) or 'Small'.

প্রতিটি order-কে 'Big' বা 'Small' হিসেবে চিহ্নিত করুন।
✨ Show Answer
ans12.sql
```
SELECT id, amount,
    CASE WHEN amount >= 10000 THEN 'Big' ELSE 'Small' END AS size
FROM o;
```
Find orders that are NOT in status 'cancelled' or 'returned'.

যেসব order 'cancelled' বা 'returned' নয় — বের করুন।
✨ Show Answer
ans13.sql
```
SELECT * FROM o
WHERE status NOT IN ('cancelled', 'returned');
```
Find books whose title contains a literal underscore. Hint: use ESCAPE.

যেসব title-এ আসল underscore আছে — তাদের বের করুন; ESCAPE ব্যবহার করুন।
✨ Show Answer
ans14.sql
```
SELECT * FROM b
WHERE title LIKE '%\_%' ESCAPE '\';
```
Why does 5 / 2 return 2 in SQLite? Two sentences.

SQLite-এ 5 / 2 কেন ২? দুই বাক্যে।

✨ Show Answer

Answer: Both operands are integer literals, so SQL performs integer division which truncates toward zero. To get a real-number result, write at least one operand with a decimal point: 5.0 / 2 returns 2.5.

দুটোই integer, তাই integer division হয় এবং দশমিক অংশ বাদ যায়। দশমিকসহ ফল চাইলে 5.0 / 2 লিখলে ২.৫ পাওয়া যাবে।
Combine LIKE and BETWEEN: products with title starting with 'P' priced between 50 and 500.

'P' দিয়ে শুরু এবং দাম ৫০–৫০০ — সেরকম product বের করুন।
✨ Show Answer
ans16.sql
```
SELECT * FROM p
WHERE title LIKE 'P%'
  AND price BETWEEN 50 AND 500;
```

Summary — Module 14

SQL operators fall into seven groups: arithmetic, comparison, logical (AND/OR/NOT), range/set (BETWEEN, IN), pattern (LIKE, GLOB), conditional (CASE WHEN) and NULL helpers (COALESCE, NULLIF). Watch out for two traps: integer division (use a decimal literal or CAST), and NOT IN with a NULL in the list (the entire condition becomes NULL). When in doubt, parenthesise.

SQL operator-গুলো সাতটি দলে — গণিত, তুলনা, যুক্তি, range/set, pattern, conditional, NULL-safe। দুটি ফাঁদ — integer division (দশমিক ব্যবহার করুন) এবং NOT IN-এ NULL (পুরো শর্তটাই NULL হয়ে যায়)। সন্দেহ হলে বন্ধনী।

Next Module → Aggregations — COUNT, SUM, AVG, GROUP BY, HAVING। লক্ষ লক্ষ row সারসংক্ষেপ।

Previous: Module 13 Next: Aggregations

1. Why Operators Matter

2. Arithmetic Operators

3. Logical Operators — AND, OR, NOT

3.1 — Short-circuit evaluation

4. Range & Set — BETWEEN, IN, IS NULL

5. Pattern Matching — LIKE, GLOB

5.1 — GLOB (case-sensitive Unix-style pattern)

6. CASE WHEN — Inline Conditional Expression

7. NULL Helpers — COALESCE and NULLIF

8. Operator Precedence — A Mental Chart

9. Practice Problems

Summary — Module 14

3. Logical Operators — `AND`, `OR`, `NOT`

4. Range & Set — `BETWEEN`, `IN`, `IS NULL`

5. Pattern Matching — `LIKE`, `GLOB`

5.1 — `GLOB` (case-sensitive Unix-style pattern)

6. `CASE WHEN` — Inline Conditional Expression

7. NULL Helpers — `COALESCE` and `NULLIF`