和之前lab的文件复制方式以及数据库构建方法一样，本文不再赘述。该练习的ER图如下：

ER Design

schema文件如下：

-- Beer rating database


create table Location (
    id          serial primary key,
    state       varchar(50),  -- not every country has states
    country     varchar(50) not null
);

create table Taster (
    id          serial primary key,
    family      varchar(30) not null,
    given       varchar(30),  -- some people have only one name
    livesIn     integer not null references Location(id)
);

create table Brewer (
    id          serial primary key,
    name        varchar(50) not null,
    locatedIn   integer not null references Location(id)
);

create table BeerStyle (
    id          serial primary key,
    name        varchar(30) not null
);

create table Beer (
    id          serial primary key,
    name        varchar(50) not null,
    style       integer not null references BeerStyle(id),
    brewer      integer not null references Brewer(id)
);

create table Ratings (
    taster      integer not null references Taster(id),
    beer        integer not null references Beer(id),
    score       integer not null
                constraint validRating
            check (score >= 1 and score <= 5)
);

Q1

Find out who made what ratings.
If you look at the Ratings table, you'll see that it contains just a bunch of numbers. This is good for being compact, but hard to work out what it all means. Write an SQL query that will put together the data from the Ratings table with the Taster and Beer tables so that you get a better idea of who rated which beer. The result should display the taster's name (given name only), along with the name of the beer, its brewer and the rating. Order the table by the tasters' names; for a given taster, sort in descreasing order of rating (i.e., highest-rated beer first).
When you invoke the view, you should see output that looks like this:

taster |          beer          |          brewer          | rating 
--------+------------------------+--------------------------+--------
 Adam   | Old                    | Toohey's                 |      4
 Adam   | Victoria Bitter        | Carlton and United       |      1
 Adam   | New                    | Toohey's                 |      1
 Geoff  | Redback                | Matilda Bay Brewing      |      4
 Geoff  | James Squire Pilsener  | Maltshovel Brewery       |      4
 Geoff  | Empire                 | Carlton and United       |      3
 Hector | Sierra Nevada Pale Ale | Sierra Nevada            |      4
 Hector | Fosters                | Carlton and United       |      3
 Jeff   | Sierra Nevada Pale Ale | Sierra Nevada            |      4
 Jeff   | Burragorang Bock       | Scharer's Little Brewery |      3
 Jeff   | Rasputin               | North Coast Brewing      |      1
 John   | Sierra Nevada Pale Ale | Sierra Nevada            |      5
 John   | 80/-                   | Calendonian Brewing      |      4
 John   | Rasputin               | North Coast Brewing      |      4
 John   | Empire                 | Carlton and United       |      3
 John   | Chimay Red             | Chimay                   |      3
 John   | Crown Lager            | Carlton and United       |      2
 John   | Victoria Bitter        | Carlton and United       |      1
 Peter  | XXXX                   | Castlemaine/Perkins      |      5
 Raghu  | Old Tire               | New Glarus Brewing       |      5
 Raghu  | Sierra Nevada Pale Ale | Sierra Nevada            |      3
 Raghu  | Rasputin               | North Coast Brewing      |      3
 Ramez  | Sierra Nevada Pale Ale | Sierra Nevada            |      4
 Ramez  | Bigfoot Barley Wine    | Sierra Nevada            |      3
 Rose   | Redback                | Matilda Bay Brewing      |      5
 Sarah  | Burragorang Bock       | Scharer's Little Brewery |      4
 Sarah  | James Squire Amber Ale | Maltshovel Brewery       |      3
 Sarah  | James Squire Pilsener  | Maltshovel Brewery       |      3
 Sarah  | Old                    | Toohey's                 |      3
 Sarah  | Scharer's Lager        | Scharer's Little Brewery |      3
 Sarah  | New                    | Toohey's                 |      2
 Sarah  | Victoria Bitter        | Carlton and United       |      1
(32 rows)

SELECT t.given, b.name, br.name, r.score
FROM Taster t, Beer b, Brewer br, Ratings r
WHERE t.id = r.taster AND r.beer = b.id AND b.brewer = br.id
ORDER BY t.given, r.score desc;
--问题要求涉及到schema的4个table，用where限制条件把4个table联系起来即可。
--order by默认升序ascd，可以不写，降序要写desc。

--为了方便后续查询，可以写成view
create or replace view AllRatings(taster,beer,brewer,rating)
as
SELECT t.given, b.name, br.name, r.score
FROM Taster t, Beer b, Brewer br, Ratings r
WHERE t.id = r.taster AND r.beer = b.id AND b.brewer = br.id
ORDER BY t.given, r.score desc;

Q2

Find out what is John's favourite beer.
Clearly, you can work out the answer to this once you've solved the query above. However, try to write a query that returns a single tuple with the name of the beer(s) and brewer(s) for the beer(s) that John Shepherd rates highest.
When you invoke the view, you should see output that looks like this:

beers=# select * from JohnsFavouriteBeer ;
    brewer     |   beer   
---------------+----------
 Sierra Nevada | Pale Ale
(1 row)

SELECT br.name as brewer, b.name as beer
FROM Beer b, Brewer br, Ratings r, Taster t 
WHERE r.score = (SELECT max(r.score) FROM Ratings r, Taster t WHERE t.given = 'John' AND t.id = r.taster)
    AND r.beer = b.id AND r.taster = t.id AND b.brewer = br.id AND  t.given = 'John';
--先写子查询找到John评分最高的啤酒分数，再限定分数为最高分数对应的brewer和beer

--如果延续Q1的view可以写成
CREATE OR REPLACE VIEW JohnsFavoriteBeer(brewer, beer) AS
SELECT brewer, beer
FROM AllRatings
WERHER taster = 'John' AND
    rating = (SELECT max(rating) FROM AllRatings WHERE taster = 'John');

Q3

Find out anyone's favourite beer.
The above view seems to provide a useful operation, but seems a little restrictive. Surely I don't always want to know what is John's favourite beer. Maybe I want to know what is Adam's favourite beer, or Sarah's favourite beer. The queries to do this would be almost the same as the one used in the view above, but with the name changed. Which naturally raises the question Can view definitions be parameterised?. The answer is No in standard SQL, but PostgreSQL provides functions, which can be used to implement something like this.

PostgreSQL functions can be defined in a number of languages, including SQL. An SQL function typically contains a single SQL query, into which parameters to the function can be interpolated, thus providing a parameterisable query. (Note that SQL functions can contain an arbitrary sequence of SQL statements, including updates, separated by semi-colons. The result of such a function is the the result of the last SQL statement.)

For the favourite beer example, define a function which takes as input the complete name of a taster (as a text string) and returns one or more tuples containing the name of the brewer and the beer, as in the above example.

Why "one or more tuples"? Maybe a taster has several equally favourite beers. Always consider this possibility when faced with an information request like "Find the largest ..." or "Find the most expensive ..."; there may be a number of equally large/expensive things in the database. Also, do not assume that a given taster's maximum rating will be equal to 5; there may be people who rate beers harshly and never give a better rating than 4.

Make sure that you test your function on all tasters, to ensure that it's working properly for the people who have several equal favourite beers, or those who haven't given a rating of 5.

Before we can define a function, we need to define a type for the return tuples. Note that this happens automatically for views, but not for functions. The tuple type can be defined as:

create type BeerInfo as (brewer text, beer text);

The function could be used as follows::

beers=# select * from FavouriteBeer('John');
    brewer     |   beer   
---------------+----------
 Sierra Nevada | Pale Ale
(1 row)

beers=# select * from FavouriteBeer('Adam');
  brewer  | beer 
----------+------
 Toohey's | Old
(1 row)

CREATE OR REPLACE FUNCTION FavouriteBeer(text) 
returns setof BeerInfo
AS $$
SELECT brewer, beer
FROM AllRatings
WHERE taster = $1 AND
    rating = (SELECT max(rating) FROM AllRatings WHERE taster = $1)
$$ LANGUAGE sql;
-- sql的函数变量不能直接在程序化语言中用变量名，而是使用$1代表第一个标量，同理$2代表第二个变量。

Q4

What style is that beer?
Sometimes beers are named after their style (e.g., Sierra Nevada Pale Ale). Other times, imaginative names are used (e.g. Rooftop Red, Old Peculier). For these latter ones, we may know the name and want to discover what the style is. Write an SQL function that takes two text string arguments (the name of a brewer and the name of a beer) and returns the text string giving the style of the beer).
The function should give the correct result, regardless of the text case of the input parameters, so the following query should also work:

beers=# select BeerStyle('Sierra Nevada','Pale Ale');
 beerstyle 
-----------
 Pale Ale
(1 row)

beers=# select BeerStyle('sierra nevada','pale ale');
 beerstyle 
-----------
 Pale Ale
(1 row)

beers=# select BeerStyle('sieera nevada','pale ale');
 beerstyle 
-----------
 
(1 row)

CREATE OR REPLACE FUNCTION BeerStyle(brewer text, beer text)
returns text 
AS $$
SELECT s.name
FROM Beer b, Brewer br, BeerStyle s
WHERE b.brewer = br.id AND s.id = b.style AND lower(br.name) = lower($1) AND lower (b.name) = lower ($2)
$$ LANGUAGE sql;
--为了同时兼容大小写，where的限制条件使用了lower()函数，统一转化为小写

Q5

Consider the following PostgreSQL SQL function to produce a representation of a taster's address:

create or replace function TasterAddress(text) returns text
as $$
   select loc.state||', '||loc.country
   from   Taster t, Location loc
   where  t.given = $1 and t.livesIn = loc.id
$$ language sql;

This function would be used as follows. The function works ok for people who have both a state and a country, but fails for people who have just a country specified; it gives a null address, when clearly some address information is known. To observe the bug, try to find Sarah's address. Modify the function so that it produces a sensible result when either the country or the state is null. Once you've fixed this bug, you ought to be able to get a result like::

beers=# select tasterAddress('John');
       tasteraddress        
----------------------------
 New South Wales, Australia
(1 row)

beers=# select tasterAddress('Jeff');
   tasteraddress    
--------------------
 California, U.S.A.
(1 row)

beers=# select tasterAddress('Sarah');
 tasteraddress 
---------------
 England
(1 row)

CREATE OR REPLACE FUNCTION TasterAddress(text) 
returns text
AS $$
SELECT CASE
    WHEN loc.state is null then loc.country
    WHEN loc.country is null then loc.state
    ELSE loc.state||', '||loc.country
    END
FROM Taster t, Location loc
WHERE t.given = $1 AND t.livesIn =loc.id
$$ LANGUAGE sql;
--为了实现country或者state为null时依然输出，使用了case when的条件判断语句

Q6

Define a function that produces a summary of beer tasting.
The function BeerSummary returns a summary for each beer consisting of the name of the beer, followed by its average rating (to 1 decimal place), followed by a comma-separated list of the given names of the people who tasted and rated the beer.
The function should produce the following results on the example database:

beers=# select BeerSummary();
            beersummary                
-------------------------------------------
 
 Beer:    80/-
 Rating:  4.0
 Tasters: John
 
 Beer:    Bigfoot Barley Wine
 Rating:  3.0
 Tasters: Ramez
 
 Beer:    Burragorang Bock
 Rating:  3.0
 Tasters: Jeff, Sarah
 
 Beer:    Chimay Red
 Rating:  3.0
 Tasters: John
 
 ... a bunch of text omitted here to save space ...
 
 Beer:    Sierra Nevada Pale Ale
 Rating:  4.0
 Tasters: Hector, Jeff, John, Raghu, Ramez
 
 Beer:    Victoria Bitter
 Rating:  1.0
 Tasters: Adam, John, Sarah
 
 Beer:    XXXX
 Rating:  5.0
 Tasters: Peter
 
(1 row)

CREATE OR REPLACE FUNCTION BeerDisplay(_beer text, _rating float, _tasters text)
returns text
AS $$
BEGIN
    return E'\n' ||
                'Beer:     ' || _beer || E'\n' ||
                'Rating:  ' || to_char(_rating, '9.9') || E'\n' ||
                'Tasters: ' || substr(_tasters, 3, length(_tasters)) || E'\n';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION BeerSummary() 
returns text
AS $$
DECLARE 
r    record;
out text := '';
curbeer text := '';
tasters text;
sum integer;
count integer;
BEGIN
    for r in SELECT * FROM AllRatings ORDER BY beer, taster
    loop
        if (r.beer <> curbeer) then
            if (curbeer <> '') then
                out := || BeerDisplay(curbeer, sum/count, tasters);
            end if;
            curbeer := r.beer;
            sum := 0; count := 0; tasters := '';
        end if;
        sum := sum + r.rating;
        count := count + 1;
        tasters := tasters || ', ' || r.tasters;
    end loop;
    out := out || beerDisplay(curbeer, sum/count, tasters);
    return out;
END;
$$ LANGUAGE 
--record type类似tuple，但是没有预先设置好的structure，可以存放一个tuple或者是row

Q7

Define a new aggregation operator to concatenate a column of strings.
You could use this operator to get a list of taster names as follows:

beers=# select concat(given) from Taster ;

                      concat                          
----------------------------------------------------------
 John,Adam,Jeff,Sarah,Raghu,Ramez,Hector,Geoff,Peter,Rose
(1 row)

CREATE OR REPLACE FUNCTION appendNext(_state text, _next text) 
returns text
AS $$
BEGIN
    return _state|| ', ' || _next;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION finalText(_final text) 
returns text
AS $$
    return substr(_final, 2, length(_final));
$$ LANGUAGE plpgsql;

CREATE AGGREGATE concat (text)
(
    stype = text,
    initcond = '',
    sfunc = appendNext,
    finalfunc = finalText
);

Q8

Define a view that produces a summary of beer tasting.
The view should return a set of tuples where each tuple contains the name of the beer, the average rating (to 1 decimal place), and a comma-separated list of the given names of people who tasted and rated the beer.
The view should produce the following results on the example database:

beers=# select * from BeerSummary;
          beer          | rating |           tasters            
------------------------+--------+------------------------------
 Redback                |  4.5   | Geoff,Rose
 Fosters                |  3.0   | Hector
 New                    |  1.5   | Adam,Sarah
 Empire                 |  3.0   | Geoff,John
 Old Tire               |  5.0   | Raghu
 Old                    |  3.5   | Adam,Sarah
 80/-                   |  4.0   | John
 Chimay Red             |  3.0   | John
 Crown Lager            |  2.0   | John
 James Squire Amber Ale |  3.0   | Sarah
 Sierra Nevada Pale Ale |  4.0   | Hector,Jeff,John,Raghu,Ramez
 Rasputin               |  2.7   | Jeff,John,Raghu
 Burragorang Bock       |  3.5   | Jeff,Sarah
 XXXX                   |  5.0   | Peter
 Scharer's Lager        |  3.0   | Sarah
 Bigfoot Barley Wine    |  3.0   | Ramez
 Victoria Bitter        |  1.0   | Adam,John,Sarah
 James Squire Pilsener  |  3.5   | Geoff,Sarah
(18 rows)

CREATE OR REPACE VIEW BeerSummary(beer, rating, tasters)
AS
SELECT beer, to_char(avg(rating), '9.9'), concat(taster)
FROM AllRatings
GROUP BY beer;