SQL [part 4/5]

• SQL queries (cont.)

• Aggregate queries
• Set queries
Aggregate queries

Aggregate queries allow evaluating properties that are dependent on sets of

tuples.
● SQL offers five main aggregate operators: count, sum, max, min, avg
● Aggregate operators constitute an important extension of SQL in
comparison with Relational Algebra
● Example: Find the highest salary earned by employees in the Administration
department
SQL
select max(Salary)
from EMPLOYEE
where Dept=‘Administration’
Aggregate queries – operator count

count returns the number of rows or distinct values

● Syntax
count ( < * | [ distinct | all ] AttributeList > )

○ * returns the number of rows

○ distinct returns the number of different values for the attributes in
AttributeList
○ all returns the number of rows that have not null values for the
attributes in AttributeList
○ if neither distinct nor all is specified, all is assumed as default
Aggregate queries – operator count

● Examples:

Q1: Find the number of rows in table EMPLOYEE

select count(*) from EMPLOYEE

Q2: Find the number of different values on the attribute Salary for all the rows
in EMPLOYEE
select count(distinct Salary) from EMPLOYEE

Q3: Find the number of rows of EMPLOYEE having a not null value on the
attribute Salary
select count(all Salary) from EMPLOYEE
Aggregate queries – operators sum, avg, max, min

sum, avg, max, min respectively return the sum, average, maximum and
minimum of values possessed by the attribute expression
● Syntax
< sum | max | min | avg > ( [ distinct | all ] AttributeExpr )

○ distinct ignores duplicates

○ all ignores null values
Aggregate queries – operators sum, avg, max, min

● Examples

Q4: Find the sum of the salaries of employees in the Administration

department.
select sum(Salary) as SumSalary
from EMPLOYEE
where Dept=‘Administration’

Q5: Find the maximum salary among the employees who work in a department
based in London.
select max(Salary) as MaxLondonSalary
from EMPLOYEE E, DEPARTMENT D
where E.Dept=D.DeptName and
D.City=‘London’
Aggregate queries – Target list of aggregate queries

● Examples

Q6: Find the maximum and minimum salaries of all employees.

select max(Salary) as MaxSal,
min(Salary) as MinSal
from EMPLOYEE

Note: The following query is incorrect. SQL syntax does not allow aggregate
functions and attribute expressions (e.g., attribute names) within the same
target list except for queries with the group by clause (described later).
select FirstName, Surname, max(Salary)
from EMPLOYEE
Aggregate queries – group by queries

group by clause allows applying aggregate operators to specific subsets of rows.

● Examples

Q7: Find the sum of salaries of all employees of the same department.

Dept TolSal
select Dept, sum(Salary) as TolSal
from EMPLOYEE Administration 85
group by Dept Distribution 45
Planning 153
Note: Even though the department name is not explicitly
required in the query, including attribute Dept in the Production 81
target list is important for the result to be meaningful. Research null
Aggregate queries – group by queries – understand the result

1. Imagine the group by 2. Divide the rows into 3. Apply the aggregate
clause did not exist subsets according to the operator (e.g., sum) to
values of the attributes each subset separately
select Dept, Salary specified in the group by
from EMPLOYEE clause (e.g., attribute Dept)
Dept Salary Dept Salary Dept TolSal
Research null Administration 40 Administration 85
1
Administration 40 Administration 45 Distribution 45
Planning 80 2 Distribution 45 Planning 153
Distribution 45 Planning 80 Production 81
3
Planning 73 Planning 73 Research null
Production 36 Production 36
4
Administration 45 Production 45
Production 45 5 Research null
 Aggregate queries – Target list of group by queries

The attributes that appear in the target list must be a subset of the attributes
used in the group by clause.
● Examples
Q8 select Office Q9 select D.City, count(*), D.DeptName
from EMPLOYEE from EMPLOYEE E, DEPARTMENT D
group by Dept where E.Dept=D.DeptName
group by D.City

Q10 select D.City, count(*) Q11 select D.City, count(*), D.DeptName

from EMPLOYEE E, DEPARTMENT D from EMPLOYEE E, DEPARTMENT D
where E.Dept=D.DeptName where E.Dept=D.DeptName
group by D.City group by D.City, D.DeptName

Note: Some DBMSs do not strictly adhere to this requirement in the SQL standard.
Aggregate queries – Group predicates

A having clause can be added after the group by clause to retain only
subsets that satisfy a group predicate.

● Examples

Q12: Find the names of the departments which spend more than 100 on
salaries.
select Dept
from EMPLOYEE
group by Dept
having sum(Salary)>100
Aggregate queries – where or having

Only predicates containing aggregate operators should appear in the argument

of the having clause.

● Examples

Q13: Find the names of the departments in which the average salary of
employees working in office number 20 is higher than 25.
select Dept
from EMPLOYEE
where Office=20
group by Dept
having avg(Salary)>25
Syntax of a SQL query

With all the clauses described so far, the syntax of a SQL query is:

select TargetList
from TableList
[ where Condition ]
[ group by GroupingAttributeList ]
[ having AggregateCondition ]
[ order by OrderingAttributeList ]
Set queries

SQL provides set operators similar to those seen in relational algebra.

● Syntax
SelectSQL { < union | intersect | except > [ all ] SelectSQL }

○ SelectSQL denotes a single SQL select statement

○ except is the equivalence of difference in relational algebra
○ Unlike the rest of the language, set operators assume elimination of duplicates
as a default. The keyword all can be used to keep duplicates.
Set queries

● Examples

Q14: Find the cities that have either a department or an employee residing in
them.
select City
from DEPARTMENT
union (duplicates are removed)
select City
from EMPLOYEE

select City
from DEPARTMENT
union all (duplicates are kept)
select City
from EMPLOYEE
Set queries

● Examples

Q15: Find the cities that have a department and also have any employee
residing in them.
select City
from DEPARTMENT
intersect
select City
from EMPLOYEE

Alternative solution (using table variables)

select distinct E.City as Name
from EMPLOYEE E, DEPARTMENT D
where E.City=D.City;
Set queries

● Examples

Q16: Find the cities that have a department but have no employee residing in
them. select City
from DEPARTMENT
except
select City
from EMPLOYEE

● except is equivalent to operator difference in relational algebra

● In some DBMSs, the operator is called minus