Scribd
Upload
3 views3 pages

Module-2 (SQL Set Operations Aggregate Functions)

The document explains set operations in SQL, including UNION, UNION ALL, INTERSECT, and EXCEPT, which are used to combine results from multiple SELECT statements. It also covers aggregate functions like COUNT, SUM, AVG, MAX, and MIN that perform calculations on groups of rows, along with examples of their usage. Additionally, it discusses using GROUP BY and HAVING clauses with aggregate functions to filter results.

Uploaded by

tanveela1
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views3 pages

Module-2 (SQL Set Operations Aggregate Functions)

The document explains set operations in SQL, including UNION, UNION ALL, INTERSECT, and EXCEPT, which are used to combine results from multiple SELECT statements. It also covers aggregate functions like COUNT, SUM, AVG, MAX, and MIN that perform calculations on groups of rows, along with examples of their usage. Additionally, it discusses using GROUP BY and HAVING clauses with aggregate functions to filter results.

Uploaded by

tanveela1
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Set Operations and Aggregate

Functions in SQL
1. Set Operations in SQL
Set operations are used to combine the results of two or more SELECT statements into a
single result set.

Types of Set Operations

1. UNION
Combines the results of two or more SELECT statements and removes duplicate rows.

Syntax:

SELECT column1, column2 FROM table1


UNION
SELECT column1, column2 FROM table2;

Example:

SELECT city FROM customers


UNION
SELECT city FROM suppliers;

2. UNION ALL
Combines results of multiple SELECT statements and includes duplicate rows.

Syntax:

SELECT column1 FROM table1


UNION ALL
SELECT column1 FROM table2;

3. INTERSECT
Returns only the common rows between two queries.

Syntax:

SELECT column1 FROM table1


INTERSECT
SELECT column1 FROM table2;

4. EXCEPT
Returns rows from the first query that are not present in the second query.
Syntax:

SELECT column1 FROM table1


EXCEPT
SELECT column1 FROM table2;

Rules for Set Operations


The number of columns must be the same in both queries.

The data types of corresponding columns must be compatible.

The order of columns must be the same.

2. Aggregate Functions in SQL


Aggregate functions perform calculations on a group of rows and return a single
summarized value.

Common Aggregate Functions


COUNT() - Counts the number of rows.

SUM() - Calculates the total value of a numeric column.

AVG() - Calculates the average value.

MAX() - Returns the maximum value.

MIN() - Returns the minimum value.

Examples
COUNT Example:

SELECT COUNT(*) FROM employees;

SUM Example:

SELECT SUM(salary) FROM employees;

AVG Example:

SELECT AVG(salary) FROM employees;

MAX Example:

SELECT MAX(salary) FROM employees;

MIN Example:

SELECT MIN(salary) FROM employees;


Using Aggregate Functions with GROUP BY
Example:

SELECT department_id, AVG(salary)


FROM employees
GROUP BY department_id;

Using HAVING with Aggregate Functions


Example:

SELECT department_id, AVG(salary)


FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

You might also like