Indian Institute of Information Technology Sri City
Database Management Systems LAB-04
Sec-3
TOPIC : Aggregate functions, Grouping
AGGREGATE FUNCTIONS: MySQL supports the following aggregate functions.
Aggregate function Description
AVG() Return the average of non-NULL values.
COUNT() Return the number of rows in a group, including rows with
NULL values.
MAX() Return the highest value (maximum) in a set of non-NULL
values.
MIN() Return the lowest value (minimum) in a set of non-NULL
values.
STDEV() Return the population standard deviation.
SUM() Return the summation of all non-NULL values in a set.
VARIANCE() Return the population standard variance.
SELECT VARIANCE(income)
FROM employee;
GROUPING: The GROUP BY clause is a SQL command that is used to group rows that have
the same values.
● The GROUP BY clause is used in the SELECT statement.
● Optionally it is used in conjunction with aggregate functions to produce summary reports
from the database.
ORDER BY CLAUSE:
Use the ORDER BY clause to display the output table of a query in either ascending or
descending alphabetical order. Whereas the GROUP BY clause gathers rows into groups and
sorts the groups into alphabetical order, ORDER BY sorts individual rows.
General syntax for using GROUP Functions:
SELECT <column, ..>, group_function(column)
FROM <table>
WHERE <condition>
[GROUP BY <column>]
[ORDER BY <column>];
Example 1: Maximum salary from employee table
SELECT emp_dept, MAX(income)
FROM employee;
Example 2: All columns in the SELECT list that are not in the group functions must be in the
GROUP BY clause.
Illegal
SELECT emp_dept, job, MAX(income)
FROM employee
GROUP BY emp_dept;
Legal
SELECT emp_dept, job, MAX(income)
FROM employee
GROUP BY emp_dept, job;
HAVING clause: We cannot use the WHERE clause to restrict groups.
Illegal
SELECT emp_dept, MAX(income)
FROM employee
WHERE MAX(income) >2000
GROUP BY emp_dept;
Legal
SELECT emp_dept, MAX(income)
FROM employee
GROUP BY emp_dept
HAVING MAX(income) >2000;
EXERCISES
Exercise:
Tables for Exercise
1. Create table customer (customer_name char(20),customer_street char(30),customer_city
char(30),PRIMARY KEY(customer_name));
2. Create table branch (branch_name char(15),branch_city char(30),assets
numeric(16,2),PRIMARY KEY(branch_name));
3. Create table account (account_number char(15),branch_name char (15),balance
numeric(12,2),PRIMARY KEY(account_number),FOREIGN KEY (branch_name)
REFERENCES branch(branch_name));
4. Create table depositor(customer_name char(20),account_number char(10),PRIMARY
KEY(customer_name,account_number),FOREIGN KEY (customer_name)
REFERENCES customer(customer_name),FOREIGN KEY (account_number)
REFERENCES account(account_number));
5. Create table loan(loan_number varchar(6),branch_name char(15),amount int,PRIMARY
KEY(loan_number),FOREIGN KEY (branch_name) REFERENCES
branch(branch_name));
6. Create table borrower(customer_name char(20),loan_number varchar(6),PRIMARY
KEY(customer_name,loan_number),FOREIGN KEY (customer_name) REFERENCES
customer(customer_name),FOREIGN KEY (loan_number) REFERENCES
loan(loan_number));
Questionnaire set:
1. Create the tables for above schema and add some 5 instances ( or try to upload the .csv file given)
2. For all customers who have loan from the bank, find their names, loan numbers and loan amount
(with and without renaming tables)
3. Find the customer names, loan numbers and loan amounts for all loans at Brooklyn branch.
4. Find the names of all branches that have assets greater than at least one branch located at Chennai.
5. List in alphabetical order all customers who have loans at the Perryridge branch.
6. Print the entire Loan relation in ascending order of amount. If several loans have the same amount,
order them in ascending order by loan number.
7. Find the average balance for all accounts.
8. Find no. of tuples in customer relation.
9. Find the total of all loan amounts.
10. Find the average account balance at the Perryridge branch.
11. Find the average account balance at each branch.
12. Find the average account balance at each branch , where the account balance is more than 2200.
13. Find the number of depositors for each branch.
14. Find the average balance
