initial commit

1 files changed, 311 insertions, 0 deletions

diff --git a/comp3311/1/ass1.sql b/comp3311/1/ass1.sql
new file mode 100644
index 0000000..9b223f8
--- /dev/null
+++ b/comp3311/1/ass1.sql
@@ -0,0 +1,311 @@
+-- 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;