• Week 03 Thursday
• Video Material
• Example Database: Beers/Bars/Drinkers
• Exercise: Delete/Update Examples
• Problem Solving in SQL
• Views
• SQL Queries
• Semantics of SELECT..WHERE
• Semantics of JOIN
• Exercise: Queries on Simple Beer DB
• Finding the biggest/smallest/etc
• Exercise: More queries on Simple Beer DB
• More Problem Solving in SQL

❖ Week 03 Thursday

In today's lecture ...

• SQL Query Language
• Stored Procedures
• PLpgSQL Procedural language (next week?)

Things to do ...

• Quiz 2 due before Friday midnight
• Set up your PostgreSQL server
  (490/575 students have logged in to vxdb2 and have /localstorage)

❖ Video Material

Slides and Videos

• (slightly) formal topic-based videos
• watch them whenever you need to know their content

Lecture Material

• videos + slides from Tue and Thu lectures
• also, exercise files (Data) produced while doing exercises
• slides should be avail (just) before the lecture
• recording initially on Echo360   (takes > 2 hours to be processed)
• YouTube version at least 1 hour after Echo360

❖ Example Database: Beers/Bars/Drinkers

Consider the following ER model:

❖ Exercise: Delete/Update Examples

Carry out the following actions on the beer database:

• Increase the prices for beers by 10%
• Remove all beers selling for more than 5.00
• Remove all drinkers who live in Randwick
• Close down the Royal in Randwick

❖ Problem Solving in SQL

Developing SQL queries ...

• relate required data to attributes in schema
• identify which tables contain these attributes
• combine data from relevant tables (FROM, JOIN)
• specify conditions to select relevant data (WHERE)
• [optional] define grouping attributes (GROUP BY)
• develop expressions to compute output values (SELECT)

Learn some query patterns and know when to apply them

❖ Views

Views are like "virtual tables"

create [or replace] view ViewName ( AttributeNames )
as
select ResultValues (one per attribute) from ... ;

Things to note:

• AttributeNames are outputs
• inherit their type from corresponding select expression
• each view has an associated tuple type (like a table does)

❖ Views (cont)

Views are very useful in simplifying complex queries

Views look like tables

• you can  refer to them in queries
• you cannot  make changes via a view   (i.e. no insert/delete/update)

If using  create or replace view  you cannot

• change the number of attributes   (by changing view's heading)
• change the types of attributes   (by changing view's query)

Need to  drop view , then  create view

❖ Views (cont)

Two ways of defining a view

create or replace view V
as
select x as a1, y as a2, z as a3
from   R
where  ... ;

-- alternatively

create or replace view V(a1, a2, a3)
as
select x, y, z
from   R
where  ...;

❖ SQL Queries

Functionality provided by SQL ...

Filtering: extract attributes from tuples, extract tuples from tables

SELECT b,c FROM R(a,b,c,d) WHERE a > 5

Combining: merging related tuples from different tables

... FROM R(x,y,z) JOIN S(a,b,c) ON R.y = S.a

Summarising: aggregating values in a single column

SELECT avg(mark) FROM ...

Set operations: union, intersection, difference

❖ SQL Queries (cont)

More functionality provided by SQL ...

Grouping: forming subsets of tuples sharing some property

... GROUP BY R.a

(forms groups of tuples from R sharing the same value of a)

Group Filtering: selecting only groups satisfying a condition

... GROUP BY R.a HAVING max(R.a) < 75

Renaming: assign a name to a component of a query

SELECT a as name
FROM Employee(a,b,c) e WHERE e.b > 50000

❖ Semantics of SELECT..WHERE

The query

SELECT * FROM R WHERE Condition

can be treated as

Results = {}
foreach tuple T in R {
   if T satisfies Condition
      add T to Results
}

❖ Semantics of JOIN

The query

SELECT * FROM R JOIN S ON R.x = S.y WHERE Condition

can be treated as

Results = {}
foreach tuple TR in R {
   foreach tuple TS in S {
      if TR.x == TS.y {
         Tjoined = TR+TS  # combined
         if Tjoined satisfies Condition
            add Tjoined to Results
      }
   }
}

❖ Exercise: Queries on Simple Beer DB

Answer these queries on the Beer database:

1. What beers are made by Toohey's?
2. Show beers with headings "Beer", "Brewer".
3. How many different beers are there?
4. How many different brewers are there?
5. Find pairs of beers by the same manufacturer.
6. (a) Which beers does John like?
   (b) Find the brewers whose beers John likes.
7. (a) How many beers does each brewer make?
   (b) Which brewer makes the most beers?
   (c) Which beers are the only one made by their brewer?

❖ Finding the biggest/smallest/etc

Example: what is the most expensive beer?

• what is the highest price for a beer
• which beers are sold for this price

As SQL:

create view maxPrice as select max(price) from Sells;

create view highestPriceBeer as
select s.beer
from   Sells s 
where  s.price = (select * from maxPrice);

❖ Finding the biggest/smallest/etc (cont)

Don't need the view; can calculate "inline"

create view highestPriceBeer as
select s.beer
from   Sells s
where  s.price = (select max(price) from Sells);

But you always need a query to extract the single value

An example of a common pattern:

• what is the X  of the Y  with the maximum/minimum Z ?

E.g.   X = name,   Y = beer,   Z = price

❖ Finding the biggest/smallest/etc (cont)

Note: there may be more than one equally biggest/smallest

The following is not a general solution

create view highestPriceBeer as
select s.beer
from   Sells s
order  by s.price desc
limit  1

Avoid limit unless doing e.g. pagination.

❖ Exercise: More queries on Simple Beer DB

8. List of beers by each brewer
9. Find the beers sold at bars where John drinks.
10. Bars where either Gernot or John drink.
11. Bars where both Gernot and John drink.
12. Bars where John drinks but Gernot doesn't.
13. Find bars that serve New at the same price
    as the Coogee Bay Hotel charges for VB.
14. Find the average price of common beers
    (i.e. served in more than two hotels).
15. Which bar sells 'New' cheapest?

❖ More Problem Solving in SQL

Example: what is cheapest beer at each bar?

for each Bar {
   find cheapest price at the bar
   fine name of beer with this price
}

Needs correlated subquery

select s.bar, s.beer, s.price:numeric(4,2)
from   Sells s
where  s.price = (select min(s1.price)
                  from   Sells s1
                  where  s1.bar = s.bar);