Lesson 15: Querying with SELECT¶
Filtering, operators, NULLs, functions and ordering
About this lesson
Course: Database Management Systems
Unit 4: Structured Query Language · 12 Lecture Hours
Lesson duration: 2 lectures
Reading: Elmasri & Navathe, Ch. 6.3 · Murach's MySQL, Ch. 3, 8, 9
Learning Outcomes
- Write
SELECTwithWHERE,DISTINCT,ORDER BYandLIMIT. - Use comparison, logical,
BETWEEN,INandLIKEoperators fluently. - Handle NULL correctly using three-valued logic and
IS NULL. - Apply string, numeric and date functions.
- State the logical order of evaluation of a
SELECTstatement.
15.1 The SELECT Statement¶
SELECT [DISTINCT] select_list
FROM table_source
[WHERE search_condition]
[GROUP BY grouping_columns]
[HAVING group_condition]
[ORDER BY sort_columns [ASC|DESC]]
[LIMIT n [OFFSET m]];
The logical order of evaluation
The clauses are written in the order above but evaluated in a different order. This explains almost every puzzling SQL error:
WRITTEN EVALUATED
1. SELECT 1. FROM -- which tables, and join them
2. FROM 2. WHERE -- filter individual ROWS
3. WHERE 3. GROUP BY -- form groups
4. GROUP BY 4. HAVING -- filter GROUPS
5. HAVING 5. SELECT -- choose/compute columns
6. ORDER BY 6. DISTINCT -- remove duplicate rows
7. LIMIT 7. ORDER BY -- sort the result
8. LIMIT -- take the first n
Two consequences to memorise:
WHEREruns beforeSELECT, so a column alias defined inSELECTcannot be used inWHERE.ORDER BYruns afterSELECT, so a column alias can be used inORDER BY.
Basic forms¶
SELECT * FROM STUDENT; -- all columns (avoid in production)
SELECT Rollno, Name FROM STUDENT; -- projection
SELECT Name AS StudentName FROM STUDENT; -- alias
SELECT DISTINCT Coursetype FROM COURSE; -- remove duplicates
SELECT vs relational algebra π
SELECT Coursetype FROM COURSE returns five rows (one per course); the algebra π<sub>Coursetype</sub>(COURSE) returns two.
SQL tables are multisets. π corresponds to SELECT DISTINCT, not to SELECT.
15.2 The WHERE Clause¶
Comparison operators¶
=, <> (or !=), <, <=, >, >=
SELECT * FROM COURSE WHERE TotalSeats > 45;
SELECT * FROM COURSE WHERE Coursetype <> 'Fulltime';
SELECT * FROM STUDENT WHERE Dateofbirth < '2002-01-01';
Logical operators — AND, OR, NOT¶
NOT binds tightest, then AND, then OR. Use parentheses whenever you mix AND and OR.
-- WRONG: reads as Fulltime AND (>50), OR (Parttime)
SELECT * FROM COURSE WHERE Coursetype='Fulltime' AND TotalSeats>50 OR Coursetype='Parttime';
-- RIGHT
SELECT * FROM COURSE
WHERE (Coursetype='Fulltime' OR Coursetype='Parttime') AND TotalSeats > 50;
BETWEEN¶
BETWEEN is inclusive of both endpoints.
SELECT * FROM COURSE WHERE TotalSeats BETWEEN 40 AND 50; -- 40 and 50 included
SELECT * FROM STUDENT WHERE Dateofbirth BETWEEN '2001-01-01' AND '2001-12-31';
SELECT * FROM COURSE WHERE TotalSeats NOT BETWEEN 40 AND 50;
IN¶
SELECT * FROM COURSE WHERE Cname IN ('Computer Science','Chemistry'); -- course Q4
SELECT * FROM STUDENT WHERE Rollno NOT IN ('X2019','X2020');
IN is shorthand for a chain of ORs and is far more readable. It also accepts a subquery (Lesson 5).
LIKE — pattern matching¶
Wildcards
| Wildcard | Matches |
|---|---|
% |
Any sequence of characters, including none |
_ |
Exactly one character |
SELECT Name FROM STUDENT WHERE Name LIKE 'A%'; -- course Q3: starts with A
SELECT Name FROM STUDENT WHERE Name LIKE '%Sharma'; -- ends with Sharma
SELECT Name FROM STUDENT WHERE Name LIKE '%Kumar%'; -- contains Kumar
SELECT Name FROM STUDENT WHERE Name LIKE '_n%'; -- 'n' is the second letter
SELECT Cname FROM COURSE WHERE TeacherInCharge LIKE '%Gupta%'; -- course Q15
Course Q5 — roll numbers starting with X or Z and ending with 9:
SELECT Name FROM STUDENT
WHERE (Rollno LIKE 'X%' OR Rollno LIKE 'Z%') AND Rollno LIKE '%9';
The parentheses are essential — without them, AND would bind to the Z% alternative only.
Two cautions about LIKE
- A leading
%prevents index use.LIKE 'A%'can use an index onName;LIKE '%Sharma'cannot, and forces a full table scan. Avoid leading wildcards on large tables. - Case sensitivity depends on the collation. With
utf8mb4_unicode_ci(ci= case-insensitive),LIKE 'a%'also matches'Ankit'. Under a_binor_cscollation it would not. UseUPPER()on both sides if you must be certain.
15.3 NULL and Three-Valued Logic¶
Three-valued logic
Any comparison involving NULL evaluates to UNKNOWN, not TRUE or FALSE. SQL therefore has three truth values.
A WHERE clause keeps a row only when its condition is TRUE. Rows evaluating to FALSE or UNKNOWN are discarded.
Truth tables:
AND |
TRUE | FALSE | UNKNOWN |
|---|---|---|---|
| TRUE | TRUE | FALSE | UNKNOWN |
| FALSE | FALSE | FALSE | FALSE |
| UNKNOWN | UNKNOWN | FALSE | UNKNOWN |
OR |
TRUE | FALSE | UNKNOWN |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| FALSE | TRUE | FALSE | UNKNOWN |
| UNKNOWN | TRUE | UNKNOWN | UNKNOWN |
NOT |
|
|---|---|
| TRUE | FALSE |
| FALSE | TRUE |
| UNKNOWN | UNKNOWN |
Testing for NULL¶
SELECT * FROM STUDENT WHERE Dateofbirth IS NULL; -- correct
SELECT * FROM STUDENT WHERE Dateofbirth IS NOT NULL; -- correct
SELECT * FROM STUDENT WHERE Dateofbirth = NULL; -- WRONG: always UNKNOWN, returns nothing
The NULL trap that catches everyone
STUDENT has 400 rows; 40 have a NULL Mobile.
SELECT COUNT(*) FROM STUDENT WHERE Mobile = '9999999999'; -- say 100
SELECT COUNT(*) FROM STUDENT WHERE Mobile <> '9999999999'; -- 260, NOT 300
The 40 NULL rows satisfy neither condition, because both comparisons yield UNKNOWN. The two counts do not add up to 400. To include them:
SELECT COUNT(*) FROM STUDENT WHERE Mobile <> '9999999999' OR Mobile IS NULL;
Handling NULL in expressions¶
SELECT Name, COALESCE(Mobile, 'Not provided') AS Contact FROM STUDENT;
SELECT Name, IFNULL(Mobile, 'N/A') FROM STUDENT; -- MySQL
SELECT Salary + COALESCE(Bonus, 0) AS Total FROM EMPLOYEE; -- avoids NULL total
COALESCE(a, b, c) returns the first non-NULL argument. Without it, Salary + Bonus is NULL whenever Bonus is NULL — so an employee with no bonus silently shows no total at all.
15.4 ORDER BY and LIMIT¶
SELECT Name, Dateofbirth FROM STUDENT ORDER BY Name; -- ascending (default)
SELECT Name, Dateofbirth FROM STUDENT ORDER BY Dateofbirth DESC; -- descending
SELECT * FROM COURSE ORDER BY Coursetype ASC, TotalSeats DESC; -- two keys
SELECT * FROM COURSE ORDER BY 3 DESC; -- by column position
Course Q9 — the youngest student in CS(P):
SELECT s.Name, s.Dateofbirth
FROM STUDENT s
JOIN ADMISSION a ON s.Rollno = a.Rollno
JOIN COURSE c ON a.SID = c.SID
WHERE c.Cname = 'CS(P)'
ORDER BY s.Dateofbirth DESC
LIMIT 1;
The youngest student is the one with the latest date of birth, hence DESC.
LIMIT 1 and ties
LIMIT 1 returns one row even if several tie for the extreme value. If two students share the latest birth date, one is arbitrarily discarded.
To return all tied rows, compare against the maximum instead:
SELECT s.Name FROM STUDENT s JOIN ADMISSION a ON s.Rollno=a.Rollno
JOIN COURSE c ON a.SID=c.SID
WHERE c.Cname='CS(P)'
AND s.Dateofbirth = (SELECT MAX(s2.Dateofbirth) FROM STUDENT s2
JOIN ADMISSION a2 ON s2.Rollno=a2.Rollno
JOIN COURSE c2 ON a2.SID=c2.SID
WHERE c2.Cname='CS(P)');
Pagination:
SELECT * FROM STUDENT ORDER BY Rollno LIMIT 10 OFFSET 20; -- rows 21-30
NULL ordering: MySQL sorts NULLs first in ASC; other systems differ. Never assume — state it explicitly if it matters.
15.5 Functions¶
String functions¶
| Function | Purpose | Example |
|---|---|---|
CONCAT(a,b) |
Join strings | CONCAT(Rollno,' - ',Name) |
UPPER(s) / LOWER(s) |
Change case | UPPER(Name) |
LENGTH(s) |
Number of characters | LENGTH(Name) > 20 |
SUBSTRING(s,p,n) |
Extract n chars from position p |
SUBSTRING(Rollno,1,1) |
LEFT(s,n) / RIGHT(s,n) |
First / last n characters |
LEFT(Rollno,1) = 'X' |
TRIM(s) |
Remove leading/trailing spaces | TRIM(Name) |
REPLACE(s,a,b) |
Substitute | REPLACE(Name,'Kumar','K.') |
INSTR(s,sub) |
Position of substring, 0 if absent | INSTR(Name,'Gupta') > 0 |
Numeric functions¶
ROUND(x,d), CEIL(x), FLOOR(x), ABS(x), MOD(a,b), POWER(x,y), SQRT(x)
Date functions¶
| Function | Purpose |
|---|---|
CURDATE(), NOW() |
Current date / date-and-time |
YEAR(d), MONTH(d), DAY(d) |
Extract a part |
DATE_ADD(d, INTERVAL n unit) |
Add to a date |
DATEDIFF(d1,d2) |
Difference in days |
TIMESTAMPDIFF(unit,d1,d2) |
Difference in a chosen unit |
DATE_FORMAT(d, fmt) |
Format for display |
Course Q24 — students older than 18:
SELECT Name, TIMESTAMPDIFF(YEAR, Dateofbirth, CURDATE()) AS Age
FROM STUDENT
WHERE TIMESTAMPDIFF(YEAR, Dateofbirth, CURDATE()) > 18;
Note the expression is repeated: the alias Age cannot be used in WHERE, because WHERE is evaluated before SELECT.
Course Q25 (first half) — students born in 2001:
SELECT Name FROM STUDENT WHERE YEAR(Dateofbirth) = 2001;
Sargability — why the alternative is faster
WHERE YEAR(Dateofbirth) = 2001 applies a function to the column, so the DBMS must compute it for every row and cannot use an index on Dateofbirth.
The range form is equivalent and is index-friendly:
WHERE Dateofbirth >= '2001-01-01' AND Dateofbirth < '2002-01-01'
General rule: keep the column bare on one side of the comparison. A condition the optimiser can satisfy with an index is called sargable.
CASE expressions¶
SELECT Cname,
CASE WHEN TotalSeats >= 50 THEN 'Large'
WHEN TotalSeats >= 40 THEN 'Medium'
ELSE 'Small'
END AS SizeCategory
FROM COURSE;
15.6 Solved Questions¶
Solved Question 1
Q. State the logical order of evaluation of a SELECT statement. Explain, using it, why a column alias works in ORDER BY but not in WHERE.
A. The evaluation order is:
FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
WHERE is evaluated at step 2, but the SELECT list — where aliases are created — is not evaluated until step 5. When the DBMS processes WHERE, the alias does not yet exist, so it cannot be referenced:
SELECT Name, TIMESTAMPDIFF(YEAR,Dateofbirth,CURDATE()) AS Age
FROM STUDENT
WHERE Age > 18; -- ERROR: unknown column 'Age'
The remedy is to repeat the expression, or to wrap the query in a derived table:
SELECT * FROM (
SELECT Name, TIMESTAMPDIFF(YEAR,Dateofbirth,CURDATE()) AS Age FROM STUDENT
) t WHERE t.Age > 18;
ORDER BY is evaluated at step 7, after SELECT, so by then the alias exists and may be used freely:
SELECT Name, TIMESTAMPDIFF(YEAR,Dateofbirth,CURDATE()) AS Age
FROM STUDENT ORDER BY Age DESC; -- works
The same reasoning explains why HAVING (step 4) can refer to aggregates but WHERE (step 2) cannot: groups do not exist until step 3.
Solved Question 2
Q. Explain three-valued logic. Why does WHERE Mobile <> '9999999999' miss rows? Give two ways to include them.
A. SQL evaluates conditions to TRUE, FALSE or UNKNOWN. Any comparison with NULL yields UNKNOWN, because NULL represents an absent value and nothing can be known about a comparison with it. A WHERE clause retains a row only if its condition is TRUE; rows evaluating to FALSE or UNKNOWN are discarded.
So for a row with Mobile IS NULL, the condition Mobile <> '9999999999' is UNKNOWN, and the row is excluded — even though, intuitively, "the mobile is not that number" seems true of a row with no mobile at all.
The consequence surprises people because the counts do not partition the table:
SELECT COUNT(*) FROM STUDENT; -- 400
SELECT COUNT(*) FROM STUDENT WHERE Mobile = '9999999999'; -- 100
SELECT COUNT(*) FROM STUDENT WHERE Mobile <> '9999999999'; -- 260, not 300
Two remedies:
-
Test for NULL explicitly:
WHERE Mobile <> '9999999999' OR Mobile IS NULL -
Substitute a value before comparing:
This is more concise but not sargable — the function on the column prevents index use — so prefer the first form on large tables.WHERE COALESCE(Mobile,'') <> '9999999999'
Solved Question 3
Q. Write SQL for course practicals 3, 4, 5, 15, 24 and 25.
A.
Q3 — students' names starting with 'A'.
SELECT Name FROM STUDENT WHERE Name LIKE 'A%';
Q4 — details of students studying Computer Science or Chemistry.
SELECT DISTINCT s.*
FROM STUDENT s
JOIN ADMISSION a ON s.Rollno = a.Rollno
JOIN COURSE c ON a.SID = c.SID
WHERE c.Cname IN ('Computer Science','Chemistry');
DISTINCT is needed because a student admitted to both courses would otherwise appear twice.
Q5 — names of students whose roll number starts with 'X' or 'Z' and ends with '9'.
SELECT Name FROM STUDENT
WHERE (Rollno LIKE 'X%' OR Rollno LIKE 'Z%') AND Rollno LIKE '%9';
AND binds tighter than OR, so the condition would read "starts with X, or (starts with Z and ends with 9)" — returning every X roll number regardless of its last character.
Q15 — full-time courses whose teacher-in-charge has 'Gupta' in the name.
SELECT Cname FROM COURSE
WHERE TeacherInCharge LIKE '%Gupta%' AND Coursetype = 'Fulltime';
Q24 — total number of students older than 18.
SELECT COUNT(*) AS StudentsOver18
FROM STUDENT
WHERE TIMESTAMPDIFF(YEAR, Dateofbirth, CURDATE()) > 18;
TIMESTAMPDIFF(YEAR, ...) gives completed years, correctly handling those whose birthday has not yet occurred this year — unlike YEAR(CURDATE()) - YEAR(Dateofbirth), which would be wrong for roughly half the students.
Q25 — students born in 2001 admitted to at least one part-time course.
SELECT DISTINCT s.Name
FROM STUDENT s
JOIN ADMISSION a ON s.Rollno = a.Rollno
JOIN COURSE c ON a.SID = c.SID
WHERE s.Dateofbirth >= '2001-01-01' AND s.Dateofbirth < '2002-01-01'
AND c.Coursetype = 'Parttime';
YEAR(Dateofbirth)=2001 so that an index on Dateofbirth can be used. DISTINCT handles students admitted to two part-time courses.
Solved Question 4
Q. Explain % and _. Write patterns for: names of exactly five letters; roll numbers whose third character is 0; names containing a literal %.
A. % matches any sequence of characters, including the empty sequence. _ matches exactly one character.
| Requirement | Pattern |
|---|---|
| Names of exactly five characters | LIKE '_____' (five underscores) |
Roll numbers whose third character is 0 |
LIKE '__0%' |
| Names beginning and ending with 'A' | LIKE 'A%A' |
| Names of at least five characters | LIKE '_____%' |
Matching a literal % requires an escape character, since % is otherwise a wildcard:
SELECT Name FROM STUDENT WHERE Name LIKE '%\%%'; -- default escape
SELECT Name FROM STUDENT WHERE Name LIKE '%!%%' ESCAPE '!'; -- explicit escape
The second form is clearer and portable: ESCAPE '!' declares ! as the escape character, so !% means a literal per-cent sign.
A cleaner alternative for "exactly five characters" is WHERE LENGTH(Name) = 5, which states the intent directly — though, being a function on the column, it is not sargable.
Solved Question 5
Q. What is sargability? Rewrite three non-sargable conditions and explain the gain.
A. A condition is sargable (from Search ARGument able) if the optimiser can use an index to satisfy it. The rule is simple: the indexed column must appear bare on one side of the comparison, with no function or arithmetic wrapped around it. Once a function is applied, the DBMS must compute it for every row and therefore scan the whole table.
| Non-sargable | Sargable rewrite | Why it helps |
|---|---|---|
WHERE YEAR(Dateofbirth) = 2001 |
WHERE Dateofbirth >= '2001-01-01' AND Dateofbirth < '2002-01-01' |
An index on Dateofbirth supports a range scan directly. |
WHERE UPPER(Name) = 'ANKIT' |
WHERE Name = 'Ankit' under a case-insensitive collation |
Removes the per-row function call; the index on Name becomes usable. |
WHERE TotalSeats * 2 > 100 |
WHERE TotalSeats > 50 |
Arithmetic moved to the constant side, which is evaluated once. |
WHERE Name LIKE '%Sharma' |
(No general rewrite; consider a reversed-string index or full-text index) | A leading wildcard defeats a B+-tree, which searches by prefix. |
The gain. On a table of a million rows an index lookup examines perhaps 20 pages, whereas a full scan examines every page. The difference is typically three to four orders of magnitude in I/O, and it is invisible on a five-row practice table — which is exactly why the habit must be formed deliberately rather than discovered.
Note the final row: a leading wildcard cannot be made sargable by rewriting, because a B+-tree index is ordered by prefix (Unit 6, Lesson 2). This is a structural limitation, not a syntax problem.
Solved Question 6
Q. SELECT Name FROM STUDENT ORDER BY Dateofbirth DESC LIMIT 1; is offered as "the youngest student". Criticise it and give a correct version.
A. The query is usually right and silently wrong in two situations.
Problem 1 — ties. If two or more students share the latest date of birth, LIMIT 1 returns exactly one of them, chosen arbitrarily by whatever order the storage engine produced. The result is non-deterministic: the same query may return a different name on a different day, and the user is never told a tie existed.
Problem 2 — NULLs. If Dateofbirth is nullable, the behaviour depends on where the DBMS sorts NULLs. In MySQL, NULLs sort first in ASC and therefore last in DESC, so this particular query is safe; but in a system that sorts NULLs first in DESC, a student with an unknown date of birth would be returned as "the youngest". Relying on unstated NULL-ordering behaviour is not portable.
Correct version — compare against the maximum, and exclude NULLs:
SELECT Name, Dateofbirth
FROM STUDENT
WHERE Dateofbirth = (SELECT MAX(Dateofbirth) FROM STUDENT);
MAX ignores NULLs automatically, and the equality test returns every student sharing that date, so ties are reported rather than hidden. This form also states the intent — "the students whose birth date is the greatest" — rather than an ordering trick.
15.7 Exercises¶
A. Short answer
- State the logical order of evaluation of a
SELECTstatement. - Why can an alias be used in
ORDER BYbut notWHERE? - What is the difference between
SELECT Coursetypeandπ<sub>Coursetype</sub>? - Is
BETWEENinclusive? - Why does
WHERE Mobile = NULLreturn nothing? - Define sargability.
- What does
COALESCEdo?
B. Write the SQL
- All courses ordered by seats, largest first.
- Names of students whose name contains 'a' as the second letter.
- Courses with 40 to 50 seats inclusive.
- Courses that are neither Computer Science nor Chemistry.
- Students whose roll number is exactly five characters.
- Names of students, in upper case, with their ages.
- Courses whose teacher's surname is 'Gupta' (surname = last word).
- Students with no recorded date of birth.
- Students who either have no mobile number or whose mobile is the default.
- The three courses with the fewest seats.
- Each course with a label 'Large', 'Medium' or 'Small' by seat count.
- Students born in the first half of any year.
- Roll numbers and names, concatenated as
X2019 - Ankit Sharma.
C. Predict and explain
STUDENThas 100 rows, 10 with NULLMobile. Give the count returned by: a.WHERE Mobile = '9999999999'(30 rows have this value) b.WHERE Mobile <> '9999999999'c.WHERE Mobile IS NULLd.WHERE NOT (Mobile = '9999999999')e.WHERE Mobile = '9999999999' OR Mobile <> '9999999999'- Explain the difference between the results of:
a.
WHERE Coursetype='Fulltime' OR Coursetype='Parttime' AND TotalSeats>50b.WHERE (Coursetype='Fulltime' OR Coursetype='Parttime') AND TotalSeats>50 - What is wrong with
SELECT Name, YEAR(Dateofbirth) AS BirthYear FROM STUDENT WHERE BirthYear = 2001;? Give two fixes.
D. Analytical
- Rewrite each in sargable form and explain the gain:
WHERE MONTH(Dateofadmission)=7;WHERE LEFT(Rollno,1)='X';WHERE TotalSeats/2 > 25. - Explain why
SELECT *is discouraged in production code. Give three reasons. - Construct a table of data and two conditions demonstrating that
NOT (A AND B)and(NOT A) OR (NOT B)behave identically even with NULLs present. - A report must show "students with no mobile" but returns nothing. Diagnose and fix.
- Design a single query listing every course with its seat count and a computed column showing what percentage of the college's total seats it represents.
E. Objective — True or False with reason
BETWEEN 40 AND 50excludes 40 and 50.LIKE '%a'can use an index on the column.NULL = NULLis TRUE.DISTINCTis applied beforeORDER BY.LIMIT 1always returns the single correct maximum row.COUNT(*)andCOUNT(Mobile)give the same answer whenMobilehas NULLs.
Answers to Section E
- False.
BETWEENis inclusive of both endpoints. - False. A leading wildcard defeats a B+-tree index, which searches by prefix; a full scan results.
- False. It is UNKNOWN. Use
IS NULL. - True. The order is SELECT → DISTINCT → ORDER BY, which is why
ORDER BYmay only reference columns that survivedDISTINCT. - False. It returns one arbitrary row among ties, and its behaviour with NULLs depends on the system's NULL ordering.
- False.
COUNT(*)counts rows;COUNT(Mobile)counts non-NULL values, so it is smaller whenever NULLs exist.
Lesson Summary
- Evaluation order is FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT — hence aliases work in
ORDER BYbut not inWHERE. SELECTreturns a multiset;πcorresponds toSELECT DISTINCT.- Parenthesise whenever
ANDandORare mixed.BETWEENis inclusive;INis readable shorthand forOR. - NULL makes comparisons UNKNOWN, and
WHEREkeeps only TRUE — so<>silently omits NULL rows. UseIS NULLandCOALESCE. - Keep the column bare on one side of a comparison to stay sargable.
LIMIT 1hides ties; compare againstMAX/MINinstead.