ICS 184: Introduction to Data Management - PowerPoint PPT Presentation

1 / 24

About This Presentation

Title:

ICS 184: Introduction to Data Management

Description:

Title: s 08 Author: Chen Li Last modified by: jhan Created Date: 8/28/1995 11:58:10 AM Document presentation format: On-screen Show Other titles – PowerPoint PPT presentation

Number of Views:19

Avg rating:3.0/5.0

Slides: 25

Provided by: Chen154

Category:

more less

Transcript and Presenter's Notes

Title: ICS 184: Introduction to Data Management

1
ICS 184 Introduction to Data Management

Lecture Note 10
SQL as a Query Language (Cont.)

2
Aggregations

MIN, MAX, SUM, COUNT, AVG
input collection of numbers/strings (depending
on operation)
output relation with a single attribute with a
single row
Example What is the minimum, maximum, average
salary of employees in the toy department
select min(sal), max(sal), avg(sal)
from Emp, Dept
where Emp.dno Dept.dno and D.dname Toy

3
Aggregations (cont)

Except count, all aggregations apply to a
single attribute
Count can be used on more than one attribute,
even
SELECT Count() FROM Emp
SELECT Count(ename) FROM Emp

Emp (ename, dno, sal)
4
Duplication in aggregations

What is the number of different dnos in the emp
table
Select count(dno)
From Emp
Wrong, since there could be duplicates.
Right query
Select count(DISTINCT dno)
From Emp

Emp
5
Group By clause

Group by used to apply aggregate function to a
group of sets of tuples. Aggregate applied to
each group separately.
Example For each department, list its total
number of employees and total salary
select dname, sum(sal), count(ename)from
Emp, Deptwhere Emp.dno Dept.dno
group by dname

Dept(dno, dname, mgr)
Emp (ename, dno, sal)
Results
6
Group By clause (cont)

Group-by attributes must be in the SELECT
attributes.
The following query cannot group the tuples.
select dname, sum(sal), count(ename)from
Emp, Deptwhere Emp.dno Dept.dno

Dept(dno, dname, mgr)
Emp (ename, dno, sal)
Result (on Informix) The column (dname) must be
in the GROUP BY list.
7
Group By clause (cont)

The following query
SELECT dno FROM Emp
GROUP BY dno
is the same as
SELECT DISTINCT dno
FROM Emp

8
Having Clause

Having clause used along with group by clause to
select some groups.
Predicate in having clause applied after the
formation of groups.
List the department name and the number of
employees in the department for all departments
with more than 1 employee. select dname,
count()
from Emp, Dept
where Emp.dno Dept.dno
group by dname
having count() gt 1

Dept(dno, dname, mgr)
Emp (ename, dno, sal)
9
A general SQL query
For each employee in two or more depts, print the
total salary of his or her managers. Assume each
dept has one manager. select e1.ename,
sum(e2.sal) -- 5 from Emp e1,
Dept, Emp e2 -- 1 where e1.dno Dept.dno
AND e2.ename Dept.mgr -- 2 group by
e1.ename -- 3 having count() gt 1
-- 4 order by ename -- 6
E1 Emp (ename, dno, sal)
E2 Emp (ename, dno, sal)
Dept(dno, dname, mgr)
10
A general SQL query (cont)
For each employee in two or more depts, print the
total salary of his or her managers. Assume each
dept has one manager. select e1.ename,
sum(e2.sal) -- 5 from Emp e1,
Dept, Emp e2 -- 1 where e1.dno Dept.dno
AND e2.ename Dept.mgr -- 2 group by
e1.ename -- 3 having count() gt 1
-- 4 order by ename --
6 Execution steps Step 1 tuples are formed
(Cartesian product) Step 2 tuples satisfying
the conditions are chosen Step 3 groups are
formed Step 4 groups are eliminated using
Having Step 5 the aggregates are computed for
the select line, flattening the groups Step 6
the output tuples are ordered and printed out.
11
Subqueries

Also called nested query. Embedded inside an
outer query.
Similar to function calls in programming
languages.
Example Who is in Sallys department?
select E1.ename
from Emp E1, Emp E2
where E2.ename Sally AND E1.dno E2.dno
OR
select ename
from Emp
where Emp.dno in
(select dno
from Emp ? subquery
where ename Sally) ? names are scoped
Semantics
A nested query returns a relation containing dno
for which Sally works
for each tuple in Emp, evaluate the nested query
and check if E.dno appears in the set of dnos
returned by nested query.

12
Conditions involving relations

Usually subqueries produce a relation as an
answer.
Conditions involving relations
s gt ALL R -- s is greater than every value in
unary relation R
s IN R -- s is equal to one of the values in R
s gt ANY R, s gt SOME R -- s is greater than at
least 1 element in unary relation R.
any is a synonym of some in SQL
EXISTS R -- R is not empty.
Other operators (lt, , lt, gt, ltgt) could be used
instead of gt.
EXISTS, ALL, ANY can be negated.

13
Example 1

Find the employees with the highest salary.
SELECT ename
FROM emp
WHERE sal gt ALL (select sal from Emp)
lt all, lt all, gt all, all, ltgt all also
permitted

14
Example 2

Who makes more than someone in the Toy
department?
SELECT ename FROM Emp
WHERE sal gt SOME
(SELECT sal FROM Emp, Dept
WHERE Emp.dno Dept.dno AND Dept.dname Toy)
lt some, lt some, gt some, gt some some, ltgt some
are permitted

15
Testing Empty Relations

Exists checks for nonempty set
Find employees who make more money than some
manager
SELECT ename
FROM Emp E1
WHERE exists
(SELECT ename
FROM Emp, Dept
WHERE (Emp.ename Dept.mgr)
AND (E1.sal gt Emp.sal))

E1 Emp(ename, dno, sal)
Dept(dno, dname, mgr)
Emp (ename, dno, sal)
16
Testing Empty Relations (cont)

The nested query uses attributes name of E1
defined in outer query. These two queries are
called correlated.
Semantics for each assignment of a value to some
term in the subquery that comes from a tuple
variable outside, the subquery needs to be
executed
Clearly the database can do a much better job
Similarly, NOT EXISTS can be used.

17
Subqueries producing one value

Sometimes subqueries produce a single value
select ename
from Emp
where Emp.dno
(select dno
from dept
where dname toy)
Assume there is only one department called toy,
then the subquery returns one value.
If it returns more, its a run-time error.

18

Joins