From 98cef5e9a772602d42acfcf233838c760424db9a Mon Sep 17 00:00:00 2001 From: Nicolas James Date: Thu, 13 Feb 2025 18:00:17 +1100 Subject: initial commit --- comp3311/1/ass1.sql | 311 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 311 insertions(+) create mode 100644 comp3311/1/ass1.sql (limited to 'comp3311/1') 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; -- cgit v1.2.3