Learn the HAVING clause and ORDER BY clause in RDBMS with syntax, examples, differences from WHERE clause, practical SQL queries, and advantages. Complete SQL tutorial for students and beginners.
HAVING Clause and ORDER BY Clause in RDBMS
Having Clause in RDBMS:
Introduction
The HAVING clause is used in SQL to filter grouped records after applying aggregate functions such as SUM(), COUNT(), AVG(), MAX(), and MIN().
Unlike the WHERE clause, which filters individual rows, the HAVING clause filters groups created using the GROUP BY clause.
Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;
Key Points
- The HAVING clause is used only with GROUP BY.
- It works on aggregate values.
- It is executed after the GROUP BY clause.
- It cannot be replaced by WHERE when aggregate functions are involved.
Example
Find departments having more than 5 employees
SELECT dept, COUNT(emp_id)
FROM employee
GROUP BY dept
HAVING COUNT(emp_id) > 5;
Difference Between WHERE and HAVING
| WHERE | HAVING |
| Filters rows | Filters groups |
| Used before GROUP BY | Used after GROUP BY |
| Cannot use aggregate functions | Uses aggregate functions |
Advantages
- Filters summarized data
- Useful in reports and analysis
- Works with aggregate functions
ORDER BY Clause in RDBMS
Introduction
The ORDER BY clause is used to sort the result set in ascending (ASC) or descending (DESC) order based on one or more columns.
Syntax
SELECT column_name
FROM table_name
ORDER BY column_name ASC | DESC;
Key Points
- The default sorting order is ASC.
- Multiple columns can be used for sorting.
- It is used at the end of the SQL query.
- It works with numeric, string, and date data types.
Example
Display students sorted by marks in descending order
SELECT name, marks
FROM student
ORDER BY marks DESC;
Sorting by Multiple Columns
SELECT name, dept, salary
FROM employee
ORDER BY dept ASC, salary DESC;
Advantages
- Improves data readability
- Helps in ranking and reporting
- Useful for generating sorted outputs
Combined Example (HAVING + ORDER BY)
SELECT dept, AVG(salary)
FROM employee
GROUP BY dept
HAVING AVG(salary) > 30000
ORDER BY AVG(salary) DESC;
Conclusion
- The HAVING clause filters grouped data using aggregate functions.
- The ORDER BY clause sorts the final result set.
Both clauses are essential for data analysis, reporting, and decision-making in RDBMS.
Practical Queries Using HAVING and ORDER BY
Table: part
| PID | pname | color | price |
| 301 | Bolt | Red | 5.00 |
| 302 | Nut | Blue | 3.00 |
| 303 | Screw | Black | 2.00 |
| 304 | Washer | Red | 1.00 |
| 305 | Gear | Silver | 50.00 |
| 306 | Bearing | Grey | 80.00 |
| 307 | Wheel | Black | 120.00 |
| 308 | Rod | Blue | 40.00 |
| 309 | Pipe | Green | 70.00 |
| 310 | Plate | Red | 30.00 |
- Display the total price of parts for each color
SELECT color, SUM(price) AS total_price
FROM part
GROUP BY color;
Table: supplier
| SID | Sname | Branch | City | State | Phone |
- Find the branch and the number of suppliers in each branch where the number of suppliers is more than 1
SELECT Branch, COUNT(*) AS no_of_suppliers
FROM supplier
GROUP BY Branch
HAVING COUNT(*) >= 2;
- Find all parts sorted by name in ascending order
SELECT *
FROM part
ORDER BY pname ASC;
- Find all parts sorted by price in descending order
SELECT *
FROM part
ORDER BY price DESC;
- Find the branch and the number of suppliers in each branch (Analytical Query)
SELECT Branch, COUNT(*) AS no_of_suppliers
FROM supplier
GROUP BY Branch;
Some More:
- POP- Introduction to Programming Using ‘C’
- DS – Data structure Using C
- OOP – Object Oriented Programming
- Java Programming
- DBMS – Database Management System
- RDBMS – Relational Database Management System
- https://defineinfoloop.blogspot.com/?m=1