Chapter 8: Retrieving Data – SELECT Statement
Almost everything you do in SQL revolves around retrieving data — asking the database questions and getting exactly the answers you need. And the star of this chapter is the SELECT statement — the most powerful and most frequently used command in SQL.
Today we’re going to master these key parts of SELECT:
- Basic SELECT
- SELECT DISTINCT (remove duplicates)
- WHERE clause (filtering rows)
- ORDER BY (sorting results)
- LIMIT / OFFSET / TOP / FETCH (getting only a few rows)
We’ll use our familiar bookshop database with the authors and books tables. (If you want to follow along, make sure you have some data inserted from Chapter 7 — or just imagine the sample data I’ll show.)
Sample Data (for reference – imagine these rows exist)
authors table:
| author_id | name | country |
|---|---|---|
| 1 | Ruskin Bond | India |
| 2 | Jhumpa Lahiri | USA |
| 3 | Chetan Bhagat | India |
| 4 | Amish Tripathi | India |
| 5 | Sudha Murty | India |
| 6 | R.K. Narayan | India |
| 7 | Khushwant Singh | India |
books table:
| book_id | title | isbn | author_id | price | stock | created_at |
|---|---|---|---|---|---|---|
| 1 | The Room on the Roof | 9780143333401 | 1 | 250.00 | 45 | 2026-01-01 10:00:00 |
| 2 | 2 States | 9788129115300 | 3 | 299.00 | 120 | 2026-01-02 11:00:00 |
| 3 | The White Tiger | 9781416562603 | 2 | 399.00 | 35 | 2026-01-03 12:00:00 |
| 4 | Wise and Otherwise | 9780143062226 | 5 | 199.00 | 80 | 2026-01-04 13:00:00 |
| 5 | The Immortals of Meluha | 9789380658742 | 4 | 349.00 | 65 | 2026-01-05 14:00:00 |
| 6 | The Blue Umbrella | 9780143333784 | 1 | 180.00 | 50 | 2026-01-06 15:00:00 |
| 7 | Malgudi Days | 9780140183917 | 6 | 220.00 | 90 | 2026-01-07 16:00:00 |
| 8 | Train to Pakistan | 9780140116144 | 7 | 280.00 | 60 | 2026-01-08 17:00:00 |
Let’s start asking questions!
1. Basic SELECT – Getting Data from a Table
Syntax (most basic):
|
0 1 2 3 4 5 6 7 |
SELECT column1, column2, ... FROM table_name; |
Example 1 – Get all columns:
|
0 1 2 3 4 5 6 |
SELECT * FROM books; |
→ Returns all rows and all columns (the * means “everything”).
Example 2 – Select specific columns:
|
0 1 2 3 4 5 6 7 |
SELECT title, price, stock FROM books; |
Example 3 – Give friendly names (aliases):
|
0 1 2 3 4 5 6 7 8 9 10 |
SELECT title AS book_title, price AS book_price_in_rupees, stock AS books_in_stock FROM books; |
Pro Tip: Always use * only when you really need everything — it’s slower and can return unnecessary data. List the columns you actually need — better performance and clearer code.
2. SELECT DISTINCT – Remove Duplicates
Shows only unique values — great for finding distinct authors, countries, etc.
Syntax:
|
0 1 2 3 4 5 6 7 |
SELECT DISTINCT column1, column2, ... FROM table_name; |
Example – Find all unique countries:
|
0 1 2 3 4 5 6 7 |
SELECT DISTINCT country FROM authors; |
→ Returns only: India USA
Example – Unique book prices:
|
0 1 2 3 4 5 6 7 |
SELECT DISTINCT price FROM books; |
Important: DISTINCT applies to the entire row when multiple columns are selected.
|
0 1 2 3 4 5 6 7 8 |
SELECT DISTINCT author_id, country FROM authors; -- Works fine |
3. WHERE Clause – Filtering Rows
This is where SELECT becomes truly powerful — you tell the database which rows you want.
Syntax:
|
0 1 2 3 4 5 6 7 8 |
SELECT column1, column2, ... FROM table_name WHERE condition; |
Common operators:
- = , != / <> , > , < , >= , <=
- AND, OR, NOT
- LIKE (pattern matching: % = any characters, _ = single character)
- IN (list of values)
- BETWEEN (range)
- IS NULL / IS NOT NULL
Example 1 – Books cheaper than ₹300:
|
0 1 2 3 4 5 6 7 8 |
SELECT title, price FROM books WHERE price < 300.00; |
Example 2 – Books by Indian authors (author_id 1,3,4,5,6,7):
|
0 1 2 3 4 5 6 7 8 |
SELECT title, author_id FROM books WHERE author_id IN (1,3,4,5,6,7); |
Example 3 – Books with “The” in the title (case-insensitive in most DBs):
|
0 1 2 3 4 5 6 7 8 |
SELECT title FROM books WHERE title LIKE '%The%'; |
Example 4 – Books in stock > 50 AND price > 250:
|
0 1 2 3 4 5 6 7 8 9 |
SELECT title, price, stock FROM books WHERE stock > 50 AND price > 250.00; |
Example 5 – NULL-safe check (if any column allows NULL):
|
0 1 2 3 4 5 6 |
SELECT * FROM books WHERE created_at IS NOT NULL; |
4. ORDER BY – Sorting Results
By default, SELECT returns rows in no particular order. ORDER BY lets you sort them.
Syntax:
|
0 1 2 3 4 5 6 7 8 9 |
SELECT ... FROM ... WHERE ... ORDER BY column_name [ASC|DESC]; |
Example 1 – Sort books by price (cheapest first):
|
0 1 2 3 4 5 6 7 8 |
SELECT title, price FROM books ORDER BY price ASC; |
Example 2 – Most expensive books first:
|
0 1 2 3 4 5 6 7 8 |
SELECT title, price FROM books ORDER BY price DESC; |
Example 3 – Sort by multiple columns:
|
0 1 2 3 4 5 6 7 8 9 |
SELECT title, author_id, price FROM books ORDER BY author_id ASC, price DESC; -- First by author, then within each author by highest price |
Pro Tip: ASC = ascending (default) DESC = descending
5. LIMIT / OFFSET / TOP / FETCH – Getting Only a Few Rows
These limit how many rows you get back — perfect for pagination, top 10 lists, etc.
MySQL / MariaDB / PostgreSQL / SQLite:
|
0 1 2 3 4 5 6 |
LIMIT number_of_rows [OFFSET skip_rows]; |
Example – Top 3 cheapest books:
|
0 1 2 3 4 5 6 7 8 9 |
SELECT title, price FROM books ORDER BY price ASC LIMIT 3; |
Example – Pagination: page 2 (rows 11–20):
|
0 1 2 3 4 5 6 7 8 9 |
SELECT title, price FROM books ORDER BY title LIMIT 10 OFFSET 10; -- Skip first 10, show next 10 |
SQL Server (uses TOP):
|
0 1 2 3 4 5 6 7 8 |
SELECT TOP 5 title, price FROM books ORDER BY price DESC; |
Modern SQL (FETCH – works in PostgreSQL, SQL Server, Oracle):
|
0 1 2 3 4 5 6 7 8 9 |
SELECT title, price FROM books ORDER BY price DESC FETCH FIRST 5 ROWS ONLY; |
With OFFSET:
|
0 1 2 3 4 5 6 |
FETCH NEXT 10 ROWS ONLY OFFSET 20; -- Skip 20, get next 10 |
Full Powerful Example – Putting It All Together
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- Find top 5 most expensive books by Indian authors, with stock > 50 SELECT b.title AS book_title, b.price AS price_in_rupees, a.name AS author_name, b.stock AS in_stock FROM books b INNER JOIN authors a ON b.author_id = a.author_id -- (we'll learn JOINs next chapter) WHERE a.country = 'India' AND b.stock > 50 ORDER BY b.price DESC LIMIT 5; |
