LIKE Patterns, NULL Handling and ORDER BY in SQL (Class 12 Computer Science)

Understand LIKE, IS NULL and ORDER BY

SELECT Name FROM EMPLOYEE WHERE Name LIKE 'K%';        -- starts with K
SELECT Name FROM EMPLOYEE WHERE City LIKE '%ai';        -- city ends with ai
SELECT Name FROM EMPLOYEE WHERE Name LIKE '_a%';        -- second letter is a

SELECT Name FROM EMPLOYEE WHERE Salary IS NULL;
SELECT Name FROM EMPLOYEE WHERE Salary IS NOT NULL;

SELECT Name, Salary FROM EMPLOYEE ORDER BY Salary DESC;
SELECT Name, DeptID, Salary FROM EMPLOYEE ORDER BY DeptID, Salary DESC;

LIKE, IS NULL and ORDER BY: key facts

Wildcards

• •% : zero or more characters
• •_ : exactly one character
• •LIKE pattern in single quotes
• •NOT LIKE reverses

Name LIKE 'K%': starts with K.

Testing NULL

• •IS NULL
• •IS NOT NULL
• •= NULL gives no rows
• •NULL is not zero or empty

Comparison with NULL is never true.

ORDER BY

ASC	low to high, the default
DESC	high to low
Several columns	first column first, ties by the next
	Written after WHERE

NULL comes first in ascending order.

Clause order

• •SELECT
• •FROM
• •WHERE
• •GROUP BY
• •HAVING
• •ORDER BY

ORDER BY is last.

How to answer LIKE, IS NULL and ORDER BY questions

1. 1Translate the pattern: letters must match exactly; % takes any run (even none); _ takes one character.
2. 2Apply to each value; mark the matching rows.
3. 3Handle NULL: IS NULL picks the missing rows; every other comparison leaves them out.
4. 4Apply WHERE first, then sort the surviving rows with ORDER BY (first key, then ties by the second key).
5. 5Remember DESC applies to the column it follows only.
6. 6Write the output in the sorted order with headings.

LIKE, IS NULL and ORDER BY in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Tricks to remember LIKE, IS NULL and ORDER BY

Short memory aids. Say each one aloud once, then use it on a question the same day.

• % many, _ one

  Percent can take many, underscore takes exactly one.

  Use it when: Reading patterns.

• IS for NULL

  NULL is never "equal"; it only "is".

  Use it when: NULL tests.

• ASC is the default

  If no word is written, it is ascending.

  Use it when: ORDER BY questions.

• First key first

  Sort by the first column, and only break ties with the next.

  Use it when: Multi-column sort.

LIKE, IS NULL and ORDER BY: solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

Where students lose marks in LIKE, IS NULL and ORDER BY

The slips examiners see most often, and what to write instead.

• ✗Mistake: Using = NULL

  A comparison with NULL is never true.

  Wrong working

  WHERE Salary = NULL

  Right working

  WHERE Salary IS NULL

  ✓Do this: Say IS NULL aloud.

  Usually costs: 1 mark

• ✗Mistake: Confusing % and _

  % takes any number of characters; _ takes one.

  Wrong working

  LIKE 'K_' to find Kiran Shah

  Right working

  LIKE 'K%'

  ✓Do this: Count the characters allowed.

  Usually costs: 1 mark

• ✗Mistake: Placing ORDER BY before WHERE

  ORDER BY is the last clause.

  Wrong working

  SELECT Name FROM EMPLOYEE ORDER BY Name WHERE Salary > 40000;

  Right working

  SELECT Name FROM EMPLOYEE WHERE Salary > 40000 ORDER BY Name;

  ✓Do this: Follow the clause order SELECT, FROM, WHERE, ORDER BY.

  Usually costs: 1 mark

Practice questions on LIKE, IS NULL and ORDER BY

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

1. Q1 · 1 mark · MCQBoard level

   The default order of ORDER BY is:
   (a) descending (b) ascending (c) random (d) by primary key

   Hint

   It sorts low to high unless told otherwise.

   Answer

   (b) ascending

2. Q2 · 1 mark · Very short answerBoard level

   Which operator is used to check whether a column has no value?

   Hint

   Not an equals sign.

   Answer

   IS NULL

3. Q3 · 3 marks · Short answerHarder board level

   Write the output of SELECT Name, City, Salary FROM EMPLOYEE WHERE City <> 'Delhi' ORDER BY Salary DESC;

   Hint

   Sort the non-Delhi rows; NULL is last in descending order.

   Answer

   Meena Iyer | Chennai | 61000
   Farah Khan | Mumbai | 45000
   Ravi Kumar | Mumbai | 38000
   Kiran Shah | Pune | NULL

4. Q4 · 1 mark · Assertion-reasonHarder board level

   Assertion (A): In the pattern '_a%', the second character must be a.
   Reason (R): The underscore matches exactly one character and % matches any number of characters.
   (a) Both A and R are true and R is the correct explanation of A. (b) Both A and R are true but R is not the correct explanation of A. (c) A is true but R is false. (d) A is false but R is true.

   Hint

   The underscore holds the first place.

   Answer

   (a) Both are true and R explains A.

5. Q5 · 3 marks · Short answerHOTS / competency

   Write a query that shows the names of the Mumbai employees with a non-NULL salary in descending order of name, and give its output. Then explain why this query would return 6 rows if the WHERE clause were removed.

   Hint

   City LIKE or = both work.

   Answer

   SELECT Name FROM EMPLOYEE WHERE City = 'Mumbai' AND Salary IS NOT NULL ORDER BY Name DESC; Output: Ravi Kumar, then Farah Khan. Without WHERE the query would return all six employees, since no condition filters any row.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1. 1.What does % match in LIKE?Show answer

   Zero or more characters.

2. 2.What does _ match in LIKE?Show answer

   Exactly one character.

3. 3.How is a missing value tested?Show answer

   IS NULL or IS NOT NULL.

4. 4.What is the default sort order?Show answer

   Ascending.

5. 5.Where does NULL appear in ascending order?Show answer

   First (MySQL).

6. 6.Which clause is last in a SELECT query?Show answer

   ORDER BY.

LIKE, IS NULL and ORDER BY: frequently asked questions