Rahul asks the right questions

WHERE, ORDER BY, LIMIT, COUNT, GROUP BY — the complete SELECT toolkit

⏱ 13 min 6 commands 5 takeaways

🔍

In this chapter

Rahul

Backend developer, week 1

The story

Rahul's database had 5 users. His app went live. Three months later it had 50,000 users. His manager needed answers: how many users in Mumbai, who signed up last week, show users sorted by city.

Rahul needed to learn SELECT properly. SELECT is the most important SQL command. It is how you ask questions of your data.

THE BASIC SELECT

SELECT * FROM users;                        -- everything
SELECT name, email FROM users;              -- specific columns
SELECT name AS 'Full Name', city AS 'Location' FROM users;   -- rename columns

WHERE -- FILTERING ROWS

SELECT * FROM users WHERE city = 'Mumbai';
SELECT * FROM users WHERE city != 'Mumbai';
SELECT * FROM users WHERE id > 100;
SELECT * FROM users WHERE city = 'Mumbai' AND id > 50;
SELECT * FROM users WHERE city = 'Mumbai' OR city = 'Delhi';

LIKE -- PARTIAL TEXT MATCHING

SELECT * FROM users WHERE name LIKE 'R%';       -- starts with R
SELECT * FROM users WHERE email LIKE '%gmail%'; -- contains gmail
SELECT * FROM users WHERE city LIKE 'Ban%';     -- starts with Ban

ORDER BY -- SORTING

SELECT * FROM users ORDER BY city ASC;
SELECT * FROM users ORDER BY name DESC;
SELECT * FROM users ORDER BY created_at DESC;   -- newest first

LIMIT -- CONTROL HOW MANY ROWS

SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;         -- skip first 20, show next 10
SELECT * FROM users ORDER BY created_at DESC LIMIT 5;   -- 5 newest users

COUNTING AND AGGREGATING

SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE city = 'Mumbai';

SELECT city, COUNT(*) AS total
FROM users
GROUP BY city
ORDER BY total DESC;

-- Cities with more than 100 users:
SELECT city, COUNT(*) AS total
FROM users
GROUP BY city
HAVING total > 100;

SELECT SUM(amount) FROM orders;
SELECT AVG(amount) FROM orders;
SELECT MAX(amount), MIN(amount) FROM orders;

BETWEEN AND IN

SELECT * FROM users WHERE id BETWEEN 10 AND 50;
SELECT * FROM users WHERE city IN ('Mumbai', 'Delhi', 'Bangalore');
SELECT * FROM users WHERE city NOT IN ('Mumbai', 'Delhi');

Rahul answered his manager in minutes:

-- Users in Mumbai:
SELECT COUNT(*) FROM users WHERE city = 'Mumbai';

-- Signed up last week:
SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY created_at DESC;

-- Sorted by city:
SELECT name, email, city FROM users ORDER BY city ASC, name ASC;

That is what SELECT mastery feels like.

Key takeaways

WHERE filters rows, ORDER BY sorts them, LIMIT controls how many you get — these three are your daily tools

LIKE with % wildcards searches partial text: 'R%' starts with R, '%gmail%' contains gmail

GROUP BY groups identical values, COUNT(*) counts them, HAVING filters the groups — essential for reports

IN ('Mumbai','Delhi') is cleaner than multiple OR conditions for list filtering

Always add LIMIT when testing — SELECT * without LIMIT on a million-row table is painfully slow

Commands from this chapter

$ SELECT name, city FROM users WHERE city = 'Mumbai';

Specific columns filtered by city

$ SELECT * FROM users WHERE name LIKE 'R%' ORDER BY name ASC;

Names starting with R, sorted

$ SELECT city, COUNT(*) AS total FROM users GROUP BY city ORDER BY total DESC;

Count users per city

$ SELECT * FROM users WHERE city IN ('Mumbai','Delhi','Bangalore') LIMIT 20;

Filter by list with limit

$ SELECT * FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

Users from the last 7 days

$ SELECT MAX(id), MIN(id), COUNT(*) FROM users;

Quick stats on any table