Practice questions: filtering and functions
Filtering and functions20 min readLesson 3 of 3
These questions cover the filtering and function rules that show up in exams and in debugging sessions. Answer each one in your own words first, then check the query output on the practice database. The engine is SQLite 3.53, and case or cast behaviour can differ on other engines.
3.1.1 Q1 What is the precedence of NOT, AND and OR, and why should you add parentheses?
Answer (English): NOT binds tighter than AND, and AND binds tighter than OR. So a OR b AND c means a OR (b AND c), not (a OR b) AND c. Parentheses change the expression tree and make the intended grouping explicit. Use them whenever a condition mixes AND and OR, even when the precedence happens to agree, because a reader should not have to reconstruct the grammar.
Hinglish: NOT sabse pehle, phir AND, phir OR. Isliye a OR b AND c ka matlab a OR (b AND c) hai. Parentheses expression tree badal dete hain aur intention clear karte hain. Jab bhi AND aur OR saath hon, parentheses lagao, chahe precedence match kare, kyunki reader ko grammar yaad nahi rakhni chahiye.
3.1.2 Q2 What do the wildcards in LIKE do, and how does LIKE differ from GLOB?
Answer (English): % matches any sequence of zero or more characters and _ matches exactly one character. GLOB is the case-sensitive SQLite alternative, using * and ? for the same two jobs. A literal wildcard needs ESCAPE. Both patterns run a character-by-character match, and a leading wildcard stops an index from helping.
Hinglish: % kisi bhi length ka sequence match karta hai aur _ ek character. GLOB SQLite ka case-sensitive version hai jo * aur ? use karta hai. Literal wildcard match karne ke liye ESCAPE chahiye. Dono patterns character-by-character match karte hain, aur shuru mein wildcard ho to index ka fayda khatam.
SELECT name FROM students WHERE name LIKE 'A%';
SELECT name FROM students WHERE name LIKE '_e%';Output
name ------------ Aarav Sharma name ---------- Meera Iyer Neel Joshi Dev Patel
3.1.3 Q3 Why can WHERE LOWER(name) = 'dev patel' force a full scan, and what would you change?
Answer (English): The index stores name, not LOWER(name), so the engine cannot use the index to find the value without computing the function for every row. Options include storing a normalized lowercase column with its own index, creating an expression index where the engine supports it, or changing the comparison to a direct match when case rules allow. The plan output is the honest test: if it says SCAN, the index was not used.
Hinglish: Index mein name stored hai, LOWER(name) nahi, isliye engine har row par function chala kar compare karta hai. Solution: normalized lowercase column aur us par index, expression index (jitne engines support karein), ya direct match. Plan output hi sach batata hai: SCAN likha ho to index use nahi hua.
3.1.4 Q4 How should dates be stored so range filters work, and how do you fetch one month of joins?
Answer (English): Store ISO 8601 text such as 2026-03-18, padded to two digits, because that format sorts and compares correctly as text. A month range uses a lower bound and an exclusive upper bound: joined_on >= '2026-03-01' AND joined_on < '2026-04-01'. Using BETWEEN '2026-03-01' AND '2026-03-31' also works for date-only values, but it breaks when timestamps are stored, because 2026-03-31 10:00 is after the upper bound.
Hinglish: Date ko ISO 8601 text ki tarah store karo, jaise 2026-03-18, do digits ke saath, kyunki isi format mein text comparison sahi chalta hai. Ek month ke liye lower bound aur exclusive upper bound rakho: >= '2026-03-01' AND < '2026-04-01'. Timestamp store ho to BETWEEN 31 March ko miss kar sakta hai.
SELECT name, joined_on FROM students
WHERE joined_on >= '2026-03-01' AND joined_on < '2026-04-01'
ORDER BY joined_on;Output
name | joined_on ------------+----------- Ishita Bose | 2026-03-01 Neel Joshi | 2026-03-10 Zoya Ali | 2026-03-18 Dev Patel | 2026-03-22
3.1.5 Q5 What does fee / 1000 return for a fee of 4999, and why?
Answer (English): It returns 4, because both operands are integers and SQLite uses integer division. The fractional part is discarded, not rounded. Cast one side to REAL before dividing to get 4.999, or use a decimal literal such as fee / 1000.0. The same trap appears in PostgreSQL with integer columns, and ROUND does not help because it cannot restore digits that the division already threw away.
Hinglish: Answer 4 aata hai, kyunki dono operands integer hain aur SQLite integer division karta hai. Fraction discard hota hai, round nahi. Decimal chahiye to kisi ek side ko REAL mein CAST karo ya fee / 1000.0 likho. PostgreSQL mein bhi integer columns ke saath same trap hai, aur ROUND baad mein kuch nahi bacha sakta.
3.1.6 Q6 When do you use COALESCE, NULLIF and CASE?
Answer (English): COALESCE returns the first non-NULL argument, so it is the fallback for missing data. NULLIF returns NULL when two values are equal, which lets you turn a sentinel value into a real NULL before an aggregate or a comparison. CASE is the general conditional; it picks a value based on one or more conditions and can replace either of the other two. The query below shows a fallback salary for instructors whose salary is unknown.
Hinglish: COALESCE pehla non-NULL value deta hai, isliye missing data ka fallback hai. NULLIF do values equal hon to NULL deta hai, jisse sentinel value ko asli NULL bana sakte ho. CASE general conditional hai jo conditions ke hisaab se value chunta hai. Neeche instructors ki unknown salary ke liye fallback dikhaya gaya hai.
SELECT name, salary, COALESCE(salary, 0) AS salary_or_zero
FROM instructors
ORDER BY instructor_id;Output
name | salary | salary_or_zero -------------+--------+--------------- Priya Menon | 72000 | 72000 Arjun Das | 65000 | 65000 Nadia Sheikh | NULL | 0 Vikram Rao | 58000 | 58000 Tara Singh | 61000 | 61000
3.1.7 Q7 What is the difference between a column alias and a table alias, and where can each be used?
Answer (English): A column alias renames an output column, while a table alias renames a table inside the query and gives you a short prefix for its columns. A table alias is required in a self join, where the same table appears twice under two roles. A column alias may be used in ORDER BY because that stage runs after SELECT; standard SQL does not allow it in WHERE. A table alias is available in every clause after FROM.
Hinglish: Column alias output column ka naam badalta hai; table alias query ke andar table ka short naam deta hai aur self join mein zaroori hota hai. Column alias ORDER BY mein chalta hai kyunki woh SELECT ke baad chalta hai, par standard SQL mein WHERE mein nahi. Table alias FROM ke baad har clause mein milta hai.
3.1.8 Q8 What does 'Hi, ' || NULL return, and how do you protect a concatenation?
Answer (English): It returns NULL, because the concatenation operator propagates NULL. An address built as city || ', ' || country disappears entirely when one part is missing. Protect each nullable part with COALESCE, or treat the whole expression as optional and format the label in the application. The same NULL propagation applies to UPPER, LOWER, LENGTH, SUBSTR and most other scalar functions.
Hinglish: 'Hi, ' || NULL NULL deta hai, kyunki concatenation NULL propagate karta hai. Address agar city || ', ' || country se banta hai aur ek part NULL ho to poora address gayab ho jaata hai. Har nullable part par COALESCE lagao, warna formatting application mein karo. UPPER, LENGTH, SUBSTR jaise functions bhi yahi karte hain.
3.1.9 Q9 Write a query that splits each student name into first name and last name.
Answer (English): Find the space with INSTR, take the characters before it with SUBSTR, and take the characters after it with a second SUBSTR. INSTR returns a 1-based position, so the first name is SUBSTR(name, 1, INSTR(name, ' ') - 1) and the last name starts one position later. A name without a space returns the whole string for the first name and an empty string for the last name, which is worth checking before shipping the split.
Hinglish: INSTR se space ki position lo, SUBSTR se pehle wale characters lo, aur doosre SUBSTR se baad wale. INSTR 1-based position deta hai, isliye first name SUBSTR(name, 1, INSTR(name, ' ') - 1) hota hai aur last name ek position baad se shuru hota hai. Bina space wale name par last name khaali string aata hai, isliye split se pehle data check karo.
SELECT name,
SUBSTR(name, 1, INSTR(name, ' ') - 1) AS first_name,
SUBSTR(name, INSTR(name, ' ') + 1) AS last_name
FROM students
ORDER BY name
LIMIT 5;Output
name | first_name | last_name -------------+------------+---------- Aarav Sharma | Aarav | Sharma Dev Patel | Dev | Patel Ishita Bose | Ishita | Bose Kabir Nair | Kabir | Nair Meera Iyer | Meera | Iyer
3.1.10 Q10 How do you match a literal percent sign with LIKE, and why does the same pattern behave differently on PostgreSQL?
Answer (English): A literal percent sign is escaped with an ESCAPE clause: LIKE '50\%' ESCAPE '\' matches the text "50%". Without the clause, the percent sign is a wildcard. Case behaviour is the portable difference: SQLite's LIKE folds ASCII case by default, while PostgreSQL's LIKE is case-sensitive and ILIKE is the case-insensitive alternative. Name the engine and the escape clause before you claim a pattern is portable.
Hinglish: Literal percent ko ESCAPE clause se match karte hain: LIKE '50\%' ESCAPE '\' sirf "50%" match karega. Bina clause ke % wildcard hai. Case rule portable nahi: SQLite ka LIKE ASCII case fold karta hai, PostgreSQL ka LIKE case-sensitive hai aur ILIKE insensitive. Pattern ko portable kehne se pehle engine aur escape clause dono batao.
3.2 Quiz pointer
The module quiz has five questions and lives apart from these answers. Attempt it without looking back, then rerun any query you missed. For a wrong option, write the corrected query by hand; that catches the difference between recognising a keyword and knowing the rule.