Chapter 8 Beginner 56 Questions

Practice Questions — LIKE Pattern Matching and NULL Handling

← Back to Notes
17 Easy
20 Medium
11 Hard

Topic-Specific Questions

Question 1
Easy
Find all students whose name starts with 'A'.
LIKE 'A%'.
SELECT name FROM students WHERE name LIKE 'A%';
Question 2
Easy
Find all students whose name ends with 'a'.
LIKE '%a'.
SELECT name FROM students WHERE name LIKE '%a';
Question 3
Easy
Find all students whose email is NULL.
IS NULL, not = NULL.
SELECT name FROM students WHERE email IS NULL;
Question 4
Easy
Find students with a gmail email address.
LIKE '%@gmail.com'.
SELECT name, email FROM students
WHERE email LIKE '%@gmail.com';
Question 5
Easy
How many rows?
SELECT name FROM students WHERE name LIKE '%ya%';
Names containing 'ya'.
3 rows
Question 6
Easy
Find students with missing marks.
IS NULL on marks.
SELECT name FROM students WHERE marks IS NULL;
Question 7
Easy
Find students from a city that starts with 'M'.
LIKE 'M%' on city.
SELECT name, city FROM students WHERE city LIKE 'M%';
Question 8
Easy
Find students whose email is NOT NULL.
IS NOT NULL.
SELECT name FROM students WHERE email IS NOT NULL;
Question 9
Easy
What is the difference between % and _ in LIKE patterns?
Think about length.
% matches zero or more characters (any length). _ matches exactly one character. So 'A%' matches 'A', 'Aa', 'Aarav', 'Apple' — any length starting with A. 'A_' matches only 2-character strings like 'Ab', 'Az', 'A1'. 'A__' matches exactly 3 characters.
Question 10
Easy
Why does WHERE email = NULL return zero rows?
SQL three-valued logic.
Because NULL means 'unknown,' any comparison with NULL evaluates to NULL (not TRUE, not FALSE). The WHERE clause only keeps rows where the condition is TRUE. So email = NULL is NULL for every row, and no row passes the filter. The correct syntax is WHERE email IS NULL.
Question 11
Medium
Find students with names having exactly 10 characters.
10 underscores in LIKE.
SELECT name FROM students
WHERE name LIKE '__________';  -- 10 underscores
Question 12
Medium
Show name and email, replacing NULL emails with 'Not provided'. Use COALESCE.
COALESCE(col, default).
SELECT name, COALESCE(email, 'Not provided') AS contact
FROM students;
Question 13
Medium
Find students who do NOT have a yahoo email. Remember to handle NULLs.
NOT LIKE plus IS NOT NULL.
SELECT name, email FROM students
WHERE email NOT LIKE '%@yahoo.com'
  AND email IS NOT NULL;
Question 14
Medium
Find students whose name second letter is 'a' or 'i' (use LIKE with underscore).
Two patterns combined with OR.
SELECT name FROM students
WHERE name LIKE '_a%' OR name LIKE '_i%';
Question 15
Medium
Show name and marks. If marks is NULL, show 0. Use IFNULL.
IFNULL(marks, 0).
SELECT name, IFNULL(marks, 0) AS marks FROM students;
Question 16
Medium
What does this return?
SELECT COALESCE(NULL, NULL, 'Hello', 'World');
First non-NULL.
Hello
Question 17
Medium
What does this return?
SELECT NULLIF(10, 10), NULLIF(10, 5);
NULLIF returns NULL when args equal.
NULL, 10
Question 18
Medium
Find students whose email contains an underscore. Remember _ is a wildcard — you need to escape it.
ESCAPE clause.
SELECT name, email FROM students
WHERE email LIKE '%\_%' ESCAPE '\\';

-- Or using a different escape char:
SELECT name, email FROM students
WHERE email LIKE '%!_%' ESCAPE '!';
Question 19
Medium
Find students from Mumbai OR whose city is missing.
LIKE 'Mumbai' or IS NULL.
SELECT name, city FROM students
WHERE city = 'Mumbai' OR city IS NULL;
Question 20
Medium
Explain when LIKE can use an index and when it cannot.
Leading wildcard vs trailing wildcard.
LIKE can use a B-tree index when the pattern is anchored at the start: LIKE 'Aarav%'. The index can quickly find all rows starting with 'Aarav'. LIKE CANNOT use a B-tree index when the pattern starts with a wildcard: LIKE '%gmail'. The database must scan every row. For leading-wildcard searches at scale, use a full-text index or trigram index.
Question 21
Medium
What does this return?
SELECT 5 + NULL, NULL * 10, CONCAT('Hi ', NULL);
NULL propagates.
NULL, NULL, NULL
Question 22
Hard
Find students whose name has exactly 5 characters in the first word (before the space). E.g., 'Aarav' has 5 chars.
LIKE '_____ %' (5 underscores, space, anything).
SELECT name FROM students
WHERE name LIKE '_____ %';
Question 23
Hard
Show name, marks, and a status column: 'Pass' if marks >= 60, 'Fail' if marks < 60, 'Not Attempted' if marks is NULL. Use CASE with IS NULL.
CASE WHEN marks IS NULL... WHEN marks >= 60... ELSE...
SELECT name, marks,
  CASE
    WHEN marks IS NULL THEN 'Not Attempted'
    WHEN marks >= 60 THEN 'Pass'
    ELSE 'Fail'
  END AS status
FROM students;
Question 24
Hard
Find average marks treating NULLs as 0 (not as missing).
AVG(COALESCE(marks, 0)).
SELECT AVG(COALESCE(marks, 0)) AS avg_marks FROM students;
Question 25
Hard
Why might an application team prefer NULLs over sentinel values like 0 or empty string?
Clarity of meaning.
NULL explicitly means 'unknown' or 'not applicable.' A 0 in marks could mean the student scored zero OR did not take the test — ambiguous. An empty string in email could mean 'no email' OR 'user has not been prompted yet.' NULL keeps these cases distinguishable. Aggregate functions also handle NULLs sensibly by ignoring them (AVG skips missing data instead of skewing the average with zeros).
Question 26
Hard
What does this return?
SELECT NULL = NULL, NULL != NULL;
Three-valued logic.
NULL, NULL

Mixed & Application Questions

Question 1
Easy
Find all students in the Computer Science course.
WHERE course = 'Computer Science'.
SELECT * FROM students WHERE course = 'Computer Science';
Question 2
Easy
Find students whose name starts with 'P' or 'S'.
LIKE with OR.
SELECT name FROM students
WHERE name LIKE 'P%' OR name LIKE 'S%';
Question 3
Easy
Find students whose name contains 'sh' anywhere (case-insensitive in MySQL).
LIKE '%sh%'.
SELECT name FROM students WHERE name LIKE '%sh%';
Question 4
Easy
Find students whose city is NULL.
IS NULL.
SELECT name FROM students WHERE city IS NULL;
Question 5
Easy
What does IFNULL(NULL, 'N/A') return?
First non-NULL.
'N/A'
Question 6
Easy
Find students with non-NULL marks.
IS NOT NULL.
SELECT * FROM students WHERE marks IS NOT NULL;
Question 7
Easy
How many rows?
SELECT * FROM students WHERE name LIKE '%';
% matches anything.
10 rows
Question 8
Medium
List students with exactly 11-character names. Use _.
11 underscores.
SELECT name FROM students WHERE name LIKE '___________';
Question 9
Medium
Show name and city, showing 'Unknown' for missing cities.
COALESCE(city, 'Unknown').
SELECT name, COALESCE(city, 'Unknown') AS city FROM students;
Question 10
Medium
Find students whose course starts with 'C'.
LIKE 'C%'.
SELECT name, course FROM students WHERE course LIKE 'C%';
Question 11
Medium
What does this return?
SELECT COUNT(*), COUNT(email), COUNT(marks) FROM students;
COUNT(col) excludes NULLs.
10, 8, 8
Question 12
Medium
Find students whose name contains 'Kumar' OR 'Nair' using LIKE.
Two LIKE conditions.
SELECT name FROM students
WHERE name LIKE '%Kumar%' OR name LIKE '%Nair%';
Question 13
Medium
Find students in Mumbai or Delhi with gmail emails.
Combine IN and LIKE.
SELECT name, email, city FROM students
WHERE city IN ('Mumbai', 'Delhi')
  AND email LIKE '%@gmail.com';
Question 14
Medium
What does this return?
SELECT AVG(marks), AVG(COALESCE(marks, 0)) FROM students;
Two different averages.
78.875, 63.1
Question 15
Medium
Show each student's name and a 'has_email' flag ('Yes' or 'No') using IFNULL.
CASE or combining IFNULL with comparison.
SELECT name,
  IF(email IS NULL, 'No', 'Yes') AS has_email
FROM students;

-- Or using CASE:
SELECT name,
  CASE WHEN email IS NULL THEN 'No' ELSE 'Yes' END AS has_email
FROM students;
Question 16
Medium
Find students with exactly 4-character first names. Split by space, check the first word.
LIKE '____ %'.
SELECT name FROM students WHERE name LIKE '____ %';
Question 17
Hard
Find students whose email has an underscore in the local-part (before @). Use ESCAPE.
Local-part = before @.
SELECT name, email FROM students
WHERE email LIKE '%\_%@%' ESCAPE '\\';
Question 18
Hard
Find students whose email domain is NOT gmail.com and whose marks are either NULL or above 80.
Three conditions: not gmail, AND (NULL OR high marks).
SELECT name, email, marks FROM students
WHERE email NOT LIKE '%@gmail.com'
  AND email IS NOT NULL
  AND (marks IS NULL OR marks > 80);
Question 19
Hard
Display a full contact info column: name (email, marks). Use COALESCE for NULLs.
CONCAT with COALESCE.
SELECT CONCAT(
    name, ' (',
    COALESCE(email, 'No email'), ', ',
    COALESCE(CAST(marks AS CHAR), 'No marks'),
    ')'
) AS contact_info
FROM students;
Question 20
Hard
What does this return?
SELECT NULLIF(CONCAT(name, ' ', email), CONCAT(name, ' '))
FROM students WHERE id = 4;
Ananya has NULL email.
NULL
Question 21
Hard
Find students whose name has 'a' as exactly the 2nd character AND whose city is known (not NULL).
LIKE '_a%' and IS NOT NULL.
SELECT name, city FROM students
WHERE name LIKE '_a%'
  AND city IS NOT NULL;
Question 22
Hard
Show each student and their marks grade: 'A' if >= 85, 'B' if >= 70, 'C' if < 70, 'Absent' if NULL. Use CASE.
CASE with multiple WHEN, NULL check first.
SELECT name, marks,
  CASE
    WHEN marks IS NULL THEN 'Absent'
    WHEN marks >= 85 THEN 'A'
    WHEN marks >= 70 THEN 'B'
    ELSE 'C'
  END AS grade
FROM students;

Multiple Choice Questions

MCQ 1
What does % wildcard match in LIKE?
  • A. Exactly one character
  • B. Zero or more characters
  • C. One or more characters
  • D. Any digit
Answer: B
B is correct. % matches zero or more of any characters. 'A%' matches 'A', 'Aa', 'Aardvark', etc.
MCQ 2
What does _ wildcard match?
  • A. Zero or more characters
  • B. Exactly one character
  • C. Whitespace only
  • D. A digit
Answer: B
B is correct. Underscore matches exactly one character. 'A_' matches any 2-char string starting with A.
MCQ 3
How do you check if a column is NULL?
  • A. WHERE col = NULL
  • B. WHERE col == NULL
  • C. WHERE col IS NULL
  • D. WHERE col EQUALS NULL
Answer: C
C is correct. Only IS NULL works. Comparing with = NULL returns NULL (not TRUE), so the row is dropped by WHERE.
MCQ 4
What does COALESCE(NULL, NULL, 5, NULL, 10) return?
  • A. NULL
  • B. 5
  • C. 10
  • D. 15
Answer: B
B is correct. COALESCE returns the FIRST non-NULL argument. NULLs are skipped, first non-NULL is 5.
MCQ 5
What does WHERE name LIKE 'A%' match?
  • A. Names containing A
  • B. Names starting with A
  • C. Names ending with A
  • D. Names of length A characters
Answer: B
B is correct. 'A%' means 'A' followed by anything — starts with A.
MCQ 6
What does 5 + NULL return?
  • A. 5
  • B. 0
  • C. NULL
  • D. Error
Answer: C
C is correct. Any arithmetic with NULL yields NULL. NULL propagates through expressions.
MCQ 7
In MySQL (default collation), does LIKE 'a%' match 'Aarav'?
  • A. Yes, MySQL LIKE is case-insensitive by default
  • B. No, MySQL LIKE is case-sensitive
  • C. Only if you use ILIKE
  • D. Syntax error
Answer: A
A is correct. MySQL's default collation utf8mb4_0900_ai_ci is case-insensitive. PostgreSQL's LIKE is case-sensitive and requires ILIKE for case-insensitive.
MCQ 8
What does NULLIF(5, 5) return?
  • A. 5
  • B. 0
  • C. NULL
  • D. TRUE
Answer: C
C is correct. NULLIF(a, b) returns NULL if a = b, else returns a. Here 5 = 5, so NULL.
MCQ 9
How many rows does SELECT * FROM students WHERE email = NULL return?
  • A. All rows with NULL email
  • B. All rows with non-NULL email
  • C. Zero rows, regardless of data
  • D. All rows
Answer: C
C is correct. = NULL always evaluates to NULL (not TRUE), so no rows are returned. This is SQL's famous NULL gotcha — always use IS NULL.
MCQ 10
Which matches a pattern containing a literal %?
  • A. LIKE '50\%%'
  • B. LIKE '%50\%%' ESCAPE '\\'
  • C. LIKE '50%%'
  • D. LIKE '%\%50\%%'
Answer: B
B is correct. The ESCAPE clause designates \\ as the escape character, so \% is a literal %. Option A is ambiguous without ESCAPE. Option C has %% which just means 'any two sequences of any length.'
MCQ 11
What does COUNT(email) return if the email column has 10 values, 3 of which are NULL?
  • A. 10
  • B. 7
  • C. 3
  • D. NULL
Answer: B
B is correct. COUNT(column) counts non-NULL values. 10 - 3 NULLs = 7. COUNT(*) would return 10.
MCQ 12
Which is the MySQL-specific function for 2-argument NULL substitution?
  • A. COALESCE
  • B. NULLIF
  • C. IFNULL
  • D. ISNULL
Answer: C
C is correct. IFNULL(x, default) is MySQL's 2-arg function. COALESCE is standard SQL and supports any number of arguments. ISNULL exists in SQL Server but not as a 2-arg substitution in MySQL.
MCQ 13
Which query finds names with second letter 'r'?
  • A. LIKE 'r%'
  • B. LIKE '%r'
  • C. LIKE '_r%'
  • D. LIKE '__r%'
Answer: C
C is correct. One underscore (any first char), then 'r', then anything. Matches 'Priya' (P-r...), not 'Rohan' (starts with R so position 2 is 'o').
MCQ 14
What does AVG(marks) return when 3 of 10 marks are NULL?
  • A. Sum of all non-NULL marks divided by 10
  • B. Sum of all non-NULL marks divided by 7
  • C. NULL (since some values are NULL)
  • D. Sum including NULLs as 0, divided by 10
Answer: B
B is correct. AVG skips NULLs — both in sum and in count. If you want NULLs treated as 0, use AVG(COALESCE(marks, 0)) to include them in the denominator.
MCQ 15
In PostgreSQL, which is equivalent to MySQL's case-insensitive LIKE 'a%'?
  • A. LIKE 'a%' (same behavior)
  • B. ILIKE 'a%'
  • C. LIKE UPPER('a%')
  • D. CASE_LIKE 'a%'
Answer: B
B is correct. PostgreSQL's LIKE is case-sensitive. ILIKE is the case-insensitive version (PostgreSQL-specific). MySQL uses collation to determine case sensitivity.
MCQ 16
What does CONCAT('Hello ', NULL, ' World') return in MySQL?
  • A. 'Hello World'
  • B. 'Hello World'
  • C. NULL
  • D. Error
Answer: C
C is correct. In MySQL's CONCAT, any NULL argument makes the whole result NULL. Use CONCAT_WS (skips NULLs) or wrap in COALESCE.
MCQ 17
Why is WHERE id NOT IN (SELECT student_id FROM banned) dangerous?
  • A. It is always slow
  • B. If banned.student_id has any NULL, the query returns 0 rows
  • C. NOT IN is banned in modern SQL
  • D. It does not use indexes
Answer: B
B is correct. NULL in the subquery result turns NOT IN into NULL comparisons, which WHERE drops. Use NOT EXISTS or filter NULLs in the subquery.
MCQ 18
What does NULL = NULL evaluate to in SQL?
  • A. TRUE (they are both NULL)
  • B. FALSE
  • C. NULL (unknown)
  • D. Error
Answer: C
C is correct. Any comparison involving NULL yields NULL — even NULL = NULL. This is three-valued logic. Use IS NULL / IS NOT NULL for NULL tests.
MCQ 19
Why might a pattern like LIKE '%abc' be slow on a million-row table?
  • A. % wildcard is computationally expensive
  • B. Leading % wildcard cannot use a B-tree index; requires full scan
  • C. MySQL throttles wildcard queries
  • D. LIKE is always slower than =
Answer: B
B is correct. B-tree indexes work left-to-right. A leading wildcard means MySQL does not know where to start, so it scans every row. Trailing wildcards (LIKE 'abc%') CAN use indexes. For leading-wildcard search at scale, use full-text indexes.
MCQ 20
What is the safest way to compute salary / bonus when bonus might be 0?
  • A. salary / bonus (let MySQL error out)
  • B. salary / IFNULL(bonus, 0)
  • C. salary / NULLIF(bonus, 0)
  • D. COALESCE(salary / bonus, 0)
Answer: C
C is correct. NULLIF(bonus, 0) returns NULL when bonus = 0. Dividing by NULL gives NULL (no error). In MySQL, divide-by-zero returns NULL already, but NULLIF makes intent explicit and is portable. Option B would still divide by 0 if bonus is 0.

Coding Challenges

Challenge 1: Gmail Finder

Easy
List all students with a Gmail address, showing name and email.
Sample Input
students table.
Sample Output
Aarav Sharma aarav@gmail.com Rohan Gupta rohan@gmail.com Sneha Iyer sneha@gmail.com Aditya Rao aditya@gmail.com
Use LIKE.
SELECT name, email FROM students WHERE email LIKE '%@gmail.com';

Challenge 2: Missing Data Report

Easy
List students with missing email OR missing marks. Show name and why they are missing data.
Sample Input
students table.
Sample Output
Ananya Singh, Vikram Reddy, Sneha Iyer (city missing), Ishita Kumar
Use IS NULL with OR. (Simpler version: just missing email OR marks, 4 rows.)
SELECT name,
  CASE
    WHEN email IS NULL AND marks IS NULL THEN 'Missing email and marks'
    WHEN email IS NULL THEN 'Missing email'
    WHEN marks IS NULL THEN 'Missing marks'
  END AS issue
FROM students
WHERE email IS NULL OR marks IS NULL;

Challenge 3: Name Length Filter

Medium
Find students whose name (first + last with space) is exactly 11 characters long.
Sample Input
students table.
Sample Output
Priya Patel, Rohan Gupta, Karan Mehta
Use LIKE with underscores.
SELECT name FROM students WHERE name LIKE '___________';  -- 11 underscores

Challenge 4: Clean Display

Medium
Return name, email (show 'N/A' if NULL), marks (show 0 if NULL), and city (show 'Unknown' if NULL).
Sample Input
students table.
Sample Output
All 10 students with NULL values replaced.
Use COALESCE.
SELECT
  name,
  COALESCE(email, 'N/A') AS email,
  COALESCE(marks, 0) AS marks,
  COALESCE(city, 'Unknown') AS city
FROM students;

Challenge 5: Complex Name Pattern

Medium
Find students whose name starts with 'A' or 'R' AND has at least 12 characters.
Sample Input
students table.
Sample Output
Aarav Sharma (12 chars), Ananya Singh (12), Rohan Gupta (11 - no), Aditya Rao (10 - no)... Actually checking: 'Aarav Sharma' = 12, 'Ananya Singh' = 12. Those pass.
Combine LIKE and length check.
SELECT name FROM students
WHERE (name LIKE 'A%' OR name LIKE 'R%')
  AND LENGTH(name) >= 12;

Challenge 6: Grade Report With Nulls

Medium
Show each student's name and grade: A (>=85), B (>=70), C (<70), Absent (NULL marks).
Sample Input
students table.
Sample Output
All 10 students with computed grade.
Use CASE with IS NULL first.
SELECT name, marks,
  CASE
    WHEN marks IS NULL THEN 'Absent'
    WHEN marks >= 85 THEN 'A'
    WHEN marks >= 70 THEN 'B'
    ELSE 'C'
  END AS grade
FROM students;

Challenge 7: Safe Division

Hard
Given a students_test table with attempted_questions and correct_questions, compute accuracy = correct / attempted, safely handling cases where attempted = 0. Write the SELECT.
Sample Input
Hypothetical table; use NULLIF pattern.
Sample Output
accuracy column is NULL when attempted = 0, otherwise ratio.
Use NULLIF to avoid divide-by-zero.
SELECT
  student_id,
  correct_questions,
  attempted_questions,
  correct_questions / NULLIF(attempted_questions, 0) AS accuracy
FROM students_test;

Challenge 8: Pattern + NULL Combo

Hard
Find students who (a) have a gmail email, (b) have non-NULL marks, (c) scored above 70, AND (d) are from a city starting with 'M' or 'D'.
Sample Input
students table.
Sample Output
Aarav Sharma (Mumbai, gmail, 85), Rohan Gupta (Delhi, gmail, 91)
Combine LIKE, IS NOT NULL, comparison.
SELECT name, email, marks, city
FROM students
WHERE email LIKE '%@gmail.com'
  AND marks IS NOT NULL
  AND marks > 70
  AND (city LIKE 'M%' OR city LIKE 'D%');

Need to Review the Concepts?

Go back to the detailed notes for this chapter.

Read Chapter Notes

Want to master SQL and databases with a mentor?

Explore our MySQL Masterclass