[Assignment Spec]  [Database Design]  [Examples]  [Testing]  [Submitting]  [Fixes+Updates]

The following will give some sample inputs and outputs that you can use to estimate the correctness of your views and functions. At present it just shows output from a few simple queries.

These examples are by no means exhaustive, and more cases will be used in the auto-marking; it is up to you to perform comprehensive checking of your solution.

Note that the default behaviour when you raise an error is to print a line giving you the CONTEXT in the PLpgSQL code where the error was raised. We did not show the CONTEXT message in the examples below. For you to prevent the message, use the psql command:

\set verbosity terse

Of course, this will probably prevent the CONTEXT message appearing if your code has a genuine error, so rather than turning the message off, you could always just ignore it. Whether it's on or off does not affect the auto-marking.

Examples for BeerDB

-- How many breweries are there?

ass1=# select count(*) from Breweries;
 count 
-------
   247
(1 row)

-- How many different beers are there?

ass1=# select count(*) from Beers;
 count 
-------
   776
(1 row)

-- How many different countries have breweries?

ass1=# select distinct(country) from Locations order by country;
    country    
---------------
 Australia
 Austria
 Belgium
 Canada
 China
 Denmark
 England
 Germany
 Hong Kong
 Italy
 Japan
 Mexico
 Netherlands
 New Zealand
 Norway
 Poland
 Scotland
 South Korea
 Sweden
 United States
 Vietnam
(21 rows)


-- Expected output for assignment views

ass1=# select * from q1 order by brewery;
       brewery        |    suburb    
----------------------+--------------
 Algorithm Brewing    | Marrickville
 Bracket Brewing      | Alexandria
 Malt Wolf Pty Ltd    | 
 Slow Lane Brewing    | Botany
 The Bondi Brewing Co | Bondi
 White Bay Beer Co    | Rozelle
(6 rows)

ass1=# select * from q2 order by beer,brewery;
          beer          |            brewery            
------------------------+-------------------------------
 Barley Wine            | Hawkers Beer
 Double Red IPA         | Mountain Culture Beer Co
 Hazy IPA               | Gwei Lo Beer
 Hazy IPA               | Hawkers Beer
 Hazy IPA               | Holgate Brewhouse
 Hazy IPA               | Malt Wolf Pty Ltd
 Hazy IPA               | One Drop Brewing Co.
 IPA                    | AleSmith Brewing Co
 IPA                    | Detour Beer Co
 IPA                    | Zytho Brewing
 Imperial IPA           | Hawkers Beer
 Imperial Red Ale       | Hope Brewery
 Imperial Stout         | Ekim Brewing Co
 Imperial Stout         | Mountain Goat Beer
 Lager                  | Balter Brewing Co
 Lager                  | Mountain Culture Beer Co
 Oatmeal Stout          | Ocean Reach Brewing
 Pale Ale               | Algorithm Brewing
 Pale Ale               | East Sydney Brewing
 Pale Ale               | Mountain Culture Beer Co
 Pale Ale               | Prancing Pony Brewery
 Pale Ale               | Sierra Nevada Brewing Company
 Porter                 | Colonial Brewing Company
 Red IPA                | Bulli Brewing Co
 Russian Imperial Stout | Bracket Brewing
 Saison                 | Exit Brewing
 Scotch Ale             | Caledonian Brewery
 West Coast IPA         | Bracket Brewing
 West Coast IPA         | Mr.Banks Brewing Co
(29 rows)


ass1=# select * from q3 order by brewery;
            brewery            | founded 
-------------------------------+---------
 Sierra Nevada Brewing Company |    1980
(1 row)


ass1=# select * from q4 order by style;
          style           | count 
--------------------------+-------
 Amber IPA                |     1
 American IPA             |     1
 Australian IPA           |     1
 Black Double IPA         |     1
 Black IPA                |     3
 Black Imperial IPA       |     1
 Brown IPA                |     1
 Brut IPA                 |     1
 Creamy IPA               |     1
 Cryo IPA                 |     1
 Dark IPA                 |     2
 Double IPA               |    43
 Double NEIPA             |    11
 Double Oat Cream IPA     |     1
 Double Red IPA           |     6
 Dry Hazy IPA             |     1
 Extra IPA                |     1
 Hazy Double IPA          |    20
 Hazy IPA                 |    72
 Hazy Imperial IPA        |     1
 Hazy Triple IPA          |     6
 IPA                      |   102
 Ice Cream Cake IPA       |     1
 Imperial Hazy IPA        |     2
 Imperial IPA             |    17
 Imperial NEIPA           |     1
 Imperial Red IPA         |     7
 Imperial Thickshake IPA  |     1
 Kettle soured Double IPA |     1
 Milkshake IPA            |     4
 NEIPA                    |    49
 New World IPA            |     1
 Oat Cream IPA            |    17
 Oat Cream NEIPA          |     2
 Quintuple IPA            |     1
 Red Double IPA           |     1
 Red IPA                  |    15
 Red Rye IPA              |     1
 Rye IPA                  |     1
 Session Hazy IPA         |     1
 Session IPA              |     2
 Sour Hazy IPA            |     1
 Sour Red IPA             |     1
 Triple IPA               |     6
 Triple NEIPA             |     1
 Tropical Brut IPA        |     1
 Vermont IPA              |     1
 West Coast IPA           |    18
 White IPA                |     1
(49 rows)


ass1=# select * from q5 order by brewery;
             brewery              |   location    
----------------------------------+---------------
 AleSmith Brewing Co              | San Diego
 Almanac Beer Company             | Alameda
 Alpine Beer Company              | Alpine
 Anderson Valley Brewing Company  | Boonville
 Ballast Point Brewing Co         | San Diego
 Bear Republic Brewing            | Cloverdale
 Belching Beaver Brewery          | Oceanside
 Cellarmaker Brewing Co           | San Francisco
 Figueroa Mountain Brewing Co     | Buellton
 Firestone Walker Brewing Company | Paso Robles
 Golden Road Brewing              | Los Angeles
 Heretic Brewing                  | Fairfeld
 High Water Brewing Inc           | Lodi
 Humble Sea Brewing               | Santa Cruz
 Karl Strauss Brewing Company     | San Diego
 Knee Deep Brewing Co             | Auburn
 Local Brewing Co                 | San Francisco
 Modern Times Beer                | Point Loma
 Monkish Brewing Company          | Torrance
 Moonraker Brewing                | Auburn
 Mother Earth Brew Company        | Vista
 Northcoast Brewing Co            | Fort Bragg
 Offshoot Beer                    | Placentia
 Pizza Port Brewing Company       | Carlsbad
 Sierra Nevada Brewing Company    | Chico
 Smog City Brewing Co             | Torrance
 Societe Brewing Company          | Kearny Mesa
 Stone Brewing                    | Escondido
 The Bruery                       | Placentia
(29 rows)


ass1=# select * from q6 order by beer;
          beer          |        brewery         | abv  
------------------------+------------------------+------
 Jumping the Shark 2015 | Moon Dog Craft Brewery | 18.4
(1 row)


ass1=# select * from q7 order by hop;
  hop  
-------
 Citra
(1 row)


ass1=# select * from q8 order by brewery;
                 brewery                 
-----------------------------------------
 Abbaye de Scourmont - Chimay
 Aegir Bryggeri
 Algorithm Brewing
 Almanac Beer Company
 Alpine Beer Company
 Bayerische Staatsbrauerei Weihenstephan
 Birra del Borgo
 Brasserie Dupont
 Brasserie d'Orval
 Brasserie de Rochefort
 Brauerei Heller
 Brauerei Schloss Eggenberg
 Brouwerij Huyghe
 Brouwerij Verhaeghe
 Caledonian Brewery
 Capital Brewing Co
 Carbon Brews
 Coopers Brewery
 Counter Culture Brewing
 Darkes Cider
 Dogfish Head Brewery
 East Sydney Brewing
 Future Mountain Brewing and Blending
 Great Leap Brewing
 Hair of the Dog Brewing Company
 Harviestoun Brewery
 Hemingway's Brewing
 Heroes Beer Co
 Kereru Brewing Co
 Lervig Aktiebryggeri
 Malt Shovel Brewers
 Mornington Peninsula Brewery
 Nogne 0 (Hansa Borg)
 Oriental Brewery
 Pelican Brewing
 Rodenbach Brewery
 Sail and Anchor Brewing Co
 Samuel Smiths Brewery
 Slow Lane Brewing
 Spencer Brewery
 To Ol
 Two Birds Brewing
 Urban Alley Brewery
 Wildflower Brewing and Blending
 Willie Smiths Cidery
 Young Master Hong Kong
(46 rows)


ass1=# select * from q9;
 grain 
-------
 Oats
(1 row)


ass1=# select * from q10;
 unused 
--------
 Acai
(1 row)


ass1=# select * from q11('Australia');
 minabv | maxabv 
--------+--------
      0 |   18.4
(1 row)

-- note that country name matching is case-sensitive
ass1=# select * from q11('australia');
 minabv | maxabv 
--------+--------
      0 |      0
(1 row)

ass1=# select * from q11('United States');
 minabv | maxabv 
--------+--------
      4 |   15.5
(1 row)

ass1=# select * from q11('Scotland');
 minabv | maxabv 
--------+--------
    4.5 |     55
(1 row)

ass1=# select * from q11('No such place');
 minabv | maxabv 
--------+--------
      0 |      0
(1 row)

ass1=# select * from q11('USA');
 minabv | maxabv 
--------+--------
      0 |      0
(1 row)


ass1=# select * from Q12('oat cream');
           beer           |           brewer           |                 info        
--------------------------+----------------------------+--------------------------------------
 Oat Cream                | Six String Brewing Company | 
 Oat Cream India Pale Ale | Grassy Knoll Brewing       | Hops: Citra,Strata                  +
                          |                            | Grain: Oats,Pale malt,Wheat
 Mango Oat Cream          | N.O.M.A.D Brewing          | 
 DDH MBC Oat Cream IPA    | Otherside Brewing Co       | Hops: Bravo,Centennial,Mosaic       +
                          |                            | Grain: Oats,Pale,Rye,Treticale,Wheat+
                          |                            | Extras: Lactose,Vanilla
(4 rows)

ass1=# select * from q12('imperial stout') order by beer, brewer;
            beer             |          brewer          | info 
-----------------------------+--------------------------+------
 Gulden Draak Imperial Stout | Brouwerij Van Steenberge | 
 Imperial Stout              | Ekim Brewing Co          | 
 Imperial Stout              | Mountain Goat Beer       | 
 Russian Imperial Stout      | Bracket Brewing          | 
(4 rows)

ass1=# select * from q12('pastry stout') order by beer, brewer;
            beer            |        brewer         |         info          
----------------------------+-----------------------+-----------------------
 Banana Pastry Stout        | Hop Nation Brewing Co | Extras: Banana
 Peanut Butter Pastry Stout | Hop Nation Brewing Co | Extras: Peanut butter
(2 rows)

ass1=# select * from Q12('Hazy IPA') order by beer, brewer;
               beer               |        brewer        |                 info                 
----------------------------------+----------------------+--------------------------------------
 Del Mar Pink Grapefruit Hazy IPA | Urbanaut Brewing Co  | 
 East Coast Hazy IPA              | Bracket Brewing      | Hops: Citra,Mosaic,Trident
 Hazy IPA                         | Bracket Brewing      | Hops: Mosaic,Motueka,Nelson sauvin
 Hazy IPA                         | Gwei Lo Beer         | 
 Hazy IPA                         | Hawkers Beer         | 
 Hazy IPA                         | Holgate Brewhouse    | Hops: Amarillo,Azacca,Mosaic
 Hazy IPA                         | Malt Wolf Pty Ltd    | 
 Hazy IPA                         | Nogne 0 (Hansa Borg) | 
 Hazy IPA                         | One Drop Brewing Co. | Hops: Nelson sauvin,Sabro,Vic-secret+
                                  |                      | Grain: Oat,Wheat
 Mango Hazy IPA                   | Seventh Day Brewery  | Extras: Mango
(10 rows)

ass1=# select * from Q12('aquarius') order by beer, brewer;
      beer       |               brewer               | info 
-----------------+------------------------------------+------
 Age of Aquarius | Garage Project + Modern Times Beer | 
(1 row)

ass1=# select * from q12('omnibus') order by beer, brewer;
      beer      |                 brewer                  |        info         
----------------+-----------------------------------------+---------------------
 Cosmic Omnibus | AleSmith Brewing Co + Modern Times Beer | Extras: Citrus zest
(1 row)

ass1=# select * from q12('No such beer');
 beer | brewer | info 
------+--------+------
(0 rows)