Lecture 4 Additional Slides CSE 344, Winter 2014 Sudeepa Roy CSE - PowerPoint PPT Presentation

Lecture 4 Additional Slides CSE 344, Winter 2014 Sudeepa Roy CSE 344 - Winter 2014 1

• NOTE: These slides were used as a whiteboard in class, and mostly contain the same material as the lecture slides and sqlite notes. CSE 344 - Winter 2014 2

Announcements • Webquiz 1 is due on Tuesday (01/14): 11:59 pm • Webquiz 2 will be posted on Wednesday (01/15): 00:00 am • Homework 1 is due on Thursday (01/16) • This Wednesday’s (01/15) office hour: 10 -10:50 am, CSE 344 (Sudeepa) • Lecture 5-8 (SQL) by Daniel Halperin CSE 344 - Winter 2014 3

Outline • Nulls in SQL (6.1.6) • Outer joins (6.3.8) • Aggregations (6.4.3 – 6.4.6) • Examples, examples, examples… CSE 344 - Winter 2014 4

NULL In SqLite Company Product pname price category manufacturer cname country GizmoWorks USA Gizmo 19.99 gadget GizmoWorks Canon Japan PowerGizmo 29.99 gadget GizmoWorks Hitachi Japan SingleTouch 149.99 photography Canon Google USA MultiTouch 199.99 photography Hitachi SELECT gadegts with price SuperGizmo 49.99 gadget Hitachi < 25 and >=25 iPad 5 NULL gadget Apple iPad5 is nowhere! • Product(pname, price, category, manufacturer) • Company(cname, country) CSE 344 - Winter 2014 5

Conditions involving NULL • We need to evaluate in SQL conditions like this: • ((price < 25) and (category = 'gadget')) or (manufacturer = 'Apple') • Suppose price = 19, category = NULL, and manufacturer = NULL • Is the predicate true or false? CSE 344 - Winter 2014 6

3-valued logic • FALSE = 0 – E.g. price<25 is FALSE when price=99 • UNKNOWN = 0.5 – E.g. price<25 is UNKNOWN when price=NULL • TRUE = 1 – E.g. price<25 is TRUE when price=19 • PREDICATES: – C1 AND C2 means min(C1,C2) – C1 OR C2 means max(C1,C2) – not C means 1-C • Answer appears only if the truth value is TRUE • Does not appear if the truth value is FALSE or UNKNOWN • Therefore, no iPad5! CSE 344 - Winter 2014 7

Compute the truth value • ((price < 25) and (category = 'gadget‘)) or (manufacturer = 'Apple') • Suppose price = 19, category = NULL, and manufacturer = NULL • Your answer in class  : = (TRUE and UNKNOWN) OR UNKNOWN = UNKNOWN OR UNKNOWN = UNKNOWN CSE 344 - Winter 2014 8

OUTERJOIN CSE 344 - Winter 2014 9

JOIN = INNER JOIN • Select pname, cname from Product, Company where manufacturer = cname; • Select pname, cname from Product (INNER) JOIN Company ON manufacturer = cname; optional • Where is ipad5 or Google? pname price category manufacturer cname country GizmoWorks USA Gizmo 19.99 gadget GizmoWorks Canon Japan PowerGizmo 29.99 gadget GizmoWorks Hitachi Japan SingleTouch 149.99 photography Canon Google USA MultiTouch 199.99 photography Hitachi SuperGizmo 49.99 gadget Hitachi Product Company iPad 5 NULL gadget Apple CSE 344 - Winter 2014 10

LEFT OUTER JOIN • Include the left tuple even if there’s no match • Select pname, cname from Product LEFT OUTER JOIN Company ON manufacturer = cname; • You should get (iPad5, null) • Reverse the order of relations. pname price category manufacturer cname country GizmoWorks USA Gizmo 19.99 gadget GizmoWorks Canon Japan PowerGizmo 29.99 gadget GizmoWorks Hitachi Japan SingleTouch 149.99 photography Canon Google USA MultiTouch 199.99 photography Hitachi SuperGizmo 49.99 gadget Hitachi Product Company iPad 5 NULL gadget Apple CSE 344 - Winter 2014 11

RIGHT OUTER JOIN • Include the right tuple even if there’s no match • Select pname, cname from Product RIGHT OUTER JOIN Company ON manufacturer = cname; • You should get (null, Google) pname price category manufacturer cname country GizmoWorks USA Gizmo 19.99 gadget GizmoWorks Canon Japan PowerGizmo 29.99 gadget GizmoWorks Hitachi Japan SingleTouch 149.99 photography Canon Google USA MultiTouch 199.99 photography Hitachi SuperGizmo 49.99 gadget Hitachi Product Company iPad 5 NULL gadget Apple CSE 344 - Winter 2014 12

FULL OUTER JOIN • Include both left and right tuples even if there’s no match • Select pname, cname from Product (FULL) OUTER JOIN Company ON manufacturer = cname; • You should get both (null, Google) and (iPad5, null) pname price category manufacturer cname country GizmoWorks USA Gizmo 19.99 gadget GizmoWorks Canon Japan PowerGizmo 29.99 gadget GizmoWorks Hitachi Japan SingleTouch 149.99 photography Canon Google USA MultiTouch 199.99 photography Hitachi SuperGizmo 49.99 gadget Hitachi Product Company iPad 5 NULL gadget Apple CSE 344 - Winter 2014 13

AGGREGATION IN SQL CSE 344 - Winter 2014 14

Aggregation in SQL >sqlite3 lecture04 Specify a filename where the database will be stored sqlite> create table Purchase (pid int primary key, product text, price float, quantity int, month varchar(15)); Other DBMSs have other ways of importing data sqlite> -- download data.txt sqlite> .import data.txt Purchase CSE 344 - Winter 2014 15

Comment about SQLite • One cannot load NULL values such that they are actually loaded as null values • So we need to use two steps: – Load null values using some type of special value – Update the special values to actual null values update Purchase set price = null where price = ‘null’ CSE 344 - Winter 2014 16

Simple Aggregations Five basic aggregate operations in SQL select count(*) from Purchase select sum(quantity) from Purchase select avg(price) from Purchase select max(quantity) from Purchase select min(quantity) from Purchase CSE 344 - Winter 2014 17

Aggregates and NULL Values Null values are not used in aggregates insert into Purchase values(12, 'gadget', NULL, NULL, 'april') Let’s try the following select count(*) from Purchase select count(quantity) from Purchase select sum(quantity) from Purchase select sum(quantity) from Purchase where quantity is not null; CSE 344 - Winter 2014 18

Counting Duplicates COUNT applies to duplicates, unless otherwise stated: same as Count(*) SELECT Count(product) FROM Purchase WHERE price > 4.99 We probably want: SELECT Count(DISTINCT product) FROM Purchase WHERE price> 4.99 CSE 344 - Winter 2014 19

More Examples SELECT Sum(price * quantity) FROM Purchase What do SELECT Sum(price * quantity) they mean ? FROM Purchase WHERE product = ‘bagel’ CSE 344 - Winter 2014 20

Simple Aggregations Purchase Product Price Quantity Bagel 3 20 Bagel 1.50 20 Banana 0.5 50 Banana 2 10 Banana 4 10 SELECT Sum(price * quantity) FROM Purchase 90 (= 60+30) WHERE product = ‘Bagel’ 21 CSE 344 - Winter 2014

GROUP BY: Grouping and Aggregation Purchase(product, price, quantity) Find total quantities for all sales over $1, by product. SELECT product, Sum(quantity) AS TotalSales FROM Purchase WHERE price > 1 GROUP BY product Let’s see what this means… CSE 344 - Winter 2014 22

Grouping and Aggregation 1. Compute the FROM and WHERE clauses. 2. Group by the attributes in the GROUPBY 3. Compute the SELECT clause: grouped attributes and aggregates. CSE 344 - Winter 2014 23

1&2. FROM-WHERE-GROUPBY SELECT product, Sum(quantity) AS TotalSales FROM Purchase Product Price Quantity WHERE price > 1 GROUP BY product Bagel 3 20 Bagel 1.50 20 Banana 0.5 50 Banana 2 10 WHERE price > 1 Banana 4 10 CSE 344 - Winter 2014 24

3. SELECT Product Price Quantity Product TotalSales Bagel 3 20 Bagel 40 Bagel 1.50 20 Banana 0.5 50 Banana 20 Banana 2 10 Banana 4 10 SELECT product, Sum(quantity) AS TotalSales FROM Purchase WHERE price > 1 GROUP BY product CSE 344 - Winter 2014 25

Other Examples Compare these two queries: SELECT product, count(*) SELECT month, count(*) FROM Purchase FROM Purchase GROUP BY product GROUP BY month SELECT product, sum(quantity) AS SumQuantity, max(price) AS MaxPrice What does FROM Purchase it mean ? GROUP BY product CSE 344 - Winter 2014 26

Need to be Careful… Product Price Quantity SELECT product, max(quantity) FROM Purchase Bagel 3 20 GROUP BY product Bagel 1.50 20 SELECT product, quantity Banana 0.5 50 FROM Purchase GROUP BY product Banana 2 10 Banana 4 10 sqlite is WRONG on Advanced DBMS (e.g. SQL Server) gives an error this query. (the attribute in SELECT must appear in an aggregate function OR GROUP BY clause) CSE 344 - Winter 2014 27

What does this query do? • select product, max(month) from purchase group by product; • Your answer in class  : Bagel september Banana september Gizmo march Gadget march Orange may CSE 344 - Winter 2014 28

Understanding groups -- 1 • select * from purchase; • How many tuples? • Answer: 12 CSE 344 - Winter 2014 29

Understanding groups -- 2 • select product, count(*) from purchase group by product; • Your answer in class  : • Bagel 2 • Orange 1 • Banana 2 • Gadget 4 • Gizmo 3 CSE 344 - Winter 2014 30