🎓 Velnov

🗄️ SQL · Topic 5 of 20

Operators, NULL & Patterns — SQL Questions with Answers

30 practice questions on Operators, NULL & Patterns, each with the correct answer and a clear explanation of why it is right. Read through them as revision notes, or take this topic as an interactive quiz to test yourself.

0 easy · 16 medium · 14 hard · Part of SQL: Databases from basics to window functions - 20 topics.

Key concepts in this topic

NULLMissing dataCOALESCEDefaultsDialectsNULLIFDivision by zeroAggregatesAVGCOUNTData qualityDISTINCTGROUP BYLIKEWildcardsESCAPEILIKECase sensitivityNOT LIKEBETWEENRangesArithmeticMODDivision

Jump to: Medium · Hard

Medium questions (16)

Q1. medium

What does NULL represent in SQL?

  1. A An UNKNOWN or missing value - not zero, not an empty string, not false
  2. B The number zero
  3. C An empty string
  4. D A deleted row

Answer: A — An UNKNOWN or missing value - not zero, not an empty string, not false

NULL means 'we do not know'. That is why arithmetic and comparisons involving NULL produce NULL rather than a definite answer.

NULLMissing data

Q3. medium

What does COALESCE(a, b, c) return?

  1. A The FIRST non-NULL value among its arguments
  2. B The sum of all values
  3. C Always the first argument
  4. D NULL if any argument is NULL

Answer: A — The FIRST non-NULL value among its arguments

COALESCE is the standard way to supply fallbacks: COALESCE(nickname, first_name, 'Guest'). It accepts any number of arguments.

COALESCEDefaults

Q4. medium

What does IFNULL(x, 0) / ISNULL(x, 0) / NVL(x, 0) do?

  1. A Returns 0 when x is NULL - the two-argument, dialect-specific version of COALESCE
  2. B Tests whether x is NULL
  3. C Deletes NULLs
  4. D Counts NULLs

Answer: A — Returns 0 when x is NULL - the two-argument, dialect-specific version of COALESCE

IFNULL is MySQL, ISNULL is SQL Server, NVL is Oracle. COALESCE works everywhere and is preferred for portable code.

COALESCEDialects

Q7. medium

What does COUNT(*) do with NULL rows?

  1. A Counts every row, including rows full of NULLs
  2. B Skips rows containing NULLs
  3. C Returns NULL
  4. D Errors

Answer: A — Counts every row, including rows full of NULLs

COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column. The difference between them tells you how many values are missing.

COUNTNULL

Q9. medium

How does DISTINCT treat multiple NULLs?

  1. A They collapse into ONE NULL in the result
  2. B Each NULL is kept separately
  3. C NULLs are removed
  4. D It errors

Answer: A — They collapse into ONE NULL in the result

For grouping and DISTINCT, NULLs are treated as 'the same unknown', even though NULL = NULL is not TRUE in comparisons. GROUP BY behaves the same way.

DISTINCTGROUP BYNULL

Q10. medium

Which pattern finds emails at gmail.com?

  1. A WHERE email LIKE '%@gmail.com'
  2. B WHERE email = '%@gmail.com'
  3. C WHERE email LIKE '@gmail.com'
  4. D WHERE email CONTAINS 'gmail'

Answer: A — WHERE email LIKE '%@gmail.com'

LIKE enables wildcards; = matches literally. The % before @gmail.com allows any username.

LIKEWildcards

Q12. medium

What does ILIKE do (PostgreSQL)?

  1. A Case-INSENSITIVE pattern matching
  2. B Inverted LIKE
  3. C Integer LIKE
  4. D Index-based LIKE

Answer: A — Case-INSENSITIVE pattern matching

PostgreSQL is case-sensitive by default, so ILIKE 'a%' matches both 'Ana' and 'ana'. Portable alternative: LOWER(name) LIKE 'a%'.

ILIKECase sensitivity

Q13. medium

What does NOT LIKE do?

WHERE email NOT LIKE '%@test.com'
   OR email IS NULL;
  1. A Matches rows that do NOT fit the pattern
  2. B Matches everything
  3. C Is invalid
  4. D Is the same as <>

Answer: A — Matches rows that do NOT fit the pattern

Remember rows where the column is NULL are excluded by NOT LIKE too - NULL never matches or 'not matches'.

NOT LIKENULL

Q15. medium

What does the modulo operator % (or MOD) return?

WHERE id % 2 = 0;   -- even ids
  1. A The remainder after division - useful for finding even/odd values
  2. B A percentage
  3. C A wildcard
  4. D Multiplication

Answer: A — The remainder after division - useful for finding even/odd values

Note the same symbol is a wildcard inside LIKE but an arithmetic operator elsewhere. Oracle uses MOD(a, b).

ArithmeticMOD

Q17. medium

How do you convert a value's type?

SELECT CAST('42' AS INTEGER) + 8;   -- 50
  1. A CAST(value AS INTEGER) - or the shorthand value::INTEGER in PostgreSQL
  2. B CONVERT_TYPE(value)
  3. C TYPE(value, INT)
  4. D Types cannot be converted

Answer: A — CAST(value AS INTEGER) - or the shorthand value::INTEGER in PostgreSQL

CAST is standard SQL. Casting matters when comparing or sorting values stored in the wrong type, e.g. numbers kept in a VARCHAR column.

CASTData types

Q20. medium

What does ANY / SOME do? WHERE price > ANY (SELECT price FROM budget_items)

  1. A TRUE if the comparison holds for AT LEAST ONE value returned by the subquery
  2. B TRUE only if it holds for all values
  3. C Returns any random row
  4. D Is invalid

Answer: A — TRUE if the comparison holds for AT LEAST ONE value returned by the subquery

> ANY means 'greater than the minimum'. Its counterpart ALL means the comparison must hold for every value, so > ALL means 'greater than the maximum'.

ANYALLSubqueries

Q22. medium

How do you concatenate text in standard SQL?

  1. A first || ' ' || last (or CONCAT(first, ' ', last))
  2. B first + last everywhere
  3. C first & last
  4. D CONCATENATE(first, last)

Answer: A — first || ' ' || last (or CONCAT(first, ' ', last))

|| is standard (PostgreSQL, Oracle, SQLite); SQL Server uses +; CONCAT works in most modern databases and often handles NULLs more gracefully.

ConcatenationDialects

Q24. medium

What does the CASE expression evaluate to when several WHEN conditions are true?

  1. A The result of the FIRST matching condition - evaluation stops there
  2. B All matching results
  3. C The last match
  4. D An error

Answer: A — The result of the FIRST matching condition - evaluation stops there

Like an if/else-if chain, order matters. Put the most specific conditions first, exactly as in the grade-boundary example.

CASEOrder

Q27. medium

What does the IS NOT DISTINCT FROM operator do?

  1. A Compares two values treating NULL as equal to NULL
  2. B The same as <>
  3. C Tests data types
  4. D Removes duplicates

Answer: A — Compares two values treating NULL as equal to NULL

Supported in PostgreSQL and some others, it solves the awkward case of comparing nullable columns without writing (a = b OR (a IS NULL AND b IS NULL)).

NULLComparison

Q28. medium

Which is TRUE about the empty string '' versus NULL?

  1. A They are different: '' is a known blank value, NULL is unknown (except in Oracle, which treats '' as NULL)
  2. B They are always identical
  3. C '' is invalid SQL
  4. D NULL is shorter to store

Answer: A — They are different: '' is a known blank value, NULL is unknown (except in Oracle, which treats '' as NULL)

Real datasets frequently contain both, so data-cleaning queries usually check for both: WHERE col IS NULL OR TRIM(col) = ''.

NULLEmpty stringOracle

Q30. medium

Which condition safely finds rows where a numeric column is missing OR zero?

  1. A WHERE COALESCE(amount, 0) = 0
  2. B WHERE amount = 0
  3. C WHERE amount = NULL OR 0
  4. D WHERE amount IS 0

Answer: A — WHERE COALESCE(amount, 0) = 0

COALESCE converts NULL to 0 first, so one condition covers both cases. (Note this prevents index use on amount - acceptable for reporting queries.)

COALESCENULL

Hard questions (14)

Q2. hard

What is the result of 100 + NULL?

SELECT salary + COALESCE(bonus, 0) AS total FROM staff;
  1. A NULL - any arithmetic with NULL yields NULL
  2. B 100
  3. C 0
  4. D An error

Answer: A — NULL - any arithmetic with NULL yields NULL

Unknown plus anything is still unknown. This silently propagates: a single NULL bonus can turn a computed total salary into NULL. Use COALESCE to substitute a default.

NULLCOALESCE

Q5. hard

What does NULLIF(a, b) return?

SELECT passed * 100.0 / NULLIF(total, 0) AS pct FROM stats;
  1. A NULL if a equals b, otherwise a
  2. B a if b is NULL
  3. C The first non-NULL value
  4. D TRUE or FALSE

Answer: A — NULL if a equals b, otherwise a

Its classic use is avoiding division by zero: total / NULLIF(count, 0) yields NULL instead of raising an error.

NULLIFDivision by zero

Q6. hard

Do aggregate functions like SUM and AVG include NULLs?

  1. A No - they IGNORE NULLs (which changes what AVG divides by)
  2. B Yes, treating them as 0
  3. C They return NULL if any value is NULL
  4. D They throw an error

Answer: A — No - they IGNORE NULLs (which changes what AVG divides by)

AVG(score) over values 10, 20, NULL is 15 (sum 30 divided by 2), not 10. If NULL should count as zero, write AVG(COALESCE(score, 0)).

  • A very common source of subtly wrong reports.

AggregatesNULLAVG

Q8. hard

What does this return when some emails are NULL? SELECT COUNT(*) - COUNT(email) FROM users;

  1. A The number of users whose email IS NULL
  2. B Zero always
  3. C The total user count
  4. D NULL

Answer: A — The number of users whose email IS NULL

A neat idiom for counting missing values without a separate WHERE clause. Equivalent to COUNT(*) FILTER (WHERE email IS NULL) where supported.

COUNTData quality

Q11. hard

How do you match a LITERAL percent sign with LIKE?

  1. A Use an escape character: LIKE '%50\%%' ESCAPE '\'
  2. B Use double %%
  3. C Put it in quotes
  4. D It is impossible

Answer: A — Use an escape character: LIKE '%50\%%' ESCAPE '\'

Without escaping, % is a wildcard. The ESCAPE clause designates a character that makes the next symbol literal - needed when searching for discounts like '50%'.

LIKEESCAPE

Q14. hard

What is the difference between BETWEEN and >= / <= ?

  1. A None functionally - BETWEEN is shorthand and is INCLUSIVE at both ends
  2. B BETWEEN excludes the endpoints
  3. C BETWEEN is faster
  4. D BETWEEN only works on numbers

Answer: A — None functionally - BETWEEN is shorthand and is INCLUSIVE at both ends

x BETWEEN 10 AND 20 is exactly x >= 10 AND x <= 20. For dates and timestamps the explicit form is safer because you can use a half-open range.

BETWEENRanges

Q16. hard

What is integer division in SQL? SELECT 3 / 2;

  1. A In many databases it returns 1 - integer inputs give an integer result
  2. B Always 1.5
  3. C An error
  4. D 0

Answer: A — In many databases it returns 1 - integer inputs give an integer result

PostgreSQL and SQL Server truncate; MySQL returns 1.5. To be safe, force decimals: 3 * 1.0 / 2 or CAST(3 AS DECIMAL) / 2.

DivisionType casting

Q18. hard

What does EXISTS test?

SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
  1. A Whether a subquery returns AT LEAST ONE row - it returns TRUE/FALSE, not data
  2. B Whether a table exists
  3. C Whether a column is NULL
  4. D Whether a value is in a list

Answer: A — Whether a subquery returns AT LEAST ONE row - it returns TRUE/FALSE, not data

EXISTS stops at the first matching row, so it is efficient. Unlike NOT IN, NOT EXISTS handles NULLs correctly - the safer choice for anti-joins.

EXISTSSubqueries

Q19. hard

Why prefer NOT EXISTS over NOT IN?

  1. A NOT IN returns NO rows if the subquery contains any NULL; NOT EXISTS handles NULLs correctly
  2. B NOT IN is invalid syntax
  3. C NOT EXISTS is shorter
  4. D They are identical

Answer: A — NOT IN returns NO rows if the subquery contains any NULL; NOT EXISTS handles NULLs correctly

This silent-empty-result bug is a favourite interview topic. Either use NOT EXISTS, or add WHERE col IS NOT NULL inside the subquery.

NOT EXISTSNULLGotchas

Q21. hard

What does > ALL (SELECT ...) mean?

  1. A Greater than EVERY returned value - i.e. greater than the maximum
  2. B Greater than at least one
  3. C Greater than the average
  4. D Invalid syntax

Answer: A — Greater than EVERY returned value - i.e. greater than the maximum

Careful: if the subquery returns no rows, > ALL is TRUE by definition (vacuous truth), while > ANY would be FALSE.

ALLANY

Q23. hard

What happens with 'Hello' || NULL in most databases?

SELECT first || ' ' || COALESCE(middle || ' ', '') || last AS full_name;
  1. A The result is NULL - concatenating with NULL wipes the whole string
  2. B 'Hello'
  3. C 'HelloNULL'
  4. D An error

Answer: A — The result is NULL - concatenating with NULL wipes the whole string

This is why full-name columns sometimes come out blank when a middle name is missing. CONCAT() in MySQL/SQL Server treats NULL as an empty string, or wrap values in COALESCE.

NULLConcatenation

Q25. hard

How do you count rows matching a condition WITHOUT a WHERE clause?

SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) AS paid,
  SUM(CASE WHEN status='due'  THEN 1 ELSE 0 END) AS due
FROM invoices;
  1. A SUM(CASE WHEN condition THEN 1 ELSE 0 END)
  2. B COUNT(condition)
  3. C COUNT(*) WHERE condition
  4. D It is impossible

Answer: A — SUM(CASE WHEN condition THEN 1 ELSE 0 END)

This 'conditional aggregation' lets you compute several different counts in a single pass - the standard way to build summary reports.

Conditional aggregationCASE

Q26. hard

Why does COUNT(CASE WHEN x THEN 1 END) work without ELSE?

  1. A Non-matching rows produce NULL, and COUNT ignores NULLs
  2. B It counts zeros
  3. C ELSE is required
  4. D It errors

Answer: A — Non-matching rows produce NULL, and COUNT ignores NULLs

A neat alternative to SUM(...ELSE 0). Both are widely used; the COUNT version relies on NULL being skipped.

COUNTNULLCASE

Q29. hard

Why is a column full of NULLs a design smell?

  1. A It often signals a missing related table or an attribute belonging to a subtype
  2. B NULLs are always forbidden
  3. C NULLs use lots of storage
  4. D It is never a problem

Answer: A — It often signals a missing related table or an attribute belonging to a subtype

If only 2% of rows use a set of columns, those attributes may belong in a separate related table. Normalisation (topic 18) addresses exactly this.

DesignNormalization

Practice “Operators, NULL & Patterns” as a quiz