Skip to content

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

  1. Write SELECT with WHERE, DISTINCT, ORDER BY and LIMIT.
  2. Use comparison, logical, BETWEEN, IN and LIKE operators fluently.
  3. Handle NULL correctly using three-valued logic and IS NULL.
  4. Apply string, numeric and date functions.
  5. State the logical order of evaluation of a SELECT statement.

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:

  1. WHERE runs before SELECT, so a column alias defined in SELECT cannot be used in WHERE.
  2. ORDER BY runs after SELECT, so a column alias can be used in ORDER 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

  1. A leading % prevents index use. LIKE 'A%' can use an index on Name; LIKE '%Sharma' cannot, and forces a full table scan. Avoid leading wildcards on large tables.
  2. Case sensitivity depends on the collation. With utf8mb4_unicode_ci (ci = case-insensitive), LIKE 'a%' also matches 'Ankit'. Under a _bin or _cs collation it would not. Use UPPER() 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
The missing 40 are the NULL rows, in neither result.

Two remedies:

  1. Test for NULL explicitly:

    WHERE Mobile <> '9999999999' OR Mobile IS NULL
    

  2. Substitute a value before comparing:

    WHERE COALESCE(Mobile,'') <> '9999999999'
    
    This is more concise but not sargable — the function on the column prevents index use — so prefer the first form on large tables.

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';
The parentheses are essential. Without them 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';
The range form is used rather than 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

  1. State the logical order of evaluation of a SELECT statement.
  2. Why can an alias be used in ORDER BY but not WHERE?
  3. What is the difference between SELECT Coursetype and π<sub>Coursetype</sub>?
  4. Is BETWEEN inclusive?
  5. Why does WHERE Mobile = NULL return nothing?
  6. Define sargability.
  7. What does COALESCE do?

B. Write the SQL

  1. All courses ordered by seats, largest first.
  2. Names of students whose name contains 'a' as the second letter.
  3. Courses with 40 to 50 seats inclusive.
  4. Courses that are neither Computer Science nor Chemistry.
  5. Students whose roll number is exactly five characters.
  6. Names of students, in upper case, with their ages.
  7. Courses whose teacher's surname is 'Gupta' (surname = last word).
  8. Students with no recorded date of birth.
  9. Students who either have no mobile number or whose mobile is the default.
  10. The three courses with the fewest seats.
  11. Each course with a label 'Large', 'Medium' or 'Small' by seat count.
  12. Students born in the first half of any year.
  13. Roll numbers and names, concatenated as X2019 - Ankit Sharma.

C. Predict and explain

  1. STUDENT has 100 rows, 10 with NULL Mobile. Give the count returned by: a. WHERE Mobile = '9999999999' (30 rows have this value) b. WHERE Mobile <> '9999999999' c. WHERE Mobile IS NULL d. WHERE NOT (Mobile = '9999999999') e. WHERE Mobile = '9999999999' OR Mobile <> '9999999999'
  2. Explain the difference between the results of: a. WHERE Coursetype='Fulltime' OR Coursetype='Parttime' AND TotalSeats>50 b. WHERE (Coursetype='Fulltime' OR Coursetype='Parttime') AND TotalSeats>50
  3. What is wrong with SELECT Name, YEAR(Dateofbirth) AS BirthYear FROM STUDENT WHERE BirthYear = 2001;? Give two fixes.

D. Analytical

  1. Rewrite each in sargable form and explain the gain: WHERE MONTH(Dateofadmission)=7; WHERE LEFT(Rollno,1)='X'; WHERE TotalSeats/2 > 25.
  2. Explain why SELECT * is discouraged in production code. Give three reasons.
  3. 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.
  4. A report must show "students with no mobile" but returns nothing. Diagnose and fix.
  5. 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

  1. BETWEEN 40 AND 50 excludes 40 and 50.
  2. LIKE '%a' can use an index on the column.
  3. NULL = NULL is TRUE.
  4. DISTINCT is applied before ORDER BY.
  5. LIMIT 1 always returns the single correct maximum row.
  6. COUNT(*) and COUNT(Mobile) give the same answer when Mobile has NULLs.

Answers to Section E

  1. False. BETWEEN is inclusive of both endpoints.
  2. False. A leading wildcard defeats a B+-tree index, which searches by prefix; a full scan results.
  3. False. It is UNKNOWN. Use IS NULL.
  4. True. The order is SELECT → DISTINCT → ORDER BY, which is why ORDER BY may only reference columns that survived DISTINCT.
  5. False. It returns one arbitrary row among ties, and its behaviour with NULLs depends on the system's NULL ordering.
  6. 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 BY but not in WHERE.
  • SELECT returns a multiset; π corresponds to SELECT DISTINCT.
  • Parenthesise whenever AND and OR are mixed. BETWEEN is inclusive; IN is readable shorthand for OR.
  • NULL makes comparisons UNKNOWN, and WHERE keeps only TRUE — so <> silently omits NULL rows. Use IS NULL and COALESCE.
  • Keep the column bare on one side of a comparison to stay sargable.
  • LIMIT 1 hides ties; compare against MAX/MIN instead.