This blog explains the SQL clauses like WHERE, HAVING, ORDER BY, GROUP BY, and other related clauses using real-life examples with the employees and departments tables.

Table of Contents

1. Tables Structure
2. WHERE Clause
3. GROUP BY Clause
4. HAVING Clause
5. ORDER BY Clause
6. LIMIT Clause
7. DISTINCT Clause
8. AND, OR, NOT Operators

Tables Structure

employees Table

departments Table

WHERE Clause

The WHERE clause is used to filter records based on specified conditions.

SQL Query

SELECT name, age, salary
FROM employees
WHERE age > 30;

Result

Explanation: The WHERE clause filters the rows to include only employees who are older than 30 years.

Example with AND Operator

SELECT name, age, salary
FROM employees
WHERE age > 30 AND salary > 5000;

Result

Explanation: The WHERE clause filters employees who are older than 30 and have a salary greater than 5000.

GROUP BY Clause

The GROUP BY clause is used to group rows that have the same values into summary rows, like finding the number of employees in each department.

SQL Query

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

Result

Explanation: The GROUP BY clause groups employees by department_id and counts the number of employees in each department.

HAVING Clause

The HAVING clause is used to filter groups created by the GROUP BY clause. It works like the WHERE clause but is used after aggregation.

SQL Query

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5500;

Result

Explanation: The HAVING clause filters the groups based on the average salary of employees in each department. Only departments with an average salary greater than 5500 are included.

ORDER BY Clause

The ORDER BY clause is used to sort the result set by one or more columns. By default, it sorts in ascending order; to sort in descending order, use DESC.

SQL Query (Ascending Order)

SELECT name, salary
FROM employees
ORDER BY salary;

Result

Explanation: The result is sorted by salary in ascending order.

SQL Query (Descending Order)

SELECT name, salary
FROM employees
ORDER BY salary DESC;

Result

Explanation: The result is sorted by salary in descending order.

LIMIT Clause

The LIMIT clause is used to specify the number of records to return from the result set. This is particularly useful for paging or limiting large result sets.

SQL Query

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Result

Explanation: The LIMIT clause restricts the output to only the top 3 highest-paid employees.

DISTINCT Clause

The DISTINCT clause is used to return only distinct (different) values in a result set, removing duplicates.

SQL Query

SELECT DISTINCT department_id
FROM employees;

Result

Explanation: The DISTINCT clause returns unique department_id values, eliminating duplicates.

AND, OR, NOT Operators

The AND, OR, and NOT operators are used to combine multiple conditions in the WHERE clause.

AND Operator

The AND operator is used to combine two or more conditions. The result will include only rows where all conditions are true.

SELECT name, age, salary
FROM employees
WHERE age > 30 AND salary > 5500;

Result

Explanation: The WHERE clause filters rows where both conditions (age > 30 and salary > 5500) are true.

OR Operator

The OR operator is used when only one of the conditions must be true.

SELECT name, age, salary
FROM employees
WHERE age  7000;

Result

Explanation: The WHERE clause filters rows where either age 7000 is true.

NOT Operator

The NOT operator is used to exclude rows where a condition is true.

SELECT name, age, salary
FROM employees
WHERE NOT salary > 6000;

Result

Explanation: The WHERE clause filters rows where salary > 6000 is false, meaning it returns employees earning 6000 or less.

Conclusion

This blog explains how to filter, group and sort data using SQL’s WHERE, HAVING, ORDER BY, GROUP BY, and other clauses with real-life examples from the employees and departments tables. Understanding these clauses is fundamental for writing efficient SQL queries, analyzing data, and managing databases effectively.