SQL Notes
Aggregate Functions & Set Operations
Aggregate Functions in SQL
Aggregate functions perform calculations on a group of values and return a single result. They
are commonly used with the SELECT statement and often combined with the GROUP BY
clause. They help in summarizing data such as finding totals, averages, maximum, and minimum
values.
Types of Aggregate Functions
1. COUNT()
The COUNT() function is used to count the number of rows in a table.
Syntax:
SELECT COUNT(column_name)
FROM table_name;
Example:
SELECT COUNT(*)
FROM Student;
Explanation: This query counts the total number of students in the Student table.
2. SUM()
The SUM() function is used to calculate the total of a numeric column.
Syntax:
SELECT SUM(column_name)
FROM table_name;
Example:
SELECT SUM(salary)
FROM Employee;
Explanation: This query calculates the total salary of all employees.
3. AVG()
The AVG() function is used to calculate the average value of a numeric column.
Syntax:
SELECT AVG(column_name)
FROM table_name;
Example:
SELECT AVG(marks)
FROM Student;
Explanation: This query calculates the average marks of students.
4. MAX()
The MAX() function is used to find the maximum value in a column.
Syntax:
SELECT MAX(column_name)
FROM table_name;
Example:
SELECT MAX(marks)
FROM Student;
Explanation: This query returns the highest marks in the Student table.
5. MIN()
The MIN() function is used to find the minimum value in a column.
Syntax:
SELECT MIN(column_name)
FROM table_name;
Example:
SELECT MIN(marks)
FROM Student;
Explanation: This query returns the lowest marks in the Student table.
Set Operations in SQL
Set operations are used to combine the results of two or more SELECT statements into a single
result set. These operations work like mathematical set operations and require that the SELECT
statements have:
• The same number of columns
• Similar data types
• Columns in the same order
Set operations are mainly used to merge or compare data from multiple tables.
Types of Set Operations in SQL
1. UNION
The UNION operator combines the results of two SELECT queries and removes duplicate
records.
Syntax:
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;
Example:
SELECT name FROM Student
UNION
SELECT name FROM Teacher;
Explanation: This query returns the names from both Student and Teacher tables, but duplicate
names are removed.
2. UNION ALL
The UNION ALL operator also combines results of two queries, but it does not remove
duplicate records.
Syntax:
SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;
Example:
SELECT name FROM Student
UNION ALL
SELECT name FROM Teacher;
Explanation: This query returns all names from both tables, including duplicates.
3. INTERSECT
The INTERSECT operator returns only the common records present in both SELECT queries.
Syntax:
SELECT column_name FROM table1
INTERSECT
SELECT column_name FROM table2;
Example:
SELECT name FROM Student
INTERSECT
SELECT name FROM Teacher;
Explanation: This query returns only those names that exist in both Student and Teacher tables.
4. EXCEPT (or MINUS)
The EXCEPT operator returns records from the first query that are not present in the second
query. (In Oracle it is called MINUS.)
Syntax:
SELECT column_name FROM table1
EXCEPT
SELECT column_name FROM table2;
Example:
SELECT name FROM Student
EXCEPT
SELECT name FROM Teacher;
Explanation: This query returns names that are in the Student table but not in the Teacher table.