Execution of a general SQL query
- The general SQL query with
all 5 clauses:
SELECT grouping-attributes or set-functions
FROM relation-list
WHERE tuple-boolean-condition
GROUP BY grouping-attribute-list
HAVING group-boolean-condition
|
Question:
- How is the
general form of the
SQL query
executed ?
|
|
Execution of a general SQL query
Execution of a general SQL query
- The general SQL query with
all 5 clauses:
SELECT grouping-attributes or set-functions
FROM relation-list
WHERE tuple-boolean-condition
GROUP BY grouping-attribute-list
HAVING group-boolean-condition
|
(2)
Group
the selected tuples
according to their values
in the
grouping attributes :
GROUP BY grouping-attribute-list
|
The result set consists of the
set of tuples
that satisfy the
tuple (WHERE) condition
separated into
groups based on
group attribute
values
|
Execution of a general SQL query
- The general SQL query with
all 5 clauses:
SELECT grouping-attributes or set-functions
FROM relation-list
WHERE tuple-boolean-condition
GROUP BY grouping-attribute-list
HAVING group-boolean-condition
|
(3) Groups that
satisfies
the group condition
in the
HAVING clause
are selected
HAVING group-boolean-condition
|
The result set consists of the
set of tuples
that satisfy the
tuple (WHERE) condition ,
separated into
groups based on
group attribute
values that
satisfies the
group condition
|
Execution of a general SQL query
Example processing
of a general SQL query
- Query
(find dno and total salary
of male employees
for depts with
> 2 male employees)
SELECT dno, sum(salary)
FROM employee
WHERE sex = 'M'
GROUP BY dno
HAVING count(*) > 2
|
Input relation:
ssn fname lname sex dno salary
--------- ------ -------- --- --- --------
123456789 John Smith M 5 30000.00
333445555 Frankl Wong M 5 40000.00
999887777 Alicia Zelaya F 4 25000.00
987654321 Jennif Wallace F 4 43000.00
666884444 Ramesh Narayan M 5 38000.00
453453453 Joyce English F 5 25000.00
987987987 Ahmad Jabbar M 4 25000.00
888665555 James Borg M 1 55000.00
|
|
Example processing
of a general SQL query
Example processing
of a general SQL query
Example processing
of a general SQL query
Example processing
of a general SQL query
❮
❯