SQL WHERE Clause, Aliasing and DISTINCT (CBSE Class 12 Computer Science - 083)

After learning SQL operators, the next step is to retrieve only the required data from a table. SQL provides the WHERE clause to filter records, the AS keyword to assign temporary names (aliases), and the DISTINCT keyword to display unique values.

Learning Objectives

• Use the WHERE clause to filter records.
• Rename columns using aliases.
• Retrieve unique values using DISTINCT.
• Write SQL queries using operators with WHERE.

---

Sample Student Table

StudentID	Name	Gender	Class	Section	City	Marks	DeptID
101	Riya	F	XII	A	Jaipur	95	1
102	Aman	M	XII	B	Delhi	91	2
103	Priya	F	XII	A	Jaipur	88	1
104	Rahul	M	XII	C	Mumbai	76	3
105	Neha	F	XII	B	Delhi	84	2
106	Arjun	M	XII	A	Jaipur	92	1
107	Simran	F	XII	C	Mumbai	81	3
108	Karan	M	XII	B	Delhi	69	2

---

WHERE Clause

The WHERE clause is used to filter records based on one or more conditions. Only the records that satisfy the specified condition are returned.

---

Syntax

SELECT column_name
FROM table_name
WHERE condition;

---

Example 1 : Display Students from Jaipur

SELECT *
FROM Student
WHERE City='Jaipur';

Output

StudentID	Name	City	Marks
101	Riya	Jaipur	95
103	Priya	Jaipur	88
106	Arjun	Jaipur	92

---

Example 2 : Students Scoring More Than 90 Marks

SELECT Name, Marks
FROM Student
WHERE Marks > 90;

---

Example 3 : Female Students

SELECT Name
FROM Student
WHERE Gender='F';

---

Example 4 : Students from Delhi with Marks Above 80

SELECT *
FROM Student
WHERE City='Delhi'
AND Marks>80;

---

Example 5 : Students from Jaipur or Mumbai

SELECT Name, City
FROM Student
WHERE City='Jaipur'
OR City='Mumbai';

---

Example 6 : Students Not Belonging to Delhi

SELECT *
FROM Student
WHERE NOT City='Delhi';

---

Aliasing (AS)

Sometimes column names displayed in the result are lengthy or not user-friendly. SQL provides the AS keyword to assign a temporary name (alias) to a column or expression.

---

Syntax

SELECT column_name AS alias_name
FROM table_name;

---

Example 1 : Rename Column

SELECT Name AS Student_Name,
Marks AS Percentage
FROM Student;

Output

Student_Name	Percentage
Riya	95
Aman	91
Priya	88

---

Example 2 : Alias with Mathematical Expression

SELECT Name,
Marks+5 AS BonusMarks
FROM Student;

---

Example 3 : Alias Without Using AS

The AS keyword is optional.

SELECT Name Student_Name,
Marks Percentage
FROM Student;

Although SQL allows aliases without the AS keyword, using AS makes queries easier to read.

---

DISTINCT Clause

The DISTINCT keyword is used to display only unique (non-duplicate) values from a column.

---

Syntax

SELECT DISTINCT column_name
FROM table_name;

---

Example 1 : Display Unique Cities

SELECT DISTINCT City
FROM Student;

Output

City
Jaipur
Delhi
Mumbai

---

Example 2 : Display Unique Sections

SELECT DISTINCT Section
FROM Student;

---

Example 3 : Display Unique Department IDs

SELECT DISTINCT DeptID
FROM Student;

---

Difference Between SELECT and SELECT DISTINCT

SELECT	SELECT DISTINCT
Displays all matching records.	Displays only unique values.
Duplicate values are shown.	Duplicate values are removed.

---

Using WHERE with DISTINCT

SELECT DISTINCT City
FROM Student
WHERE Marks>80;

---

Using Alias with WHERE

Aliases are used only in the output. They cannot generally be used in the WHERE clause of the same SELECT statement.

Correct Query

SELECT Marks AS Percentage
FROM Student
WHERE Marks>90;

---

Execution Flow

SELECT
   │
   ▼
WHERE
   │
   ▼
DISTINCT
   │
   ▼
DISPLAY RESULT

---

Comparison of Clauses

Clause	Purpose
WHERE	Filters records.
AS	Assigns temporary names to columns.
DISTINCT	Removes duplicate values.

---

Common Errors

• Forgetting quotation marks around text values.
• Using = instead of > or < when required.
• Assuming DISTINCT sorts the data.
• Trying to use an alias inside the WHERE clause.
• Using DISTINCT on unnecessary columns.

---

Exam Tips

---

Frequently Asked Questions (FAQs)

1. What is the purpose of the WHERE clause?

The WHERE clause filters records according to specified conditions.

2. What is an alias?

An alias is a temporary name assigned to a column or expression using the AS keyword.

3. What is the purpose of DISTINCT?

DISTINCT displays only unique values by removing duplicate records from the selected column.

4. Can an alias permanently change a column name?

No. An alias changes only the column heading in the query result.

5. Can WHERE and DISTINCT be used together?

Yes. Records are first filtered using WHERE, and then duplicate values are removed using DISTINCT.

---

Summary

• The WHERE clause filters records based on specified conditions.
• The AS keyword assigns temporary names to columns or expressions.
• The DISTINCT keyword removes duplicate values from the result.
• WHERE is commonly used with relational and logical operators.
• These clauses improve the accuracy, readability, and usefulness of SQL query results.