Transcript Review of relational model and SQL

CS 440
Database Management Systems
Lecture 3: Review of Relational Model and
SQL
1
Announcements
• You may find the chapters related to each lecture in the
schedule page of the course Web site.
• Check out the textbook website for practice problems
with solutions: pages.cs.wisc.edu/~dbbook/
• Change in our TA office hour:
– This week: Wednesday 4 – 5 pm.
– Rest of the term: Monday 11 am- 12 pm.
2
Example schema
Beers(name, manf)
Bars(name, addr, license)
Drinkers( name, addr, phone)
Likes(drinker, beer)
Sells(bar, beer, price)
Frequents(drinker, bar)
3
Aggregation functions
• Compute some value based on the values of an attribute.
– Example functions: Count, Sum, Avg, Min, Max
– Each RDBMS may define additional functions.
• Example: Using Bars(name, addr, license), find the
number of bars.
Select Count(name)
From Bars;
4
Aggregation functions
• Using Distinct, aggregation functions ignore duplicates.
• Example: Using Likes(drinker, beer), find the number of
drinkers who like Bud Lite.
Select Count( Distinct drinker)
From Likes
Where beer =‘Bud Lite’;
5
Aggregation functions
•
Generally, aggregation functions do not consider NULL
values.
The number of priced beers
sold by Joe Bar.
Select Count(price)
From Sells
Where bar=‘Joe Bar’;
The number of beers
sold by Joe Bar.
Select Count(beer)
From Sells
Where bar=‘Joe Bar’;
The number of beers
sold by Joe Bar.
Select Count(*)
From Sells
Where bar=‘Joe Bar’;
6
Aggregation functions over groups
• We want to aggregate values for groups of tuples.
• Example: Using Sells(bar, beer, price) find the minimum
price of each beer.
– Group tuples in Sells based on beer.
– Compute Min over the prices in each group of tuples.
bar
Joe Bar
Big Horse
Joe Bar
Big Horse
Red Lion
Red Lion
Joe Bar
Blind Pig
beer
Bud
Bud Lite
Imported
Bud
Imported
Bud
Bud Lite
Bud
price
9
10
15
10
20
16
5
10
beer
Bud
Imported
Bud Lite
minprice
9
15
5
7
Group by
• Example: Using Sells(bar, beer, price) find the minimum
price of each beer.
Select beer, Min(price) As minprice
From Sells
Group By beer;
bar
Joe Bar
Big Horse
Joe Bar
Big Horse
Red Lion
Red Lion
Joe Bar
Blind Pig
beer
Bud
Bud Lite
Imported
Bud
Imported
Bud
Bud Lite
Bud
price
9
10
15
10
20
16
5
10
beer
Bud
Imported
Bud Lite
optional
minprice
9
15
5
8
Group by
• You may use multiple attributes for grouping.
• The attributes in the Select clause are either aggregated
values or attributes in the Group By clause.
Select beer, Min(price),bar
error
From Sells
Group By beer;
– Exceptions in some RDBMS, e.g., MySQL 5.7.
•
Generally, Group By does not sort the groups.
– There are exceptions, e.g., older versions of MySQL, but do not trust
them!
9
Grouping attributes from different relations.
• Example: Using Likes(drinker, beer) and Sells(bar, beer, price),
for each drinker find the minimum price of every beer he/she likes.
Select drinker, beer, Min(price) As minprice
From Likes, Sells
Where Likes.beer = Sells.beer
Group By drinker, beer;
bar
Joe Bar
Big Horse
Joe Bar
Big Horse
Red Lion
Red Lion
Joe Bar
Blind Pig
beer
Bud
Bud Lite
Imported
Bud
Imported
Bud
Bud Lite
Bud
price
9
10
15
10
20
16
5
10
drinker
John
Ben
Smith
John
beer
Bud
Bud Lite
Imported
Bud Lite
drinker
John
John
Ben
Smith
beer
Bud
Bud Lite
Bud Lite
Imported
minprice
9
5
5
15
10
Filtering groups
• We may filter out some groups using their attributes’ values.
Select beer, Min(price) As minprice
From Sells
Where bar=‘Red Lion’ or bar=‘Big Horse’
Group By beer;
bar
Joe Bar
Big Horse
Joe Bar
Big Horse
Red Lion
Red Lion
Joe Bar
Blind Pig
beer
Bud
Bud Lite
Imported
Bud
Imported
Bud
Bud Lite
Bud
price
9
10
15
10
20
16
5
10
beer
Bud
Imported
Bud Lite
minprice
10
20
10
11
Filtering groups based on aggregated values
• Example: Using Sells(bar, beer, price), find the minimum
price of each beer whose maximum price is less than 11.
bar
Joe Bar
Big Horse
Joe Bar
Big Horse
Red Lion
Red Lion
Joe Bar
Blind Pig
beer
Bud
Bud Lite
Imported
Bud
Imported
Bud
Bud Lite
Bud
price
9
10
15
10
20
16
5
10
beer
Bud Lite
minprice
5
Select beer, Min(price) As minprice
From Sells
error
Where Max(price) < 11
Group By beer
12
Having clause
• We use Having clauses to filter out groups based on their
aggregated values.
Select beer, Min(price) As minprice
From Sells
Group By beer
Having Max(price) < 11
bar
Joe Bar
Big Horse
Joe Bar
Big Horse
Red Lion
Red Lion
Joe Bar
Blind Pig
beer
Bud
Bud Lite
Imported
Bud
Imported
Bud
Bud Lite
Bud
price
9
10
15
10
20
16
5
10
beer
Bud Lite
minprice
5
13
Having clause
• We may use aggregated values over attributes other than the ones
in the Group By clause.
• Example: Using Sells(bar, beer, price), find the minimum price
of each beer sold in more than three bars.
bar
Joe Bar
Big Horse
Joe Bar
Big Horse
Red Lion
Red Lion
Joe Bar
Blind Pig
beer
Bud
Bud Lite
Imported
Bud
Imported
Bud
Bud Lite
Bud
price
9
10
15
10
20
16
5
10
beer
Bud
minprice
9
Select beer, Min(price) As minprice
From Sells
Group By beer
Having Count(bar) > 3
14
Having clause may act as a Where clause
• Example: Using Sells(bar, beer, price), find the minimum price of
Bud or beers whose maximum price is less than 11.
Select beer, Min(price)
From Sells
Group By beer
Having (Max(price) < 11) Or (beer=‘Bud’)
• It works only for the attributes in the Group By clause.
Select beer, Min(price)
From Sells
error
Group By beer
Having (Max(price) < 11) Or (bar=‘Red Lion’)
15
Sorting the output
• Example: Using Sells(bar, beer, price) find the minimum price of each
beer whose maximum price is at least 15 and sort the results according to
beers’ names.
Select beer, Min(price) As minprice
From Sells
Group By beer
Having Max(price) >= 15
Order By beer;
bar
Joe Bar
Big Horse
Joe Bar
Big Horse
Red Lion
Red Lion
Joe Bar
Blind Pig
beer
Imported
Bud Lite
Bud
Bud
Imported
Bud
Bud Lite
Bud
price
15
10
9
10
20
16
5
10
beer
minprice
Bud
9
Imported 15
16
Sorting the output
• One may use Desc to change the sort order.
• Previous example in descending order of beers’ names:
Select beer, Min(price) As minprice
From Sells
Group By beer
Having Max(price) >= 15
Order By beer Desc;
• You may use Order By without Group By and Having.
• Example: Using Sells(bar, beer, price), provide a list of beer prices
sorted by bars’ and beers’ names.
Select bar, beer, price
From Sells
Order By bar, beer;
17
Review problems:
people betting on OSU football games
Out(game, outcome)
Bets(who, outcome, game, amt)
game
USC
outcome
W
who
John
outcome game
W
USC
amt
200
UCLA
Stanford
L
W
John
John
Kevin
W
L
L
UCLA
Arizona
UO
100
150
210
Kevin
Kevin
L
W
UCLA
50
Stanford 120
Some games have not been played yet, e.g., Arizona.
18
Problem 1
List the completed games that nobody bet on.
game
USC
outcome
W
who
John
outcome game
W
USC
amt
200
UCLA
Stanford
L
W
John
John
Kevin
W
L
L
UCLA
Arizona
UO
100
150
210
Kevin
Kevin
L
W
UCLA
50
Stanford 120
game
19
Problem 1
List the completed games that nobody bet on.
(Select Game
From Out)
Except
(Select Game
From Bets)
20
Problem 2
Who bet the most money on a single game?
game
USC
outcome
W
who
John
outcome game
W
USC
amt
200
UCLA
Stanford
L
W
John
John
Kevin
W
L
L
UCLA
Arizona
UO
100
150
210
Kevin
Kevin
L
W
UCLA
50
Stanford 120
who
amt
Kevin
210
21
Problem 2
Who bet the most money on a single game?
Select Who, Amt
From Bets
Where Amt >= All
(Select Amt
From Bets)
22
Problem 3
List the games that all bettors agree on.
game
USC
outcome
W
who
John
outcome game
W
USC
amt
200
UCLA
Stanford
L
W
John
John
Kevin
W
L
L
UCLA
Arizona
UO
100
150
210
Kevin
Kevin
L
W
UCLA
50
Stanford 120
game
Stanford
Arizona
UO
23
Problem 3
List the games that all bettors agree on.
(Select game
From Bets)
Except
(Select Bets1.game
From Bets Bets1, Bets Bets2
Where (Bets1.game = Bets2.game)
And (Bets1.outcome <> Bets2.outcome))
24
Problem 4
For each game, the number of people betting on OSU to
win and the number betting on OSU to lose.
game
outcome
who
outcome game
amt
USC
W
UCLA
L
Stanford
W
John
John
John
Kevin
W
W
L
L
USC
UCLA
Arizona
UO
200
100
150
210
Kevin
Kevin
L
W
UCLA
50
Stanford 120
game
outcome
num
Stanford
W
1
UO
L
1
UCLA
W
1
UCLA
L
1
USC
W
1
Arizona
L
1
25
Problem 4
For each game, the number of people betting on OSU to
win and the number betting on OSU to lose.
Select game, outcome, Count(who) As num
From Bets
Group By game, outcome
26
Problem 5
Find the people who have made two or more bets on OSU
to lose.
game
USC
outcome
W
UCLA
Stanford
L
W
who
Kevin
who
outcome game
amt
John
John
John
Kevin
W
W
L
L
USC
UCLA
Arizona
UO
200
100
150
210
Kevin
Kevin
L
W
UCLA
50
Stanford 120
27
Problem 5
Find the people who have made two or more bets on OSU
to lose.
Select who
From Bets
Where outcome = ‘L’
Group By who
Having Count(outcome) >= 2
28
Problem 6
Who bet the most money overall?
game
USC
outcome
W
UCLA
Stanford
L
W
who
sumAmt
John
450
who
outcome game
amt
John
John
John
Kevin
W
W
L
L
USC
UCLA
Arizona
UO
200
100
150
210
Kevin
Kevin
L
W
UCLA
50
Stanford 120
29
Problem 6
Who bet the most money overall?
Select who, Sum(amt) As sumAmt
From Bets
Group By who
Having Sum(amt) >= ALL
(Select Sum(amt)
From Bets
Group By who)
30
Problem 7
Who has bet on every game?
game
USC
outcome
W
UCLA
Stanford
L
W
Who
who
outcome game
amt
John
John
John
Kevin
W
W
L
L
USC
UCLA
Arizona
UO
200
100
150
210
Kevin
Kevin
L
W
UCLA
50
Stanford 120
31
Problem 7
Who has bet on every game?
Create View AllGames As
(Select game From Out) Union
(Select game From Bets)
Select who
From Bets
Group By who
Having Count(Distinct game) =
(Select Count(Distinct game)
From AllGames)
32
Problem 8
What games have won the most money for the people who
bet on OSU to win?
game
USC
outcome
W
UCLA
Stanford
L
W
game
USC
who
outcome
game
amt
John
John
John
Kevin
W
W
L
L
USC
UCLA
Arizona
UO
200
100
150
210
Kevin L
Kevin W
UCLA
Stanfor
d
50
120
33
Problem 8
What games have won the most money for the people who bet on
OSU to win?
Create View Success-Win As
(Select Bets.game, Sum(Bets.amt) As SumAmt
From Bets, Out
Where out.game = Bets.game And
Bets.outcome = ‘W’ And Out.outcome = ‘W’
Group By Game)
Select Distinct game
From Success-Win
Where SumAmt >= All
(Select SumAmt
From Success-Win)
34
Problem 9
List the people who won some money so far.
game
USC
outcome
W
UCLA
Stanford
L
W
who
John
Kevin
who
outcome
game
amt
John
John
John
Kevin
W
W
L
L
USC
UCLA
Arizona
UO
200
100
150
210
Kevin L
Kevin W
UCLA
Stanfor
d
50
120
35
Problem 9
List the people who won some money so far.
Create View Success As
(Select who, Sum(amt) As pAmt
From Bets, Out
Where Out.game = Bets.game And Bets.outcome = Out.outcome
Group By who)
Create View Failure As
(Select who, Sum(amt) As nAmt
From Bets, Out
Where Out.game = Bets.game And Bets.outcome <> Out.outcome
Group By who)
36
Problem 9
List the people who won some money so far.
Select who
From Success, Failure
Where Success.who = Failure.who And
Success.pAmt > Failure.nAmt
37
Data manipulation: insertion
• Inserting new tuple(s) into a relation.
Insert into R(A1,…,An) Values (a1,…,an);
• Example: insert tuple (‘Big Horse’,’Bud’,3) in Sells.
Insert into Sells(bar, beer, price)
Values (‘Big Horse’,‘Bud’,3);
• The input of Insert can be the result of another query.
Insert into Beers(name)
Select beer From Sells;
38
Data manipulation: deletion
• Removing tuple(s) from a relation.
Delete From R
Where R.A = ‘a’;
• Remove all tuples from relation R.
Delete From R;
• Example: John does not go to Old Horse anymore.
Delete From Frequents
Where drinker = ‘John’ and
bar = ‘Old Horse’;
39
Data manipulation: update
• Updating the values of given attributes in certain tuples
of a relation.
Update R
Set R.A = a
Where R.B = b;
• Old Horse has changed the price of Bud to 3.
Update Sells
Set price = 3
Where bar = ‘Old Horse’ And
beer = ‘Bud’;
40
Is SQL enough?
• Using IsParent(parent,child), find all descendants of a
given person.
• It is proved that it is not possible to write this query in
SQL that we have discussed so far.
– It (relational algebra/ calculus) express first order logic.
We need more expressive to describe recursion.
• There are generally three methods to make SQL
stronger to express these queries.
41
Recursion
• Use Connect by (Oracle, DB2, …) to travers hierarchical data
Select Leverl, child, parent
From IsParent
Start with parent Is Null
Connect by prior child = parent.
• Common Table Expression (CTE)
– Introduced in SQL-99 standard.
– It can be defined based on its own content.
– Implemented by major RDBMSs Oracle, SQL Server, DB2,
PostgreSQL, …
• Stored procedures
– Enriches SQL with the constructs of procedural languages
• Loops, variables, …
42