SQL Aggregate Functions
An aggregate function is a function that performs a calculation on a set of values and returns a single
value.
Aggregate functions are often used with the GROUP BY clause of the SELECT statement. The GROUP
BY clause splits the result-set into groups of values, and the aggregate function can be used to return
a single value for each group.
The most commonly used SQL aggregate functions are:
MIN() - returns the smallest value within the selected column
MAX() - returns the largest value within the selected column
COUNT() - returns the number of rows in a set
SUM() - returns the total sum of a numerical column
AVG() - returns the average value of a numerical column
Aggregate functions ignore null values (except for COUNT(*)).
SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between
them.
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the
right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from
the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
The SQL GROUP BY Statement
The GROUP BY statement groups rows that have the same values into summary rows, like "find the
number of customers in each country".
The GROUP BY statement is often used with aggregate functions
(COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
GROUP BY Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
The SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate
functions.
Syntax : SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
Assignment 7:
🧱 Step 1: Create Tables
-- Department Table
CREATE TABLE Department (
dno INT PRIMARY KEY,
dname VARCHAR(50),
dloc VARCHAR(50)
);
-- Employee Table
CREATE TABLE Emp (
eno INT PRIMARY KEY,
ename VARCHAR(50),
designation VARCHAR(30),
salary DECIMAL(10,2),
dno INT,
FOREIGN KEY (dno) REFERENCES Department(dno)
);
🔗 Step 2: Queries with JOIN, GROUP BY, HAVING
1️⃣ Display the name of employee whose department
is at Pimpri location
SELECT e.ename
FROM Emp e
JOIN Department d ON e.dno = d.dno
WHERE d.dloc = 'Pimpri';
2️⃣ Display the name of manager from each department
SELECT d.dname, e.ename
FROM Emp e
JOIN Department d ON e.dno = d.dno
WHERE e.designation = 'Manager';
3️⃣ Count the number of employees in each department
SELECT d.dname, COUNT(e.eno) AS Employee_Count
FROM Emp e
JOIN Department d ON e.dno = d.dno
GROUP BY d.dname;
4️⃣ Display identical names of employees
SELECT ename
FROM Emp
GROUP BY ename
HAVING COUNT(*) > 1;
5️⃣ Count and display details of employees having identical salary
SELECT salary, COUNT(*) AS Count_Same_Salary
FROM Emp
GROUP BY salary
HAVING COUNT(*) > 1;
6️⃣ Display sum of salaries according to identical names of employees
SELECT ename, SUM(salary) AS Total_Salary
FROM Emp
GROUP BY ename;
7️⃣ Display sum of salaries >3000 of identical names of employees
SELECT ename, SUM(salary) AS Total_Salary
FROM Emp
GROUP BY ename
HAVING SUM(salary) > 3000;
Q2 :
🧱 Step 1: Create Tables
-- Area Table
CREATE TABLE Area (
aname VARCHAR(50) PRIMARY KEY,
area_type VARCHAR(10) CHECK (area_type IN ('urban', 'rural'))
);
-- Person Table
CREATE TABLE Person (
pnumber INT PRIMARY KEY,
pname VARCHAR(50),
birthdate DATE,
income DECIMAL(10,2),
aname VARCHAR(50),
FOREIGN KEY (aname) REFERENCES Area(aname)
);
🔍 Step 2: SQL Queries
1️⃣ Display the name of person whose area is urban
SELECT p.pname
FROM Person p
JOIN Area a ON p.aname = a.aname
WHERE a.area_type = 'urban';
2️⃣ Find the count of people area-wise
SELECT a.aname, COUNT(p.pnumber) AS People_Count
FROM Person p
JOIN Area a ON p.aname = a.aname
GROUP BY a.aname;
3️⃣ List names of people with income between ₹40,000 and ₹50,000 from rural area
SELECT p.pname
FROM Person p
JOIN Area a ON p.aname = a.aname
WHERE a.area_type = 'rural' AND p.income BETWEEN 40000 AND 50000;
4️⃣ Count and display details of persons having same income
SELECT income, COUNT(*) AS Count_Same_Income
FROM Person
GROUP BY income
HAVING COUNT(*) > 1;
5️⃣ Display sum of incomes according to identical persons
SELECT pname, SUM(income) AS Total_Income
FROM Person
GROUP BY pname;
6️⃣ Display average income > ₹5000 of identical persons
SELECT pname, AVG(income) AS Average_Income
FROM Person
GROUP BY pname
HAVING AVG(income) > 5000;
