baking=> DELETE FROM episodes WHERE firstaired is NULL ; DELETE 0 baking=> SELECT * FROM episodes ; id | title | firstaired | viewers7day | signature | technical | showstopper ----+--------------------------+------------+-------------+--------------------------+-------------------------------------------------------+-------------------------------- 1 | Biscuits | 2018-08-28 | 9.55 | 24 Regional Biscuits | 8 Wagon Wheels | 3D Biscuit Self-Portrait 2 | Cakes | 2018-09-04 | 9.31 | 16 Traybakes | Le Gâteau Vert | Chocolate Collar Cake 3 | Bread | 2018-09-11 | 8.91 | 12 Chelsea Buns | 8 Non-Yeasted Garlic Naan Breads | Korovai 4 | Desserts | 2018-09-18 | 8.88 | Meringue Roulade | Raspberry Blancmange with 12 Langues du Chat biscuits | Melting Chocolate Ball Dessert 5 | Spice | 2018-09-25 | 8.67 | Ginger Cake | 12 Ma'amoul | Spiced Biscuit Chandelier 6 | Pastry | 2018-10-02 | 9.3 | 12 Samosas | 6 Puits D'amour | Shaped Banquet Pie 7 | Vegan | 2018-10-09 | 9.54 | 8 Savoury Vegan Tartlets | Vegan Tropical Fruit Pavlova | Vegan Celebratory Cake 8 | Danish | 2018-10-16 | 9.69 | 2 Smørrebrød | 14 Æbleskiver | Kagemand/Kagekone 9 | Pâtisserie (Semi-final) | 2018-10-23 | 9.5 | 24 Madeleines | Torta Setteveli | Parisian Pâtisserie Window 10 | Final | 2018-10-30 | 10.34 | 12 Iced Doughnuts | 6 Campfire Pita Breads | Landscape Dessert (10 rows) baking=> INSERT INTO episodes(id, title, signature, technical, showstopper) baking-> VALUES(11,'The Great Christmas Bake Off','12 Iced Biscuits','6 Laufabrauð','Hidden Design Christmas Present Cake') ; INSERT 0 1 baking=> SELECT * FROM episodes ; id | title | firstaired | viewers7day | signature | technical | showstopper ----+------------------------------+------------+-------------+--------------------------+-------------------------------------------------------+-------------------------------------- 1 | Biscuits | 2018-08-28 | 9.55 | 24 Regional Biscuits | 8 Wagon Wheels | 3D Biscuit Self-Portrait 2 | Cakes | 2018-09-04 | 9.31 | 16 Traybakes | Le Gâteau Vert | Chocolate Collar Cake 3 | Bread | 2018-09-11 | 8.91 | 12 Chelsea Buns | 8 Non-Yeasted Garlic Naan Breads | Korovai 4 | Desserts | 2018-09-18 | 8.88 | Meringue Roulade | Raspberry Blancmange with 12 Langues du Chat biscuits | Melting Chocolate Ball Dessert 5 | Spice | 2018-09-25 | 8.67 | Ginger Cake | 12 Ma'amoul | Spiced Biscuit Chandelier 6 | Pastry | 2018-10-02 | 9.3 | 12 Samosas | 6 Puits D'amour | Shaped Banquet Pie 7 | Vegan | 2018-10-09 | 9.54 | 8 Savoury Vegan Tartlets | Vegan Tropical Fruit Pavlova | Vegan Celebratory Cake 8 | Danish | 2018-10-16 | 9.69 | 2 Smørrebrød | 14 Æbleskiver | Kagemand/Kagekone 9 | Pâtisserie (Semi-final) | 2018-10-23 | 9.5 | 24 Madeleines | Torta Setteveli | Parisian Pâtisserie Window 10 | Final | 2018-10-30 | 10.34 | 12 Iced Doughnuts | 6 Campfire Pita Breads | Landscape Dessert 11 | The Great Christmas Bake Off | | | 12 Iced Biscuits | 6 Laufabrauð | Hidden Design Christmas Present Cake (11 rows) baking=> DELETE FROM episodes WHERE firstaired is NULL ; DELETE 1 baking=> SELECT * FROM episodes ; id | title | firstaired | viewers7day | signature | technical | showstopper ----+--------------------------+------------+-------------+--------------------------+-------------------------------------------------------+-------------------------------- 1 | Biscuits | 2018-08-28 | 9.55 | 24 Regional Biscuits | 8 Wagon Wheels | 3D Biscuit Self-Portrait 2 | Cakes | 2018-09-04 | 9.31 | 16 Traybakes | Le Gâteau Vert | Chocolate Collar Cake 3 | Bread | 2018-09-11 | 8.91 | 12 Chelsea Buns | 8 Non-Yeasted Garlic Naan Breads | Korovai 4 | Desserts | 2018-09-18 | 8.88 | Meringue Roulade | Raspberry Blancmange with 12 Langues du Chat biscuits | Melting Chocolate Ball Dessert 5 | Spice | 2018-09-25 | 8.67 | Ginger Cake | 12 Ma'amoul | Spiced Biscuit Chandelier 6 | Pastry | 2018-10-02 | 9.3 | 12 Samosas | 6 Puits D'amour | Shaped Banquet Pie 7 | Vegan | 2018-10-09 | 9.54 | 8 Savoury Vegan Tartlets | Vegan Tropical Fruit Pavlova | Vegan Celebratory Cake 8 | Danish | 2018-10-16 | 9.69 | 2 Smørrebrød | 14 Æbleskiver | Kagemand/Kagekone 9 | Pâtisserie (Semi-final) | 2018-10-23 | 9.5 | 24 Madeleines | Torta Setteveli | Parisian Pâtisserie Window 10 | Final | 2018-10-30 | 10.34 | 12 Iced Doughnuts | 6 Campfire Pita Breads | Landscape Dessert (10 rows) baking=> \d episodes Table "public.episodes" Column | Type | Collation | Nullable | Default -------------+------------------------+-----------+----------+--------- id | integer | | not null | title | character varying(100) | | | firstaired | date | | | viewers7day | double precision | | | signature | character varying(100) | | | technical | character varying(100) | | | showstopper | character varying(100) | | | Indexes: "episodes_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "favorites" CONSTRAINT "favorites_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "results" CONSTRAINT "results_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "showstoppers" CONSTRAINT "showstoppers_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "signatures" CONSTRAINT "signatures_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "technicals" CONSTRAINT "technicals_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) baking=> \d favorites Table "public.favorites" Column | Type | Collation | Nullable | Default -----------+------------------------+-----------+----------+--------- episodeid | integer | | not null | baker | character varying(100) | | not null | Indexes: "favorites_pkey" PRIMARY KEY, btree (episodeid, baker) Foreign-key constraints: "favorites_baker_fkey" FOREIGN KEY (baker) REFERENCES bakers(baker) "favorites_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) baking=> SELECT 1 baking-> FROM results r baking-> WHERE f.episodeid = r.episodeid baking-> AND f.baker=r.baker baking-> AND r.result = 'star baker' ; ERROR: missing FROM-clause entry for table "f" LINE 3: WHERE f.episodeid = r.episodeid ^ baking=> SELECT r.baker, r.episodeid baking-> FROM results r baking-> WHERE r.result = 'star baker' ; baker | episodeid ---------+----------- Manon | 1 Rahul | 2 Rahul | 3 Dan | 4 Kim-Joy | 5 Briony | 6 Kim-Joy | 7 Ruby | 8 Ruby | 9 (9 rows) baking=> SELECT * FROM favorites; episodeid | baker -----------+--------- 1 | Briony 2 | Jon 2 | Dan 3 | Dan 4 | Rahul 4 | Jon 5 | Rahul 6 | Rahul 6 | Ruby 7 | Rahul 8 | Briony 9 | Kim-Joy (12 rows) baking=> DELETE FROM baking-> favorites f baking-> WHERE baking-> EXISTS (SELECT 1 baking(> FROM results r baking(> WHERE f.episodeid = r.episodeid baking(> AND f.baker=r.baker baking(> AND r.result = 'star baker'); DELETE 0 baking=> SELECT * FROM favorites; episodeid | baker -----------+--------- 1 | Briony 2 | Jon 2 | Dan 3 | Dan 4 | Rahul 4 | Jon 5 | Rahul 6 | Rahul 6 | Ruby 7 | Rahul 8 | Briony 9 | Kim-Joy (12 rows) baking=> SELECT * FROM results; episodeid | baker | result -----------+---------+------------ 1 | Manon | star baker 1 | Imelda | eliminated 2 | Rahul | star baker 2 | Luke | eliminated 3 | Rahul | star baker 3 | Antony | eliminated 4 | Dan | star baker 5 | Terry | eliminated 5 | Kim-Joy | star baker 5 | Karen | eliminated 6 | Briony | star baker 6 | Dan | eliminated 7 | Kim-Joy | star baker 7 | Jon | eliminated 8 | Ruby | star baker 8 | Manon | eliminated 9 | Ruby | star baker 9 | Briony | eliminated (18 rows) baking=> INSERT INTO results VALUES(10, 'Rahul', 'winner'); INSERT 0 1 baking=> INSERT INTO results(baker, episodeid, result) VALUES('Ruby', 10, 'runner up'); INSERT 0 1 baking=> INSERT INTO results(baker, episodeid) VALUES('Kim-Joy', 10); INSERT 0 1 baking=> SELECT * FROM results; episodeid | baker | result -----------+---------+------------ 1 | Manon | star baker 1 | Imelda | eliminated 2 | Rahul | star baker 2 | Luke | eliminated 3 | Rahul | star baker 3 | Antony | eliminated 4 | Dan | star baker 5 | Terry | eliminated 5 | Kim-Joy | star baker 5 | Karen | eliminated 6 | Briony | star baker 6 | Dan | eliminated 7 | Kim-Joy | star baker 7 | Jon | eliminated 8 | Ruby | star baker 8 | Manon | eliminated 9 | Ruby | star baker 9 | Briony | eliminated 10 | Rahul | winner 10 | Ruby | runner up 10 | Kim-Joy | (21 rows) baking=> UPDATE results SET result = 'runner up' WHERE episodeid=10 AND baker='Kim-Joy'; UPDATE 1 baking=> SELECT * FROM results; episodeid | baker | result -----------+---------+------------ 1 | Manon | star baker 1 | Imelda | eliminated 2 | Rahul | star baker 2 | Luke | eliminated 3 | Rahul | star baker 3 | Antony | eliminated 4 | Dan | star baker 5 | Terry | eliminated 5 | Kim-Joy | star baker 5 | Karen | eliminated 6 | Briony | star baker 6 | Dan | eliminated 7 | Kim-Joy | star baker 7 | Jon | eliminated 8 | Ruby | star baker 8 | Manon | eliminated 9 | Ruby | star baker 9 | Briony | eliminated 10 | Rahul | winner 10 | Ruby | runner up 10 | Kim-Joy | runner up (21 rows) baking=> \d episodes Table "public.episodes" Column | Type | Collation | Nullable | Default -------------+------------------------+-----------+----------+--------- id | integer | | not null | title | character varying(100) | | | firstaired | date | | | viewers7day | double precision | | | signature | character varying(100) | | | technical | character varying(100) | | | showstopper | character varying(100) | | | Indexes: "episodes_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "favorites" CONSTRAINT "favorites_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "results" CONSTRAINT "results_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "showstoppers" CONSTRAINT "showstoppers_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "signatures" CONSTRAINT "signatures_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "technicals" CONSTRAINT "technicals_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) baking=> ALTER TABLE episodes ADD season int ; ALTER TABLE baking=> ALTER TABLE episodes ADD year int ; ALTER TABLE baking=> \d episodes Table "public.episodes" Column | Type | Collation | Nullable | Default -------------+------------------------+-----------+----------+--------- id | integer | | not null | title | character varying(100) | | | firstaired | date | | | viewers7day | double precision | | | signature | character varying(100) | | | technical | character varying(100) | | | showstopper | character varying(100) | | | season | integer | | | year | integer | | | Indexes: "episodes_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "favorites" CONSTRAINT "favorites_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "results" CONSTRAINT "results_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "showstoppers" CONSTRAINT "showstoppers_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "signatures" CONSTRAINT "signatures_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "technicals" CONSTRAINT "technicals_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) baking=> SELECT * FROM episodes; id | title | firstaired | viewers7day | signature | technical | showstopper | season | year ----+--------------------------+------------+-------------+--------------------------+-------------------------------------------------------+--------------------------------+--------+------ 1 | Biscuits | 2018-08-28 | 9.55 | 24 Regional Biscuits | 8 Wagon Wheels | 3D Biscuit Self-Portrait | | 2 | Cakes | 2018-09-04 | 9.31 | 16 Traybakes | Le Gâteau Vert | Chocolate Collar Cake | | 3 | Bread | 2018-09-11 | 8.91 | 12 Chelsea Buns | 8 Non-Yeasted Garlic Naan Breads | Korovai | | 4 | Desserts | 2018-09-18 | 8.88 | Meringue Roulade | Raspberry Blancmange with 12 Langues du Chat biscuits | Melting Chocolate Ball Dessert | | 5 | Spice | 2018-09-25 | 8.67 | Ginger Cake | 12 Ma'amoul | Spiced Biscuit Chandelier | | 6 | Pastry | 2018-10-02 | 9.3 | 12 Samosas | 6 Puits D'amour | Shaped Banquet Pie | | 7 | Vegan | 2018-10-09 | 9.54 | 8 Savoury Vegan Tartlets | Vegan Tropical Fruit Pavlova | Vegan Celebratory Cake | | 8 | Danish | 2018-10-16 | 9.69 | 2 Smørrebrød | 14 Æbleskiver | Kagemand/Kagekone | | 9 | Pâtisserie (Semi-final) | 2018-10-23 | 9.5 | 24 Madeleines | Torta Setteveli | Parisian Pâtisserie Window | | 10 | Final | 2018-10-30 | 10.34 | 12 Iced Doughnuts | 6 Campfire Pita Breads | Landscape Dessert | | (10 rows) baking=> UPDATE episodes SET season = 9 ; UPDATE 10 baking=> SELECT * FROM episodes; id | title | firstaired | viewers7day | signature | technical | showstopper | season | year ----+--------------------------+------------+-------------+--------------------------+-------------------------------------------------------+--------------------------------+--------+------ 1 | Biscuits | 2018-08-28 | 9.55 | 24 Regional Biscuits | 8 Wagon Wheels | 3D Biscuit Self-Portrait | 9 | 2 | Cakes | 2018-09-04 | 9.31 | 16 Traybakes | Le Gâteau Vert | Chocolate Collar Cake | 9 | 3 | Bread | 2018-09-11 | 8.91 | 12 Chelsea Buns | 8 Non-Yeasted Garlic Naan Breads | Korovai | 9 | 4 | Desserts | 2018-09-18 | 8.88 | Meringue Roulade | Raspberry Blancmange with 12 Langues du Chat biscuits | Melting Chocolate Ball Dessert | 9 | 5 | Spice | 2018-09-25 | 8.67 | Ginger Cake | 12 Ma'amoul | Spiced Biscuit Chandelier | 9 | 6 | Pastry | 2018-10-02 | 9.3 | 12 Samosas | 6 Puits D'amour | Shaped Banquet Pie | 9 | 7 | Vegan | 2018-10-09 | 9.54 | 8 Savoury Vegan Tartlets | Vegan Tropical Fruit Pavlova | Vegan Celebratory Cake | 9 | 8 | Danish | 2018-10-16 | 9.69 | 2 Smørrebrød | 14 Æbleskiver | Kagemand/Kagekone | 9 | 9 | Pâtisserie (Semi-final) | 2018-10-23 | 9.5 | 24 Madeleines | Torta Setteveli | Parisian Pâtisserie Window | 9 | 10 | Final | 2018-10-30 | 10.34 | 12 Iced Doughnuts | 6 Campfire Pita Breads | Landscape Dessert | 9 | (10 rows) baking=> INSERT INTO episodes(id, title, signature, technical, showstopper) baking-> VALUES(11,'The Great Christmas Bake Off','12 Iced Biscuits','6 Laufabrauð','Hidden Design Christmas Present Cake') ; INSERT 0 1 baking=> SELECT * FROM episodes; id | title | firstaired | viewers7day | signature | technical | showstopper | season | year ----+------------------------------+------------+-------------+--------------------------+-------------------------------------------------------+--------------------------------------+--------+------ 1 | Biscuits | 2018-08-28 | 9.55 | 24 Regional Biscuits | 8 Wagon Wheels | 3D Biscuit Self-Portrait | 9 | 2 | Cakes | 2018-09-04 | 9.31 | 16 Traybakes | Le Gâteau Vert | Chocolate Collar Cake | 9 | 3 | Bread | 2018-09-11 | 8.91 | 12 Chelsea Buns | 8 Non-Yeasted Garlic Naan Breads | Korovai | 9 | 4 | Desserts | 2018-09-18 | 8.88 | Meringue Roulade | Raspberry Blancmange with 12 Langues du Chat biscuits | Melting Chocolate Ball Dessert | 9 | 5 | Spice | 2018-09-25 | 8.67 | Ginger Cake | 12 Ma'amoul | Spiced Biscuit Chandelier | 9 | 6 | Pastry | 2018-10-02 | 9.3 | 12 Samosas | 6 Puits D'amour | Shaped Banquet Pie | 9 | 7 | Vegan | 2018-10-09 | 9.54 | 8 Savoury Vegan Tartlets | Vegan Tropical Fruit Pavlova | Vegan Celebratory Cake | 9 | 8 | Danish | 2018-10-16 | 9.69 | 2 Smørrebrød | 14 Æbleskiver | Kagemand/Kagekone | 9 | 9 | Pâtisserie (Semi-final) | 2018-10-23 | 9.5 | 24 Madeleines | Torta Setteveli | Parisian Pâtisserie Window | 9 | 10 | Final | 2018-10-30 | 10.34 | 12 Iced Doughnuts | 6 Campfire Pita Breads | Landscape Dessert | 9 | 11 | The Great Christmas Bake Off | | | 12 Iced Biscuits | 6 Laufabrauð | Hidden Design Christmas Present Cake | | (11 rows) baking=> UPDATE baking-> episodes baking-> SET baking-> year = extract(year from firstaired) baking-> WHERE baking-> firstaired is not null ; UPDATE 10 baking=> SELECT * FROM episodes; id | title | firstaired | viewers7day | signature | technical | showstopper | season | year ----+------------------------------+------------+-------------+--------------------------+-------------------------------------------------------+--------------------------------------+--------+------ 11 | The Great Christmas Bake Off | | | 12 Iced Biscuits | 6 Laufabrauð | Hidden Design Christmas Present Cake | | 1 | Biscuits | 2018-08-28 | 9.55 | 24 Regional Biscuits | 8 Wagon Wheels | 3D Biscuit Self-Portrait | 9 | 2018 2 | Cakes | 2018-09-04 | 9.31 | 16 Traybakes | Le Gâteau Vert | Chocolate Collar Cake | 9 | 2018 3 | Bread | 2018-09-11 | 8.91 | 12 Chelsea Buns | 8 Non-Yeasted Garlic Naan Breads | Korovai | 9 | 2018 4 | Desserts | 2018-09-18 | 8.88 | Meringue Roulade | Raspberry Blancmange with 12 Langues du Chat biscuits | Melting Chocolate Ball Dessert | 9 | 2018 5 | Spice | 2018-09-25 | 8.67 | Ginger Cake | 12 Ma'amoul | Spiced Biscuit Chandelier | 9 | 2018 6 | Pastry | 2018-10-02 | 9.3 | 12 Samosas | 6 Puits D'amour | Shaped Banquet Pie | 9 | 2018 7 | Vegan | 2018-10-09 | 9.54 | 8 Savoury Vegan Tartlets | Vegan Tropical Fruit Pavlova | Vegan Celebratory Cake | 9 | 2018 8 | Danish | 2018-10-16 | 9.69 | 2 Smørrebrød | 14 Æbleskiver | Kagemand/Kagekone | 9 | 2018 9 | Pâtisserie (Semi-final) | 2018-10-23 | 9.5 | 24 Madeleines | Torta Setteveli | Parisian Pâtisserie Window | 9 | 2018 10 | Final | 2018-10-30 | 10.34 | 12 Iced Doughnuts | 6 Campfire Pita Breads | Landscape Dessert | 9 | 2018 (11 rows) baking=> \d List of relations Schema | Name | Type | Owner --------+--------------+-------+--------- public | bakers | table | kkuzmin public | episodes | table | kkuzmin public | favorites | table | kkuzmin public | results | table | kkuzmin public | showstoppers | table | kkuzmin public | signatures | table | kkuzmin public | technicals | table | kkuzmin (7 rows) baking=> \d bakers Table "public.bakers" Column | Type | Collation | Nullable | Default ------------+------------------------+-----------+----------+--------- baker | character varying(10) | | not null | fullname | character varying(100) | | | age | integer | | | occupation | character varying(100) | | | hometown | character varying(100) | | | Indexes: "bakers_pkey" PRIMARY KEY, btree (baker) Referenced by: TABLE "favorites" CONSTRAINT "favorites_baker_fkey" FOREIGN KEY (baker) REFERENCES bakers(baker) TABLE "results" CONSTRAINT "results_baker_fkey" FOREIGN KEY (baker) REFERENCES bakers(baker) TABLE "showstoppers" CONSTRAINT "showstoppers_baker_fkey" FOREIGN KEY (baker) REFERENCES bakers(baker) TABLE "signatures" CONSTRAINT "signatures_baker_fkey" FOREIGN KEY (baker) REFERENCES bakers(baker) TABLE "technicals" CONSTRAINT "technicals_baker_fkey" FOREIGN KEY (baker) REFERENCES bakers(baker) baking=> \d results Table "public.results" Column | Type | Collation | Nullable | Default -----------+------------------------+-----------+----------+--------- episodeid | integer | | not null | baker | character varying(100) | | not null | result | character varying(20) | | | Indexes: "results_pkey" PRIMARY KEY, btree (episodeid, baker) Foreign-key constraints: "results_baker_fkey" FOREIGN KEY (baker) REFERENCES bakers(baker) "results_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) baking=> ALTER TABLE bakers ADD numwins INT ; ALTER TABLE baking=> SELECT * FROM bakers; baker | fullname | age | occupation | hometown | numwins ---------+---------------------+-----+-----------------------------------+---------------+--------- Antony | Antony Amourdoux | 30 | Banker | London | Briony | Briony Williams | 33 | Full-time parent | Bristol | Dan | Dan Beasley-Harling | 36 | Full-time parent | London | Imelda | Imelda McCarron | 33 | Countryside recreation officer | County Tyrone | Jon | Jon Jenkins | 47 | Blood courier | Newport | Karen | Karen Wright | 60 | In-store sampling assistant | Wakefield | Kim-Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds | Luke | Luke Thompson | 30 | Civil servant/house and techno DJ | Sheffield | Manon | Manon Lagrève | 26 | Software project manager | London | Rahul | Rahul Mandal | 30 | Research scientist | Rotherham | Ruby | Ruby Bhogal | 29 | Project manager | London | Terry | Terry Hartill | 56 | Retired air steward | West Midlands | (12 rows) baking=> SELECT count(*) baking-> FROM results r baking-> WHERE r.baker = bakers.baker AND r.result='star baker' baking-> ; ERROR: missing FROM-clause entry for table "bakers" LINE 3: WHERE r.baker = bakers.baker AND r.result=... ^ baking=> SELECT count(*) baking-> FROM results r baking-> WHERE r.baker = 'Rahul' AND r.result='star baker'; count ------- 2 (1 row) baking=> UPDATE baking-> bakers baking-> SET baking-> numwins = (SELECT count(*) baking(> FROM results r baking(> WHERE r.baker = 'Briony' AND r.result='star baker') ; UPDATE 12 baking=> SELECT * FROM bakers; baker | fullname | age | occupation | hometown | numwins ---------+---------------------+-----+-----------------------------------+---------------+--------- Antony | Antony Amourdoux | 30 | Banker | London | 1 Briony | Briony Williams | 33 | Full-time parent | Bristol | 1 Dan | Dan Beasley-Harling | 36 | Full-time parent | London | 1 Imelda | Imelda McCarron | 33 | Countryside recreation officer | County Tyrone | 1 Jon | Jon Jenkins | 47 | Blood courier | Newport | 1 Karen | Karen Wright | 60 | In-store sampling assistant | Wakefield | 1 Kim-Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds | 1 Luke | Luke Thompson | 30 | Civil servant/house and techno DJ | Sheffield | 1 Manon | Manon Lagrève | 26 | Software project manager | London | 1 Rahul | Rahul Mandal | 30 | Research scientist | Rotherham | 1 Ruby | Ruby Bhogal | 29 | Project manager | London | 1 Terry | Terry Hartill | 56 | Retired air steward | West Midlands | 1 (12 rows) baking=> SELECT count(*) baking-> FROM results r baking-> WHERE r.baker = 'Briony' AND r.result='star baker' baking-> ; count ------- 1 (1 row) baking=> UPDATE baking-> bakers baking-> SET baking-> numwins = (SELECT count(*) baking(> FROM results r baking(> WHERE r.baker = bakers.baker AND r.result='star baker') ; UPDATE 12 baking=> SELECT * FROM bakers; baker | fullname | age | occupation | hometown | numwins ---------+---------------------+-----+-----------------------------------+---------------+--------- Antony | Antony Amourdoux | 30 | Banker | London | 0 Briony | Briony Williams | 33 | Full-time parent | Bristol | 1 Dan | Dan Beasley-Harling | 36 | Full-time parent | London | 1 Imelda | Imelda McCarron | 33 | Countryside recreation officer | County Tyrone | 0 Jon | Jon Jenkins | 47 | Blood courier | Newport | 0 Karen | Karen Wright | 60 | In-store sampling assistant | Wakefield | 0 Kim-Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds | 2 Luke | Luke Thompson | 30 | Civil servant/house and techno DJ | Sheffield | 0 Manon | Manon Lagrève | 26 | Software project manager | London | 1 Rahul | Rahul Mandal | 30 | Research scientist | Rotherham | 2 Ruby | Ruby Bhogal | 29 | Project manager | London | 2 Terry | Terry Hartill | 56 | Retired air steward | West Midlands | 0 (12 rows) baking=> SELECT baking-> baker baking-> ,COUNT(*) as numfavorites baking-> FROM baking-> favorites baking-> GROUP BY baking-> baker; baker | numfavorites ---------+-------------- Rahul | 4 Ruby | 1 Dan | 2 Briony | 2 Jon | 2 Kim-Joy | 1 (6 rows) baking=> SELECT baking-> baker baking-> ,COUNT(*) as numstars baking-> FROM baking-> results baking-> WHERE baking-> result = 'star baker' baking-> GROUP BY baking-> baker; baker | numstars ---------+---------- Briony | 1 Dan | 1 Kim-Joy | 2 Manon | 1 Rahul | 2 Ruby | 2 (6 rows) baking=> SELECT * baking-> FROM baking-> (SELECT baking(> baker baking(> ,COUNT(*) as numfavorites baking(> FROM baking(> favorites baking(> GROUP BY baking(> baker ) as numf; baker | numfavorites ---------+-------------- Rahul | 4 Ruby | 1 Dan | 2 Briony | 2 Jon | 2 Kim-Joy | 1 (6 rows) baking=> SELECT baking-> b.baker baking-> ,COUNT(*) as numfavorites baking-> FROM baking-> bakers b LEFT JOIN favorites f ON b.baker = f.baker baking-> GROUP BY baking-> b.baker; baker | numfavorites ---------+-------------- Rahul | 4 Imelda | 1 Jon | 2 Karen | 1 Ruby | 1 Luke | 1 Dan | 2 Antony | 1 Manon | 1 Briony | 2 Terry | 1 Kim-Joy | 1 (12 rows) baking=> SELECT baking-> b.baker baking-> ,COUNT(f.baker) as numfavorites baking-> FROM baking-> bakers b LEFT JOIN favorites f ON b.baker = f.baker baking-> GROUP BY baking-> b.baker; baker | numfavorites ---------+-------------- Rahul | 4 Imelda | 0 Jon | 2 Karen | 0 Ruby | 1 Luke | 0 Dan | 2 Antony | 0 Manon | 0 Briony | 2 Terry | 0 Kim-Joy | 1 (12 rows) baking=> SELECT baking-> b.baker baking-> ,COUNT(r.baker) as numstar baking-> FROM baking-> bakers b LEFT JOIN results r ON b.baker = r.baker baking-> WHERE baking-> r.result = 'star baker' baking-> GROUP BY baking-> b.baker; baker | numstar ---------+--------- Briony | 1 Dan | 1 Kim-Joy | 2 Manon | 1 Rahul | 2 Ruby | 2 (6 rows) baking=> SELECT baking-> b.baker baking-> ,COUNT(r.baker) as numstar baking-> FROM baking-> bakers b LEFT JOIN results r ON b.baker = r.baker AND r.result = 'star baker' baking-> GROUP BY baking-> b.baker; baker | numstar ---------+--------- Rahul | 2 Imelda | 0 Jon | 0 Karen | 0 Ruby | 2 Luke | 0 Dan | 1 Antony | 0 Manon | 1 Briony | 1 Terry | 0 Kim-Joy | 2 (12 rows) baking=> WITH numf AS ( baking(> SELECT baking(> b.baker baking(> ,COUNT(f.baker) as numfavorites baking(> FROM baking(> bakers b LEFT JOIN favorites f ON b.baker = f.baker baking(> GROUP BY baking(> b.baker baking(> ), baking-> nums AS ( baking(> SELECT baking(> b.baker baking(> ,COUNT(r.baker) as numstar baking(> FROM baking(> bakers b LEFT JOIN results r ON b.baker = r.baker AND r.result = 'star baker' baking(> GROUP BY baking(> b.baker baking(> ) baking-> SELECT baking-> numf.baker, baking-> nums.baker, baking-> numf.numfavorites, baking-> nums. numstar baking-> FROM baking-> numf baking-> FULL JOIN nums baking-> ON numf.baker = nums.baker ; baker | baker | numfavorites | numstar ---------+---------+--------------+--------- Rahul | Rahul | 4 | 2 Imelda | Imelda | 0 | 0 Jon | Jon | 2 | 0 Karen | Karen | 0 | 0 Ruby | Ruby | 1 | 2 Luke | Luke | 0 | 0 Dan | Dan | 2 | 1 Antony | Antony | 0 | 0 Manon | Manon | 0 | 1 Briony | Briony | 2 | 1 Terry | Terry | 0 | 0 Kim-Joy | Kim-Joy | 1 | 2 (12 rows) baking=>