Learn WHERE, Logical Operators, ORDER BY & LIMIT/TOP - Textnotes

Learn WHERE, Logical Operators, ORDER BY & LIMIT/TOP

Master SQL filtering and sorting with this beginner-friendly guide. Learn the WHERE clause, comparison operators (=, <>, >, <, >=, <=), logical operators (AND, OR, NOT), ORDER BY for sorting, and LIMIT/TOP to restrict results. Practice filtering employees by salary, sorting students by marks, and using multiple conditions.

1. Introduction

Filtering and sorting allow you to retrieve only the data you need and present it in a meaningful order.

Key Points:

  1. Filtering reduces the number of rows returned using conditions.
  2. Sorting organizes data in ascending or descending order.
  3. Logical operators allow combining multiple conditions.

2. The WHERE Clause

The WHERE clause filters rows based on specified conditions.

Syntax:


SELECT column1, column2
FROM table_name
WHERE condition;

Comparison Operators:

OperatorMeaningExample
=EqualSalary = 50000
<>Not equalDepartment <> 'IT'
>Greater thanSalary > 50000
<Less thanSalary < 60000
>=Greater or equalSalary >= 50000
<=Less or equalSalary <= 60000

Example:


-- Employees with salary greater than 50000
SELECT *
FROM Employees
WHERE Salary > 50000;

3. Logical Operators (AND, OR, NOT)

Combine multiple conditions using:

OperatorDescription
ANDBoth conditions must be true
OREither condition can be true
NOTNegates a condition

Examples:


-- Employees in IT with salary above 55000
SELECT *
FROM Employees
WHERE Department = 'IT' AND Salary > 55000;

-- Employees in HR or Finance
SELECT *
FROM Employees
WHERE Department = 'HR' OR Department = 'Finance';

-- Employees not in IT
SELECT *
FROM Employees
WHERE NOT Department = 'IT';

4. ORDER BY Clause

The ORDER BY clause sorts results by one or more columns. Default is ascending (ASC); use descending (DESC) for reverse order.

Syntax:


SELECT column1, column2
FROM table_name
ORDER BY column1 ASC|DESC;

Examples:


-- Sort employees by salary ascending
SELECT *
FROM Employees
ORDER BY Salary ASC;

-- Sort students by marks descending
SELECT *
FROM Students
ORDER BY Marks DESC;

5. LIMIT / TOP

Use LIMIT (MySQL/PostgreSQL) or TOP (SQL Server) to restrict the number of rows returned.

Examples:


-- MySQL/PostgreSQL
SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 5;

-- SQL Server
SELECT TOP 5 *
FROM Employees
ORDER BY Salary DESC;

6. Practical Exercises

  1. Filter employees with salary > 50000.
  2. Sort students by marks in descending order.
  3. Filter employees in the IT department with salary > 60000.
  4. Retrieve the top 3 highest-paid employees using LIMIT/TOP.
  5. Exclude employees from the HR department using NOT.

7. Tips for Beginners

  1. Test WHERE conditions carefully to avoid unexpected results.
  2. Use AND/OR operators to combine conditions logically.
  3. Always use ORDER BY for readable query output.
  4. Combine WHERE + ORDER BY + LIMIT/TOP for precise data retrieval.