Where Clause:

• This is used to filter the Records.
• It is a powerful tool to filter and retrieve only specific rows you need from a Table.

Syntax:

SELECT column1, column2, … FROM table_name WHERE condition;

Example:

Retrieve the Records who are living in the Hyderbad city

SELECT * From Employee where City = ‘Hyderabad’;

Operators in Where clause:

We can use the following operators with WHERE Clause

=         Equal

<          less than

• Greater than

<=       less than or equal to

>=      greater than or equal to

<>      Not Equal to

BETWEEN   It used to retrieve certain range

LIKE    search for a pattern

IN         To specify multiple possible values for a column

Retrieve The Records who have salary above 20000 from Emp Table

SELECT * FROM Employee Where salary > 20000;

Distinct Clause:

It is used to eliminate duplicate rows from the results of a query, ensuring that each unique value appears only once.

Syntax:

SELECT DISTINCT column1, column2, … FROM table_name;

Example:

SELECT DISTINCT City FROM Employee;

Get a list of distinct employee last names and their job titles:

SELECT DISTINCT LastName, JobTitle FROM Employees;

ORDER BY Clause:

This is used to sort the results in ascending or descending order.

Examples:

Sort Employees by their last name in alphabetical order (ascending):

SELECT * FROM Employee ORDER BY LastName ASC;

Retrieve the top 10 highest-rated products, sorted by rating in descending order:

SELECT * FROM Products ORDER BY Rating DESC LIMIT 10;

Key points:

• ORDER BY allows you to organize data according to your needs, making it easier to analyze and interpret.
• You can sort by multiple columns, specifying the priority through their order in the clause.
• The default sorting order is ascending, but you can always specify DESC for descending order.
• Combining ORDER BY with other clauses like WHERE and LIMIT offers further control over your query results.

GROUP BY Clause:

This is used to group the rows that have same values into summary rows like “find the number of employees in each country”.

This clause often is used with aggregate functions like MAX(), MIN(), SUM(), AVG() etc..

Syntax:

SELECT column1, aggregate_function(column2)

FROM table_name

WHERE condition

GROUP BY column1;

Example 1:

Count the number of Employees in each city:

SELECT City, COUNT(*) AS EmployerCount

FROM Employee

GROUP BY City;

Example 2:

Calculate the average salary for each department:

SELECT Department, AVG(Salary) AS AverageSalary

FROM Employee

GROUP BY Department;

Having Clause:

This is used in the place of WHERE clause because we cannot use WHERE clause with aggregate functions.

Syntax:

SELECT column1, aggregate_function(column2)

FROM table_name

GROUP BY column1

HAVING condition;

Example 1: List departments with an average salary above 40,000:

SELECT Department, AVG(Salary) AS AverageSalary

FROM Employees

GROUP BY Department

HAVING AVG(Salary) > 40000;

Example 2: Identify departments where the highest salary exceeds 100,000:

SELECT Department, MAX(Salary) AS HighestSalary

FROM Employees

GROUP BY Department

HAVING MAX(Salary) > 100000;

Key Points:

• HAVING filters groups after GROUP BY, while WHERE filters individual rows before grouping.
• It’s often used with aggregate functions like COUNT, SUM, AVG, MAX, and MIN.
• You can combine multiple conditions in HAVING using logical operators.
• It’s useful for refining results and extracting specific insights from grouped data.
• Understanding its usage alongside GROUP BY is essential for effective SQL data analysis.