Computer ScienceCBSE Class 12 · Structured Query Language (SQL)

SELECT with WHERE: Operators, Alias, DISTINCT, IN and BETWEEN (Class 12 Computer Science)

Updated 17 October 2026 · Fact-checked

SELECT chooses columns (or *, or expressions) from a table; WHERE keeps only the rows that satisfy a condition built with relational operators (=, <>, <, >, <=, >=) and logical operators (AND, OR, NOT). AS gives a column an alias, DISTINCT removes duplicates, IN tests membership in a list and BETWEEN tests an inclusive range.

Understand SELECT with WHERE: Operators, Aliases, DISTINCT, IN and BETWEEN

The examples use the EMPLOYEE table of the chapter (EmpID, Name, DeptID, Salary, City, DOJ).

EmpID	Name	DeptID	Salary	City	DOJ
E01	Asha Rao	D1	52000	Delhi	2019-04-12
E02	Ravi Kumar	D2	38000	Mumbai	2021-07-01
E03	Meena Iyer	D1	61000	Chennai	2017-01-20
E04	Joy Das	D3	45000	Delhi	2022-11-15
E05	Kiran Shah	D2	NULL	Pune	2023-02-10
E06	Farah Khan	D3	45000	Mumbai	2020-09-05

Basic forms.

SELECT * FROM EMPLOYEE;
SELECT Name, Salary FROM EMPLOYEE;
SELECT Name, Salary * 12 AS Annual FROM EMPLOYEE WHERE City = 'Delhi';
SELECT DISTINCT DeptID FROM EMPLOYEE;

* means all columns.
An expression with the mathematical operators (+, -, *, /, %) can appear in the column list.
AS gives the column a temporary alias for the output heading (the stored column is not renamed).
DISTINCT removes duplicate rows from the result, so DeptID shows D1, D2 and D3 once each.

WHERE with relational operators. =, <> (or !=), <, <=, >, >=.
Text and dates are in single quotes; dates compare as 'YYYY-MM-DD'.

Logical operators. AND (both true), OR (either true), NOT (reverses). AND is applied before OR, so use brackets when you want OR first:

SELECT Name FROM EMPLOYEE WHERE City = 'Pune' OR City = 'Delhi' AND Salary > 50000;
SELECT Name FROM EMPLOYEE WHERE (City = 'Pune' OR City = 'Delhi') AND Salary > 50000;

The first returns Asha Rao and Kiran Shah (Kiran qualifies through the Pune test alone); the second returns Asha Rao only.

IN and BETWEEN. City IN ('Delhi', 'Pune') is true when the city is any listed value (a shorter form of OR); NOT IN reverses it. Salary BETWEEN 40000 AND 52000 is true for 40000 and 52000 as well, because both limits are included.

NULL rows. A comparison with NULL is never true, so Kiran Shah (Salary NULL) is not returned by Salary > 45000, Salary <= 45000, Salary <> 45000 or by a BETWEEN. Use IS NULL for that, covered in the next topic with LIKE and ORDER BY.

Rows appear in table order unless ORDER BY is used.

SELECT with WHERE: Operators, Aliases, DISTINCT, IN and BETWEEN: key facts

Relational operators: Equal =
Not equal <>
Less or greater < <= > >=
Text and dates in single quotes; Comparison with NULL is never true.
Logical operators: AND both conditions true
OR at least one true
NOT reverses the condition
AND is evaluated before OR; Use brackets to force OR first.
IN and BETWEEN: IN (list) value equals any listed item
NOT IN none of them
BETWEEN a AND b from a to b, both included; NOT BETWEEN excludes the range.
Alias and DISTINCT: AS gives a temporary heading
DISTINCT removes duplicate rows
the star symbol selects all columns; The alias does not rename the column in the table.

Equal	=
Not equal	<>
Less or greater	< <= > >=
	Text and dates in single quotes

AND	both conditions true
OR	at least one true
NOT	reverses the condition
	AND is evaluated before OR

IN (list)	value equals any listed item
NOT IN	none of them
BETWEEN a AND b	from a to b, both included

How to answer SELECT with WHERE: Operators, Aliases, DISTINCT, IN and BETWEEN questions

For an SQL output question, evaluate the WHERE row by row, then keep only the selected columns.

1Write the table rows and mark which satisfy the WHERE condition; remember NULL never satisfies a comparison.
2Apply AND before OR when both appear; use the brackets if present.
3For IN and BETWEEN, check the list items and both limits (included).
4Apply the column list: pick the columns, compute the expressions and use the alias as the heading.
5Apply DISTINCT last: drop repeated output rows.
6Write the result as a table with a heading, in table order unless ORDER BY is used.

SELECT with WHERE: Operators, Aliases, DISTINCT, IN and BETWEEN 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.

Salary slip report

Suppose an accountant wants the yearly pay of the Delhi employees from the monthly salary column.

Write the query and give its result for Asha Rao (52000) and Joy Das (45000).

Try it first, then show the solutionSolution

Multiply the monthly salary by 12 in the column list and name it with AS.
52000 x 12 = 624000; 45000 x 12 = 540000.

Answer: SELECT Name, Salary * 12 AS Annual FROM EMPLOYEE WHERE City = 'Delhi'; gives Asha Rao 624000 and Joy Das 540000.

Memory hook: Alias is a name tag worn only for the report.

Which cities?

Suppose the manager wants the list of different cities where employees are posted, each city once.

Write the query and give the result.

Try it first, then show the solutionSolution

Select the City column with DISTINCT so that Delhi and Mumbai are not repeated.
Cities in the table: Delhi, Mumbai, Chennai, Pune.

Answer: SELECT DISTINCT City FROM EMPLOYEE; gives Delhi, Mumbai, Chennai, Pune.

Memory hook: DISTINCT lists each different item once.

Tricks to remember SELECT with WHERE: Operators, Aliases, DISTINCT, IN and BETWEEN

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

AND before OR
AND binds tighter, like multiplication before addition.
Use it when: Mixed logical conditions.
BETWEEN includes both ends
From and to are both inside the range.
Use it when: BETWEEN questions.
IN = a short OR
City IN (a, b) means City = a OR City = b.
Use it when: Rewriting conditions.
NULL fails every test
A NULL row is never selected by a comparison.
Use it when: Missing rows in output.

SELECT with WHERE: Operators, Aliases, DISTINCT, IN and BETWEEN: solved examples

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

Example 1: DISTINCT · 1 mark · MCQBoard level

Which keyword is used in SELECT to remove duplicate values from the result?
(a) UNIQUE (b) DISTINCT (c) ONLY (d) DIFFERENT

Try it first, then show the solutionSolution

DISTINCT shows each different value once.

Answer: (b) DISTINCT

Example 2: Write two queries · 2 marks · Short answerBoard level

Write queries on EMPLOYEE to (i) display all details of the employees of Delhi, (ii) display the Name and Salary of the employees of department D3.

Try it first, then show the solutionSolution

(i) SELECT * FROM EMPLOYEE WHERE City = 'Delhi'; (1 mark)
(ii) SELECT Name, Salary FROM EMPLOYEE WHERE DeptID = 'D3'; (1 mark)

Answer: See the two queries above.

Example 3: Expression and alias · 3 marks · Short answerHarder board level

Write the output of: SELECT Name, Salary * 12 AS Annual FROM EMPLOYEE WHERE City = 'Delhi';

Try it first, then show the solutionSolution

Rows with City Delhi: E01 Asha Rao (52000) and E04 Joy Das (45000). (1 mark)
Annual = 52000 x 12 = 624000 and 45000 x 12 = 540000. (1 mark)
Heading Name, Annual. (1 mark)

Answer: Name | Annual
Asha Rao | 624000
Joy Das | 540000

Example 4: IN and BETWEEN · 3 marks · Short answerHarder board level

Write the output of (i) SELECT Name, City FROM EMPLOYEE WHERE City IN ('Delhi', 'Pune'); (ii) SELECT Name, Salary FROM EMPLOYEE WHERE Salary BETWEEN 40000 AND 52000;

Try it first, then show the solutionSolution

(i) Asha Rao Delhi; Joy Das Delhi; Kiran Shah Pune. (1.5 marks)
(ii) Asha Rao 52000; Joy Das 45000; Farah Khan 45000. Both 52000 and 40000 are included; Kiran (NULL) is not. (1.5 marks)

Answer: (i) 3 rows: Asha Rao, Joy Das, Kiran Shah (ii) 3 rows: Asha Rao 52000, Joy Das 45000, Farah Khan 45000

Example 5: Case: HR requests · 4 marks · Case-basedHOTS / competency

Suppose the HR manager asks for these reports from EMPLOYEE. Write the output of each query.
(i) SELECT DISTINCT DeptID FROM EMPLOYEE WHERE Salary >= 45000; (1 mark)
(ii) SELECT Name FROM EMPLOYEE WHERE City = 'Pune' OR City = 'Delhi' AND Salary > 50000; (1 mark)
(iii) SELECT Name FROM EMPLOYEE WHERE (City = 'Pune' OR City = 'Delhi') AND Salary > 50000; (1 mark)
(iv) SELECT Name, Salary + 5000 AS NewSal FROM EMPLOYEE WHERE DeptID IN ('D2', 'D3') AND City <> 'Delhi'; (1 mark)

Try it first, then show the solutionSolution

(i) Salaries 52000, 61000, 45000, 45000 qualify: departments D1 and D3 (each shown once).
(ii) AND first: Delhi and above 50000 gives Asha Rao; OR Pune gives Kiran Shah. Result: Asha Rao, Kiran Shah.
(iii) Brackets first: Asha Rao qualifies; Kiran Shah has NULL salary, so the comparison fails. Result: Asha Rao.
(iv) D2 or D3 employees not in Delhi: Ravi Kumar (Mumbai), Kiran Shah (Pune), Farah Khan (Mumbai). NewSal: 43000, NULL, 50000.

Answer: (i) D1, D3 (ii) Asha Rao, Kiran Shah (iii) Asha Rao (iv) Ravi Kumar 43000; Kiran Shah NULL; Farah Khan 50000

Where students lose marks in SELECT with WHERE: Operators, Aliases, DISTINCT, IN and BETWEEN

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

Mistake: Forgetting that AND is evaluated before OR
The result changes when the brackets are missing.
Wrong working
Pune OR Delhi AND Salary > 50000 read as (Pune OR Delhi) AND Salary > 50000: Asha Rao only
Right working
Pune OR (Delhi AND Salary > 50000): Asha Rao and Kiran Shah
Do this: Insert brackets in your head: AND pairs first.
Usually costs: 1 mark
Mistake: Excluding a boundary of BETWEEN
BETWEEN includes both limits.
Wrong working
Salary BETWEEN 40000 AND 52000 excludes 52000
Right working
The result includes 52000
Do this: Treat BETWEEN as >= and <=.
Usually costs: 1 mark
Mistake: Comparing text without quotes
Text must be in single quotes.
Wrong working
WHERE City = Delhi
Right working
WHERE City = 'Delhi'
Do this: Quote text and dates.
Usually costs: 0.5 mark

Exam tips

Write the output as a small table with the column headings; the alias is the heading.
Mark the qualifying rows on the source table before writing the answer.
NULL values never satisfy relational conditions: say so if a row seems to be missing.
Use brackets in your own queries to avoid AND/OR confusion.
DISTINCT applies to the whole selected row, not to a single column when several are listed.

Practice questions on SELECT with WHERE: Operators, Aliases, DISTINCT, IN and BETWEEN

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

Q1 · 1 mark · MCQBoard level
What does SELECT * FROM EMPLOYEE; display?
(a) the first row only (b) all the columns and all the rows (c) only the primary key (d) the number of rows
Hint
The star is for all columns.
Answer
(b) all the columns and all the rows
Q2 · 1 mark · Very short answerBoard level
Is the value 52000 included in the result of Salary BETWEEN 40000 AND 52000?
Hint
Think of the limits.
Answer
Yes, both limits are included.
Q3 · 2 marks · Short answerHarder board level
Write a query to display the EmpID and Name of employees whose salary is not between 40000 and 52000, and give the result.
Hint
NULL fails the test.
Answer
SELECT EmpID, Name FROM EMPLOYEE WHERE Salary NOT BETWEEN 40000 AND 52000; Result: E02 Ravi Kumar (38000) and E03 Meena Iyer (61000). Kiran Shah (NULL) is not returned.
Q4 · 1 mark · Assertion-reasonHarder board level
Assertion (A): SELECT Name FROM EMPLOYEE WHERE Salary <> 45000; returns Kiran Shah, whose salary is NULL.
Reason (R): A comparison with NULL is never true.
(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
Does NULL <> 45000 evaluate to true?
Answer
(d) A is false (Kiran is not returned) but R is true.
Q5 · 3 marks · Short answerHOTS / competency
The query SELECT Name, Salary + 1000 AS Raised FROM EMPLOYEE WHERE DeptID = 'D2'; is run on EMPLOYEE. (i) Write the output. (ii) Explain why one value is NULL. (iii) Rewrite the query so that it returns only the rows with a non-NULL Raised.
Hint
NULL plus 1000 is NULL.
Answer
(i) Ravi Kumar 39000; Kiran Shah NULL. (ii) Kiran Shah's Salary is NULL and any arithmetic with NULL gives NULL. (iii) Add AND Salary IS NOT NULL to the WHERE clause.

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.What does * mean in SELECT?Show answer
All columns.
2.Which keyword gives a column an alias?Show answer
AS.
3.Which keyword removes duplicates?Show answer
DISTINCT.
4.Which comes first, AND or OR?Show answer
AND.
5.Are the limits included in BETWEEN?Show answer
Yes, both.
6.Does Salary > 45000 return a row with NULL salary?Show answer
No.

SELECT with WHERE: Operators, Aliases, DISTINCT, IN and BETWEEN: frequently asked questions

What is the use of the WHERE clause?

WHERE keeps only the rows that satisfy a condition, so the SELECT shows just the required records.

What is the difference between IN and BETWEEN?

IN checks whether a value matches any item in a list; BETWEEN checks whether a value falls in a range, with both ends included.

What does the AS keyword do?

It gives a column or expression a temporary name in the output; the stored column name is not changed.

Why is my NULL row missing from the output?

A comparison with NULL is never true, so such rows are left out. Use IS NULL or IS NOT NULL to test for NULL.