Query 7: Find the total number of copies in stock for each poet who has more than 2 books poet book copies in stock Douglas Livingstone The Skull 21 Douglas Livingstone A Littoral Zone Mongane wally Tstetlo Mongane wall Must Weep Mongane wally A Tough Tale poet dum Mongane wally 13 SELECT poet SUM(copies in stock) as sum FROM Writer GROUP BY poet HAVING count(*)>2 COMP3311 Fall 2011 CSE, HKUST Slide 26
COMP3311 Fall 2011 CSE, HKUST Slide 26 Query 7: Find the total number of copies in stock for each poet who has more than 2 books 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 count(*)>2
DATA WAREHOUSES and OLAP On-Line Transaction Processing oLTP) Systems manipulate operational data, necessary for day-to day operations. Most existing database systems belong this category On-Line Analytical Processing(oLAP) Systems support specific types of queries(based on group bys and aggregation operators)useful for decision making Data Mining tools discover interesting patterns in the data
DATA WAREHOUSES and OLAP ◼ On-Line Transaction Processing (OLTP) Systems manipulate operational data, necessary for day-today operations. Most existing database systems belong this category. ◼ On-Line Analytical Processing (OLAP) Systems support specific types of queries (based on groupbys and aggregation operators) useful for decision making. ◼ Data Mining tools discover interesting patterns in the data
Why oLtp is not sufficient for Decision Making Lets say that Welcome supermarket uses a relational database to keep track of sales in all of stores simultaneously SALES table product store quantity date/time of id id sold Sa le 1997-10-22 567 09:35:14 219 141 1997-1022 09:35:14 1997-10-22 219 09:35:17
Why OLTP is not sufficient for Decision Making Lets say that Welcome supermarket uses a relational database to keep track of sales in all of stores simultaneously SALES table product id store id quantity sold date/time of sale 567 17 1 1997-10-22 09:35:14 219 16 4 1997-10-22 09:35:14 219 17 1 1997-10-22 09:35:17
Example(cont) PRoDUCTS table Prod. productproduct Manufac id name category t id 567 Colgate Gel toothpast Pump 6.4 OZ. 219 Diet Coke 12 soda oz can STORES table CITIES table stor cit store phone id name state Popul. e idy id location number 1634510Main415-555 Street 1212 34 Francisco/ California700,000 175813Mape91455 58 East Fishkill New York 30,000 Avenue 1212 COMP231 Spring 2009 CSE, HKUST Slide 29
COMP231 Spring 2009 CSE, HKUST Slide 29 Example (cont.) PRODUCTS table Prod. id product name product category Manufac t. id 567 Colgate Gel Pump 6.4 oz. toothpast e 68 219 Diet Coke 12 oz. can soda 5 ... STORES table stor e id cit y id store location phone number 16 34 510 Main Street 415-555- 1212 17 58 13 Maple Avenue 914-555- 1212 CITIES table id name state Popul. 34 San Francisco California 700,000 58 East Fishkill New York 30,000
Example(cont An executive, asks "i noticed that there was a Colgate promotion recently directed at people who live in small towns. How much Colgate toothpaste did we sell in those towns yesterday? and how much on the same day a month ago select sum(sales quantity- sold) from sales, products, stores, cities where products manufacturer_id =68 -restrict to Colgate- and products. product_ category =toothpaste and cities. population 40000 and sales. datetime_ of sale: date yesterday: date and sales product id= products product id and sales, store id s stores, store id and stores.city_id cities city_id COMP231 Spring 2009 CSE, HKUST Slide 30
COMP231 Spring 2009 CSE, HKUST Slide 30 Example (cont.) ◼ An executive, asks "I noticed that there was a Colgate promotion recently, directed at people who live in small towns. How much Colgate toothpaste did we sell in those towns yesterday? And how much on the same day a month ago?" select sum(sales.quantity_sold) from sales, products, stores, cities where products.manufacturer_id = 68 -- restrict to Colgateand products.product_category = 'toothpaste‘ and cities.population < 40000 and sales.datetime_of_sale::date = 'yesterday'::date and sales.product_id = products.product_id and sales.store_id = stores.store_id and stores.city_id = cities.city_id