path: root/comp3311/1/ass1.sql

-- COMP3311 21T3 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 a 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 errorunder these conditions


-- Q1: oldest brewery

create or replace view Q1(brewery)
as
SELECT Breweries.name
FROM Breweries
WHERE Breweries.founded IN (
    SELECT MIN(Breweries.founded) FROM Breweries)
;

-- Q2: collaboration beers

create or replace view Q2(beer)
as
SELECT B.name
FROM Beers B
INNER JOIN (
    SELECT Brewed_by.beer
    FROM Brewed_by
    GROUP BY Brewed_by.beer
    HAVING COUNT(Brewed_by.beer) > 1) AS INNER_COUNT
ON INNER_COUNT.beer = B.id
;

-- Q3: worst beer
create or replace view Q3(worst)
as
SELECT Beers.name
FROM Beers
WHERE Beers.rating IN (
    SELECT MIN(Beers.rating) FROM Beers)
;

-- Q4: too strong beer

create or replace view Q4(beer,abv,style,max_abv)
as
SELECT Beers.name, Beers.abv, Styles.name, Styles.max_abv
FROM Beers
INNER JOIN Styles
ON Styles.id = Beers.style
WHERE Beers.abv > Styles.max_abv
;

-- Q5: most common style

create or replace view Q5(style)
as
SELECT S.name
FROM Styles S
INNER JOIN (
    SELECT Beers.style
    FROM Beers
    GROUP BY Beers.style
    HAVING COUNT(Beers.style) IN (
        SELECT MAX(COUNTER.COUNT)
        FROM (
            SELECT Beers.style, COUNT(Beers.style)
            FROM Beers
            GROUP BY Beers.style
        ) AS COUNTER
    )
) AS INNER_COUNT
ON INNER_COUNT.style = S.id;
;
-- Q6: duplicated style names

create or replace view Q6(style1,style2)
as
SELECT s1.name, s2.name
FROM Styles as s1, Styles as s2
WHERE UPPER(s1.name) = UPPER(s2.name) AND s1.name < s2.name;
;

-- Q7: breweries that make no beers

create or replace view Q7(brewery)
as
SELECT Breweries.name
FROM Breweries
EXCEPT
SELECT Breweries.name
FROM Breweries, Brewed_by
WHERE Breweries.id = Brewed_by.brewery
;

-- Q8: city with the most breweries

create or replace view Q8(city,country)
as
SELECT Locations.metro, Locations.country
FROM Locations
INNER JOIN (
	SELECT COUNTER.located_in
	FROM (
		SELECT Breweries.located_in, COUNT(Breweries.located_in)
		FROM Breweries
		GROUP BY Breweries.located_in
	) AS COUNTER
	GROUP BY (COUNTER.located_in, COUNTER.count)
	HAVING COUNTER.count IN (
		SELECT MAX(COUNTER.COUNT)
	       	FROM (
		        SELECT Breweries.located_in, COUNT(Breweries.located_in)
		        FROM Breweries
		        GROUP BY Breweries.located_in
		) AS COUNTER)
) AS MAX_COUNT_LOCATIONS
ON MAX_COUNT_LOCATIONS.located_in = Locations.id;
;

-- Q9: breweries that make more than 5 styles

create or replace view Q9(brewery,nstyles)
as
SELECT SEARCH.name, COUNT(SEARCH.name)
FROM (
        SELECT Breweries.name, C.id
        FROM Breweries
        INNER JOIN (
            SELECT Brewed_by.brewery, Brewed_by.beer
            FROM Brewed_by
            GROUP BY(Brewed_by.brewery, Brewed_by.beer)
        ) AS A ON A.brewery = Breweries.id
        INNER JOIN (
            SELECT Beers.id, Beers.style
            FROM Beers
            GROUP BY (Beers.id, Beers.style)
        ) AS B ON B.id = A.beer
        INNER JOIN (
            SELECT Styles.id
            FROM Styles
        ) AS C ON C.id = B.style
        GROUP BY (Breweries.name, C.id)
) AS SEARCH
GROUP BY (SEARCH.name)
HAVING COUNT(Search.name) > 5
;

-- Q10: beers of a certain style
create type BeerInfo as (beer text, brewery text, style text, year YearValue, abv ABVvalue);
create or replace function
    q10(_style text) returns setof BeerInfo
as $$
DECLARE
    ret RECORD;
    rett RECORD;
    inf BeerInfo%ROWTYPE;
BEGIN
    FOR ret IN
    SELECT Beers.name AS beername,
	Beers.id AS beerid,
        Styles.name AS style,
        Beers.brewed AS year,
        Beers.abv AS abv
    FROM Beers, Styles
    WHERE Styles.name = _style
        AND Beers.style = Styles.id
        LOOP
            inf.beer = ret.beername;
            inf.style = ret.style;
            inf.year = ret.year;
            inf.abv = ret.abv;
	    inf.brewery = '';

	    FOR rett IN
	    SELECT breweries.name as brewname
	    FROM Brewed_by, Breweries
	    WHERE Brewed_by.beer = ret.beerid
		    AND Brewed_by.brewery = Breweries.id
	    ORDER BY breweries.name
            LOOP
            IF (inf.brewery = '') THEN
                inf.brewery = rett.brewname;
            ELSE
                inf.brewery = inf.brewery || ' + ' || rett.brewname;
            END IF;
            END LOOP;

            return next inf;
        END LOOP;
    RETURN;
END;
$$
language plpgsql;

-- Q11: beers with names matching a pattern
create or replace function Q11(partial_name text) returns setof text
AS $$
DECLARE
    ret RECORD;
    rett RECORD;
    line text;
BEGIN
    FOR ret IN
    SELECT Beers.name AS beername,
        Beers.id AS beerid,
        Beers.abv AS beerabv,
        Styles.name AS stylename
    FROM Beers, Styles
    WHERE Styles.id = Beers.style
	    AND (UPPER(Beers.name) LIKE '%' || UPPER(partial_name) || '%')
    LOOP
        line = '';

	FOR rett IN
        SELECT Breweries.name AS brewname
        FROM Brewed_by, Breweries
        WHERE Brewed_by.beer = ret.beerid
		AND Breweries.id = Brewed_by.brewery
        ORDER BY breweries.name
        LOOP
            IF (line = '') THEN
                line = rett.brewname;
            ELSE
                line = line || ' + ' || rett.brewname;
            END IF;
        END LOOP;

        line = '"' || ret.beername || '", ' || line;
        line = line || ', ' || ret.stylename || ', ' || ret.beerabv || '% ABV';
        RETURN NEXT line;

    END LOOP;
    RETURN;
END;
$$
language plpgsql;

-- Q12: breweries and the beers they make
create or replace function Q12(partial_name text) returns setof text
as $$
DECLARE
ret RECORD;
rett RECORD;
line text;
BEGIN
    FOR ret IN
    SELECT Breweries.id AS brewid,
        Breweries.name AS brewname,
        Breweries.founded AS brewfounded,
        Locations.country AS country,
        Locations.region AS region,
        Locations.metro AS metro,
        Locations.town AS town

    FROM Breweries, Locations
    WHERE (UPPER(Breweries.name) LIKE '%' || UPPER(partial_name) || '%')
        AND Locations.id = Breweries.located_in
    ORDER BY Breweries.name
    LOOP
        -- First line
        line = ret.brewname || ', founded ' || ret.brewfounded;
        RETURN NEXT line;

        -- Second line
        line = 'located in ';
        IF (ret.town != '' AND ret.metro != '') THEN
            line = line || ret.town;
        ELSEIF (ret.metro != ' ') THEN
            line = line || ret.metro;
        ELSEIF (ret.town != ' ') THEN
            line = line || ret.town;
        END IF;
        line = line || ', ';

        IF (ret.region != '') THEN
            line = line || ret.region || ', ';
        END IF;

        line = line || ret.country;
        RETURN NEXT line;

        -- Third line
        line = '';
        FOR rett IN
        SELECT Beers.name AS beername,
            Beers.brewed AS beerbrewed,
            Styles.name AS stylename,
            Beers.abv AS beerabv
        FROM Beers, Styles, Brewed_by
        WHERE Brewed_by.brewery = ret.brewid
            AND Brewed_by.beer = Beers.id
            AND Styles.id = Beers.style
        ORDER BY (Beers.brewed, Beers.name)
        LOOP
            line = '  "' || rett.beername || '"' || ', '
                || rett.stylename || ', ' || rett.beerbrewed || ', '
                || rett.beerabv || '% ABV';
            RETURN NEXT line;
        END LOOP;
        IF (line = '') THEN
            RETURN NEXT '  No known beers';
        END IF;


    END LOOP;
    RETURN;
END;
$$
language plpgsql;