Further GroupBy & Extend Operations

1. 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.

2. EMPLOYEE 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 ]

3. Whole-Relation ‘GroupBy’ Procedure 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.

4. SQL : Whole-Relation Group 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 ;

5. After a GroupBy ? • 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.

6. Executing an SQL ‘Select’ Statement 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.

7. ‘Having’ Phrase 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.

8. Example of the ‘Having’ Phrase (1) “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.

9. Example of the ‘Having’ Phrase (2) “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 ;

10. ‘Before’ and ‘After’ Restrictions 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

11. BANK 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 ]

12. Definition of ‘Extend’ • 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.

13. SQL : Extend 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.

14. SQL Examples • 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 ;

15. Combining Algebra Operations • 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.