ORDER BY, GROUP BY, HAVING, and WHERE clauses
Sure! Let's start by creating a sample table and then go through ORDER BY, GROUP BY, HAVING, and WHERE clauses in detail with 10 examples.
Step 1: Create a Sample Table
We will create an employees table to work with.
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary INT,
experience INT
);
Step 2: Insert Sample Data
INSERT INTO employees (id, name, department, salary, experience) VALUES
(1, 'Alice', 'HR', 60000, 5),
(2, 'Bob', 'IT', 75000, 3),
(3, 'Charlie', 'IT', 65000, 7),
(4, 'David', 'Finance', 50000, 2),
(5, 'Eve', 'Finance', 70000, 6),
(6, 'Frank', 'IT', 80000, 8),
(7, 'Grace', 'HR', 45000, 4),
(8, 'Hank', 'IT', 90000, 9),
(9, 'Ivy', 'Finance', 60000, 3),
(10, 'Jack', 'HR', 55000, 6);
Step 3: Understanding Clauses
1. WHERE Clause
-
Filters individual rows before aggregation (
GROUP BY) happens. -
Cannot use aggregate functions (SUM, AVG, COUNT) in WHERE.
✅ Example: Get employees with salary greater than 60,000
SELECT * FROM employees
WHERE salary > 60000;
💡 This filters only those employees whose salary is more than 60,000 before aggregation.
2. GROUP BY Clause
-
Groups rows based on a column and allows performing aggregate functions (SUM, COUNT, AVG, etc.) on those groups.
-
Comes before HAVING and ORDER BY.
✅ Example: Find total salary for each department
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
💡 Groups all employees by department and calculates the total salary for each department.
3. HAVING Clause
-
Filters groups after
GROUP BYhas been applied. -
Can use aggregate functions in HAVING (e.g., SUM, AVG).
✅ Example: Find departments where total salary is more than 1,50,000
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 150000;
💡 Filters only the departments where total salary is above 1,50,000.
4. ORDER BY Clause
-
Sorts the final result in ascending (
ASC) or descending (DESC) order. -
Comes at the end of the query.
✅ Example: Get employees sorted by salary in descending order
SELECT * FROM employees
ORDER BY salary DESC;
💡 Sorts all employees based on salary from highest to lowest.
Step 4: 10 Practice Problems
Here are 10 SQL queries for you to solve and practice:
Basic Queries
1️⃣ Get all employees from the "IT" department.
SELECT * FROM employees WHERE department = 'IT';
2️⃣ Find employees with experience more than 5 years.
SELECT * FROM employees WHERE experience > 5;
3️⃣ Display employees sorted by experience in ascending order.
SELECT * FROM employees ORDER BY experience ASC;
Using GROUP BY and Aggregate Functions
4️⃣ Find the total salary per department.
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
5️⃣ Find the average salary per department.
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
6️⃣ Count the number of employees in each department.
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
Using HAVING Clause
7️⃣ Find departments where more than 2 employees work.
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;
8️⃣ Find departments where the total salary is greater than 1,50,000.
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 150000;
Combining WHERE, GROUP BY, HAVING, and ORDER BY
9️⃣ Find departments where the average salary is above 60,000, but only consider employees with more than 3 years of experience.
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE experience > 3
GROUP BY department
HAVING AVG(salary) > 60000;
🔟 Find the highest-paid employee per department and sort the results in descending order of salary.
SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department
ORDER BY highest_salary DESC;
Step 5: Understanding Clause Execution Order
SQL executes these clauses in the following order:
1️⃣ FROM (Select the table)
2️⃣ WHERE (Filter individual rows)
3️⃣ GROUP BY (Group rows together)
4️⃣ HAVING (Filter groups after aggregation)
5️⃣ SELECT (Choose what to display)
6️⃣ ORDER BY (Sort the final result)
Key Takeaways
✔ Use WHERE to filter rows before grouping.
✔ Use GROUP BY to aggregate data by column.
✔ Use HAVING to filter grouped results.
✔ Use ORDER BY to sort the final output.
Try running these queries and let me know if you have any doubts! 🚀
Comments
Post a Comment