-- COMP3311 22T3 Assignment 1
--
-- Fill in the gaps ("...") below with your code
-- You can add any auxiliary views/function that you like
-- The code in this file *MUST* load into an empty database in one pass
-- It will be tested as follows:
-- createdb test; psql test -f ass1.dump; psql test -f ass1.sql
-- Make sure it can load without error under these conditions


-- Q1: new breweries in Sydney in 2020

create or replace view Q1(brewery,suburb)
as
...
;

-- Q2: beers whose name is same as their style

create or replace view Q2(beer,brewery)
as
...
;

-- Q3: original Californian craft brewery

create or replace view Q3(brewery,founded)
as
...
;

-- Q4: all IPA variations, and how many times each occurs

create or replace view Q4(style,count)
as
...
;

-- Q5: all Californian breweries, showing precise location

create or replace view Q5(brewery,location)
as
...
;

-- Q6: strongest barrel-aged beer

create or replace view Q6(beer,brewery,abv)
as
...
;

-- Q7: most popular hop

create or replace view Q7(hop)
as
...
;

-- Q8: breweries that don't make IPA or Lager or Stout (any variation thereof)

create or replace view Q8(brewery)
as
...
;

-- Q9: most commonly used grain in Hazy IPAs

create or replace view Q9(grain)
as
...
;

-- Q10: ingredients not used in any beer

create or replace view Q10(unused)
as
...
;

-- Q11: min/max abv for a given country

drop type if exists ABVrange cascade;
create type ABVrange as (minABV float, maxABV float);

create or replace function
	Q11(_country text) returns ABVrange
as $$
...
$$
language plpgsql;

-- Q12: details of beers

drop type if exists BeerData cascade;
create type BeerData as (beer text, brewer text, info text);

create or replace function
	Q12(partial_name text) returns setof BeerData
as $$
...
$$
language plpgsql;
