Server [localhost]: Database [postgres]: baking Port [5432]: Username [postgres]: kkuzmin Active code page: 1252 psql (13.2) Type "help" for help. baking=> select id1, vala, id2, valb baking-> from a right join b on a.id1 = b.id2 ; id1 | vala | id2 | valb -----+------+-----+------ 2 | def | 2 | qwe 2 | def | 2 | tyu 3 | ghi | 3 | ery | | 4 | kjh | | 5 | pwe (5 rows) baking=> select * from a right outer join b on a.id1 = b.id2 ; id1 | vala | id2 | valb -----+------+-----+------ 2 | def | 2 | qwe 2 | def | 2 | tyu 3 | ghi | 3 | ery | | 4 | kjh | | 5 | pwe (5 rows) baking=> select * from a left outer join b on a.id1 = b.id2 ; id1 | vala | id2 | valb -----+------+-----+------ 1 | abc | | 2 | def | 2 | qwe 2 | def | 2 | tyu 3 | ghi | 3 | ery (4 rows) baking=> select * from a full join b on a.id1 = b.id2 ; id1 | vala | id2 | valb -----+------+-----+------ 1 | abc | | 2 | def | 2 | qwe 2 | def | 2 | tyu 3 | ghi | 3 | ery | | 4 | kjh | | 5 | pwe (6 rows) baking=> select id1, vala, id2, valb baking-> from a full outer join b on a.id1 = b.id2 ; id1 | vala | id2 | valb -----+------+-----+------ 1 | abc | | 2 | def | 2 | qwe 2 | def | 2 | tyu 3 | ghi | 3 | ery | | 4 | kjh | | 5 | pwe (6 rows) baking=> select id1, vala, id2, valb baking-> from a full join b on a.id1 = b.id2 ; id1 | vala | id2 | valb -----+------+-----+------ 1 | abc | | 2 | def | 2 | qwe 2 | def | 2 | tyu 3 | ghi | 3 | ery | | 4 | kjh | | 5 | pwe (6 rows) baking=> SELECT baking-> b.baker baking-> , count(*) baking-> FROM baking-> bakers b baking-> left join technicals t baking-> on b.baker = t.baker and t.rank <= 3 baking-> GROUP BY baking-> b.baker ; baker | count ---------+------- Rahul | 5 Imelda | 1 Jon | 4 Karen | 1 Ruby | 7 Luke | 1 Dan | 3 Antony | 1 Manon | 3 Briony | 4 Terry | 1 Kim-Joy | 3 (12 rows) baking=> SELECT baking-> b.baker baking-> , count(t.episodeid) baking-> FROM baking-> bakers b baking-> left join technicals t baking-> on b.baker = t.baker and t.rank <= 3 baking-> GROUP BY baking-> b.baker ; baker | count ---------+------- Rahul | 5 Imelda | 0 Jon | 4 Karen | 0 Ruby | 7 Luke | 0 Dan | 3 Antony | 0 Manon | 3 Briony | 4 Terry | 1 Kim-Joy | 3 (12 rows) baking=> SELECT baking-> s.baker baking-> ,s.make baking-> ,tr1.numwins baking-> FROM baking-> ( baking(> SELECT baker, count(*) as numwins baking(> FROM techincals baking(> where rank = 1 baking(> group by baker baking(> having count(*) >= 1 baking(> ) as tr1, baking-> signatures s baking-> WHERE baking-> s.baker = tr1.baker ; ERROR: relation "techincals" does not exist LINE 8: FROM techincals ^ baking=> SELECT baking-> s.baker baking-> ,s.make baking-> ,tr1.numwins baking-> FROM baking-> ( baking(> SELECT baker, count(*) as numwins baking(> FROM technicals baking(> where rank = 1 baking(> group by baker baking(> having count(*) >= 1 baking(> ) as tr1, baking-> signatures s baking-> WHERE baking-> s.baker = tr1.baker ; baker | make | numwins ---------+---------------------------------------------------------------------------+--------- Briony | Apple Cider Empire Biscuits | 2 Dan | Lemon and Strawberry Shrewsburys | 1 Jon | Aberffraw 'Creams' | 1 Kim-Joy | Orange Blossom York Biscuits | 1 Rahul | Fennel and Coconut Pitcaithly Bannock | 1 Ruby | Masala Chai Devon Flats | 3 Terry | Lake District Ginger Shortbread | 1 Briony | Turron and Orange Traybake | 2 Dan | Black Forest Slice | 1 Jon | Lemon Meringue Traybake | 1 Kim-Joy | Pandan Chiffon Cake with Palm Sugar Cream | 1 Rahul | Lemon and Cardamom Traybake | 1 Ruby | Boozy Black Forest Traybake | 3 Terry | Rum and Raisin Traybake | 1 Briony | Balsamic Strawberry Chelsea Buns | 2 Dan | Sticky Spiced Orange Chelsea Buns | 1 Jon | Cardiff City vs Chelsea Buns | 1 Kim-Joy | Pistachio and Cardamom Tangzhong Chelsea Buns | 1 Rahul | Mango and Cranberry Chelsea Buns | 1 Ruby | Gujarela Chelsea Buns with Dates, Almonds and Raisins | 3 Terry | Tangy Citrus Sticky Chelsea Buns | 1 Briony | Treacle Tart Roulade | 2 Dan | Florida Roulade | 1 Jon | Mango and Passion Fruit Roulade | 1 Kim-Joy | 'Sweet Dreams' Roulade | 1 Rahul | Rhubarb and Custard Roulade | 1 Ruby | Pina Colada Roulade | 3 Briony | Honey and Apricot Ginger Cake | 2 Dan | Ginger and Lemon Drip Cake | 1 Jon | Family Christmas Ginger Cake | 1 Kim-Joy | Stem Ginger Cake with Poached Pears | 1 Rahul | Bonfire Night Caramel Ginger Cake | 1 Ruby | Jamaican Me Crazy Ginger Cake | 3 Terry | Caramelised Pear and Stem Ginger Cake | 1 Briony | Home Comforts | 2 Dan | Festive Samosas | 1 Jon | A Romantic Dinner For Two, Samosa Style | 1 Kim-Joy | Flavours of India | 1 Rahul | Paneer Singara and Misti Singara | 1 Ruby | Traditional Samosas | 3 Briony | French Onion Tartlets and Celeriac & Apple Tartlets | 2 Jon | Garlic Mushroom Tartlets and Falafel & Hummus Tartlets | 1 Kim-Joy | Broccoli & Tomato Quiches and Mascarpone Squirrel Tartlets | 1 Rahul | Coriander Posto & Veg Tartlets and Ghugni Chaat Tartlets | 1 Ruby | Sage & Butternut Tartlets and 'Cheesy Greens' Tartlets | 3 Briony | Spanish & West Country Smørrebrød | 2 Kim-Joy | Bumblebee Eggs & Fish Smørrebrød | 1 Rahul | Smoked Salmon & Roasted Vegetable Smørrebrød | 1 Ruby | Post-Gym Smørrebrød | 3 Briony | Mojito Madeleines & Espresso Martini Madeleines | 2 Kim-Joy | Ginger and Lemon Madeleines & Orange Bunny Madeleines | 1 Rahul | Lemon and Raspberry Madeleines & Orange Curd Madeleines | 1 Ruby | Pick Your Own Madeleines | 3 Kim-Joy | Amaretto Diplomat Filled Doughnuts & Lemon Ring Doughnuts | 1 Rahul | Mango Créme Pâtissière Filled Doughnuts & Spiced Orange Ring Doughnuts | 1 Ruby | Dulce De Leche Filled Doughnuts & Raspberry and Cardamom Ring Doughnuts | 3 (56 rows) baking=> SELECT baking-> s.baker baking-> ,s.make baking-> ,COUNT(DISTINCT t.episodeid) as numwins baking-> FROM baking-> signatures s baking-> , technicals t baking-> WHERE baking-> s.baker = t.baker baking-> and t.rank = 1 baking-> GROUP BY s.baker baking-> HAVING baking-> COUNT(DISTINCT t.episodeid) >= 1 ; ERROR: column "s.make" must appear in the GROUP BY clause or be used in an aggregate function LINE 3: ,s.make ^ baking=> SELECT baking-> s.baker baking-> ,s.make baking-> ,COUNT(DISTINCT t.episodeid) as numwins baking-> FROM baking-> signatures s baking-> , technicals t baking-> WHERE baking-> s.baker = t.baker baking-> and t.rank = 1 baking-> GROUP BY s.baker, s.make baking-> HAVING baking-> COUNT(DISTINCT t.episodeid) >= 1 ; baker | make | numwins ---------+---------------------------------------------------------------------------+--------- Briony | Apple Cider Empire Biscuits | 2 Briony | Balsamic Strawberry Chelsea Buns | 2 Briony | French Onion Tartlets and Celeriac & Apple Tartlets | 2 Briony | Home Comforts | 2 Briony | Honey and Apricot Ginger Cake | 2 Briony | Mojito Madeleines & Espresso Martini Madeleines | 2 Briony | Spanish & West Country Smørrebrød | 2 Briony | Treacle Tart Roulade | 2 Briony | Turron and Orange Traybake | 2 Dan | Black Forest Slice | 1 Dan | Festive Samosas | 1 Dan | Florida Roulade | 1 Dan | Ginger and Lemon Drip Cake | 1 Dan | Lemon and Strawberry Shrewsburys | 1 Dan | Sticky Spiced Orange Chelsea Buns | 1 Jon | A Romantic Dinner For Two, Samosa Style | 1 Jon | Aberffraw 'Creams' | 1 Jon | Cardiff City vs Chelsea Buns | 1 Jon | Family Christmas Ginger Cake | 1 Jon | Garlic Mushroom Tartlets and Falafel & Hummus Tartlets | 1 Jon | Lemon Meringue Traybake | 1 Jon | Mango and Passion Fruit Roulade | 1 Kim-Joy | 'Sweet Dreams' Roulade | 1 Kim-Joy | Amaretto Diplomat Filled Doughnuts & Lemon Ring Doughnuts | 1 Kim-Joy | Broccoli & Tomato Quiches and Mascarpone Squirrel Tartlets | 1 Kim-Joy | Bumblebee Eggs & Fish Smørrebrød | 1 Kim-Joy | Flavours of India | 1 Kim-Joy | Ginger and Lemon Madeleines & Orange Bunny Madeleines | 1 Kim-Joy | Orange Blossom York Biscuits | 1 Kim-Joy | Pandan Chiffon Cake with Palm Sugar Cream | 1 Kim-Joy | Pistachio and Cardamom Tangzhong Chelsea Buns | 1 Kim-Joy | Stem Ginger Cake with Poached Pears | 1 Rahul | Bonfire Night Caramel Ginger Cake | 1 Rahul | Coriander Posto & Veg Tartlets and Ghugni Chaat Tartlets | 1 Rahul | Fennel and Coconut Pitcaithly Bannock | 1 Rahul | Lemon and Cardamom Traybake | 1 Rahul | Lemon and Raspberry Madeleines & Orange Curd Madeleines | 1 Rahul | Mango and Cranberry Chelsea Buns | 1 Rahul | Mango Créme Pâtissière Filled Doughnuts & Spiced Orange Ring Doughnuts | 1 Rahul | Paneer Singara and Misti Singara | 1 Rahul | Rhubarb and Custard Roulade | 1 Rahul | Smoked Salmon & Roasted Vegetable Smørrebrød | 1 Ruby | Boozy Black Forest Traybake | 3 Ruby | Dulce De Leche Filled Doughnuts & Raspberry and Cardamom Ring Doughnuts | 3 Ruby | Gujarela Chelsea Buns with Dates, Almonds and Raisins | 3 Ruby | Jamaican Me Crazy Ginger Cake | 3 Ruby | Masala Chai Devon Flats | 3 Ruby | Pick Your Own Madeleines | 3 Ruby | Pina Colada Roulade | 3 Ruby | Post-Gym Smørrebrød | 3 Ruby | Sage & Butternut Tartlets and 'Cheesy Greens' Tartlets | 3 Ruby | Traditional Samosas | 3 Terry | Caramelised Pear and Stem Ginger Cake | 1 Terry | Lake District Ginger Shortbread | 1 Terry | Rum and Raisin Traybake | 1 Terry | Tangy Citrus Sticky Chelsea Buns | 1 (56 rows) baking=> SELECT max(viewers7day) from episodes ; max ------- 10.34 (1 row) baking=> SELECT * baking-> FROM baking-> episodes baking-> WHERE baking-> viewers7day = (SELECT max(viewers7day) from episodes); id | title | firstaired | viewers7day | signature | technical | showstopper ----+-------+------------+-------------+-------------------+------------------------+------------------- 10 | Final | 2018-10-30 | 10.34 | 12 Iced Doughnuts | 6 Campfire Pita Breads | Landscape Dessert(1 row) baking=> SELECT * baking-> FROM baking-> episodes baking-> WHERE baking-> viewers7day = 10.34; id | title | firstaired | viewers7day | signature | technical | showstopper ----+-------+------------+-------------+-------------------+------------------------+------------------- 10 | Final | 2018-10-30 | 10.34 | 12 Iced Doughnuts | 6 Campfire Pita Breads | Landscape Dessert(1 row) baking=>