Chapter 6: Querying Data with SELECT
The SELECT statement is what you will use 80–90% of the time when working with databases. It’s how you ask questions to your data and get answers back.
Today we’ll master:
- Selecting only the columns we want
- Filtering rows with WHERE (the real power!)
- Sorting results with ORDER BY
- Limiting how many rows we see with LIMIT & OFFSET
- Giving nice names to columns with Aliases (AS)
First – let’s make sure we have good sample data Run this in your my_coaching database to reset and fill the students table with nice, varied data:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
TRUNCATE TABLE students; INSERT INTO students (full_name, phone, email, date_of_birth, gender, join_date, is_active) VALUES ('Priya Sharma', '9876543210', 'priya.sharma@gmail.com', '2008-05-12', 'F', '2025-01-05', TRUE), ('Rahul Patel', '9123456789', 'rahul.p@gmail.com', '2007-11-20', 'M', '2025-01-10', TRUE), ('Sneha Joshi', '9988776655', 'sneha.joshi@yahoo.com', '2009-03-03', 'F', '2025-02-15', TRUE), ('Aarav Khan', '9765432109', 'aarav.khan@outlook.com', '2006-08-15', 'M', '2025-03-01', TRUE), ('Kavya Iyer', '9898989898', 'kavya.iyer@gmail.com', '2010-04-22', 'F', '2025-03-10', TRUE), ('Rohan Mehta', '9012345678', 'rohan.mehta@live.com', '2005-12-01', 'M', '2024-12-20', FALSE), ('Ananya Gupta', '9223344556', 'ananya.gupta@gmail.com', '2009-07-18', 'F', '2025-02-28', TRUE), ('Vikram Singh', NULL, 'vikram.singh@proton.me', '2007-09-09', 'M', '2025-01-25', TRUE); |
Now run:
|
0 1 2 3 4 5 6 |
SELECT * FROM students; |
You should see 8 students with different join dates, genders, and one with NULL phone and one inactive.
1. Selecting Specific Columns (Instead of *)
|
0 1 2 3 4 5 6 7 |
SELECT full_name, phone, join_date FROM students; |
Result (only these 3 columns):
|
0 1 2 3 4 5 6 7 8 9 10 11 |
+---------------+------------+------------+ | full_name | phone | join_date | +---------------+------------+------------+ | Priya Sharma | 9876543210 | 2025-01-05 | | Rahul Patel | 9123456789 | 2025-01-10 | ... |
Why do this?
- Faster (less data transferred)
- Cleaner output
- You only show what the user needs
2. WHERE Clause – Filtering Rows (This is the magic!)
Syntax:
|
0 1 2 3 4 5 6 7 8 |
SELECT columns FROM table_name WHERE condition; |
Common Operators:
| Operator | Meaning | Example |
|---|---|---|
| = | Equal | gender = ‘F’ |
| > / < | Greater / Less than | join_date > ‘2025-02-01’ |
| >= / <= | Greater or equal / Less or equal | date_of_birth <= ‘2008-01-01’ |
| != / <> | Not equal | gender != ‘M’ |
| LIKE | Pattern matching | full_name LIKE ‘P%’ (starts with P) |
| IN | Match any in list | gender IN (‘F’, ‘O’) |
| BETWEEN | Range (inclusive) | join_date BETWEEN ‘2025-01-01’ AND ‘2025-01-31’ |
| IS NULL | Value is empty (NULL) | phone IS NULL |
| IS NOT NULL | Value exists | email IS NOT NULL |
Examples – Try these one by one!
A. Only female students
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name, gender FROM students WHERE gender = 'F'; |
B. Students who joined in January 2025
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name, join_date FROM students WHERE join_date BETWEEN '2025-01-01' AND '2025-01-31'; |
C. Students whose name starts with ‘A’ or ‘R’
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name FROM students WHERE full_name LIKE 'A%' OR full_name LIKE 'R%'; |
D. Students with no phone number
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name, phone FROM students WHERE phone IS NULL; |
E. Active students born after 2008
|
0 1 2 3 4 5 6 7 8 9 |
SELECT full_name, date_of_birth, is_active FROM students WHERE is_active = TRUE AND date_of_birth > '2008-01-01'; |
F. Students from specific emails
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name, email FROM students WHERE email IN ('priya.sharma@gmail.com', 'sneha.joshi@yahoo.com'); |
3. ORDER BY – Sorting Results
Syntax:
|
0 1 2 3 4 5 6 7 8 9 |
SELECT columns FROM table_name WHERE condition ORDER BY column_name [ASC|DESC]; |
- ASC = Ascending (small → big, A → Z) → default
- DESC = Descending (big → small, Z → A)
Examples:
A. Sort by join date (oldest first)
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name, join_date FROM students ORDER BY join_date ASC; |
B. Sort by name alphabetically
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name FROM students ORDER BY full_name ASC; |
C. Newest students first (latest join date)
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name, join_date FROM students ORDER BY join_date DESC; |
D. Multiple columns – First by gender, then by name
|
0 1 2 3 4 5 6 7 8 |
SELECT full_name, gender, join_date FROM students ORDER BY gender ASC, full_name ASC; |
4. LIMIT and OFFSET – Show Only Some Rows
Syntax:
|
0 1 2 3 4 5 6 7 8 9 |
SELECT columns FROM table_name ORDER BY ... LIMIT how_many OFFSET skip_how_many; |
- LIMIT 5 → Show only first 5 rows
- OFFSET 10 → Skip first 10 rows
Very useful for pagination (like showing 10 students per page)
Examples:
A. Show only first 3 students (alphabetical order)
|
0 1 2 3 4 5 6 7 8 9 |
SELECT full_name FROM students ORDER BY full_name ASC LIMIT 3; |
B. Show students 4 to 6 (page 2 if 3 per page)
|
0 1 2 3 4 5 6 7 8 9 |
SELECT full_name FROM students ORDER BY full_name ASC LIMIT 3 OFFSET 3; |
5. Aliases (AS) – Giving Friendly Names to Columns
Syntax:
|
0 1 2 3 4 5 6 |
SELECT column_name AS new_name, ... |
Example – Make output beautiful
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT full_name AS 'Student Full Name', phone AS 'Contact Number', join_date AS 'Date of Joining', gender AS 'Gender' FROM students WHERE gender = 'F' ORDER BY join_date DESC; |
You can also alias expressions:
|
0 1 2 3 4 5 6 7 8 9 |
SELECT full_name, YEAR(CURDATE()) - YEAR(date_of_birth) AS 'Age (approx)' FROM students; |
That’s it for Chapter 6! 🎉 You now know how to ask almost any question from your data!
Homework for today (do it right now – very important!) Run these queries and paste the final output here:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- 1. All active female students, sorted by join date (newest first) SELECT full_name, join_date, gender FROM students WHERE is_active = TRUE AND gender = 'F' ORDER BY join_date DESC; -- 2. Students born in 2009 or later, only name and birth date SELECT full_name, date_of_birth FROM students WHERE date_of_birth >= '2009-01-01' ORDER BY date_of_birth ASC LIMIT 4; -- 3. Students whose name contains 'a' (anywhere), sorted alphabetically SELECT full_name FROM students WHERE full_name LIKE '%a%' ORDER BY full_name ASC; |
