Download Report

ITEC212 Database Management Systems
Laboratory 4
Aim : We will learn how to use group (or aggregate) functions in SQL.
GROUP FUNCTIONS
Group functions perform an operation on a group of rows and return a single value, that is
representative of that group. For example, if you were given the task of finding the number of rows in a
table, the input would be all rows of that table and output would be a single number that is equal to the
number of rows. The function you would use for this purpose is count(*).
Group functions operate on rows selected by the WHERE construct if it exists. There is also
another construct the HAVING construct, that is used with group functions very often.
The group functions are listed in the SELECT clause not the WHERE clause.
The list of the group functions we will use is given below. There are other functions that can be used to
calculate some statistical values such as standard deviation. You can find out about these from
reference books.
count(<attribute_name>) : Finds the number of rows that have a not NULL value for
<attribute_name> that also satisfy the conditions given in the WHERE clause.
count(*) : Finds the number of rows that satisfy the conditions given in the WHERE clause.
avg(<attribute_name>) : Finds the average value of all <attribute_name> values in the rows that
satisfy the conditions given in the WHERE clause.
sum(<attribute_name>) : Finds the summation of all <attribute_name> values in the rows that
satisfy the conditions given in the WHERE clause.
min(<attribute_name>) or max(<attribute_name>) : Finds the minimum (or maximum) of all
<attribute_name> values in the rows that satisfy the conditions given in the WHERE clause.
Examples:
1.Find the number of employees.
SELECT count(*)
FROM employees;
2.Find the number of employees who earn more than $1000 per month.
SELECT count(*)
FROM employees
WHERE salary > 1000 ;
3.Find the average, minimum, and maximum salary of all employees who work in department 50.
SELECT avg(sal), min(sal), max(sal)
FROM employees
WHERE department_id =35 ;
In all examples given above, we calculated a single value for the whole table. Sometimes you need
to find such a value for each group in the table. For example you might want to find the average
salary earned in each department. This can be achieved by using the GROUP BY <attribute_name>
construct.
GROUP BY groups the rows of the table so that all rows whose <attribute_name> value is the same
are in the same group. The rows that are in the same group are treated as if they were a separate table.
Group by and WHERE can be used together. In such cases the WHERE clause is applied before the
GROUP BY clause.
4.Find the number of employees in each department.
SELECT department_id, count(*)
FROM employees
GROUP BY department_id ;
5.Find the number of employees whose name starts with M, in each department.
SELECT department_id, count(*)
FROM employees
WHERE upper(first_name) like ‘M%’
GROUP BY deptno ;
When you use the GROUP BY clause or any of the group functions , you should be very careful when
printing attributes other than the one listed in the GROUP BY clause.
Does the following query make sense?
SELECT first_name, count(*)
FROM employees
GROUP BY department_id ;
It does NOT make sense, because we are finding the number of employees in each department. For
department 10 this number is 3. It means that I have 3 employees. Whose name will be written?
Using GROUP BY to group the rows into logical clusters and treating those clusters separately is very
useful but sometimes you need to exclude some of these groups from the output. In such cases you use
the HAVING construct. HAVING is very similar to the WHERE clause except that it chooses groups
instead of rows.
When you have WHERE, GROUP BY, and HAVING together, WHERE is applied first, then
GROUP BY and then HAVING.
6.Find the number of all employees who work in each department that has more than 3
employees.
SELECT department_id, count(*)
FROM employees
GROUP BY department_id
HAVING count(*) > =3 ;
7.Find the number of employees that work in each department that has a minimum salary greater than
or equal to 1000.
SELECT department_id, count(*)
FROM employees
GROUP BY department_id
HAVING min(salary) > =1000;
8.Find the number of employees that has at least a salary of 1000, in each department.
SELECT department_id, count(*)
FROM intructor
WHERE salary >= 1000
GROUP BY department_id;
9.Find the number of all employees who earn at least $1000 a month in each department whose
minimum salary is at least $1000.
SELECT department_id, count(*)
FROM instructor
WHERE salary> 1000
GROUP BY department_id
HAVING avg(salary) > =1000 ;
Notes for exercises :
Dates are enclosed in single quotes (‘’) in ORACLE, but in other systems you can also use curly braces
({}). Dates are treated like numbers so you can perform arithmetic operations on them. You can also
compare two dates. But you cannot compare a date with a number.
You should query the user_tables view of dictionary to find which tables you have in your
account. Then use the DESC command of SQL*Plus to see the structure of a table.
Exercises:
1) List last name of all students whose first name is longer than 4 letters in ascending order
according to the last name. Duplicated rows should be removed from the output.
2) Count the total number of rooms in Location.
3) Find the number of students in each major.
4) Find the number of employees in each department who get no commission or have salary less than
5000.
5) Find the maximum salary of employees in each department that the employee was hired 15 years
before now.
6) Find the last name of all employees that were not hired on Tuesday (Use a set operator to
answer this question)
7) Find the number of employees in each department who have a manager.
8) Find the number of employees for each manager whose employees' minimum salary is greater than
5000.
9) Find the number of employees for each job and department.In the output list the number that you
have found together with the job id and department id.
10) Find the number of students for each faculty, but list only the faculties that have more than one
student.
11) Find the average, highest, and lowest age for students.
12) Find summation of maximum count by term by course.
13) Display courses and prerequisites. If there is no prerequisite, display "none" or else display
"one".
14) Count number of faculty members by each department.
15) Display average employee salary by department, but don't include departments with an
average salary of less than $75,000.