Query 2: For each poet, find the max, min, avg and total number of copies in stock poet book copies in stock Douglas livingstone The skull Douglas livingstone A Littoral zone Mongane wally Tstetlo Mongane wally Must Weep 2382 Mongane wally A Tough Tale poet max nIn avg sum Douglas Livingstone 21 2|115 23 Mongane wally 24.33 13 SELECT poet, MAX(copies_in_ stock AS max, MIN(copies_in_stock) As min, avG(copies in stock) as avg, SUM (copies in stock AS sum FROM writer GROUP BY poet COMP3311 Fall 2011 CSE, HKUST Slide 21
COMP3311 Fall 2011 CSE, HKUST Slide 21 Query 2: For each poet, find the max, min, avg and total number of copies in stock SELECT poet, MAX(copies_in_stock) AS max, MIN(copies_in_stock) AS min, AVG(copies_in_stock) AS avg, SUM(copies_in_stock) AS sum FROM writer GROUP BY poet poet book copies_in_stock Douglas Livingstone The Skull 21 Douglas Livingstone A Littoral Zone 2 Mongane Wally Tstetlo 3 Mongane Wally Must Weep 8 Mongane Wally A Tough Tale 2 poet max min avg sum Douglas Livingstone 21 2 11.5 23 Mongane Wally 8 2 4.33 13
Query 3: For each poet, find the max, min, avg and total number of copies in stock -take into account only books that have >5 copies in stock poet book copies in stock Douglas livingstone The Skull 21 Douglas Livingstone A Littoral Zone Mongane wally Tstetlo Mongane wally Must Weep 238 Mongane wally A Tough Tale poet max nn g sum Douglas livingstone 21 2121 Mongane wal SELECT poet, MaX(copies in stock) AS max MIN(copies in stock) AS min, avG(copies in stock) as avg, SUM(copies in stock AS sum FROM writer WHERE copies in stock>5 GROUP BY poet COMP3311 Fall 2011 CSE, HKUST Slide 22
COMP3311 Fall 2011 CSE, HKUST Slide 22 Query 3: For each poet, find the max, min, avg and total number of copies in stock – take into account only books that have > 5 copies in stock SELECT poet, MAX(copies_in_stock) AS max, MIN(copies_in_stock) AS min, AVG(copies_in_stock) AS avg, SUM(copies_in_stock) AS sum FROM writer WHERE copies_in_stock > 5 GROUP BY poet poet book copies_in_stock Douglas Livingstone The Skull 21 Douglas Livingstone A Littoral Zone 2 Mongane Wally Tstetlo 3 Mongane Wally Must Weep 8 Mongane Wally A Tough Tale 2 poet max min avg sum Douglas Livingstone 21 21 21 21 Mongane Wally 8 8 8 8
Query 4: Find the total number of copies in stock for each poet who has a total of more than 20 copies in stock poet book copies in stock Douglas livingstone The skull 21 Douglas livingstone A Littoral zone Mongane Wally Tstetlo Mongane Wally Must Weep Mongane wally A Tough Tale poet sum Douglas livingstone 23 Mongane wal 13 SELECT poet, SUM(copies in stock) As sum FROM writer GROUP BY poet HAVING sum>20 COMP3311 Fall 2011 CSE, HKUST Slide 23
COMP3311 Fall 2011 CSE, HKUST Slide 23 Query 4: Find the total number of copies in stock for each poet who has a total of more than 20 copies in stock SELECT poet, SUM(copies_in_stock) AS sum FROM writer GROUP BY poet HAVING sum>20 poet book copies_in_stock Douglas Livingstone The Skull 21 Douglas Livingstone A Littoral Zone 2 Mongane Wally Tstetlo 3 Mongane Wally Must Weep 8 Mongane Wally A Tough Tale 2 poet sum Douglas Livingstone 23 Mongane Wally 13
Query 5: Find the total number of copies in stock for each poet who has a total of more than 20 copies in stock - take into account only books that have more than 5 copies in stock poet book copies in stock Douglas Livingstone The Skull 21 Douglas livingstone A Littoral zone Mongane wally Tstetlo Mongane wally Must Weep 2382 Mongane wally A Tough Tale poet sum Douglas livingstone 21 Mongane Wally 8 SELECT poet SUM(copies in stock) As sum FROM writer WHERE copies in stock>5 GROUP BY poet HAVING sum>20 COMP3311 Fall 2011 CSE, HKUST Slide 24
COMP3311 Fall 2011 CSE, HKUST Slide 24 Query 5: Find the total number of copies in stock for each poet who has a total of more than 20 copies in stock – take into account only books that have more than 5 copies in stock SELECT poet, SUM(copies_in_stock) AS sum FROM writer WHERE copies_in_stock > 5 GROUP BY poet HAVING sum>20 poet book copies_in_stock Douglas Livingstone The Skull 21 Douglas Livingstone A Littoral Zone 2 Mongane Wally Tstetlo 3 Mongane Wally Must Weep 8 Mongane Wally A Tough Tale 2 poet sum Douglas Livingstone 21 Mongane Wally 8
Query 6: Find the total number of copies in stock for each poet whose name starts with any letter after"E poet book copies in stock Douglas livingstone The Skull 21 Douglas livingstone A Littoral zone Mongane wally Stelo Mongane wally Must Weep 382 Mongane Wally A Tough Tale poet sum Mongane wally 13 SELECT poet, SUM(copies_in_stock) SELECT poet, SUM(copies_in stock as sum as sum FROM writer FROM Writer WHERE Poet>“E” GROUP BY poet GROUP BY poet HAVING poet>‘E COMP3311 Fall 2011 CSE, HKUST Slide 25
COMP3311 Fall 2011 CSE, HKUST Slide 25 Query 6: Find the total number of copies in stock for each poet whose name starts with any letter after “E” SELECT poet, SUM(copies_in_stock) as sum FROM writer WHERE poet > “E” GROUP BY poet poet book copies_in_stock Douglas Livingstone The Skull 21 Douglas Livingstone A Littoral Zone 2 Mongane Wally Tstetlo 3 Mongane Wally Must Weep 8 Mongane Wally A Tough Tale 2 poet sum Mongane Wally 13 SELECT poet, SUM(copies_in_stock) as sum FROM writer GROUP BY poet HAVING poet > “E