Class 11 Informatics Practices Chapter 10 · 23 min read

🔍 Querying with SQL

Unit 3 · Ch 10 Sep 30, 2026
Chapter Quiz

You've created a table and filled it with records. Now comes the most useful part — asking questions of your data! 🔍 "Which students scored above 80?" "Who lives in Kolkata?" "List everyone by marks, highest first." In SQL, each such question is a query, and almost every query starts with SELECT. This chapter answers the BIG questions: How do we show only the columns and rows we want? How do operators, IN, BETWEEN and LIKE help us filter? How do we handle missing (NULL) values and sort the result? And how do we change or remove records with UPDATE and DELETE? Let's start asking! 🙋

💡 How to use these notes

Every query in this chapter runs on the SAME table — Student, shown below. Keep it in front of you and predict the output before you look at the answer. The most tested topics are WHERE with AND/OR, BETWEEN, IN, LIKE with % and _, IS NULL, ORDER BY, DISTINCT, and UPDATE / DELETE. "Write the query" and "write the output" questions come from every one of these!

10.1 🏁 Our Sample Table — Student

SELECT * FROM Student;
+--------+-------+--------+------------+-------+------+---------+
| RollNo | Name  | Gender | Stream     | Marks | Fee  | City    |
+--------+-------+--------+------------+-------+------+---------+
|      1 | Riya  | F      | Science    |    92 | 4500 | Kolkata |
|      2 | Aman  | M      | Commerce   |    78 | 4000 | Delhi   |
|      3 | Zoya  | F      | Science    |    85 | 4500 | Mumbai  |
|      4 | Kabir | M      | Humanities |    64 | 3500 | Kolkata |
|      5 | Meera | F      | Commerce   |  NULL | 4000 | Pune    |
|      6 | Arjun | M      | Science    |    71 | 4500 | Delhi   |
|      7 | Sana  | F      | Humanities |    88 | 3500 | NULL    |
|      8 | Rohit | M      | Commerce   |    55 | 4000 | Mumbai  |
+--------+-------+--------+------------+-------+------+---------+
🧠 Look carefully at the NULLs 🧠

Meera's Marks and Sana's City are NULL — unknown. They'll behave in surprising ways later in this chapter!

10.2 🔍 The SELECT Statement

SELECT is used to fetch (retrieve) data from a table. It shows the result on the screen — it never changes the table.

SELECT  column1, column2, ...     ← WHICH columns to show
FROM    table_name                ← FROM which table
WHERE   condition                 ← WHICH rows (optional)
ORDER BY column;                  ← in what order (optional)

Analogy: SELECT is like asking the school office clerk 🧑‍💼: "Show me the names and marks (SELECT) from the Class 11 register (FROM) of only the students from Delhi (WHERE), arranged alphabetically (ORDER BY)."

10.2.1 Selecting All Columns — *

SELECT * FROM Student; shows every column of every row — you saw it above. The * means "all columns".

10.2.2 Selecting Particular Columns

List the column names, separated by commas, in the order you want them shown:

SELECT Name, Marks FROM Student;
+-------+-------+
| Name  | Marks |
+-------+-------+
| Riya  |    92 |
| Aman  |    78 |
| Zoya  |    85 |
| Kabir |    64 |
| Meera |  NULL |
| Arjun |    71 |
| Sana  |    88 |
| Rohit |    55 |
+-------+-------+
⚠ The column names must be spelt correctly!

SELECT Nmae FROM Student; gives ERROR 1054: Unknown column 'Nmae'.

10.3 🧮 Expressions and Column Aliases

10.3.1 Arithmetic in SELECT

We can do calculations on columns with + - * / %. The table itself doesn't change — only the displayed result.

SELECT Name, Fee, Fee * 12 FROM Student;
+-------+------+----------+
| Name  | Fee  | Fee * 12 |
+-------+------+----------+
| Riya  | 4500 |    54000 |
| Aman  | 4000 |    48000 |
| Zoya  | 4500 |    54000 |
| Kabir | 3500 |    42000 |
| Meera | 4000 |    48000 |
| Arjun | 4500 |    54000 |
| Sana  | 3500 |    42000 |
| Rohit | 4000 |    48000 |
+-------+------+----------+

10.3.2 Column Alias — AS 🏷️

The heading Fee * 12 looks ugly. An alias gives a column a temporary new heading in the output, using AS:

SELECT Name, Fee * 12 AS AnnualFee FROM Student;
+-------+-----------+
| Name  | AnnualFee |
+-------+-----------+
| Riya  |     54000 |
| Aman  |     48000 |
| Zoya  |     54000 |
| Kabir |     42000 |
| Meera |     48000 |
| Arjun |     54000 |
| Sana  |     42000 |
| Rohit |     48000 |
+-------+-----------+

If the alias has a space, put it in quotes:

SELECT Name AS "Student Name", Marks + 5 AS "After Grace" FROM Student WHERE RollNo <= 3;
+--------------+-------------+
| Student Name | After Grace |
+--------------+-------------+
| Riya         |          97 |
| Aman         |          83 |
| Zoya         |          90 |
+--------------+-------------+
🧠 NULL in calculations 🧠

Any calculation with NULL gives NULL. NULL + 5 is still NULL — "unknown plus 5" is still unknown! (That's why Meera's result would be NULL.)

10.4 🎯 DISTINCT — Removing Duplicates

DISTINCT shows each value only once, removing repeated values from the result.

SELECT Stream FROM Student;
+------------+
| Stream     |
+------------+
| Science    |
| Commerce   |
| Science    |
| Humanities |
| Commerce   |
| Science    |
| Humanities |
| Commerce   |
+------------+
SELECT DISTINCT Stream FROM Student;
+------------+
| Stream     |
+------------+
| Science    |
| Commerce   |
| Humanities |
+------------+

Analogy: You ask everyone in class their favourite colour — 30 answers but many repeats. DISTINCT gives you just the list of different colours! 🎨

⚠ DISTINCT applies to the whole row of selected columns

SELECT DISTINCT Stream, Fee removes rows where both Stream AND Fee repeat together. Also, NULL counts as one value — SELECT DISTINCT City shows NULL once.

10.5 🚦 WHERE — Choosing Rows

WHERE picks only those rows that satisfy a condition.

SELECT * FROM Student WHERE Marks > 80;
+--------+------+--------+------------+-------+------+---------+
| RollNo | Name | Gender | Stream     | Marks | Fee  | City    |
+--------+------+--------+------------+-------+------+---------+
|      1 | Riya | F      | Science    |    92 | 4500 | Kolkata |
|      3 | Zoya | F      | Science    |    85 | 4500 | Mumbai  |
|      7 | Sana | F      | Humanities |    88 | 3500 | NULL    |
+--------+------+--------+------------+-------+------+---------+

10.5.1 Relational (Comparison) Operators ⚖️

Operator Meaning Example
= Equal to City = 'Delhi'
> / < Greater / less than Marks > 80
>= / <= Greater / less than or equal to Fee <= 4000
<> or != Not equal to Stream <> 'Science' 
SELECT Name, City FROM Student WHERE City = 'Kolkata';
+-------+---------+
| Name  | City    |
+-------+---------+
| Riya  | Kolkata |
| Kabir | Kolkata |
+-------+---------+
SELECT Name, Stream FROM Student WHERE Stream <> 'Science';
+-------+------------+
| Name  | Stream     |
+-------+------------+
| Aman  | Commerce   |
| Kabir | Humanities |
| Meera | Commerce   |
| Sana  | Humanities |
| Rohit | Commerce   |
+-------+------------+
⚠ In SQL, "equal to" is ONE = sign!

Python uses == to compare, but SQL uses a single =. Also, text must be in quotes: City = 'Kolkata', not City = Kolkata.

10.5.2 Logical Operators — AND, OR, NOT 🧠

Operator Row is shown when…
AND Both conditions are true
OR At least one condition is true
NOT The condition is false 
SELECT Name, Stream, Marks FROM Student
WHERE Stream = 'Science' AND Marks > 80;
+------+---------+-------+
| Name | Stream  | Marks |
+------+---------+-------+
| Riya | Science |    92 |
| Zoya | Science |    85 |
+------+---------+-------+
SELECT Name, City FROM Student
WHERE City = 'Delhi' OR City = 'Mumbai';
+-------+--------+
| Name  | City   |
+-------+--------+
| Aman  | Delhi  |
| Zoya  | Mumbai |
| Arjun | Delhi  |
| Rohit | Mumbai |
+-------+--------+
SELECT Name, Gender FROM Student WHERE NOT Gender = 'M';
+-------+--------+
| Name  | Gender |
+-------+--------+
| Riya  | F      |
| Zoya  | F      |
| Meera | F      |
| Sana  | F      |
+-------+--------+
⚠ AND is checked before OR — use brackets!

WHERE Stream = 'Science' OR Stream = 'Commerce' AND Marks > 75 means Science OR (Commerce AND Marks > 75). If you mean "Science or Commerce students with marks above 75", write: WHERE (Stream = 'Science' OR Stream = 'Commerce') AND Marks > 75

📋 Typical question

"Write a query to display the names and fees of female students paying more than 4000." — 1 mark Answer:

SELECT Name, Fee FROM Student WHERE Gender = 'F' AND Fee > 4000;

10.6 📏 BETWEEN — A Range of Values

BETWEEN a AND b picks values from a to b, both included.

SELECT Name, Marks FROM Student WHERE Marks BETWEEN 70 AND 85;
+-------+-------+
| Name  | Marks |
+-------+-------+
| Aman  |    78 |
| Zoya  |    85 |
| Arjun |    71 |
+-------+-------+

It is a short way of writing Marks >= 70 AND Marks <= 85. Use NOT BETWEEN for values outside the range:

SELECT Name, Marks FROM Student WHERE Marks NOT BETWEEN 60 AND 90;
+-------+-------+
| Name  | Marks |
+-------+-------+
| Riya  |    92 |
| Rohit |    55 |
+-------+-------+
🔑 BETWEEN includes BOTH ends

BETWEEN 70 AND 85 includes 70 and 85. Always write the smaller value first — BETWEEN 85 AND 70 returns no rows!

10.7 📋 IN — Matching a List of Values

IN (list) picks rows where the value matches any value in the list.

SELECT Name, City FROM Student WHERE City IN ('Delhi', 'Pune', 'Mumbai');
+-------+--------+
| Name  | City   |
+-------+--------+
| Aman  | Delhi  |
| Zoya  | Mumbai |
| Meera | Pune   |
| Arjun | Delhi  |
| Rohit | Mumbai |
+-------+--------+

This is a short way of writing City = 'Delhi' OR City = 'Pune' OR City = 'Mumbai'. NOT IN picks the rest:

SELECT Name, City FROM Student WHERE City NOT IN ('Delhi', 'Mumbai');
+-------+---------+
| Name  | City    |
+-------+---------+
| Riya  | Kolkata |
| Kabir | Kolkata |
| Meera | Pune    |
+-------+---------+
⚠ NOT IN skips NULL rows too!

Sana's City is NULL, so she is not shown by NOT IN above — MySQL can't say that "unknown" is not Delhi. To find NULLs, use IS NULL (see 10.9).

Analogy: IN is like a guest list at a party 🎉 — "Only Delhi, Pune and Mumbai people may enter." Everyone whose city is on the list gets in!

10.8 🔤 LIKE — Pattern Matching

LIKE searches for a pattern in text, using two wildcard characters:

✳️

% (percent)

Stands for ANY number of characters — zero one or many

Any length
➖

_ (underscore)

Stands for EXACTLY ONE character

Exactly one
Pattern Matches Examples
'R%' Starts with R Riya, Rohit
'%a' Ends with a Riya, Zoya, Meera, Sana
'%ab%' Contains "ab" anywhere Kabir
'_o%' Second letter is o Zoya, Rohit
'____' (4 underscores) Exactly 4 letters Riya, Aman, Zoya, Sana 
SELECT Name FROM Student WHERE Name LIKE 'R%';
+-------+
| Name  |
+-------+
| Riya  |
| Rohit |
+-------+
SELECT Name FROM Student WHERE Name LIKE '_o%';
+-------+
| Name  |
+-------+
| Zoya  |
| Rohit |
+-------+
SELECT Name FROM Student WHERE Name LIKE '____';
+------+
| Name |
+------+
| Riya |
| Aman |
| Zoya |
| Sana |
+------+
SELECT Name, City FROM Student WHERE City LIKE '%i';
+-------+--------+
| Name  | City   |
+-------+--------+
| Aman  | Delhi  |
| Zoya  | Mumbai |
| Arjun | Delhi  |
| Rohit | Mumbai |
+-------+--------+
🧠 Memory Trick: "% is a BAG, _ is a BOX" 🧠

% is a bag 👜 — it can hold nothing, one thing or many things. _ is a small box 📦 — it holds exactly one thing!

⚠ LIKE needs wildcards — and = doesn't understand them!

WHERE Name = 'R%' looks for a name that is literally "R%". Use LIKE 'R%'. And without any wildcard, LIKE 'Riya' is the same as = 'Riya'.

📋 Typical question

"Write a query to display the names of students whose name has 'e' as the second letter." — 1 mark Answer:

SELECT Name FROM Student WHERE Name LIKE '_e%';

(Result: Meera)

10.9 🚫 Handling NULL — IS NULL and IS NOT NULL

NULL means unknown or missing. It is not zero and not a blank space. You cannot compare it with =.

SELECT Name, Marks FROM Student WHERE Marks = NULL;
Empty set (0.00 sec)

😲 Nothing! Because "unknown = unknown" is not TRUE. Use IS NULL instead:

SELECT Name, Marks FROM Student WHERE Marks IS NULL;
+-------+-------+
| Name  | Marks |
+-------+-------+
| Meera |  NULL |
+-------+-------+
SELECT Name, City FROM Student WHERE City IS NOT NULL AND Stream = 'Humanities';
+-------+---------+
| Name  | City    |
+-------+---------+
| Kabir | Kolkata |
+-------+---------+
🔑 The NULL rule

Always write IS NULL or IS NOT NULL. Never = NULL or <> NULL — they never match any row.

Analogy: NULL is like a sealed envelope ✉️ — you don't know what's inside. You can't say it "equals" another sealed envelope; you can only say "this envelope is sealed" (IS NULL)!

10.10 🔃 ORDER BY — Sorting the Result

ORDER BY arranges the result in ascending (ASC) order — the default — or descending (DESC) order.

SELECT Name, Marks FROM Student WHERE Marks IS NOT NULL ORDER BY Marks DESC;
+-------+-------+
| Name  | Marks |
+-------+-------+
| Riya  |    92 |
| Sana  |    88 |
| Zoya  |    85 |
| Aman  |    78 |
| Arjun |    71 |
| Kabir |    64 |
| Rohit |    55 |
+-------+-------+
SELECT Name, City FROM Student WHERE City IS NOT NULL ORDER BY City, Name;
+-------+---------+
| Name  | City    |
+-------+---------+
| Aman  | Delhi   |
| Arjun | Delhi   |
| Kabir | Kolkata |
| Riya  | Kolkata |
| Rohit | Mumbai  |
| Zoya  | Mumbai  |
| Meera | Pune    |
+-------+---------+
🧠 Sorting on two columns 🧠

ORDER BY City, Name sorts by City first; when two rows have the same city, they are sorted by Name. (Aman and Arjun are both from Delhi — so they're arranged A-m before A-r.)

⚠ ORDER BY always comes LAST!

The order of clauses is fixed: SELECT → FROM → WHERE → ORDER BY. Writing ORDER BY before WHERE gives a syntax error.

🧠 Where do NULLs go? 🧠

In MySQL, NULLs come first in ascending order and last in descending order.

10.11 ✏️ UPDATE — Changing Records

UPDATE changes the values in existing rows.

UPDATE table_name
SET    column1 = value1, column2 = value2, ...
WHERE  condition;
UPDATE Student SET Marks = 80 WHERE RollNo = 5;
SELECT Name, Marks FROM Student WHERE RollNo = 5;
+-------+-------+
| Name  | Marks |
+-------+-------+
| Meera |    80 |
+-------+-------+

Changing many rows at once — increase the fee of all Science students by 500:

UPDATE Student SET Fee = Fee + 500 WHERE Stream = 'Science';
SELECT Name, Stream, Fee FROM Student WHERE Stream = 'Science';
+-------+---------+------+
| Name  | Stream  | Fee  |
+-------+---------+------+
| Riya  | Science | 5000 |
| Zoya  | Science | 5000 |
| Arjun | Science | 5000 |
+-------+---------+------+
⚠ Forgot WHERE? Every row changes! 😱

UPDATE Student SET Marks = 0; sets the marks of ALL students to 0. Always double-check the WHERE clause before running an UPDATE.

10.12 🗑️ DELETE — Removing Records

DELETE removes rows from a table. The table and its structure remain.

DELETE FROM table_name WHERE condition;
DELETE FROM Student WHERE Marks < 60;
SELECT RollNo, Name, Marks FROM Student;
+--------+-------+-------+
| RollNo | Name  | Marks |
+--------+-------+-------+
|      1 | Riya  |    92 |
|      2 | Aman  |    78 |
|      3 | Zoya  |    85 |
|      4 | Kabir |    64 |
|      5 | Meera |  NULL |
|      6 | Arjun |    71 |
|      7 | Sana  |    88 |
+--------+-------+-------+

Rohit (55 marks) is gone. Meera stays — her Marks is NULL, and NULL is not "less than 60".

⚠ DELETE FROM Student; with no WHERE removes ALL rows!

The table becomes empty but still exists — you can insert new rows into it. DROP TABLE Student; would remove the table itself.

DELETE DROP TABLE
DML command DDL command
Removes rows (all or some) Removes the whole table
Table structure remains Structure and data are gone
Can use WHERE No WHERE
📋 Typical question

"Differentiate between DELETE and DROP TABLE." — 2 marks Answer: DELETE is a DML command that removes some or all rows of a table; the table structure remains and a WHERE clause can choose which rows to delete. DROP TABLE is a DDL command that removes the entire table — both its structure and its data.

🧾 Query Clauses at a Glance

Clause / Operator Purpose Example
SELECT * All columns SELECT * FROM Student;
SELECT col1, col2 Chosen columns SELECT Name, Marks …
AS Column alias Fee * 12 AS AnnualFee
DISTINCT Remove duplicates SELECT DISTINCT Stream …
WHERE Choose rows WHERE Marks > 80
AND / OR / NOT Combine conditions WHERE Gender = 'F' AND Fee > 4000
BETWEEN a AND b Range (both ends included) WHERE Marks BETWEEN 70 AND 85
IN (…) Match a list WHERE City IN ('Delhi', 'Pune')
LIKE Pattern: % any, _ one WHERE Name LIKE 'R%'
IS NULL / IS NOT NULL Check missing values WHERE Marks IS NULL
ORDER BY … ASC/DESC Sort the result ORDER BY Marks DESC
UPDATE … SET … WHERE Change values UPDATE Student SET Marks = 80 WHERE RollNo = 5;
DELETE FROM … WHERE Remove rows DELETE FROM Student WHERE Marks < 60;

⚠️ Common Errors and Misconceptions

Mistake What's Wrong Correct Understanding
WHERE City = Delhi Text without quotes WHERE City = 'Delhi'
WHERE Marks == 80 SQL uses a single = WHERE Marks = 80
WHERE Marks = NULL Never matches any row WHERE Marks IS NULL
WHERE Name = 'R%' = doesn't use wildcards WHERE Name LIKE 'R%'
BETWEEN 90 AND 60 Smaller value must come first BETWEEN 60 AND 90
"BETWEEN excludes the end values" Both ends are included 70 and 85 are included in BETWEEN 70 AND 85
_ matches any number of characters That's % _ matches exactly one character
ORDER BY written before WHERE Clause order is fixed SELECT → FROM → WHERE → ORDER BY
UPDATE or DELETE without WHERE Affects every row Always add the right WHERE condition
"DELETE removes the table" It removes rows only DROP TABLE removes the table
"SELECT changes the table" SELECT only displays Only UPDATE / DELETE / INSERT change data

🔑 Quick Revision — Exam Ready!

  • SELECT → fetch data; * = all columns; never changes the table
  • Clause order → SELECT … FROM … WHERE … ORDER BY …
  • Expressions → + - * / % on columns; NULL in a calculation gives NULL
  • AS → alias (temporary heading); quotes if it has a space
  • DISTINCT → removes duplicate values from the result
  • WHERE → filter rows; = > < >= <= <> !=
  • AND (both), OR (either), NOT (reverse); AND before OR — use brackets
  • BETWEEN a AND b → range, both ends included; NOT BETWEEN
  • IN (list) → matches any value in the list; NOT IN
  • LIKE → % any number of characters, _ exactly one
  • IS NULL / IS NOT NULL → never = NULL
  • ORDER BY col ASC/DESC → ASC is default; many columns allowed
  • UPDATE t SET col = value WHERE … → changes values
  • DELETE FROM t WHERE … → removes rows; table remains
  • DELETE (DML, rows) vs DROP TABLE (DDL, whole table)

🎯 Sample Exam Questions

Use the Student table from 10.1 for all questions.

Q1: Very Short Answer [1 mark each]

a) Which keyword removes duplicate values from the output? Answer: DISTINCT

b) Which wildcard stands for exactly one character? Answer: _ (underscore)

c) Which clause is used to sort the result? Answer: ORDER BY

d) What is the default sort order of ORDER BY? Answer: Ascending (ASC)

e) Which operator is used to check for a missing value? Answer: IS NULL

Q2: Write Queries [1 mark each]

a) Display the names and streams of students from Kolkata.

SELECT Name, Stream FROM Student WHERE City = 'Kolkata';

b) Display all details of students whose fee is between 3500 and 4000.

SELECT * FROM Student WHERE Fee BETWEEN 3500 AND 4000;

c) Display the names of students whose name ends with 'a', in alphabetical order.

SELECT Name FROM Student WHERE Name LIKE '%a' ORDER BY Name;

d) Display the different cities, without repetition.

SELECT DISTINCT City FROM Student;

e) Increase the marks of Kabir by 5.

UPDATE Student SET Marks = Marks + 5 WHERE Name = 'Kabir';

Q3: Output Based [2 marks]

Write the output of:

SELECT Name, Marks FROM Student WHERE Gender = 'M' AND Marks >= 70;

Answer:

+-------+-------+
| Name  | Marks |
+-------+-------+
| Aman  |    78 |
| Arjun |    71 |
+-------+-------+

Q4: Output Based [2 marks]

Write the output of:

SELECT Name, Fee / 100 AS Units FROM Student WHERE City IN ('Mumbai', 'Pune') ORDER BY Name;

Answer:

+-------+---------+
| Name  | Units   |
+-------+---------+
| Meera | 40.0000 |
| Rohit | 40.0000 |
| Zoya  | 45.0000 |
+-------+---------+

(In MySQL, / gives a decimal result, shown here with 4 decimal places.)

Q5: Output Based [2 marks]

Write the output of:

SELECT Name FROM Student WHERE Name LIKE '%r%' OR City IS NULL;

Answer:

+-------+
| Name  |
+-------+
| Riya  |
| Kabir |
| Meera |
| Arjun |
| Sana  |
| Rohit |
+-------+

Kabir, Meera and Arjun contain r. Riya and Rohit also match, because LIKE is not case sensitive in MySQL — '%r%' matches a capital R too. Sana is shown because her City is NULL.

Q6: Short Answer [2 marks]

What is the difference between WHERE Marks = NULL and WHERE Marks IS NULL?

Answer: WHERE Marks = NULL returns no rows, because NULL (unknown) cannot be compared using =. WHERE Marks IS NULL correctly returns the rows whose Marks value is missing — here, Meera.

✏️ Practice Problems

Use the Student table from 10.1.

  1. Display the RollNo and Name of all Commerce students.
  2. Display the names of students who are not from Delhi. (Think: will Sana be shown?)
  3. Display Name and Marks + 10 with the heading "Revised Marks".
  4. Display the names of students whose marks are between 60 and 80, in descending order of marks.
  5. Display the details of students from Kolkata, Pune or Delhi using IN.
  6. Display the names of students whose name starts with 'A' and has exactly 4 letters.
  7. Display the names of students whose city is not known.
  8. Display the different streams in alphabetical order.
  9. Change Sana's city to 'Chennai'.
  10. Delete the records of Humanities students who scored less than 70.
  11. Write the output of SELECT Name FROM Student WHERE Fee = 4500 AND Gender = 'M';
  12. What is the difference between BETWEEN and IN? Give an example of each.
Take the Chapter 10 Quiz Custom Quiz
Learning Support

Need Help With This Chapter?

Save key topics for exam revision, ask questions to teachers, or submit content corrections.

Verified Doubts & Teacher Answers

Take the Chapter 10 Quiz