COMP3311 23T1
Assignment 1
Example Test Cases
Database Systems
Last updated: Friday 10th March 9:02am
Most recent changes are shown in red ... older changes are shown in brown.
[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
ass1=# select * from q1 order by beer;
beer | sold in | alcohol
-----------------------------------+--------------+---------
Breakfast Stout | 355ml bottle | 29.5ml
Double Red IPA | 355ml can | 30.2ml
Double Red IPA | 355ml can | 28.4ml
Frozen Sea | 375ml can | 31.9ml
Hops-In-A-Can | 473ml can | 49.7ml
Jumping The Shark 2013 | 375ml bottle | 57.7ml
Jumping The Shark 2013/2021 | 375ml bottle | 58.5ml
Jumping The Shark 2015 | 375ml bottle | 69.0ml
Jumping The Shark 2019 | 375ml bottle | 45.4ml
Lark Barrel-Aged Imperial Jsp III | 440ml can | 56.3ml
Matt | 355ml bottle | 44.4ml
Narwhal (Ba) | 473ml can | 54.4ml
Noa (Pecan Mud) | 330ml bottle | 36.3ml
Sculpin | 355ml can | 24.9ml
Sink The Bismarck | 375ml bottle | 153.8ml
Sunrise Valley | 440ml can | 35.2ml
Tactical Nuclear Penguin | 375ml bottle | 120.0ml
Victory At Sea | 355ml bottle | 35.5ml
Whisky Aged Cider | 750ml bottle | 73.5ml
Yakima Valley | 440ml can | 35.2ml
Zamboni Haze | 473ml can | 37.8ml
(21 rows)


ass1=# select * from q2 order by beer;
beer | style | abv | reason
--------------------------+----------------+------+---------------------
California IPA | IPA | 4.2 | too weak by 1.3%
DDH Hi-Res | Double IPA | 11.1 | too strong by 1.1%
Farm Trail Harvest | Baltic Porter | 7 | too weak by 2.0%
Feelin' Fine | Oat Cream IPA | 5.9 | too weak by 0.1%
Frozen Sea | Baltic Porter | 8.5 | too weak by 0.5%
Giblin | Imperial Stout | 7.7 | too weak by 0.3%
Hazy IPA | Hazy IPA | 4.2 | too weak by 1.8%
IPA | IPA | 5.4 | too weak by 0.1%
Kono | Hazy IPA | 4.7 | too weak by 1.3%
Punk IPA | IPA | 5.1 | too weak by 0.4%
Sink The Bismarck | Imperial IPA | 41 | too strong by 23.0%
Tactical Nuclear Penguin | Eisbock | 32 | too strong by 13.5%
(12 rows)


ass1=# select * from q3 order by country;
country | #beers
--------------------+--------
Afghanistan | 0
Albania | 0
Argentina | 0
Australia | 530
Austria | 1
Belgium | 19
Bolivia | 0
Brazil | 0
Cameroon | 0
Canada | 12
Chile | 0
China | 2
Colombia | 0
Croatia | 1
Cuba | 0
Cyprus | 0
Czech Republic | 0
Denmark | 4
... many others, and finally ...
Ukraine | 0
United States | 168
Uruguay | 0
Venezuela | 0
Vietnam | 2
Wales | 0
Zambia | 0
Zimbabwe | 0
(90 rows)



ass1=# select * from q4 order by beer;
beer | brewery | country
----------------------+--------------------------------+-------------
0.0 | Heineken Brewing | Netherlands
Budweiser | Anheuser-Busch Inbev | Belgium
Carlton Draught | Carlton And United Breweries | Australia
Cero | Grupo Modelo - Corona | Mexico
Corona Extra | Grupo Modelo - Corona | Mexico
New | Tooheys Brothers | Australia
Nort | Modus Operandi Brewing Company | Australia
Tooheys New | Tooheys Brothers | Australia
Victoria Bitter (VB) | Carlton And United Breweries | Australia
XXXX Bitter | Castlemaine Perkins Brewery | Australia
(10 rows)


ass1=# select * from q5 order by beer;
beer | ingredient | type
-----------------+--------------+-------
A Million Stars | Pilsner Malt | grain
Beer | Saaz | hop
Beer | Pilsner Malt | grain
Bohemian Lager | Saaz | hop
Bohemian Lager | Pilsner Malt | grain
Feels Good Man | Pilsner Malt | grain
Kamchatka | Saaz | hop
Simple Coercion | Pilsner Malt | grain
The Leader | Pilsner Malt | grain
(9 rows)


ass1=# select * from q6 order by beer;
beer
--------------------------
Bicycle Day
Num Num Juice
Oat & About
Oat Cream India Pale Ale
Super Juicy
(5 rows)


ass1=# select * from q7 order by brewery;
brewery
-----------------------------------------
Abbaye De Scourmont - Chimay
Alpine Beer Company
Bayerische Staatsbrauerei Weihenstephan
(3 rows)


ass1=# select * from q8(9999);
q8
--------------
No such beer
(1 row)

ass1=# select * from q8(561);
q8
------------------------
Sierra Nevada Pale Ale
(1 row)

ass1=# select * from q8(130);
q8
-------------------------------------
Mountain Culture Chicken Duck Woman
(1 row)

ass1=# select * from q8(571);
q8
-----------------------
Stone Peak Conditions
(1 row)

ass1=# select * from q8(712);
q8
--------------------------------
Beerbliotek Sweet Smelly Trees
(1 row)

ass1=# select * from q8(664);
q8
------------------------------------------
Bracket + Mountain Culture Six Four Four
(1 row)

ass1=# select * from q8(103);
q8
----------------------------------
Exit + Rocky Ridge Brekkie Juice
(1 row)


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

ass1=# select * from q9('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 q9('aquarius') order by beer, brewer;
beer | brewer | info
-----------------+------------------------------------+------
Age Of Aquarius | Garage Project + Modern Times Beer |
(1 row)

ass1=# select * from q9('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 Malt,Wheat
(9 rows)

ass1=# select * from q9('Nicki') order by beer, brewer;
beer | brewer | info
-------+----------------------------------------------------------------+------
Nicki | Mountain Culture Beer Co + Mr.Banks Brewing Co + Range Brewing |
(1 row)

ass1=# select * from q9('Peace Tree');
beer | brewer | info
------+--------+------
(0 rows)

ass1=# \x
Expanded display is on.
ass1=# select * from q9('spills');
-[ RECORD 1 ]----------------------------------------------
beer | The Light That Spills Out Of The Hole In Your Head
brewer | Tired Hands Brewing Company
info | Hops: Amarillo,Nelson Sauvin +
| Extras: Clementines
原文
提供更好的翻译建议