Experiment No.
5
Environment: Microsoft Windows
Tools/ Language: Oracle
OBJECTIVE: To implement the concept of aggregating & grouping of
Data. Theory:
Group Functions:
Group functions operate on set of rows, result is based on group of rows rather than one result
per row as returned by single row functions.
1) Avg return average value of n
Syntax:
Avg ([distinct/all] n)
2) Min return minimum value of expr.
Syntax:
MIN([distinct/all] expr)
3) Count Returns the no of rows where expr is not null
Syntax:
Count ([distinct/all] expr)
Count (*) Returns the no rows in the table, including duplicates and those with nulls.
4) Max Return max value of expr
Syntax:
Max ([distinct/all]expr)
5) Sum Returns sum of values of n
Syntax:
Sum ([distinct/all]n)
Example Queries:
Aggregate Functions Examples:
select AVG(GPA) as “Average GPA” from student;
Average GPA
3.56666667
Select AVG(DISTINCT GPA) as “Distinct GPA” from student
Distinct GPA
3.54285714
Select MIN(GPA) as “Minimum GPA” from student
Minimum GPA
2.9
Select COUNT(*) from student
COUNT(*)
12
Select COUNT(sName) from student
COUNT(SNAME)
12
select COUNT(distinct sName) from student
COUNT(DISTINCTSNAME)
10
Select MAX(GPA) from student
MAX(GPA)
3.9
Select SUM(GPA) from student
SUM(GPA)
42.8
[Link] average GPA without using Average function.
Select SUM(GPA)/COUNT(GPA) as “Average” from student
Average
3.56666667
Grouping Data From Tables:
There are circumstances where we would like to apply the aggregate function not only to a
single set of tuples, but also to a group of sets of tuples, we specify this wish in SQL using the
group by clause. The attribute or attributes given in the group by clause are used to form group.
Tuples with the same value on all attributes in the group by clause are placed in one group.
Syntax:
SELECT columnname, columnname
FROM tablename
GROUP BY columnname;
HAVING clause:
The HAVING clause can be used in conjunction with the GROUP BY clause. HAVING
imposes a condition on the GROUP BY clause, this further filters the groups created by the
GROUP BY clause.
HAVING clause can be used to find duplicates in a relation or in other words find unique values in
the situations where DISTINCT cannot apply.
NOTE: Each column specifications specified in HAVING clause must occur in the list of columns
named in the GROUP BY clause.
Example
1. select state, count(cname) from college GROUP BY state;
STATE COUNT(CNAME)
CA 2
MA 1
NY 1
2. select state, count(cname) from college GROUP BY state having count(cname)>1;
STATE COUNT(CNAME)
CA 2
Practical Assignment - 5
Department: Computer Engineering & Applications
Course: BCA
Subject: Database Management System Lab (BCAC0816)
Year: 1ST Semester: 2ND
Create these tables which consist of following attributes
College Student
Column Column
Data type Size Data type Size
Name Name
cName varchar2 10 sID int
cstate varchar2 10 sName varchar2 10
enrollment int CGPA number 2,1
marks int
DoB date
Apply
Column
Data type Size
Name
sID int
cName varchar2 10
major varchar2 20
decision char 1
Insert the following data to these tables:
Student Apply
sID sName CGPA Marks sID cName major decision
123 Amit 3.9 1000 123 Stanford CS Y
234 Balbir 3.6 1500 123 Stanford EE N
345 chirag 3.5 500 123 Berkeley CS Y
456 Dev 3.9 1000 123 Cornell EE Y
567 Eshan 2.9 2000 234 Berkeley biology N
678 Faizal 3.8 200 345 MIT bioengineering Y
789 Garvit 3.4 800 345 Cornell bioengineering N
987 Himani 3.7 800 345 Cornell CS Y
876 Ishan 3.9 400 345 Cornell EE N
765 Jay 2.9 1500 678 Stanford history Y
987 Stanford CS Y
654 Amit 3.9 1000
987 Berkeley CS Y
543 chirag 3.4 2000
876 Stanford CS N
College 876 MIT biology Y
cName state enrollment 876 MIT marine biology N
Stanford CA 15000 765 Stanford history Y
Berkeley CA 36000 765 Cornell history N
MIT MA 10000 765 Cornell psychology Y
Cornell NY 21000 543 MIT CS N
Harvard MA 50040
State the SQL Queries for each of the following:
Q1. Count the total number of Students.
Q2. Calculate the average CGPA of all Student.
Q3. Determine the minimum and maximum GPA. Rename the titles as ‘max_CGPA’ and
‘min_CGPA’ respectively.
Q4. Count the number of students having CGPA greater than or equal to 3.7.
Q5. Find Maximum, Average, Minimum, total CGPA of all student.
Q6. Find total number of colleges in our Application Database.
Q7. Find how many different majors student had applied in.
Q8. Find total no. of Applications in our Application System’s Database.
Q9. Find average of all distinct CGPA.
Q10. Display the total number of application accepted.
Q11. Find number of students having CGPA>3.4 and coming from high school having
marks>1000.
Q12. Find how many students applied to ‘marine biology’.
Q13. Find how many applications were rejected and accepted by the colleges.
Q14. Give state and number of colleges of a state that has more than 1 college.
Q15. Find the name of students that are duplicate.