SELECT Basics — Filtering, Sorting, DISTINCT

SELECT — filter, sort, DISTINCT

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

1. The Most-Used Statement in Software

If you measure SQL usage by row count, no statement comes close to SELECT. Every product list, every search box, every report, every dashboard begins as a SELECT. By the end of this module you will know how to choose the rows you want (WHERE), order them (ORDER BY), eliminate duplicates (DISTINCT), paginate them (LIMIT/OFFSET), and handle the most subtle topic in SQL — NULL.

SQL-এ সবচেয়ে বেশি ব্যবহৃত statement হলো SELECT। প্রতিটি product তালিকা, search box, report, dashboard — সব SELECT দিয়েই শুরু হয়। এই module শেষে আপনি জানবেন — row বাছাই (WHERE), sort (ORDER BY), duplicate বাদ (DISTINCT), pagination (LIMIT/OFFSET) এবং SQL-এর সবচেয়ে সূক্ষ্ম বিষয় — NULL — কীভাবে কাজ করে।

Logical evaluation order Although you write SQL as SELECT … FROM … WHERE … ORDER BY …, the database evaluates it roughly in this order: FROM → WHERE → SELECT → DISTINCT → ORDER BY → LIMIT. Knowing this saves you from many "but why doesn't it work?" moments.

যদিও আমরা SELECT … FROM … WHERE … ORDER BY … এই ক্রমে লিখি, database প্রকৃতপক্ষে এটি চালায় FROM → WHERE → SELECT → DISTINCT → ORDER BY → LIMIT ক্রমে। এই ক্রম মনে থাকলে অনেক বিভ্রান্তি কমে যাবে।

2. `SELECT … FROM` — Picking Columns

The simplest SELECT picks a list of columns from a single table. The literal * means "all columns" — convenient in interactive sessions but discouraged in production code, because adding a new column later silently changes the result shape and can break consumers.

সবচেয়ে সাধারণ SELECT মানে একটি table থেকে কয়েকটি column বের করে আনা। * চিহ্ন মানে "সব column" — interactive কাজের জন্য সুবিধাজনক হলেও production কোডে এড়িয়ে যাওয়া ভালো; নতুন column যুক্ত হলে আগের code চুপচাপ ভেঙে যায়।

select_basic.sql

-- Pick just the columns you need (good practice)
SELECT name, dept, cgpa
FROM students;

2.1 — Aliases with `AS`

You can rename columns in the result using AS. This is essential for human-friendly reports and for distinguishing computed columns:

aliases.sql

SELECT
    title      AS item,
    price      AS price_bdt,
    price * 1.05 AS price_with_vat
FROM products;

3. `WHERE` — Filtering Rows

WHERE applies a predicate — a boolean expression — to each row. Only rows for which the predicate evaluates to TRUE survive into the result. This is the workhorse of every real-world query.

WHERE প্রতিটি row-এর উপর একটি boolean expression চালায় — যেগুলোতে এক্সপ্রেশন TRUE হয়, সেগুলো result-এ যায়। বাস্তব জীবনের প্রায় প্রতিটি query-তে WHERE থাকে।

3.1 — Comparison operators

Operator	Meaning	Example
`=`	Equal to	`dept = 'CSE'`
`<>` or `!=`	Not equal	`status <> 'failed'`
`<`, `<=`	Less than (or equal)	`cgpa < 3.0`
`>`, `>=`	Greater than (or equal)	`price >= 1000`
`BETWEEN`	Range (inclusive)	`price BETWEEN 100 AND 500`
`IN`	Membership	`dept IN ('CSE','EEE')`
`LIKE`	Pattern	`name LIKE 'A%'`
`IS NULL`	Tests for NULL	`cgpa IS NULL`

where_demo.sql

-- Orders worth more than 500 BDT, that have already shipped.
SELECT customer, amount, status
FROM orders
WHERE amount > 500
  AND status = 'shipped';

4. `ORDER BY` — Sorting the Result

Without ORDER BY, SQL gives no guarantee about the order of rows. If you need them sorted — and almost every UI does — say so explicitly. ASC means ascending (the default), DESC means descending. You can sort on multiple columns; the second column breaks ties of the first, and so on.

ORDER BY না দিলে SQL row-এর কোনো ক্রম গ্যারান্টি দেয় না। UI-এর জন্য সাধারণত sorted ফলাফল দরকার, তাই স্পষ্টভাবে ORDER BY লিখতে হয়। ASC মানে ছোট থেকে বড় (default), DESC মানে বড় থেকে ছোট। একাধিক column দিয়ে sort করা যায় — প্রথম column-এর tie দ্বিতীয় column ভাঙে।

order_demo.sql

-- Sort by department first, then by cgpa descending
SELECT name, dept, cgpa
FROM students
ORDER BY dept ASC, cgpa DESC;

Tip — sort by alias or computed expression ORDER BY can refer to a column alias defined in SELECT, or to any expression. For example: ORDER BY price * 1.05 DESC sorts by price-with-VAT.

ORDER BY alias বা যে কোনো expression ধরে sort করতে পারে — যেমন ORDER BY price * 1.05 DESC।

5. `DISTINCT` — Eliminating Duplicates

SELECT DISTINCT removes duplicate rows from the result. Duplicate is defined across all the columns you select — so SELECT DISTINCT dept returns each department once, but SELECT DISTINCT name, dept only collapses rows where both name and dept match.

SELECT DISTINCT result থেকে duplicate row বাদ দেয়। Duplicate বলতে বোঝায় — আপনি যে যে column SELECT করেছেন সেগুলো একইরকম। তাই SELECT DISTINCT dept প্রতিটি department একবার দেখায়, কিন্তু SELECT DISTINCT name, dept তখনই দুটিকে এক করে যখন name ও dept দুটোই মিলে যায়।

distinct_demo.sql

SELECT DISTINCT city
FROM customers
ORDER BY city;

6. `LIMIT` and `OFFSET` — Pagination

Most pages of products, posts, and tweets show ~20 rows at a time — never the entire database. LIMIT n caps the number of rows returned; OFFSET k skips the first k rows. Together they implement classic page-based pagination.

বেশিরভাগ Product, post বা tweet-এর তালিকা একসাথে ২০টির মতো দেখায়, পুরো database একবারে নয়। LIMIT n মানে সর্বোচ্চ nটি row, OFFSET k মানে প্রথম kটি row বাদ দিয়ে শুরু — দুটি মিলে pagination।

Page (size 5)	Query
Page 1 (rows 1–5)	`LIMIT 5 OFFSET 0`
Page 2 (rows 6–10)	`LIMIT 5 OFFSET 5`
Page 3 (rows 11–15)	`LIMIT 5 OFFSET 10`
Page n	`LIMIT 5 OFFSET (n-1)*5`

page2.sql

-- Page 2 with 5 rows per page
SELECT id, title
FROM posts
ORDER BY id
LIMIT 5 OFFSET 5;

Always pair LIMIT with ORDER BY Without ORDER BY the database is free to return rows in any order — page 2 might overlap page 1 between requests. Always combine LIMIT with a deterministic ORDER BY.

LIMIT-এর সাথে সবসময় ORDER BY দিন। নইলে দুই request-এ একই row বারবার আসতে পারে।

7. `NULL` — Three-Valued Logic

NULL is SQL's way of saying "the value is unknown / not yet supplied." It is not zero, not the empty string, not FALSE. Once you accept that, the rest is mechanical: any comparison with NULL is itself NULL (which is treated as "unknown"). So price = NULL is never true — even when the price actually is NULL. To test for null you must use IS NULL or IS NOT NULL.

NULL মানে "value-টি অজানা"। এটি শূন্য নয়, খালি string নয়, FALSE নয়। তাই NULL-এর সাথে যেকোনো তুলনার ফল নিজেই NULL (অজানা)। অর্থাৎ price = NULL কখনোই TRUE নয় — যদি price আসলে NULL-ও হয়। NULL পরীক্ষা করতে হলে IS NULL বা IS NOT NULL ব্যবহার করতে হয়।

Figure 13.1 — SQL boolean logic with NULL.

null_test.sql

-- This returns 0 rows — NULL = NULL is NULL, not TRUE!
SELECT name FROM students WHERE cgpa = NULL;

-- The correct way:
SELECT name FROM students WHERE cgpa IS NULL;

✅ Right way (সঠিক)

cgpa IS NULL
cgpa IS NOT NULL
COALESCE(cgpa, 0) > 3.0

⚠️ Wrong way (ভুল)

cgpa = NULL
cgpa <> NULL
cgpa = '' — empty string ≠ NULL

8. Putting It All Together

A real-world query usually combines all of the above. Here is the kind of statement you will write inside a Daraz product-listing page handler:

listing.sql

-- "Show me the 3 cheapest in-stock electronics."
SELECT DISTINCT title, price, stock
FROM products
WHERE category = 'electronics'
  AND stock > 0
ORDER BY price ASC
LIMIT 3;

The recipe FROM what table → WHERE filter rows → SELECT the columns → DISTINCT dedupe → ORDER BY sort → LIMIT a page.

ক্রম মনে রাখুন — FROM → WHERE → SELECT → DISTINCT → ORDER BY → LIMIT।

9. Practice Problems

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

List every student's name and department.

প্রতিটি student-এর নাম ও department দেখান।
✨ Show Answer
ans1.sql
```
SELECT name, dept FROM students;
```
Find all CSE students.

CSE department-এর সব student দেখান।
✨ Show Answer
ans2.sql
```
SELECT * FROM students WHERE dept = 'CSE';
```
Students with CGPA at least 3.5, sorted highest first.

যাদের CGPA ৩.৫ বা বেশি — sorted, সবচেয়ে বেশি আগে।
✨ Show Answer
ans3.sql
```
SELECT name, cgpa
FROM students
WHERE cgpa >= 3.5
ORDER BY cgpa DESC;
```
List all distinct cities customers come from.

Customer-দের সব unique city দেখান।
✨ Show Answer
ans4.sql
```
SELECT DISTINCT city FROM customers ORDER BY city;
```
Page 3 of orders, 4 per page, sorted by id.

Order-এর তৃতীয় page (প্রতি page-এ ৪টি)।
✨ Show Answer
ans5.sql
```
SELECT * FROM orders
ORDER BY id
LIMIT 4 OFFSET 8;
```
Find students with no recorded CGPA.

যাদের CGPA এখনো নেই — তাদের দেখান।
✨ Show Answer
ans6.sql
```
SELECT name FROM students WHERE cgpa IS NULL;
```
Cheapest 5 grocery items, ascending price.

সবচেয়ে সস্তা ৫টি grocery item।
✨ Show Answer
ans7.sql
```
SELECT title, price
FROM products
WHERE category = 'grocery'
ORDER BY price ASC
LIMIT 5;
```
Use BETWEEN to find students with CGPA between 3.0 and 3.5 inclusive.

BETWEEN দিয়ে CGPA ৩.০–৩.৫ এর মধ্যে student বের করুন।
✨ Show Answer
ans8.sql
```
SELECT name, cgpa
FROM students
WHERE cgpa BETWEEN 3.0 AND 3.5;
```
Use IN to filter by 3 departments at once.

IN দিয়ে তিনটি department একসাথে filter করুন।
✨ Show Answer
ans9.sql
```
SELECT name, dept
FROM students
WHERE dept IN ('CSE', 'EEE', 'ME');
```
Why does cgpa = NULL return zero rows? Two-sentence explanation.

cgpa = NULL কেন কোনো row দেয় না? দুই বাক্যে।

✨ Show Answer

Answer: NULL means "unknown," and any comparison with an unknown value is itself unknown — never TRUE. Since WHERE only keeps rows where the condition is TRUE, rows with NULL silently disappear.

NULL মানে "অজানা" — অজানার সাথে তুলনা করলে ফলও অজানা থাকে, কখনো TRUE হয় না। তাই সেই row WHERE-এর পরীক্ষায় বাদ পড়ে যায়।
List the top 3 most expensive products.

সবচেয়ে দামি ৩টি product দেখান।
✨ Show Answer
ans11.sql
```
SELECT title, price
FROM products
ORDER BY price DESC
LIMIT 3;
```
Sort orders by status (asc) and within each status by amount (desc).

Order-গুলো status (asc) এবং প্রতিটি status-এর মধ্যে amount (desc) দিয়ে sort করুন।
✨ Show Answer
ans12.sql
```
SELECT id, status, amount
FROM orders
ORDER BY status ASC, amount DESC;
```
Use DISTINCT on (dept, year) to list unique pairs.

(dept, year) এর unique জোড়া বের করুন।
✨ Show Answer
ans13.sql
```
SELECT DISTINCT dept, year
FROM students
ORDER BY dept, year;
```
Why should every LIMIT have an ORDER BY? Two sentences.

LIMIT-এর সাথে ORDER BY কেন বাধ্যতামূলক — দুই বাক্যে।

✨ Show Answer

Answer: Without ORDER BY, SQL is free to return rows in any order — which can change between executions. Pagination then becomes unstable: page 2 may overlap or skip rows that page 1 already returned.

ORDER BY ছাড়া SQL যেকোনো ক্রমে row ফেরত দিতে পারে, এবং সেই ক্রম বার বার বদলায়। তখন pagination অস্থির হয়ে যায় — page 2 এ আগেও দেখা row আবার আসতে পারে।
List items priced strictly between 100 and 1000 BDT (exclusive on both sides).

দাম ১০০ থেকে ১০০০ টাকার মাঝে (exclusive) — সেই product-গুলো বের করুন।
✨ Show Answer
ans15.sql
```
SELECT title, price
FROM products
WHERE price > 100 AND price < 1000;
```
Show every order alongside amount * 1.05 (5% VAT) as total.

প্রতিটি order-এর সাথে amount * 1.05 (৫% VAT) দিন।
✨ Show Answer
ans16.sql
```
SELECT id, amount, amount * 1.05 AS total
FROM orders
ORDER BY total DESC;
```

Summary — Module 13

A SELECT statement names columns (SELECT), the source (FROM), the filter (WHERE), the sort (ORDER BY), and the page (LIMIT/OFFSET); DISTINCT drops duplicates after selection. The database evaluates these clauses in a fixed logical order, and NULL follows three-valued logic — always test it with IS NULL, never = NULL. Master these six pieces and you can express most day-to-day reads in a single statement.

SELECT-এর ছয়টি অংশ মনে রাখুন — column বাছাই, table (FROM), filter (WHERE), sort (ORDER BY), pagination (LIMIT/OFFSET) এবং DISTINCT। NULL সবসময় IS NULL দিয়ে পরীক্ষা করুন। এই ছয়টি দিয়ে দৈনন্দিন প্রায় সব read query লেখা যায়।

Next Module → Operators & Expressions — যেকোনো boolean condition তৈরির পদ্ধতি।

Previous: Module 12 Next: Operators & Expressions

1. The Most-Used Statement in Software

2. SELECT … FROM — Picking Columns

2.1 — Aliases with AS

3. WHERE — Filtering Rows

3.1 — Comparison operators

4. ORDER BY — Sorting the Result

5. DISTINCT — Eliminating Duplicates

6. LIMIT and OFFSET — Pagination

7. NULL — Three-Valued Logic

✅ Right way (সঠিক)

⚠️ Wrong way (ভুল)

8. Putting It All Together

9. Practice Problems

Summary — Module 13

2. `SELECT … FROM` — Picking Columns

2.1 — Aliases with `AS`

3. `WHERE` — Filtering Rows

4. `ORDER BY` — Sorting the Result

5. `DISTINCT` — Eliminating Duplicates

6. `LIMIT` and `OFFSET` — Pagination

7. `NULL` — Three-Valued Logic