SQL Clauses

WHERE Clause

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

Example

SELECT *
FROM Employee
WHERE emp_id = 5;

SELECT *
FROM Employee
WHERE salary >= 50000;

---

Using Operators with WHERE

OR Operator

The OR operator returns rows if at least one of the conditions is true.

SELECT *
FROM Employee
WHERE dept = 'HR' OR dept = 'Finance';

---

AND Operator

The AND operator returns rows only if all specified conditions are true.

SELECT *
FROM Employee
WHERE dept = 'IT' AND salary > 50000;

---

IN Operator

The IN operator is a shorthand for multiple OR conditions. It makes queries shorter and easier to read.

Instead of writing:

SELECT *
FROM Employee
WHERE dept = 'IT'
   OR dept = 'HR'
   OR dept = 'Finance';

You can simply write:

SELECT *
FROM Employee
WHERE dept IN ('IT', 'HR', 'Finance');

---

BETWEEN

The BETWEEN operator is used to filter values within a specified range. The range is inclusive, meaning both boundary values are included.

SELECT *
FROM Employee
WHERE salary BETWEEN 50000 AND 65000;

Equivalent query:

SELECT *
FROM Employee
WHERE salary >= 50000
  AND salary <= 65000;

---

DISTINCT

The DISTINCT keyword returns only unique values by removing duplicate records from the selected column(s).

SELECT DISTINCT dept
FROM Employee;

---

ORDER BY

The ORDER BY clause sorts the result set based on one or more columns.

Sort in Ascending Order (Default)

SELECT *
FROM Employee
ORDER BY fname;

or

SELECT *
FROM Employee
ORDER BY fname ASC;

Sort in Descending Order

SELECT *
FROM Employee
ORDER BY fname DESC;

Sort by Multiple Columns

SELECT *
FROM Employee
ORDER BY dept ASC, salary DESC;

This first sorts employees by department (A-Z), and employees within the same department are sorted by salary from highest to lowest.

---

LIMIT

The LIMIT clause restricts the number of rows returned by the query.

Return the First 5 Rows

SELECT *
FROM Employee
LIMIT 5;

Skip the First 5 Rows and Return the Next 5

SELECT *
FROM Employee
LIMIT 5 OFFSET 5;

---

LIKE Operator

The LIKE operator is used to search for a specific pattern in text values. It is commonly used with two wildcard characters:

Wildcard	Meaning
%	Represents zero, one, or multiple characters
_	Represents exactly one character

---

Common LIKE Patterns

Starts with 'A'

SELECT *
FROM Employee
WHERE fname LIKE 'A%';

Examples:

• Alice
• Arif
• Anika

---

Ends with 'a'

SELECT *
FROM Employee
WHERE fname LIKE '%a';

Examples:

• Maria
• Suma
• Tina

---

Contains 'i'

SELECT *
FROM Employee
WHERE fname LIKE '%i%';

Examples:

• Nila
• Rifat
• Smith

---

Exactly Two Characters

SELECT *
FROM Employee
WHERE fname LIKE '__';

Examples:

• Al
• Jo
• Xi

Note: Use _ (underscore), not - (hyphen). Each underscore represents exactly one character.

---

Exactly Three Characters

SELECT *
FROM Employee
WHERE fname LIKE '___';

Examples:

• Sam
• Tom
• Joy

---

Starts with 'A' and Ends with 'n'

SELECT *
FROM Employee
WHERE fname LIKE 'A%n';

Examples:

• Arian
• Aron
• Asif Hasan

---

Second Character is 'a'

SELECT *
FROM Employee
WHERE fname LIKE '_a%';

Examples:

• Karim
• David
• Nabila

---

Starts with 'S' and Has Exactly Four Characters

SELECT *
FROM Employee
WHERE fname LIKE 'S___';

Examples:

• Sara
• Sumi
• Saye

---

Starts with 'A' and Has Exactly Three Characters

SELECT *
FROM Employee
WHERE fname LIKE 'A__';

Examples:

• Ali
• Apu
• Ara

---

Summary of LIKE Wildcards

Pattern	Meaning
A%	Starts with A
%A	Ends with A
%A%	Contains A anywhere
_	Exactly one character
__	Exactly two characters
___	Exactly three characters
A_	Starts with A and has exactly two characters
A__	Starts with A and has exactly three characters
_a%	Second character is 'a'
%an	Ends with "an"
A%n	Starts with A and ends with n

6