Grouping Operators - E-Commerce - Lecture Slides, Slides of Fundamentals of E-Commerce

Download Grouping Operators - E-Commerce - Lecture Slides and more Slides Fundamentals of E-Commerce in PDF only on Docsity!

Grouping Operators

• Statistical

COUNT, SUM, AVE, MIN, MAX, STDEV, VAR, FIRST, LAST

• Selection

EXPRESSION, WHERE

• Grouping

GROUP BY

Group By: Example

SELECT Catalog.CatalogID, Catalog.Name,Count(Product.ProductID) AS CountofProductsFROM [Catalog] LEFT JOIN Product ONCatalog.CatalogID = Product.CatalogIDGROUP BY Catalog.CatalogID, Catalog.Name;

CatalogID

Name

CountofProducts

1

Neverland

Supply

31

2

It

Suits

You

0

3

Gameland

0

Sub Query Example: Duplicates

SELECT DISTINCTROW product.Productname,Product.ProductIDFROM productWHERE (( (product.Productname) In(SELECT Productname FROM product As TmpGROUP BY Productname HAVING Count(*)>1 ) ))ORDER BY product.Productname

Sub-Query is in

BOLD

Note: Alternate use of IN qualifier for a sub-query!

SQL Overview

Day 3: Action and Special Queries

Action Queries: DELETE

• DELETE * FROM Table WHERE criteria• Examples

Delete * From Authors Where Dead = TRUEDelete * From Publishers Where PubID > 30Delete * From MooCows Delete *

(This query is EVIL)

(Again ANSI SQL omits the * entirely)

• Delete powerful, usually no way to UNDO.