Page 58 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 3: Structured Query Language

The number of duplicate tuples in the result is equal to the total number of duplicates that Notes
appear in both d and b. Thus, if Jones has three accounts and two loans at the bank, then there
will be five tuples with the name Jones in the result.

The Intersect Operation

To find all customers who have both a loan and an account at the bank, we write
(select distinct customer-name from depositor)
intersect (select distinct customer-name from borrower)

The intersect operation automatically eliminates duplicates. Thus, in the preceding query, if a
customer-say, Jones-has several accounts and loans at the bank, then Jones will appear only once
in the result.
If we want to retain all duplicates, we must write intersect all in place of intersect:
(select customer-name from depositor)
intersect all (select customer-name from borrower)
The number of duplicate tuples that appear in the result is equal to the minimum number of
duplicates in both d and b. Thus, if Jones has three accounts and two loans at the bank, then there
will be two tuples with the name Jones in the result.

3.10 Aggregate Functions

Different aggregate operators that SQL support are,

1. Count: COUNT followed by a column name returns the count of tuple in that column.
If DISTINCT keyword is used then it will return only the count of unique tuple in the
column. Otherwise, it will return count of all the tuples (including duplicates) count (*)
indicates all the tuples of the column.
2. SUM: SUM followed by a column name returns the sum of all the values in that columns.
If DISTINCT keyword is used then it will return the sum of all unique values in the
columns.

3. AVG: AVG followed by a column name returns the average value of that column values.
If DISTINCT keyword is used then it will return the average of distinct values only.
4. MAX: MAX followed by a column name returns the maximum value of that column.

5. MIN: Min followed by column name returns the minimum value of that column.
Queries Based on Aggregate Functions

Query (a): Find the sum of salaries of all the employees and also the minimum, maximum and
average salary.
Solution:
SELECT SUM(E.esal) AS sum_salary, MAX(E.esal) AS Max_salary, MIN(E.esal) AS Min_salary,
AVG([DISTINCT] E.esal) AS Average_salary
FROM Employee E.
This query calculates the total, minimum, maximum and average salaries and also renames the
column names.

LOVELY PROFESSIONAL UNIVERSITY 51