Theory
One thousand rows, one question
The marks table now holds the whole college: thousands of rows. Nobody ever wants all of them.
The principal wants DBMS scores between 60 and 80. The office wants which cities students come from (each city once). The notice board wants the top 5. The exam cell wants students who missed a paper (score missing).
SQL answers each with a small filtering keyword, and this lesson is the full toolbox: ten keywords, one job each, on one table you already know.
Theory
Sieves on one pipeline
Think of the query as a pipeline from the table to your screen, and each keyword as a sieve you clip on:
WHERE keeps matching rows, DISTINCT melts duplicates, LIMIT cuts the stream after n rows. And when you have TWO pipelines, UNION pours them together, INTERSECT keeps only what both carry, EXCEPT subtracts one from the other. Any combination, same table.
At a glance
The row-filter family (all live inside WHERE, except DISTINCT/LIMIT)
| Tool | Keeps rows where... | Example |
|---|---|---|
| WHERE | the condition is true | WHERE subject = 'DBMS' |
| BETWEEN | value is in a range, ends INCLUDED | score BETWEEN 60 AND 80 |
| IN | value is in a list | city IN ('Surat', 'Navsari') |
| LIKE | text matches a pattern (% any, _ one) | name LIKE 'R%' |
| IS NULL | the value is missing | score IS NULL |
| DISTINCT | (output) duplicates removed | SELECT DISTINCT city |
| LIMIT | (output) only first n rows | LIMIT 5 |
Practical
The office's four questions, answered
-- 1. DBMS scores from 60 to 80 (BOTH ends included)
SELECT roll, score FROM marks
WHERE subject = 'DBMS' AND score BETWEEN 60 AND 80;
-- 2. every city once
SELECT DISTINCT city FROM students;
-- 3. top 5 DBMS scores
SELECT roll, score FROM marks
WHERE subject = 'DBMS'
ORDER BY score DESC
LIMIT 5;
-- 4. students who missed the paper (score missing)
SELECT roll FROM marks
WHERE subject = 'DBMS' AND score IS NULL;
-- names starting with R: 'R%' second letter i: '_i%'
SELECT name FROM students WHERE name LIKE 'R%';
This example runs in Gri-Learn on the web, where you can edit it and see the output.
Theory
Combining whole queries: UNION, INTERSECT, EXCEPT
The set operators work on two complete SELECTs with the same number of columns:
UNION: rows appearing in either query (duplicates removed;UNION ALLkeeps them).INTERSECT: rows appearing in both.EXCEPT: rows in the first but not the second.
Example: rolls who passed DBMS INTERSECT rolls who passed Maths gives students who passed both. Swap INTERSECT for EXCEPT and you get those who passed DBMS but not Maths: the re-exam list.
Quiz
A student writes: SELECT roll FROM marks WHERE score = NULL; to find absentees. It returns nothing, even though NULL scores exist. Why?
- NULL is unknown, so score = NULL is never true; the correct test is score IS NULL
- The query needs DISTINCT before roll
- NULL rows can only be found with LIKE 'NULL'
- SQLite stores NULL as 0, so the test should be score = 0
Show the answer
NULL is unknown, so score = NULL is never true; the correct test is score IS NULL
NULL means unknown, and comparing anything with unknown yields unknown, never true, so = NULL matches no rows: the single most classic SQL trap. The only correct tests are IS NULL and IS NOT NULL. Option D is dangerous: NULL is NOT zero (a 0 score is a real score); options B and C decorate the query without touching the actual problem.
Think first
Predict the row counts
marks has DBMS scores 55, 60, 80, 95 (four rows). Before tapping, count the rows each returns:
1. WHERE score BETWEEN 60 AND 80
2. WHERE score IN (55, 95)
3. WHERE score > 60 LIMIT 1
Show the answer
1. 2 rows (60 and 80: BETWEEN includes BOTH endpoints, the fact exams test).
2. 2 rows (exact list membership: 55 and 95).
3. 1 row (80 and 95 qualify, LIMIT keeps only the first delivered).
If you said 1 row for BETWEEN, you excluded the endpoints: BETWEEN 60 AND 80 means >= 60 AND <= 80, always inclusive.
Watch out
The three habitual mark-losers
= NULL instead of IS NULL: returns nothing, silently.
BETWEEN endpoints: inclusive, both ends. Write ">= and <=" next to it in your answer to show you know.
LIKE anchors: 'R%' = starts with R; '%R' = ends with R; '%R%' = contains R; '_i%' = second letter i. Read the pattern position by position and say what % and _ each mean when asked to explain a query.
Theory
You met the basics in BCA105
WHERE and DISTINCT appeared in BCA105 on Meera's shop; what is new here is the full toolbox in one place plus the set operators. In Unit 3 these exact queries travel into Python strings for cursor.execute(), and in Unit 4 pandas re-implements the same idea as boolean masks (df[df.score > 60]). Filtering is one concept wearing three costumes across this subject.
Summary
Key takeaways
- WHERE tests each row; BETWEEN (inclusive), IN (list), LIKE (% any run, _ one char), IS NULL refine it.
- = NULL never matches: IS NULL / IS NOT NULL are the only correct null tests.
- DISTINCT removes duplicate output rows; LIMIT n caps output (pair with ORDER BY for top-n).
- UNION (either, no duplicates), INTERSECT (both), EXCEPT (first minus second) combine two same-width SELECTs.
- UNION ALL keeps duplicates.
- Memory hook: sieves clipped onto one pipeline.