1 / 15

# Further GroupBy & Extend Operations - PowerPoint PPT Presentation

Further GroupBy & Extend Operations. Objectives of the Lecture :. To consider “whole relation” Grouping; To consider the SQL Grouping option Having; To consider the Extend operator & its implementation in SQL. EMPLOYEE. ENo. EName. M-S. Sal. Sal. 3. 3. E1. 7. 7. Robson. D. 6. 6.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about ' Further GroupBy & Extend Operations' - halia

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Objectives of the Lecture :

• To consider “whole relation” Grouping;

• To consider the SQL Grouping option Having;

• To consider the Extend operator & its implementation in SQL.

ENo

EName

M-S

Sal

Sal

3

3

E1

7

7

Robson

D

6

6

6

6

32,500

6

6

32,500

E2

Atkins

M

24,000

24,000

Total

1

1

1

E3

Smith

5

5

5

2

2

2

S

2

2

2

18,000

1

1

1

238,500

2

2

2

18,000

2

2

2

E4

Fenwick

S

40,000

40,000

2

2

E5

6

6

Mitchell

M

4

4

24,000

4

4

24,000

4

4

4

E6

4

4

8

Blake

8

8

8

8

M

8

54,000

8

8

8

54,000

8

8

8

E7

Watson

D

28,000

28,000

E8

Jones

D

18,000

18,000

Example : Whole Relation as a Group

Get the total salary bill.

No attribute

EMPLOYEEGroupBy[ ] With[ Total  Bag[ Sal ] Sum ]

1. Remove irrelevant attribute(s). AS USUAL.

2.Add an empty set of attributes to the operand as the grouping attributes. It does not alter the operand, since it is empty ! The empty ‘attribute’ has the same value (i.e. the empty set) in every tuple.Group on this attribute; this yields only one group.

3. Create one result tuple / group consisting of the grouping attribute.Thus there is one tuple initially with no attributes, i.e. an attribute containing the empty set.

4. Carry out aggregations. AS USUAL.This will add aggregation result attribute(s) to the one tuple.

The SQL syntax for a whole-relation GroupBy is :

Select “aggregation”As Result-Name

From RELATION_NAME;

Repeat this foreach aggregation.

The Group By phrase is omitted -no grouping attributes to put in it.

No grouping attributes inthe Select phrase.

The syntax of “aggregation“ remains unchanged.

Example: “Get the total salary bill”. (Previous example).

Select Sum( All Sal ) As TotalFrom EMPLOYEE ;

• Sometimes it is useful to carry out another operation on the result of a GroupBy.

• There is no need to do a Projection on the result since it can be incorporated into the GroupBy.

• To do a Restriction on the result, SQL provides a Having phrase. This is because SQL’s built-in sequence of operations has already executed all Restrictions. There is no way to do another without introducing a special phrase, i.e. the Having phrase.

• The Having restriction condition is limited to using :

• a grouping attribute,

• an aggregation results.

Because the result of a GroupBycontains nothing but these.

The phrases are executed in the following order.

From

Restrictions done here.

Where

Joins /CartesianProductsdone here.

Grouping done here.

Group By

Sequencing done here.

Having

AnotherRestrictionhere !

Order By

Select

Projections done here.

The SQL syntax for a Group By with a Having phrase is :

Select GroupingAttribute(s), “aggregation” As Result_Name

From RELATION_NAMEGroup By GroupingAttribute(s)Havingcondition;

The Having phrasemust immediatelyfollow a Group Byphrase, and not beanywhere else.

“condition” can contain only grouping attribute(s)and aggregate function applications in a Booleancombination of comparisons.

“Get the total salary paid to each marital-status group, for groups where the total exceeds £50,000.”

EMPLOYEE GroupBy[ M-S ] With[ Total Bag[ Sal ] Sum ] Restrict[ Total > 50000 ]

A GroupBy followed by a Restriction.

Select M-S, Sum( All Sal )As Total FromEMPLOYEEGroup By M-SHaving Sum( Sal ) > 50000 ;

Cannot use the name Total.

“How many different shipment sizes are there per supplier, for suppliers who ship more than 2 shipment sizes ?”

SHIP GroupBy[ SNo ] With[ Sizes Project[ Qty ] Count ] Restrict[ Sizes > 2 ]

Select SNo, Count( Distinct Qty )As Sizes FromSHIPGroup By SNoHaving Count( Distinct Qty ) > 2 ;

It is possible to write algebraic expressions like :

( …expression… )Restrict[ … ] GroupBy[ .. ] With[ … ] Restrict[ … ]

Before

After

The equivalent in SQL is :

Select …From …Where …Group By …Having … ;

Before

After

A/C

Amt

O/D

Euro

MaxB

10/23A

Amt

O/D

A/C

125

5

100

5

125

5

125

100

10/23A

10/29C

0

6

50

6

0

0

6

50

10/29C

32/14X

BANK

192

225

1000

7

0

1000

7

1000

7

0

32/14X

47/18G

A/C

Amt

O/D

0

50

546

8

0

8

546

8

546

0

47/18G

10/23A

100

1538

1000

10/29C

50

840

546

32/14X

0

47/18G

0

Example of Extend

BANK Extend[ Euro Amt/0.65 ; MaxB  Amt + O/D ]

• Creates a new relation formed from its operand by adding one or more named attributes to it.

• Each new attribute(s) contain values specified in a ‘scalar expression’. The expression’s type is the type of the new attribute.

• An expression can contain one or more of :

• a constant;

• an attribute name : this yields that attribute’s value in the same tuple;

• scalar functions & operators (of the relevant data type);

• parentheses to control the execution sequence.

The SQL equivalent of an Extension has the syntax :-

Select RELATION_NAME.*, calculationAs Result_NameFrom RELATION_NAME ;

This yields allthe relation’sexisting attributes.

Repeat foreach newattribute.

An ‘*’ on its own isonly allowed if thereis nothing else in theSelect phrase.

• The previous example is written in SQL as follows :

Select BANK.*,Amt/0.65As Euro, Amt + O/D As MaxBFrom BANK ;

• “In addition to the details of all bank accounts, show how much the amount in each account would be above an overdraft limit that was 5 times greater than the current overdraft limit”.

Select BANK.*,Amt + (5 * O/D) As PossibleFrom BANK ;

• SQL treats an Extension as the reverse of a Projection, by putting both in the Select phrase.

• As the Select phrase is the last to be executed by SQL, then an Extension is always the last operation to be executed, in parallel with a Projection.

• This applies even when there is a Group By (with / without Having).In this case, since Extend applies scalar expressions and an aggregation can be embedded in a scalar expression, the Extension comes after the GroupBy.