SQL language: basics

SQL language: basics

➢SQL Language
➢Language Instruction
➢Sample notation and database
➢SELECT Statement
➢Aggregate Functions
➢Operator GROUP BY

1
The SQL language
• A language for managing relational databases
• Structured Query Language
• SQL provides commands to
• define the schema of a relational database
• read and write data
• define the schema of derived tables
• define user access privileges
• manage transactions
• The SQL language may be used in two ways
• interactive
• compiled
• a host language encapsulates the SQL commands
• SQL commands can be distinguished from the host language commands
by means of appropriate syntactic mechanisms
2
The SQL language
• SQL is a set-level language
• operators are applied to relations (tables)
• the result is always a relation (table)
• SQL is a declarative language
• it describes what to do and not how to do it
• it has a higher level of abstraction compared to traditional programming
languages

3
SQL instructions
The SQL language

4
The SQL language
• Can be divided into
• DML (Data Manipulation Language)
• language for querying and updating the data
• DDL (Data Definition Language)
• language for defining the database structure

5
Data Manipulation Language
• To query a database in order to extract data of interest
• SELECT
• To modify a database instance
• INSERT: insertion of new information into a table
• UPDATE: update of the information in the database
• DELETE: cancellazione di dati obsoleti

6
Data Definition Language
• To define a database schema
• creation, modification and deletion of tables: CREATE, ALTER, DROP TABLE
• To define derived tables
• creation, modification and deletion of tables whose content is obtained from
other database tables: CREATE, ALTER, DROP VIEW
• To define complementary data structures for efficiently retrieving the
data
• creation and deletion of indices: CREATE, DROP INDEX
• To define user access privileges
• grant and revocation of privileges on resources: GRANT, REVOKE
• To define transactions
• termination of a transaction: COMMIT, ROLLBACK
7
Notation and example database
The SQL Language

8
Syntax of SQL commands
• Notation
• language keywords
• upper case
• variable terms
• Grammar
• angle brackets < >
• to isolate a syntactic term
• square brackets [ ]
• the enclosed term is optional
• braces { }
• the enclosed term may not appear or may be repeated an arbitrary number of items
• vertical bar |
• a term must be chosen among the options separated by the vertical bars

9
 Example database: Supply-Product
Foreign Foreign
P PId PName Color Size Store key key

P1 Jumper Red 40 London

SP
P2 Jeans Green 48 Paris SId PId Qty
P3 Blouse Blue 48 Rome S1 P1 300
P4 Blouse Blue 44 London S1 P2 200
P5 Skirt Blue 40 Paris S1 P3 400
S1 P4 200
S1 P5 100
S SId SName #Employees City S2 P1 300
S1 Smith 20 London S2 P2 400
S2 Jones 10 Paris S3 P2 200
S3 Blake 30 Paris S4 P3 200
S4 Clark 20 London
S5 Adams 30 Athens
10
Example database: Supply-Product
• Supplier and part DB
• table P describes the available products
• primary key: PId
• table S describes the suppliers
• primary key: SId
• table SP describes supplies, by relating each product to the suppliers that
provide it
• primary key: (SId, PId)
• PId: Foreign key (SP) REFERENCES PId(P)
• Sid: Foreign key (SP) REFERENCES SId(S)

11
The SELECT statement: basics
The SQL language

12
SELECT
SELECT [DISTINCT] ListOfAttributesToDisplay
FROM ListOfTablesToUse
[WHERE TupleConditions ]
[GROUP BY ListOfGroupingAttributes ]
[HAVING AggregateConditions ]
[ORDER BY ListOfOrderingAttributes ]

13
Basic SELECT(n.1)
• Find the codes and the number of employees of the suppliers based
in Paris
R
SELECT SId, #Employees
pSId, #Empolyees
FROM S
WHERE City=‘Paris'; sCity=‘Paris'

S
S
SId SName #Employees City R
S1 Smith 20 London
SId #Employees
S2 Jones 10 Paris
S2 10
S3 Blake 30 Paris
S3 30
S4 Clark 20 London
S5 Adams 30 Athens

14
Basic SELECT(n.2)
• Find the codes of all products in the database

R
SELECT PId
pPId
FROM P;
P

P R
PId PName Color Size Store PId
P1 Jumper Red 40 London P1
P2 Jeans Green 48 Paris P2
P3 Blouse Blue 48 Rome P3
P4 Blouse Blue 44 London P4
P5 Skirt Blue 40 Paris P5
P6 Shorts Red 42 London P6
Basic SELECT(n.3)
• Find the codes of the products supplied by at least one supplier

SP R
SId PId Qty PId
S1 P1 300 P1
S1 P2 200 P2
S1 P3 400 P3
S1 P4 200 SELECT PId P4
S1 P5 100 FROM SP; P5
S1 P6 100 P6
S2 P1 300 P1
S2 P2 400 P2
S3 P2 200 P2
S4 P3 200 P3
S4 P4 300 P4
S4 P5 400 P5
Basic SELECT(n.3)
• Find the codes of the products supplied by at least one supplier

R
SELECT PId pPId
FROM SP;
SP

• It does not eliminate duplicates

Elimination of duplicates: DISTINCT
• DISTINCT keyword allows the elimination of duplicates

• Find the codes of the distinct products supplied by at least one

supplier SP
SId PID Qty
S1 P1 300
S1 P2 200
S1 P3 400
R
S1 P4 200 PId
SELECT DISTINCT PId
S1 P5 100 P1
FROM SP;
S1 P6 100 P2
S2 P1 300 P3
S2 P2 400 P4
S3 P2 200 P5
S4 P3 200 P6
S4 P4 300
S4 P5 400 18
Selection of all information
• Find all information related to products

SELECT PId, PName, Color, Size, Store

FROM P;
or
SELECT *
FROM P;
R
PId PName Color Size Store
P1 Jumper Red 40 London
P2 Jeans Green 48 Paris
P3 Blouse Blue 48 Rome
P4 Blouse Blue 44 London
P5 Skirt Blue 40 Paris
P6 Shorts Red 42 London

19
Selection with an expression
• Find the codes of the products and the sizes expressed with the US
standard
SELECT PId, Size-14 [AS USSize]
FROM P;
P R
PId PName Color Size Store PId USSize
P1 Jumper Red 40 London P1 26
P2 Jeans Green 48 Paris P2 34
P3 Blouse Blue 48 Rome P3 34
P4 Blouse Blue 44 London P4 30
P5 Skirt Blue 40 Paris P5 26
P6 Shorts Red 42 London P6 38

• Definition of a new temporary column for the computed expression

• the name of the temporary column may be defined by means of the AS keyword
20
 • It allows expressing selection conditions applied to
each tuple individually
• A Boolean expression composed by one or more
The WHERE predicates
clause • Simple predicates
• comparison between attributes and constants
• text search
• NULL values

21
The WHERE clause (n.1)
• Find the codes of the suppliers based in Paris

SELECT SId
FROM S
WHERE City=‘Paris’;

F
SId SName #Employees City R
S1 Smith 20 London Sid
S2 Jones 10 Paris S2
S3 Blake 30 Paris S3
S4 Clark 20 London
S5 Adams 30 Athens
The WHERE clause (no.2)
• Find the codes and the number of employees of the suppliers that are
not based in Paris

SELECT SId, #Employees

FROM S
WHERE City<>‘Paris’;

F R
SId SName #Employees City
S1 Smith 20 London SId #Employees
S2 Jones 10 Paris S1 20
S3 Blake 30 Paris S4 20
S4 Clark 20 London S5 30
S5 Adams 30 Athens
Boolean expressions (no.1)
• Find the codes of the suppliers based in Paris that have more than 20
employees
SELECT SId
FROM S
WHERE City=‘Paris' AND #Employees>20;

S
SId SName #Employees City
S1 Smith 20 London
R
S2 Jones 10 Paris Sid
S3 Blake 30 Paris S3
S4 Clark 20 London
S5 Adams 30 Athens

24
Boolean expressions (no.2)
• Find the codes and the number of employees of the suppliers based
in Paris or London
SELECT SId, #Employees
FROM S
WHERE City=‘Paris' OR City=‘London';

S R
SId SName #Employees City SId #Employees
S1 Smith 20 London F1 20
S2 Jones 10 Paris F2 10
S3 Blake 30 Paris F3 30
S4 Clark 20 London F4 20
S5 Adams 30 Athens

25
Boolean expressions (no.3)
• Find the codes and the number of employees of the suppliers based
in Paris and in London
• the query may not be satisfied
• each supplier has only one city

S
SId SName #Employees City
S1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens
 • LIKE operator
AttributeName LIKE CharacterString

• the _ character represents a single arbitrary character

Text search (non-empty)

• the % character represents an arbitrary sequence of

characters (possibly empty)

27
Text search (no.1)
• Find the codes and the names of the products whose name begins with
the letter B
SELECT PId, PName
FROM P
WHERE PName LIKE ‘B%’;

P
PId PName Color Size Store
P1 Jumper Red 40 London R
P2 Jeans Green 48 Paris PId PName
P3 Blouse Blue 48 Rome P3 Blouse
P4 Blouse Blue 44 London P4 Blouse
P5 Skirt Blue 40 Paris
P6 Shorts Red 42 London

28
Text search (no.2)
• The Address attribute contains the string ‘London’
Address LIKE '%London%'

• The supplier identification number is 3 and

• it is preceded by a single unknown character
• it is exactly 2 characters long
SId LIKE ‘_3’

• The Store attribute does not have an ‘e’ in the second position
Store NOT LIKE '_e%'

29
 • IS special operator
Searching for AttributeName IS [NOT] NULL
NULL values
• With NULL values, any comparison predicate is false

30
Managing NULL values
• Find the codes and the names of products with a size greater than 44
SELECT PId, PName
FROM P
WHERE Size>44;
P
PId PName Color Size Store R
P1 Jumper Red 40 London PId PName
P2 Jeans Green 48 Paris P2 Jeans
P3 Blouse Blue 48 Rome P3 Blouse
P4 Blouse Blue 44 London
P5 Skirt Blue NULL Paris
P6 Shorts Red 42 London

• The tuples with NULL size are not selected: the predicate Size>44 evaluates
to false
• With NULL values, any comparison predicate is false
Searching for NULL values (no.1)
• Find the codes and the names of the products whose size is unknown

SELECT PId, PName

FROM P
WHERE Size IS NULL;

P
PId PName Color Size Store
P1 Jumper Red 40 London
R
P2 Jeans Green 48 Paris
PId PName
P3 Blouse Blue 48 Rome
P5 Skirt
P4 Blouse Blue 44 London
P5 Skirt Blue NULL Paris
P6 Shorts Red 42 London

32
Searching for NULL values(n.2)
• Find the codes and the names of products with a size greater than 44,
or that may have a size greater than 44

SELECT PId, PName

FROM P
WHERE Size>44 OR Size IS NULL;

P
PId PName Color Size Store R
P1 Jumper Red 40 London PId Pname
P2 Jeans Green 48 Paris P2 Jeans
P3 Blouse Blue 48 Rome P3 Blouse
P4 Blouse Blue 44 London P5 Skirt
P5 Skirt Blue NULL Paris
P6 Shorts Red 42 London
 • ORDER BY clause
ORDER BY AttributeName [ASC | DESC]
{, AttributeName [ASC | DESC]}
Result • the default ordering is ascending
ordering • if DESC is not specified
• the ordering attributes must appear in the SELECT
clause
• even implicitly (as in SELECT *)

34
Result ordering (no.1)
• Find the codes of the products and their sizes, ordering the result by
decreasing size

SELECT PId, Size

FROM P
ORDER BY Size DESC;

P R
PId PName Color Size Store PId Size
P1 Jumper Red 40 London P2 48
P2 Jeans Green 48 Paris P3 48
P4 44
P3 Blouse Blue 48 Rome
P6 42
P4 Blouse Blue 44 London
P1 40
P5 Skirt Blue 40 Paris P5 40
P6 Shorts Red 42 London
 Result ordering (no.2)
• Find all information related to the products, ordering the result by
increasing name and decreasing size

SELECT PId, PName, Color, Size, Store SELECT *

FROM P FROM P
ORDER BY PName, Size DESC; ORDER BY PName, Size DESC;

R
PId PName Color Size Store
P3 Blouse Blue 48 Rome
P4 Blouse Red 44 London
P2 Jeans Green 48 Paris
P1 Jumper Red 40 London
P6 Shorts Red 42 London
P5 Skirt Blue 40 Paris
36
Result ordering (no.3)
• Find the codes of the products and the sizes expressed with the US
standard, ordering the result by increasing size

SELECT PId, Size-14 AS USSize

FROM P
ORDER BY USSize;

P R
PId PName Color Size Store PId USSize
P1 Jumper Red 40 London P5 26
P2 Jeans Green 48 Paris P1 28
P3 Blouse Blue 48 Rome P6 28
P4 Blouse Blue 44 London P4 30
P2 34
P5 Skirt Blue 40 Paris
P6 Shorts Red 42 London P3 34
37
 • Defined by the FROM and WHERE clauses
• The result and efficiency of the query
• are independent of the order of the tables in the FROM
clause
• are independent of the predicate order in the WHERE
Join clause
• the optimal execution order is selected by the DBMS
(optimizer module)
• FROM clause with N Tables
• at least N-1 join conditions in the WHERE clause

38
Join (n.1)
• Find the names of the suppliers that provide product P2

SId PID Qty

SId SName #Employees City S1 P1 300
S1 Smith 20 London S1 P2 200
S2 Jones 10 Paris S1 P3 400
S3 Blake 30 Paris S1 P4 200
S4 Clark 20 London S1 P5 100
S5 Adams 30 Athens S1 P6 100
S2 P1 300
S2 P2 400
S3 P2 200

39
Cartesian product
• Find the names of the suppliers that provide product P2

SELECT SName
FROM S, SP ;
Cartesian product

[Link] [Link] S.#Empl [Link] [Link] [Link] [Link]

S1 Smith 20 London S1 P1 300
S1 Smith 20 London S1 P2 200
S1 Smith 20 London S1 P3 400
S1 Smith 20 London S1 P4 200
S1 Smith 20 London S1 P5 100
S1 Smith 20 London S1 P6 100
S1 Smith 20 London S2 P1 300
… … … … … … …
S2 Jones 10 Paris S1 P1 300
… … … … … … …
S2 Jones 10 Paris S2 P1 300
… … … … … … …
41
Join (n.1)

=
[Link] [Link] S.#Empl [Link] [Link] [Link] [Link]
S1 Smith 20 London S1 P1 300
S1 Smith 20 London S1 P2 200
S1 Smith 20 London S1 P3 400
S1 Smith 20 London S1 P4 200
S1 Smith 20 London S1 P5 100
S1 Smith 20 London S1 P6 100
S1 Smith 20 London S2 P1 300
… … … … … … …
S2 Jones 10 Paris S1 P1 300
… … … … … … …
S2 Jones 10 Paris S2 P1 300
… … … … … … …
42
Join (n.1)
=
[Link] [Link] S.#Empl [Link] [Link] [Link] [Link]
S1 Smith 20 London S1 P1 300
S1 Smith 20 London S1 P2 200
S1 Smith 20 London S1 P3 400
S1 Smith 20 London S1 P4 200
S1 Smith 20 London S1 P5 100
S1 Smith 20 London S1 P6 100
S2 Jones 10 Paris S2 P1 300
S2 Jones 10 Paris S2 P2 400
S3 Blake 30 Paris S3 P2 200
S4 Clark 20 London S4 P3 200
S4 Clark 20 London S4 P4 300
S4 Clark 20 London S4 P5 400
43
Join (n.1)
• Find the names of the suppliers that provide product P2

SELECT SName Join condition

FROM S, SP
WHERE [Link]=[Link];

[Link]

44
Join (n.1)
• Find the names of the suppliers that provide product P2

SELECT SName Join condition

FROM S, SP
WHERE [Link]=[Link] AND PId='P2';

[Link]

45
Join (n.1)
[Link]='P2'
=
[Link] [Link] S.#Empl [Link] [Link] [Link] [Link]
S1 Smith 20 London S1 P1 300
S1 Smith 20 London S1 P2 200
S1 Smith 20 London S1 P3 400
S1 Smith 20 London S1 P4 200
S1 Smith 20 London S1 P5 100
S1 Smith 20 London S1 P6 100
S2 Jones 10 Paris S2 P1 300
S2 Jones 10 Paris S2 P2 400
S3 Blake 30 Paris S3 P2 200
S4 Clark 20 London S4 P3 200
S4 Clark 20 London S4 P4 300
S4 Clark 20 London S4 P5 400
46
Join (n.1)

[Link] [Link] S.#Empl [Link] [Link] [Link] [Link]

S1 Smith 20 London S1 P2 200
S2 Jones 10 Paris S2 P2 400
S3 Blake 30 Paris S3 P2 200

R
SName
Smith
Jones
Blake

47
Join (n.1)
• Find the names of the suppliers that provide product P2
• in relational algebra

[Link] [Link]
sPID=‘P2’

S sPId=‘P2’

SP S SP
Join (n.1)
• Find the names of the suppliers that provide product P2
• in relational algebra

SELECT SName SELECT SName

FROM S, SP FROM S,SP
WHERE [Link]=[Link] WHERE PId='P2' AND
AND PId='P2'; [Link]=[Link];

• The result and efficiency are independent

• from the order of the predicates in the WHERE clause
• from the order of the tables in the FROM clause
SQL Declarability
• In relational algebra (procedural language) we define the order in which
the operators are applied
• In SQL (declarative language) the best order is chosen by the optimizer
independently
• from the order of the conditions in the WHERE clause
• from the order of the tables in the FROM clause
Join (n.2)
• Find the name of suppliers who provide at least one red product

SELECT SName
FROM S, SP, P
WHERE [Link]=[Link] AND [Link]=[Link]
AND Color=‘Red';

• FROM Clause with N Tables

• at least N-1 join conditions in the WHERE clause
Join (n.2)
• Find the pairs of supplier codes such that both suppliers are based
in the same city

SELECT [Link], [Link]

FROM S AS SX, S AS SY
WHERE [Link]=[Link];

S AS SX S AS SY
SId SName #Employees City SId SName #Employees City
S1 Smith 20 London S1 Smith 20 London
S2 Jones 10 Paris S2 Jones 10 Paris
S3 Blake 30 Paris S3 Blake 30 Paris
S4 Clark 20 London S4 Clark 20 London
S5 Adams 30 Athens S5 Adams 30 Athens
52
Join (n.2)
• Find the pairs of supplier codes such that both suppliers are based
in the same city

SELECT [Link], [Link]

FROM S AS SX, S AS SY R
WHERE [Link]=[Link]; [Link] [Link]
S1 S1
S1 S4
S2 S2
S2 S3
• The result includes S3 S2
• pairs of identical values S3 S3
• permutations of the same pairs of values S4 S1
S4 S4
S5 S5

53
Join (n.2)
• Find the pairs of supplier codes such that both suppliers are based
in the same city

SELECT [Link], [Link]

FROM S AS SX, S AS SY R
WHERE [Link]=[Link] AND
[Link] [Link]
[Link] <> [Link]; S1 S1
S1 S4
S2 S2
S2 S3
S3 S2
• It removes pairs of identical values
S3 S3
S4 S1
S4 S4
S5 S5

54
Join (n.2)
• Find the pairs of supplier codes such that both suppliers are based
in the same city

SELECT [Link], [Link]

FROM S AS SX, S AS SY R
WHERE [Link]=[Link] AND [Link] [Link]
[Link] < [Link]; S1 S1
S1 S4
S2 S2 R
S2 S3 [Link] [Link]
S3 S2 S1 S4
• It eliminates the permutations of the same S2 S3
S3 S3
pairs of values S4 S1
S4 S4
S5 S5

55
Join: alternative syntax
• Different types of join may be specified
• outer join
• It allows differentiating between
• join conditions and
• tuple selection conditions
SELECT [DISTINCT] Attributes
FROM Table JoinType JOIN Table ON
JoinCondition
[WHERE TupleConditions];

JoinType = < INNER | [FULL | LEFT | RIGHT] OUTER >

56
INNER join
• Find the names of the suppliers that supply at least one red product

SELECT SName
FROM P INNER JOIN SP ON [Link]=[Link]
INNER JOIN S ON [Link]=[Link]
WHERE [Link]=‘Red';

57
OUTER join
• Find the codes and the names of the suppliers together with
the codes of the products they provide, also including the
suppliers that are not supplying any product
[Link] [Link] [Link]
S1 Smith P1
S1 Smith P2
S1 Smith P3

SELECT [Link], SName, PId S1 Smith P4

S1 Smith P5
FROM S LEFT OUTER JOIN SP ON S1 Smith P6

[Link]=[Link]; S2 Jones P1
S2 Jones P2
S3 Blake P2
S4 Clark P3
S4 Clark P4
S4 Clark P5
S5 Adams NULL 58
Aggregate Functions
Introduction to SQL

59
Aggregate function
• It operates on a set of values
• It produces a single (aggregate) value as a result
• It is specified in the SELECT clause
• non-aggregate attributes may not be specified at the same time
• multiple aggregate functions may be specified simultaneously
• Aggregate functions are only evaluated once all predicates in the
WHERE clause have been applied

60
Aggregate functions

COUNT: count of elements in a given attribute

SUM: sum of values for a given attribute

AVG: average of values for a given attribute

MAX: maximum value of a given attribute

MIN: minimum value of a given attribute

61
 • Counts the number of elements in a set
• rows in a table
• (possibly distinct) values for one or more attributes

COUNT (<*| [DISTINCT | ALL] ListOfAttributes >)}

COUNT
• If the function argument is preceded by DISTINCT, it
counts the number of distinct values of the
argument

62
The COUNT function (n.1)
• Find the number of suppliers

SELECT COUNT(*)
FROM S;

S
SId SName #Employees City R
S1 Smith 20 London
S2 Jones 10 Paris
5
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens

63
The COUNT function (n.2)
• Find the number of suppliers that supply at least one product
SP
SId PId Qty SELECT COUNT(*)
S1 P1 300 FROM SP;
S1 P2 200
S1 P3 400 R
S1 P4 200
S1 P5 100
12
S1 P6 100
S2 P1 300
S2 P2 400
S3 P2 200 • It counts the number of supplied products, not the
S4 P3 200
suppliers
S4 P4 300
S4 P5 400

64
The COUNT function (n.2)
• Find the number of suppliers that supply at least one product
SP
SId PId Qty SELECT COUNT(SId)
S1 P1 300 FROM SP;
S1 P2 200
S1 P3 400 R
S1 P4 200
S1 P5 100
12
S1 P6 100
S2 P1 300
S2 P2 400
S3 P2 200 • It still counts the number of supplied products, not the
S4 P3 200
suppliers
S4 P4 300
S4 P5 400

65
The COUNT function (n.2)
• Find the number of suppliers that supply at least one product
SP
SId PId Qty SELECT COUNT(DISTINCT SId)
S1 P1 300 FROM SP;
S1 P2 200
S1 P3 400 R
S1 P4 200
S1 P5 100
4
S1 P6 100
S2 P1 300
S2 P2 400
S3 P2 200 • It counts the number of distinct suppliers
S4 P3 200
S4 P4 300
S4 P5 400

66
 Aggregate
functions and • Aggregate functions are only evaluated once all
WHERE predicates in the WHERE clause have been applied

67
 Aggregate functions and WHERE
• Find the number of suppliers providing product P2
SP
SId PId Qty SELECT COUNT(*)
S1 P1 300
FROM SP
S1 P2 200
S1 P3 400 WHERE PId='P2';
S1 P4 200
S1 P5 100
Sid Pid Qty R
S1 P6 100
S1 P2 200
S2 P1 300
S2 P2 400 3
S2 P2 400
S3 P2 200
S3 P2 200
S4 P3 200
S4 P4 300
S4 P5 400

• Aggregate functions are only evaluated once all predicates in the WHERE clause
have been applied 68
 • SUM, MAX, MIN and AVG
• they allow an attribute or an expression as argument
• SUM and AVG
SUM, MAX, • they only allow numeric type or time interval attributes
MIN, AVG • MAX and MIN
• they require an expression that can be ordered
• may also be applied to character strings and time instants

69
The SUM function
• Find the overall quantity of supplied pieces for product P2

SP
SId PId Qty
SELECT SUM(Qty)
S1 P1 300 FROM SP
S1 P2 200
S1 P3 400
WHERE PId='P2';
S1 P4 200
S1 P5 100
SId PId Qty R
S1 P6 100
S1 P2 200
S2 P1 300
S2 P2 400 800
S2 P2 400
S3 P2 200
S3 P2 200
S4 P3 200
S4 P4 300
S4 P5 400

70
The GROUP BY operator
Introduction to SQL

71
 • Grouping clause
GROUP BY ListOfGroupingAttributes

• The order of grouping attributes is irrelevant

• In the SELECT statement only
• attributes specified in the GROUP BY clause
GROUP BY • aggregate functions
are allowed to appear
• Attributes that are unambiguously determined by
other attributes already present in the GROUP BY
clause may be added without altering the result

72
 Grouping
• For each product, find the overall quantity of supplied pieces

SP SP
SId PId Qty SId PId Qty
S1 P1 300 S1 P1 300
R
S1 P2 200 S2 P1 300
PId
S1 P3 400 S1 P2 200
P1 600
S1 P4 200 S2 P2 400
S1 P5 100 S3 P2 200 P2 800
S1 P6 100 S1 P3 400 P3 600
S2 P1 300 S4 P3 200 P4 500
S2 P2 400 S1 P4 200 P5 500
S3 P2 200 S4 P4 300 P6 100
S4 P3 200 S1 P5 100
S4 P4 300 S4 P5 400
S4 P5 400 S1 P6 100

73
 Grouping
• For each product, find the overall quantity of supplied pieces

SP SP
SId PId Qty SId PId Qty
S1 P1 300 S1 P1 300
R
S1 P2 200 S2 P1 300
PId
S1 P3 400 S1 P2 200
P1 600
S1 P4 200 S2 P2 400 SELECT PId, SUM(Qty)
S1 P5 100 S3 P2 200 P2 800
P3 600
FROM SP
S1 P6 100 S1 P3 400
S2 P1 300 S4 P3 200 P4 500 GROUP BY PId;
S2 P2 400 S1 P4 200 P5 500
S3 P2 200 S4 P4 300 P6 100
S4 P3 200 S1 P5 100
S4 P4 300 S4 P5 400
S4 P5 400 S1 P6 100

74
GROUP BY and WHERE
• For each product, find the overall quantity of pieces supplied by
suppliers based in Paris

SP
SId PId Qty
S S1 P1 300
SId SName #Employees City S1 P2 200
S1 Smith 20 London S1 P3 400
S2 Jones 10 Paris S1 P4 200
S3 Blake 30 Paris S1 P5 100
S4 Clark 20 London S1 P6 100
S5 Adams 30 Athens S2 P1 300
S2 P2 400
S3 P2 200
S4 P3 200
S4 P4 300
S4 P5 400
75
GROUP BY and WHERE
• For each product, find the overall quantity of pieces supplied by
suppliers based in Paris

SELECT ...
FROM SP, S
WHERE [Link]=[Link] AND City=‘Paris'
...

76
GROUP BY and WHERE
• For each product, find the overall quantity of pieces supplied by
suppliers based in Paris

[Link] [Link] S.#Employees [Link] [Link] [Link] [Link]

S1 Smith 20 London S1 P1 300
S1 Smith 20 London S1 P2 200
S1 Smith 20 London S1 P3 400
S1 Smith 20 London S1 P4 200
S1 Smith 20 London S1 P5 100
S1 Smith 20 London S1 P6 100
S2 Jones 10 Paris S2 P1 300
S2 Jones 10 Paris S2 P2 400
S3 Blake 30 Paris S3 P2 200
S4 Clark 20 London S4 P3 200
S4 Clark 20 London S4 P4 300
S4 Clark 20 London S4 P5 400
77
GROUP BY and WHERE
• For each product, find the overall quantity of pieces supplied by
suppliers based in Paris

SELECT PId, SUM(Qty)

FROM SP, F
WHERE [Link]=[Link] AND City=‘Paris'
GROUP BY PId;

• Products that are not supplied by any supplier are not included in the
result

78
GROUP BY and WHERE
• For each product, find the overall quantity of pieces supplied by
suppliers based in Paris

[Link] [Link] R
[Link]
P1 300
P1 300
P2 400
P2 600
P2 200

79
GROUP BY and SELECT
• For each product, find the code, the name and the overall supplied
quantity

SELECT [Link], PName, SUM(Qty)

FROM P, SP
WHERE [Link]=[Link]
GROUP BY [Link], PName

• attributes that are unambiguously determined by other attributes

already present in the GROUP BY clause may be added without
altering the result
80
 • You cannot use the WHERE clause to define selection
conditions on groups

• Selection condition on groups expressed in HAVING

Group selection clause:
condition: HAVING HAVING Group Conditions

• it is possible to specify conditions only on aggregated

functions

81
Group selection condition(n.1)
• Find the overall quantity of supplied pieces for the products for which
at least 600 pieces are supplied overall
SP SP
SId PId Qty SId PId Qty
S1 P1 300 S1 P1 300
S1 P2 200 S2 P1 300
S1 P3 400 S1 P2 200 R
S1 P4 200 S2 P2 400 PId
S1 P5 100 S3 P2 200
P1 600
S1 P6 100 S1 P3 400
P2 800
S2 P1 300 S4 P3 200
S2 P2 400 P3 600
S1 P4 200
S3 P2 200 S4 P4 300
S4 P3 200 S1 P5 100
S4 P4 300 S4 P5 400
S4 P5 400 S1 P6 100
82
Group selection condition (n.1)
• Find the overall quantity of supplied pieces for the products for which
at least 600 pieces are supplied overall

SELECT PId, SUM(Qty)

FROM SP
GROUP BY PId
HAVING SUM(Qty)>=600;

• The HAVING clause allows the specification of conditions on the

aggregate functions

83
Group selection condition (n.2)
• Find the codes of the red products supplied by more than one
supplier
SP
SId PId Qty
P S1 P1 300
PId PName Color Size Store S1 P2 200
P1 Jumper Red 40 London
S1 P3 400
P2 Jeans Green 48 Paris
S1 P4 200
P3 Blouse Blue 48 Rome
S1 P5 100
P4 Blouse Blue 44 London
S1 P6 100
P5 Skirt Blue 40 Paris
S2 P1 300
P6 Shorts Red 42 London S2 P2 400
S3 P2 200
S4 P3 200
S4 P4 300
S4 P5 400

84
Group selection condition(n.2)
• Find the codes of the red products supplied by more than one
supplier

SELECT [Link]
FROM SP, P
WHERE [Link]=[Link] AND Color=‘Red'
GROUP BY [Link]
HAVING COUNT(*)>1;

85
Group selection condition (n.2)
• Find the codes of the red products supplied by more than one
supplier

[Link] [Link] [Link] [Link] [Link] [Link] [Link] [Link]

S1 P1 300 P1 Jumper Red 40 London
S2 P1 300 P1 Jumper Red 40 London
S1 P6 100 P6 Shorts Red 42 London

R
PId
P1

86