DBMS SELECT WHERE

on

Namaste again. In the last DBMS post you learned what a table is, what a primary key does, and saw SELECT * FROM Students, which really just means "show me everything." Today we go properly deeper, picking exactly which columns you want, and filtering down to exactly the rows you care about. This is the part of SQL you'll actually use the most, in real life and in your exam.

What's in this post

  1. Selecting specific columns
  2. The WHERE clause
  3. Comparison operators
  4. Combining conditions with AND, OR, NOT
  5. BETWEEN and IN
  6. Searching text with LIKE
  7. Sorting with ORDER BY
  8. DISTINCT, removing duplicates
  9. Try real queries yourself
  10. Example 1 (Easy): Who passed?
  11. Example 2 (Medium): Toppers of one section
  12. Example 3 (Harder): A marks range
  13. Example 4 (Hero level): Searching by surname
  14. Common mistakes
  15. Practice tasks

1. Selecting specific columns

The asterisk in SELECT * gets you every column, which is often more than you actually need. You can ask for exactly the columns you want, separated by commas.

select_columns.sql
SELECT Name, Marks FROM Students;

2. The WHERE clause

Analogy: without WHERE, asking a database a question is like walking up to a librarian and saying "show me every book in the library." With WHERE, you can say "show me only the books written after 2010," and get back exactly what you meant. That's the entire purpose of this clause, narrowing down.
where_basic.sql
SELECT Name, Marks
FROM Students
WHERE Marks >= 40;

3. Comparison operators

Operator Meaning
= equal to
!= or <> not equal to
> / < greater than / less than
>= / <= greater than or equal to / less than or equal to
Easy to forget: when comparing text, wrap the value in quotes. WHERE Section = 'A' is correct, WHERE Section = A will confuse the database, since without quotes it looks like you're referring to another column named A.

4. Combining conditions with AND, OR, NOT

Same logical operators you've already met in QBasic and Python, same meaning here too.

and_or.sql
SELECT Name, Section, Marks
FROM Students
WHERE Section = 'A' AND Marks >= 60;
AND means both sides must be true, OR means either side is enough. Mixing these up is the single most common WHERE mistake, if your query returns far more or far fewer rows than you expected, this is the first thing worth checking.

5. BETWEEN and IN

Two shortcuts that save you from writing longer AND chains.

between_in.sql
-- BETWEEN includes both endpoints
SELECT Name FROM Students WHERE Marks BETWEEN 60 AND 80;

-- IN checks against a list of allowed values
SELECT Name FROM Students WHERE Section IN ('A', 'C');

6. Searching text with LIKE

Analogy: this is exactly like typing a partial name into your phone's contact search, you don't need the full name, just enough to find a match. The % symbol means "anything can go here."
like.sql
-- Anyone whose name contains "Thapa" anywhere
SELECT Name FROM Students WHERE Name LIKE '%Thapa%';

7. Sorting with ORDER BY

order_by.sql
SELECT Name, Marks
FROM Students
ORDER BY Marks DESC;

ASC (ascending, smallest first) is the default if you don't write anything. DESC (descending, largest first) has to be stated explicitly, which is exactly what you want for a leaderboard of toppers.

8. DISTINCT, removing duplicates

distinct.sql
SELECT DISTINCT Section FROM Students;

If ten students are in Section A, a plain SELECT would list "A" ten times. DISTINCT collapses repeats, showing each unique value only once, perfect for answering "how many different sections exist?"

9. Try real queries yourself

This is a genuine, working mini SQL engine, running entirely in your browser, against the sample table below. Type a query and click Run.

Students table (sample data)

RollNo Name Section Marks
1 Sunita Thapa A 78
2 Ramesh Gurung B 55
3 Anita Rai A 92
4 Suresh Karki B 38
5 Priya Sharma A 67
6 Bikash Lama C 81
7 Nisha Koirala B 45
8 Deepak Thapa C 90

Click an example to load it, edit it, or write your own. Supports SELECT, WHERE (with =, !=, >, <, >=, <=, AND, OR, LIKE '%...%', IN (...), BETWEEN...AND...), and ORDER BY.

Who passed? Section A, highest first Marks between 60 and 80 Search surname Thapa

10. Example 1 (Easy): Who passed?

passed.sql
SELECT Name, Marks FROM Students WHERE Marks >= 40;

11. Example 2 (Medium): Toppers of one section

section_toppers.sql
SELECT Name, Marks
FROM Students
WHERE Section = 'A'
ORDER BY Marks DESC;

12. Example 3 (Harder): A marks range

marks_range.sql
SELECT Name FROM Students WHERE Marks BETWEEN 60 AND 80;

13. Example 4 (Hero level): Searching by surname

surname_search.sql
SELECT Name, Marks
FROM Students
WHERE Name LIKE '%Thapa%'
ORDER BY Marks DESC;

Notice this pulls back two different students who happen to share a surname, exactly the kind of situation that makes RollNo, not Name, the right primary key, the same lesson from the very first DBMS post, showing up again here.

14. Common mistakes

  1. Forgetting quotes around text values in WHERE.
  2. Mixing up AND and OR, leading to far too few or far too many results.
  3. Expecting ORDER BY to default to descending. It defaults to ascending, DESC must be written out.
  4. Using LIKE without the % wildcard, which just checks for an exact match, no different from using =.

15. Practice tasks

  1. Using the simulator above, write a query for every student who scored below 40.
  2. Find every student in Section B or Section C, sorted by marks, highest first.
  3. Find any student whose name contains the letter combination "an" anywhere.
  4. Challenge: write a query that finds every student scoring between 70 and 100 who is not in Section A.

SELECT and WHERE together are genuinely most of what you'll use SQL for day to day, picking the right rows and the right columns. Next in the rotation, we're back to Python for Part 4, lists. After that, DBMS Part 3, where we finally start changing data with INSERT, UPDATE, and DELETE.

— Diwakar

← DBMS Part 1: Tables, Rows, and Primary Keys DBMS Part 3: INSERT, UPDATE, DELETE (coming soon) →

Comments

Post a Comment