Home » DBMS

Nested Queries, Correlated Nested Queries and Set Comparison Operators in DBMS

In this tutorial, we are going to learn about the Aggregate Operators, GROUP BY and HAVING clause in DBMS.
Submitted by Anushree Goswami, on June 14, 2019

Aggregate Operators

To calculate aggregate values, one requires some aggregate operators to perform this task. These operators run over the columns of a relation. The total number of five aggregate operators is supported by SQL and these are:

  1. COUNT – To calculate the number of values present in a particular column.
  2. SUM – To calculate the sum of all the values present in a particular column.
  3. AVG – To calculate the average of all the values present in a particular column.
  4. MAX – To find the maximum of all the values present in a particular column.
  5. MIN – To find the minimum of all the values present in a particular column.

Mostly, with COUNT, SUM, and AVG, DISTINCT is specified in conjunction in order to eliminate any duplicate data. But for MAX and MIN, DISTINCT is not required to be specified as that will anyway not going to affect the output.

For example: Find the average salary of all the employees.

SELECT AVG (E.salary)
FROM Employee E

GROUP BY Clause

GROUP BY Clause is used to group the attributes with similar features under the given condition.

Let us consider a table of student.

ID Name Marks Section
1 Shiv 89 A
2 Parth 78 B
3 Ankush 95 A
4 Nimish 83 B

Question: Find the highest marks of the student for each section.

To find the highest marks section wise, we need to write two queries as:

SELECT MAX (S.Marks)
FROM Student S
WHERE S.Section = 'A'

SELECT MAX (S.Marks)
FROM Student S
WHERE S.Section = 'B'

As such, if we have many sections, we have to write the query that number of time. This looks quite lengthy.

GROUP BY clause made the solution easier as we don't require writing the queries the number of times of section, Instead, we write:

SELECT MAX (S.Marks)
FROM Student S
GROUP BY S.Section

In this way, writing just one query, we will get the expected output.

ID Name Marks Section
3 Ankush 95 A
4 Nimish 83 B

HAVING Clause

HAVING Clause determines whether the answer needs to be generated for a given condition or not.

Let us consider the previous example.

ID Name Marks Section
1 Shiv 89 A
2 Parth 78 B
3 Ankush 95 A
4 Nimish 83 B

Question: Find the highest marks of the student for each section having marks greater than 90.

SELECT MAX (S.Marks)
FROM Student S
GROUP BY S.Section
HAVING S.Marks > 90

Thus, our output will be:

ID Name Marks Section
3 Ankush 95 A





Comments and Discussions

Ad: Are you a blogger? Join our Blogging forum.




Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing
» Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates


© https://www.includehelp.com some rights reserved.