• Week 03 Wednesday
• Video Material
• Example Database: Beers/Bars/Drinkers
• Exercise: Queries on Simple Beer DB
• Problem Solving in SQL
• Views
• Exercise: Defining Views on simple Beer DB
• Exercise: More queries on Simple Beer DB
• Problem Solving Patterns for SQL
• Exercise: SQL Set and Join Operations
• What's wrong with SQL?
• Database Programming

❖ Week 03 Wednesday

Things to Note ...

• Next Monday is a public holiday ... no lecture, no tutes
  • for lecture,I will make a video as if in CLB7
  • for tutes, attend a different tute sometime in Week 04
• A new ass1.dump was added on Sept 22
• Auto-testing is coming soon-ish ... use Examples until then

Things to do ...

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

In today's lecture ...

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

❖ Video Material

Topic Videos

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

Lectures

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

❖ Example Database: Beers/Bars/Drinkers

Consider the following ER model:

❖ 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?

❖ 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 provide a useful tool for abstraction

• use a view to give a "table" that makes it simple to solve a query

❖ 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)

Example view definition:

create table R (x integer, y text, z char(3));
create view RR(a, b) as select x,y from R;

The view RR has type (integer,text)

You cannot redefine it as e.g.

create view RR(a, b) as select y,z from R;

This has type (text,char(3)), incompatible with the original type.

To change the number/types of view attributes:

drop view RR;
create view RR(a, b) as select y,z from R;

❖ 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  ...;

❖ Exercise: Defining Views on simple Beer DB

Define the following:

• define a view bb(brewery,nbeers)
  to give a count of the number of beers made by each brewer
• define a view avgprice(bar,price)
  to give average beer prices at each bar
• define a view liked(drinker,beers)
  to give a list of beers liked by each drinker

❖ Exercise: More queries on Simple Beer DB

Define views to answer each of the following:

8. Comma-separated 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?

❖ Problem Solving Patterns for SQL

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);

❖ Problem Solving Patterns for SQL (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

❖ Problem Solving Patterns for SQL (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.

❖ Problem Solving Patterns for SQL (cont)

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);

❖ Problem Solving Patterns for SQL (cont)

Answering queries like: Which X's are RelatedTo all Y's

AllYs = select Y from Ys
for each X {
   YsRelatedToX = select Y from R where ...X...
   if YsRelatedToX == AllYs
      X is added to the results
}

In SQL, you can't compare sets directly, so

not exists (
   (select Y from Ys)  -- all Y tuples
   except
   (select Y from R where ...X...)
)

❖ Problem Solving Patterns for SQL (cont)

Example: Which beers are sold at all bars?

select b.name
from   Beers b
where  not exists (
          (select name from Bars)
          except
          (select bar from Sells where beer = b.name)
       )
;

❖ Problem Solving Patterns for SQL (cont)

Some queries require all tuples from R to appear in   R join S

• even if some tuples in R have no related tuples in S

Example: How many bars in are there in suburbs where drinkers live?

• include all suburbs where drinkers live, even those with no bars

select d.addr, count(b.id) as nbars
from   Drinkers d
         left outer join Bars b on d.addr=b.addr
group  by d.addr
;

❖ Exercise: SQL Set and Join Operations

Consider two tables:

R(a char(1), b integer)   S(a char(1), c integer)

R  a  |  b         S  a  |  c
 -----+-----        -----+-----
   a  |  1            a  |  2
   b  |  2            b  |  2
   c  |  3            d  |  2
   d  |  4            e  |  2

What is the result of:

• R union S,     R intersect S,     R except S
• R join S,     R natural join S,     R left outer join S

❖ What's wrong with SQL?

Consider the problem of withdrawal from a bank account:

If a bank customer attempts to withdraw more funds than they have in their account, then indicate "Insufficient Funds", otherwise update the account

An attempt to implement this in SQL:

select 'Insufficient Funds'
from   Accounts
where  acctNo = AcctNum and balance < Amount;
update Accounts
set    balance = balance - Amount
where  acctNo = AcctNum and balance >= Amount;
select 'New balance: '||balance
from   Accounts
where  acctNo = AcctNum;

❖ What's wrong with SQL? (cont)

Two possible evaluation scenarios:

• displays "Insufficient Funds", UPDATE has no effect,
• UPDATE occurs as required, displays changed balance

Some problems:

• SQL doesn't allow parameterisation (e.g. AcctNum)
• always attempts UPDATE, even when it knows it's invalid
• need to evaluate (balance < Amount) test twice
• always displays balance, even when not changed

To accurately express the "business logic", we need facilities like conditional execution and parameter passing.

❖ Database Programming

Database programming requires a combination of

• manipulation of data in DB   (via SQL)
• conventional programming   (via procedural code)

This combination is realised in a number of ways:

• passing SQL commands via a "call-level" interface
  (prog lang is decoupled from DBMS; most flexible; e.g. Java/JDBC, JDBCython/psycopg2)
• embedding SQL into augmented programming languages
  (requires pre-processor for language; typically DBMS-specific; e.g. SQL/C)
• special-purpose programming languages in the DBMS
  (closely integrated with DBMS; enable extensibility; e.g. SQL/PSM, PL/SQL, PLpgSQL)

❖ Database Programming (cont)

Combining SQL and procedural code solves the "withdrawal" problem:

create function
    withdraw(acctNum text, amount integer) returns text
declare bal integer;
begin
    set bal = (select balance
               from   Accounts
               where  acctNo = acctNum);
    if (bal < amount) then
        return 'Insufficient Funds';
    else
        update Accounts
        set    balance = balance - amount
        where  acctNo = acctNum;
        set bal = (select balance
                   from   Accounts
                   where  acctNo = acctNum);
        return 'New Balance: ' || bal;
    end if
end;

(This example is actually a stored procedure, using SQL/PSM syntax)