more sql
play

More SQL January 30, 2020 Data Science CSCI 1951A Brown University - PowerPoint PPT Presentation

Oct 30, 2022 •177 likes •1.58k views

More SQL January 30, 2020 Data Science CSCI 1951A Brown University Instructor: Ellie Pavlick HTAs: Josh Levin, Diane Mutako, Sol Zitter 1 Announcements People with overrides-Friday EOD to use codes, then we will give then to someone

  1. Clicker Question! PERSON RETWEET Handle Name Person Tweet s 1 s Sol s 2 d Diane d 1 j Josh SELECT Name SELECT * (a) (b) FROM PERSON AS p, FROM PERSON AS p, RETWEET AS r RETWEET AS r WHERE r.Person = p.Name WHERE r.Person = p.Handle SELECT Name (c) FROM PERSON AS p, RETWEET AS r WHERE r.Person = p.Handle 21

  2. Clicker Question! PERSON RETWEET Handle Name Person Tweet s 1 s Sol s 2 d Diane d 1 j Josh SELECT Name SELECT * (a) (b) FROM PERSON AS p, FROM PERSON AS p, RETWEET AS r RETWEET AS r WHERE r.Person = p.Name WHERE r.Person = p.Handle SELECT Name (c) FROM PERSON AS p, RETWEET AS r WHERE r.Person = p.Handle 22

  3. JOINS SELECT ID, Text FROM TWEET, AUTHOR WHERE ID = Tweet AND Person = “d” 23

  4. JOINS SELECT ID, Text FROM TWEET, AUTHOR WHERE ID = Tweet AND Person = “d” = SELECT ID, Text FROM (TWEET JOIN AUTHOR ON ID = Tweet) WHERE Person = “d” 24

  5. JOINS SELECT ID, Text FROM TWEET, AUTHOR WHERE ID = Tweet AND Person = “d” = SELECT ID, Text FROM (TWEET JOIN AUTHOR ON ID = Tweet) WHERE Person = “d” 25

  6. JOINS TWEET ID Text 389472 hey SELECT ID, Text 596208 :-D FROM (TWEET JOIN AUTHOR 782138 1951A 4 lyfe ON ID = Tweet) 173902 i <3 1951A 893110 i <3 1951A Person Tweet ID Text s 389472 389472 hey AUTHOR j 596208 596208 :-D Person Tweet j 173902 173902 i <3 1951A s 389472 s 893110 893110 i <3 1951A j 596208 j 173902 d 672109 s 893110 782138 1951A 4 lyfe d 672109 26

  7. JOINS TWEET ID Text 389472 hey SELECT ID, Text 596208 :-D FROM (TWEET JOIN AUTHOR 782138 1951A 4 lyfe ON ID = Tweet) 173902 i <3 1951A 893110 i <3 1951A Person Tweet ID Text s 389472 389472 hey AUTHOR j 596208 596208 :-D Person Tweet j 173902 173902 i <3 1951A s 389472 s 893110 893110 i <3 1951A j 596208 j 173902 d 672109 s 893110 782138 1951A 4 lyfe d 672109 27

  8. JOINS TWEET ID Text 389472 hey SELECT ID, Text 596208 :-D FROM (TWEET JOIN AUTHOR 782138 1951A 4 lyfe ON ID = Tweet) 173902 i <3 1951A 893110 i <3 1951A Person Tweet ID Text s 389472 389472 hey AUTHOR j 596208 596208 :-D Person Tweet j 173902 173902 i <3 1951A s 389472 s 893110 893110 i <3 1951A j 596208 j 173902 d 672109 s 893110 782138 1951A 4 lyfe d 672109 28

  9. JOINS TWEET Inner Join ID Text 389472 hey SELECT ID, Text 596208 :-D FROM (TWEET JOIN AUTHOR 782138 1951A 4 lyfe ON ID = Tweet) 173902 i <3 1951A 893110 i <3 1951A Person Tweet ID Text s 389472 389472 hey AUTHOR j 596208 596208 :-D Person Tweet j 173902 173902 i <3 1951A s 389472 s 893110 893110 i <3 1951A j 596208 j 173902 d 672109 s 893110 782138 1951A 4 lyfe d 672109 29

  10. JOINS TWEET Left Outer Join ID Text SELECT ID, Text 389472 hey FROM ( TWEET LEFT OUTER JOIN AUTHOR 596208 :-D ON ID = Tweet) 782138 1951A 4 lyfe 173902 i <3 1951A 893110 i <3 1951A Person Tweet ID Text s 389472 389472 hey AUTHOR j 596208 596208 :-D Person Tweet j 173902 173902 i <3 1951A s 389472 s 893110 893110 i <3 1951A j 596208 j 173902 d 672109 s 893110 NULL NULL 782138 1951A 4 lyfe d 672109 30

  11. JOINS TWEET Right Outer Join ID Text SELECT ID, Text 389472 hey FROM (TWEET RIGHT OUTER JOIN AUTHOR 596208 :-D ON ID = Tweet) 782138 1951A 4 lyfe 173902 i <3 1951A 893110 i <3 1951A Person Tweet ID Text s 389472 389472 hey AUTHOR j 596208 596208 :-D Person Tweet j 173902 173902 i <3 1951A s 389472 s 893110 893110 i <3 1951A j 596208 NULL NULL j 173902 d 672109 s 893110 782138 1951A 4 lyfe d 672109 31

  12. JOINS TWEET Full Outer Join ID Text SELECT ID, Text 389472 hey FROM (TWEET FULL OUTER JOIN AUTHOR 596208 :-D ON ID = Tweet) 782138 1951A 4 lyfe 173902 i <3 1951A 893110 i <3 1951A Person Tweet ID Text s 389472 389472 hey AUTHOR j 596208 596208 :-D Person Tweet j 173902 173902 i <3 1951A s 389472 s 893110 893110 i <3 1951A j 596208 NULL NULL j 173902 d 672109 s 893110 NULL NULL 782138 1951A 4 lyfe d 672109 32

  13. Natural Join JOINS TWEET ID Text 389472 hey SELECT ID, Text 596208 :-D FROM (TWEET JOIN AUTHOR) 782138 1951A 4 lyfe 173902 i <3 1951A 893110 i <3 1951A assumes condition is AUTHOR ALL PAIRS of attributes with Person Tweet matching names s 389472 j 596208 j 173902 s 893110 d 672109 33

  14. Natural Join JOINS TWEET ID Text 389472 hey SELECT ID, Text 596208 :-D FROM (TWEET JOIN AUTHOR) 782138 1951A 4 lyfe 173902 i <3 1951A 893110 i <3 1951A if no matches, forms full AUTHOR cross product Person Tweet s 389472 j 596208 j 173902 s 893110 d 672109 34

  15. Natural Join JOINS TWEET ID Text SELECT ID, Text 389472 hey FROM (TWEET AS t(tweetid, text) JOIN 596208 :-D AUTHOR AS a(person, tweetid) 782138 1951A 4 lyfe 173902 i <3 1951A 893110 i <3 1951A Person tweetid tweetid Text s 389472 389472 hey AUTHOR j 596208 596208 :-D Person Tweet j 173902 173902 i <3 1951A s 389472 s 893110 893110 i <3 1951A j 596208 NULL NULL j 173902 d 672109 s 893110 NULL NULL 782138 1951A 4 lyfe d 672109 35

  16. Natural (Inner) JOINS Join TWEET ID Text SELECT ID, Text 389472 hey FROM (TWEET AS t(tweetid, text) JOIN 596208 :-D AUTHOR AS a(person, tweetid) 782138 1951A 4 lyfe 173902 i <3 1951A 893110 i <3 1951A Person tweetid tweetid Text s 389472 389472 hey AUTHOR Person Tweet j 596208 596208 :-D s 389472 j 173902 173902 i <3 1951A j 596208 s 893110 893110 i <3 1951A j 173902 s 893110 d 672109 36

  17. Natural (Inner) JOINS Join TWEET ID Text SELECT ID, Text 389472 hey FROM (TWEET AS t(tweetid, text) JOIN 596208 :-D AUTHOR AS a(person, tweetid) 782138 1951A 4 lyfe 173902 i <3 1951A 893110 i <3 1951A Person tweetid Text s 389472 hey AUTHOR Person Tweet j 596208 :-D s 389472 j 173902 i <3 1951A j 596208 s 893110 i <3 1951A j 173902 s 893110 d 672109 37

  18. Natural (Inner) JOINS Join TWEET ID Text SELECT ID, Text 389472 hey FROM (TWEET AS t(tweetid, foo) JOIN 596208 :-D AUTHOR AS a(foo, tweetid) 782138 1951A 4 lyfe 173902 i <3 1951A 893110 i <3 1951A foo tweetid foo s 389472 hey AUTHOR Person Tweet j 596208 :-D s 389472 j 173902 i <3 1951A j 596208 s 893110 i <3 1951A j 173902 s 893110 d 672109 38

  19. Natural (Inner) JOINS Join TWEET ID Text SELECT ID, Text 389472 hey FROM (TWEET AS t(tweetid, foo) JOIN 596208 :-D AUTHOR AS a(foo, tweetid) 782138 1951A 4 lyfe 173902 i <3 1951A 893110 i <3 1951A foo tweetid AUTHOR Person Tweet s 389472 j 596208 j 173902 s 893110 d 672109 39

  20. Clicker Question! (x2) 40

  21. Clicker Question! STUDENT GRADES ID Name Student Course Grade 1 Diane 1 32 A 2 Sol 2 1951A A J 3 Josh 6 32 A 4 Karlly 5 Mounika SELECT Name, Course FROM (STUDENT LEFT OUTER JOIN GRADES ON ID = Student) Name Course Name Course Diane 32 Diane 32 Sol 1951A Sol 1951A J Josh NULL NULL 32 Karlly NULL (a) (b) Mounika NULL 41

  22. Clicker Question! STUDENT GRADES ID Name Student Course Grade 1 Diane 1 32 A 2 Sol 2 1951A A J 3 Josh 6 32 A 4 Karlly 5 Mounika SELECT Name, Course FROM (STUDENT LEFT OUTER JOIN GRADES ON ID = Student) Name Course Name Course Diane 32 Diane 32 Sol 1951A Sol 1951A J Josh NULL NULL 32 Karlly NULL (a) (b) Mounika NULL 42

  23. Clicker Question! STUDENT GRADES ID Name Student Course Grade 1 Diane 1 32 A 2 Sol 2 1951A A J 3 Josh 4 Karlly 6 32 A 5 Mounika Name Course Diane 32 Target -> Sol 1951A J NULL 32 SELECT Name, Course (a) FROM (STUDENT RIGHT OUTER JOIN GRADES ON ID = Student) SELECT Name, Course (b) FROM (STUDENT NATURAL JOIN GRADES ON ID = Student) 43

  24. Clicker Question! STUDENT GRADES ID Name Student Course Grade 1 Diane 1 32 A 2 Sol 2 1951A A J 3 Josh 4 Karlly 6 32 A 5 Mounika Name Course Diane 32 Target -> Sol 1951A J NULL 32 SELECT Name, Course SELECT Name, Course (a) FROM (STUDENT RIGHT OUTER JOIN GRADES ON ID = Student) FROM (STUDENT RIGHT OUTER JOIN GRADES ON ID = Student) SELECT Name, Course (b) FROM (STUDENT NATURAL JOIN GRADES ON ID = Student) 44

  25. And now…a laundry list of keywords… 45

  26. ORDER BY TWEET ID Time Text SELECT Text 782138 2019-01-04 15:04:57 1951A 4 lyfe FROM Tweet 389472 2019-01-01 12:34:56 hey ORDER BY Time 123794 2019-01-01 12:34:57 lol 127890 2019-01-04 17:30:07 hey 893110 2019-01-06 12:21:53 i <3 1951A 596208 2019-01-02 3:14:15 :-D Text 173902 2019-01-05 3:34:18 i <3 1951A hey lol :-D 1951A 4 lyfe hey i <3 1951A i <3 1951A 46

  27. ORDER BY TWEET ID Time Text SELECT Text 782138 2019-01-04 15:04:57 1951A 4 lyfe FROM Tweet 389472 2019-01-01 12:34:56 hey ORDER BY ID 123794 2019-01-01 12:34:57 lol 127890 2019-01-04 17:30:07 hey 893110 2019-01-06 12:21:53 i <3 1951A 596208 2019-01-02 3:14:15 :-D Text 173902 2019-01-05 3:34:18 i <3 1951A lol hey i <3 1951A hey :-D 1951A 4 lyfe i <3 1951A 47

  28. GROUP BY TWEET ID Likes Text SELECT Text, 782138 1,000 1951A 4 lyfe Count(*), AVG(Likes) 389472 10 hey FROM Tweet 123794 100 lol GROUP BY Text 127890 0 hey 893110 8,000,000 i <3 1951A 596208 1 :-D Text Count(*) AVG(Likes) 173902 1,000,000,000 i <3 1951A lol 1 100 hey 2 5 i <3 1951A 2 504,000,000 :-D 1 1 1951A 4 lyfe 1 1,000 48

  29. GROUP BY TWEET ID Likes Text SELECT Text, 782138 1,000 1951A 4 lyfe Count(*), AVG(Likes) 389472 10 hey FROM Tweet 123794 100 lol GROUP BY Text 127890 0 hey 893110 8,000,000 i <3 1951A 596208 1 :-D Text Count(*) AVG(Likes) 173902 1,000,000,000 i <3 1951A lol 1 100 hey 2 5 i <3 1951A 2 504,000,000 :-D 1 1 SUM, MIN, MAX, 1951A 4 lyfe 1 1,000 COUNT, AVG 49

  30. HAVING TWEET SELECT Text, ID Likes Text Count(*), AVG(Likes) 782138 1,000 1951A 4 lyfe FROM Tweet 389472 10 hey GROUP BY Text 123794 100 lol 127890 0 hey HAVING COUNT(*) > 1 893110 8,000,000 i <3 1951A 596208 1 :-D Text Count(*) AVG(Likes) 173902 1,000,000,000 i <3 1951A hey 2 5 i <3 1951A 2 504,000,000 50

  31. HAVING TWEET SELECT Text, ID Likes Text Count(*), AVG(Likes) 782138 1,000 1951A 4 lyfe FROM Tweet 389472 10 hey GROUP BY Text 123794 100 lol 127890 0 hey HAVING COUNT(*) > 1 893110 8,000,000 i <3 1951A 596208 1 :-D Text Count(*) AVG(Likes) 173902 1,000,000,000 i <3 1951A hey 2 5 i <3 1951A 2 504,000,000 similar behavior to “WHERE”, but only used with aggregations/GROUP BYs 51

  32. LIKE TWEET ID Likes Text SELECT Text, Count(*), 782138 1,000 1951A 4 lyfe AVG(Likes) 389472 10 hey FROM Tweet 123794 100 lol WHERE Text LIKE ‘%1951A%’ 127890 0 hey GROUP BY Text 893110 8,000,000 i <3 1951A 596208 1 :-D 173902 1,000,000,000 i <3 1951A Text Count(*) AVG(Likes) 1951A 4 lyfe 1 1,000 i <3 1951A 2 504,000,000 52

  33. IN STUDENT ID Name 1 Diane SELECT Name 2 Sol FROM STUDENT J 3 Josh WHERE ID IN 4 Karlly (SELECT Student 5 Mounika FROM GRADES WHERE Course = 1951A GRADES ) Student Course Grade 1 32 A Find names of 2 1951A A students in 1951A 6 32 A 53

  34. “Subquery” IN (More later, get STUDENT excited) ID Name 1 Diane SELECT Name 2 Sol FROM STUDENT J 3 Josh WHERE ID IN 4 Karlly (SELECT Student 5 Mounika FROM GRADES WHERE Course = 1951A GRADES ) Student Course Grade 1 32 A Find names of 2 1951A A students in 1951A 6 32 A 54

  35. IN Returns “bag” of student IDs STUDENT ID Name 1 Diane SELECT Name 2 Sol FROM STUDENT J 3 Josh WHERE ID IN 4 Karlly (SELECT Student 5 Mounika FROM GRADES WHERE Course = 1951A GRADES ) Student Course Grade 1 32 A Find names of 2 1951A A students in 1951A 6 32 A 55

  36. IN Returns True if ID is in that bag STUDENT ID Name 1 Diane SELECT Name 2 Sol FROM STUDENT J 3 Josh WHERE ID IN 4 Karlly (SELECT Student 5 Mounika FROM GRADES WHERE Course = 1951A GRADES ) Student Course Grade 1 32 A Find names of 2 1951A A students in 1951A 6 32 A 56

  37. ALL/ANY STUDENT ID Name SELECT Grade 1 Diane 2 Sol FROM GRADES J 3 Josh WHERE Course = “1951A” 4 Karlly AND Grade >= ALL 5 Mounika (SELECT Grade FROM GRADES WHERE Course = 1951A GRADES ) Student Course Grade 1 1951A 3.5 What is the highest 2 1951A 3.5 grade in 1951A? 6 1951A 2.8 57

  38. ALL/ANY Returns True if condition holds STUDENT for all tuples in bag ID Name SELECT Grade 1 Diane 2 Sol FROM GRADES J 3 Josh WHERE Course = “1951A” 4 Karlly AND Grade >= ALL 5 Mounika (SELECT Grade FROM GRADES WHERE Course = 1951A GRADES ) Student Course Grade 1 1951A 3.5 What is the highest 2 1951A 3.5 grade in 1951A? 6 1951A 2.8 58

  39. ALL/ANY STUDENT ID Name SELECT Grade 1 Diane 2 Sol FROM GRADES J 3 Josh WHERE Course = “1951A” 4 Karlly AND Grade > ANY 5 Mounika (SELECT Grade FROM GRADES WHERE Course = 1951A GRADES ) Student Course Grade 1 1951A 3.5 2 1951A 3.5 ??? 6 1951A 2.8 59

  40. ALL/ANY STUDENT ID Name SELECT Grade 1 Diane 2 Sol FROM GRADES J 3 Josh WHERE Course = “1951A” 4 Karlly AND Grade > ANY 5 Mounika (SELECT Grade FROM GRADES WHERE Course = 1951A GRADES ) Student Course Grade 1 1951A 3.5 Return all grades 2 1951A 3.5 except the lowest one . 6 1951A 2.8 60

  41. ALL/ANY STUDENT ID Name SELECT Grade 1 Diane 2 Sol FROM GRADES J 3 Josh WHERE Course = “1951A” 4 Karlly AND Grade > NOT ANY 5 Mounika (SELECT Grade FROM GRADES WHERE Course = 1951A GRADES ) Student Course Grade 1 1951A 3.5 Return the lowest 2 1951A 3.5 grade. 6 1951A 2.8 61

  42. ALL/ANY STUDENT SELECT Grade ID Name FROM GRADES 1 Diane WHERE Course = “1951A” 2 Sol AND Grade >= ALL J 3 Josh (SELECT Grade 4 Karlly FROM GRADES 5 Mounika WHERE Course = 1951A ) GRADES Student Course Grade 1 1951A 3.5 2 1951A 3.5 Grade 6 1951A 2.8 3.5 3.5 62

  43. DISTINCT STUDENT SELECT DISTINCT Grade ID Name FROM GRADES 1 Diane WHERE Course = “1951A” 2 Sol AND Grade >= ALL J 3 Josh (SELECT Grade 4 Karlly FROM GRADES 5 Mounika WHERE Course = 1951A ) GRADES Student Course Grade 1 1951A 3.5 Grade 2 1951A 3.5 3.5 6 1951A 2.8 63

  44. DISTINCT STUDENT SELECT DISTINCT Grade ID Name FROM GRADES 1 Diane WHERE Course = “1951A” 2 Sol AND Grade >= ALL J 3 Josh (SELECT Grade 4 Karlly FROM GRADES 5 Mounika WHERE Course = 1951A ) GRADES Student Course Grade 1 1951A 3.5 Grade 2 1951A 3.5 3.5 6 1951A 2.8 Set operations (Union, Intersection, etc.) remove duplicates by default. 64

  45. EXISTS STUDENT ID Name SELECT NAME 1 Diane 2 Sol FROM STUDENT s J 3 Josh WHERE NOT EXISTS 4 Karlly (SELECT * 5 Mounika FROM GRADES WHERE Course = 1951A AND Student = s.ID GRADES ) Student Course Grade 1 1951A 3.5 2 1951A 3.5 ??? 6 1951A 2.8 65

  46. EXISTS STUDENT True as long as bag is not empty ID Name SELECT NAME 1 Diane 2 Sol FROM STUDENT s J 3 Josh WHERE NOT EXISTS 4 Karlly (SELECT * 5 Mounika FROM GRADES WHERE Course = 1951A AND Student = s.ID GRADES ) Student Course Grade 1 1951A 3.5 2 1951A 3.5 ??? 6 1951A 2.8 66

  47. EXISTS STUDENT ID Name SELECT NAME 1 Diane 2 Sol FROM STUDENT s J 3 Josh WHERE NOT EXISTS 4 Karlly (SELECT * 5 Mounika FROM GRADES WHERE Course = 1951A AND Student = s.ID GRADES ) Student Course Grade 1 1951A 3.5 Students who are 2 1951A 3.5 not in 1951A 6 1951A 2.8 67

  48. 68

  49. Outline • Last time: SQL for creating/manipulating data tables • Today: SQL for querying databases ✔ • Keywords • NULLs • Execution Order, Nested Queries, Optimization 69

  50. NULL! • Black hole! NULL is NULL is NULL and there is no coming back from it… • If an operand is NULL, the result is NULL: • NULL + 1 = NULL • NULL * 0 = NULL • Comparisons: All comparisons that involve a null value, evaluate to unknown • NULL = NULL -> Unknown • NULL != NULL -> Unknown • NULL < 13 -> Unknown • NULL > NULL -> Unknown 70

  51. NULL! p q p OR q p AND q p = q TRUE TRUE TRUE TRUE TRUE TRUE FALSE TRUE FALSE FALSE FALSE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 71

  52. NULL! p q p OR q p AND q p = q TRUE TRUE TRUE TRUE TRUE TRUE FALSE TRUE FALSE FALSE FALSE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE UNK TRUE UNK UNK FALSE UNK UNK FALSE UNK UNK TRUE TRUE UNK UNK UNK FALSE UNK FALSE UNK UNK UNK UNK UNK UNK 72

  53. NULL! WHERE: Only tuples which evaluate to true are part of the query result. (I.e. unknown and false treated equivalently.) TWEET SELECT COUNT(*) ID Text Likes FROM TWEET 389472 NULL 100 WHERE Likes > 10 123794 NULL 3 596208 :-D NULL 782138 1951A 4 lyfe NULL 173902 i <3 1951A 19 Count(*) 893110 i <3 1951A 7539 3 73

  54. NULL! GROUP BY: If NULL exists, then there is a group for NULL. SELECT Text, COUNT(*) TWEET FROM TWEET ID Text Likes 389472 NULL 100 GROUP BY Text 123794 NULL 3 596208 :-D NULL 782138 1951A 4 lyfe NULL Text Count(*) 173902 i <3 1951A 19 NULL 2 893110 i <3 1951A 7539 :-D 1 1951A 4 lyfe 1 i <3 1951A 2 74

  55. NULL! For predicates with NULL, use IS (as opposed to “=“) SELECT Text ID TWEET FROM TWEET ID Text Likes 389472 NULL 100 WHERE Text = NULL 123794 NULL 3 596208 :-D NULL 782138 1951A 4 lyfe NULL 173902 i <3 1951A 19 ID 893110 i <3 1951A 7539 75

  56. NULL! For predicates with NULL, use IS (as opposed to “=“) SELECT Text ID TWEET FROM TWEET ID Text Likes 389472 NULL 100 WHERE Text IS NULL 123794 NULL 3 596208 :-D NULL 782138 1951A 4 lyfe NULL 173902 i <3 1951A 19 ID 893110 i <3 1951A 7539 389472 123794 76

  57. NULL! • count(att) : NULL is ignored • sum(att) : NULL is ignored • avg(att) : results from SUM and COUNT • min(att) and max(att) : NULL is ignored • Exception! If NULL is the only value in the column, then sum/avg/min/max all return “NULL” 77

  58. Clicker Question! (x2) 78

  59. Clicker Question! Count(*) SELECT COUNT(*) FROM TWEET 100 SELECT COUNT(*) Count(*) FROM TWEET WHERE Text = “:)” 15 SELECT COUNT(*) What will be the result of FROM TWEET this query? WHERE Text != “:)” (a) (b) (c) I…don’t…know… Count(*) Count(*) 85 100 79

  60. Clicker Question! Count(*) SELECT COUNT(*) FROM TWEET 100 SELECT COUNT(*) Count(*) FROM TWEET Can’ t say WHERE Text = “:)” 15 how many are NULL SELECT COUNT(*) What will be the result of FROM TWEET this query? WHERE Text != “:)” (a) (b) (c) I…don’t…know… Count(*) Count(*) 85 100 80

  61. Clicker Question! RACES RUNNERS Event_ID Event Winner_ID ID Name 1 PVD Marathon 2 1 Diane 2 PVD Half 3 2 Sol 3 PVD 2 yard jump 2 3 Josh Race4NULL: 
 4 NULL 4 Jon Raising Awareness What will be the result of the below query? SELECT COUNT(*) FROM RUNNERS WHERE ID NOT IN SELECT(Winner_ID FROM RACES) (a) (b) (c) Count(*) Count(*) Count(*) 1 0 2 81

  62. Clicker Question! RACES RUNNERS Event_ID Event Winner_ID ID Name 1 PVD Marathon 2 1 Diane 2 PVD Half 3 2 Sol 3 PVD 2 yard jump 2 3 Josh Race4NULL: 
 4 NULL 4 Jon Raising Awareness What will be the result of the below query? SELECT COUNT(*) ID NOT IN (2,3,NULL) is the same FROM RUNNERS as ID!=2 AND ID!=3 and ID!=NULL WHERE ID NOT IN SELECT(Winner_ID FROM RACES) (a) (b) (c) Count(*) Count(*) Count(*) 1 0 2 82

  63. 83

  64. Outline • Last time: SQL for creating/manipulating data tables • Today: SQL for querying databases ✔ • Keywords ✔ • NULLs • Execution Order, Nested Queries, Optimization 84

  65. Execution Order TWEET SQL ID Time Text 389472 12:34:56 hey SELECT ID, Text 123794 12:34:57 lol FROM TWEET 596208 3:14:15 :-D 782138 15:04:57 1951A 4 lyfe WHERE Text = “hey” 173902 3:34:18 i <3 1951A 893110 12:21:53 i <3 1951A Execution Tree SELECT ID Text 389472 hey WHERE TWEET 85

  66. Execution Order TWEET SQL ID Time Text 389472 12:34:56 hey SELECT ID, Text 123794 12:34:57 lol FROM TWEET 596208 3:14:15 :-D 782138 15:04:57 1951A 4 lyfe WHERE Text = “hey” 173902 3:34:18 i <3 1951A 893110 12:21:53 i <3 1951A Execution Tree SELECT ID Text 389472 hey WHERE FROM 86

  67. Execution Order TWEET SQL ID Time Text 389472 12:34:56 hey SELECT ID, Text 123794 12:34:57 lol FROM TWEET 596208 3:14:15 :-D 782138 15:04:57 1951A 4 lyfe WHERE Text = “hey” 173902 3:34:18 i <3 1951A 893110 12:21:53 i <3 1951A Execution Tree SELECT ID Text 389472 hey WHERE FROM 87

  68. Execution Order TWEET SQL ID Time Text 389472 12:34:5 hey SELECT ID, Text 123794 12:34:57 lol FROM TWEET 596208 3:14:15 :-D 782138 15:04:57 1951A 4 lyfe WHERE Text = “hey” 173902 3:34:18 i <3 1951A 893110 12:21:53 i <3 1951A Execution Tree SELECT ID Text 389472 hey WHERE FROM 88

  69. Execution Order TWEET SQL ID Time Text 389472 12:34:56 hey SELECT ID, Text 123794 12:34:57 lol FROM TWEET 596208 3:14:15 :-D 782138 15:04:57 1951A 4 lyfe WHERE Text = “hey” 173902 3:34:18 i <3 1951A 893110 12:21:53 i <3 1951A Execution Tree WHERE ID Text 389472 hey SELECT A query can have multiple FROM “equivalent” trees 89

  70. Execution Order TWEET SQL ID Time Text 389472 12:34:5 hey SELECT ID, Text 123794 12:34:5 lol FROM TWEET 7 596208 3:14:15 :-D 782138 15:04:5 1951A 4 lyfe WHERE Text = “hey” 7 173902 3:34:18 i <3 1951A 893110 12:21:5 i <3 1951A Execution Tree WHERE ID Text 389472 hey SELECT A query can have multiple FROM “equivalent” trees 90

  71. Execution Order TWEET SQL ID Time Text 389472 12:34:5 hey SELECT ID, Text 123794 12:34:5 lol FROM TWEET 596208 3:14:15 :-D 782138 15:04:5 1951A 4 lyfe WHERE Text = “hey” 7 173902 3:34:18 i <3 1951A 893110 12:21:5 i <3 1951A Execution Tree WHERE ID Text 389472 hey SELECT A query can have multiple FROM “equivalent” trees 91

  72. Clicker Question! 92

  73. Clicker Question! Which is better? (a) WHERE(SELECT(FROM)) (b) SELECT(WHERE(FROM)) (c) I…don’t…know…it depends 93

  74. Clicker Question! Which is better? (a) WHERE(SELECT(FROM)) (b) SELECT(WHERE(FROM)) (c) I…don’t…know…it depends 94

  75. Execution Order TWEET ID Time Text 389472 12:34:56 hey SQL 123794 12:34:57 lol 596208 3:14:15 :-D SELECT ID, Text 782138 15:04:57 1951A 4 lyfe FROM TWEET 173902 3:34:18 i <3 1951A 893110 12:21:53 i <3 1951A WHERE Text = “hey” Execution Tree WHERE(SELECT(FROM)) SELECT(WHERE(FROM)) 95

  76. Execution Order TWEET ID Time Text 389472 12:34:56 hey SQL 123794 12:34:57 lol 596208 3:14:15 :-D SELECT ID, Time 782138 15:04:57 1951A 4 lyfe FROM TWEET 173902 3:34:18 i <3 1951A 893110 12:21:53 i <3 1951A WHERE Text = “hey” Execution Tree WHERE(SELECT(FROM)) SELECT(WHERE(FROM)) 96

  77. Execution Order TWEET ID Time Text 389472 12:34:56 hey SQL 123794 12:34:57 lol 596208 3:14:15 :-D SELECT ID, Time 782138 15:04:57 1951A 4 lyfe FROM TWEET 173902 3:34:18 i <3 1951A 893110 12:21:53 i <3 1951A WHERE Text = “hey” Execution Tree WHERE(SELECT(FROM)) SELECT(WHERE(FROM)) 97

  78. Execution Order TWEET ID Time Text 389472 12:34:56 hey SQL 123794 12:34:57 lol 596208 3:14:15 :-D SELECT ID, Time 782138 15:04:57 1951A 4 lyfe FROM TWEET 173902 3:34:18 i <3 1951A 893110 12:21:53 i <3 1951A WHERE Text = “hey” Execution Tree ID Time Text 389472 12:34:56 hey WHERE(SELECT(FROM)) 123794 12:34:57 lol 596208 3:14:15 :-D 782138 15:04:57 1951A 4 lyfe SELECT(WHERE(FROM)) 173902 3:34:18 i <3 1951A 893110 12:21:53 i <3 1951A 98

  79. Execution Order TWEET ID Time Text 389472 12:34:56 hey SQL 123794 12:34:57 lol 596208 3:14:15 :-D SELECT ID, Time 782138 15:04:57 1951A 4 lyfe FROM TWEET 173902 3:34:18 i <3 1951A 893110 12:21:53 i <3 1951A WHERE Text = “hey” Execution Tree ID Time 389472 12:34:56 WHERE(SELECT(FROM)) 123794 12:34:57 596208 3:14:15 782138 15:04:57 SELECT(WHERE(FROM)) 173902 3:34:18 893110 12:21:53 99

  80. Execution Order TWEET ID Time Text 389472 12:34:56 hey SQL 123794 12:34:57 lol 596208 3:14:15 :-D SELECT ID, Time 782138 15:04:57 1951A 4 lyfe FROM TWEET 173902 3:34:18 i <3 1951A 893110 12:21:53 i <3 1951A WHERE Text = “hey” Execution Tree ID Time 389472 12:34:56 WHERE(SELECT(FROM)) 123794 12:34:57 596208 3:14:15 782138 15:04:57 SELECT(WHERE(FROM)) 173902 3:34:18 893110 12:21:53 100

Download Presentation
Download Policy: The content available on the website is offered to you 'AS IS' for your personal information and use only. It cannot be commercialized, licensed, or distributed on other websites without prior consent from the author. To download a presentation, simply click this link. If you encounter any difficulties during the download process, it's possible that the publisher has removed the file from their server.

Recommend

Observe your neighbors and remove your seatbelt Type introspection and type-unsafety in Qt

Observe your neighbors and remove your seatbelt Type introspection and type-unsafety in Qt

Observe your neighbors and remove your seatbelt Type introspection and type-unsafety in Qt Stephen Kelly stephen.kelly@kdab.com KDAB Stephen Kelly C++/Qt user since 2006 KDE contributor since 2007 Qt contributor since 2009

769 views • 45 slides
HolographicAlgorithms Beyond Matchgates Heng Guo (joint work with Jin-Yi Cai and Tyson Williams )

HolographicAlgorithms Beyond Matchgates Heng Guo (joint work with Jin-Yi Cai and Tyson Williams )

HolographicAlgorithms Beyond Matchgates Heng Guo (joint work with Jin-Yi Cai and Tyson Williams ) University of Wisconsin-Madison Kbenhavn July 11th 2014 Heng Guo (CS, UW-Madison) General Holographic Algorithms ICALP 2014 1 / 22 Counting

1.24k views • 94 slides
Becoming a TPM The Dos and Donts of Technical Project Management #drupaltpm Introductions

Becoming a TPM The Dos and Donts of Technical Project Management #drupaltpm Introductions

Becoming a TPM The Dos and Donts of Technical Project Management #drupaltpm Introductions Jessi Fischer Anne Stefanyk Pantheon Kanopi Studios Definition of a Project Manager GATEKEEP BRIDGE EDUCATE ANTICIPATE Taming Two Lions How

588 views • 33 slides
CS 410: Advanced Software Engineering Reid Holmes Lecture Summary Administrivia

CS 410: Advanced Software Engineering Reid Holmes Lecture Summary Administrivia

CS 410: Advanced Software Engineering Reid Holmes Lecture Summary Administrivia Expectations Project Quick Assignment REID HOLMES - CPSC 410: ADVANCED SOFTWARE ENGINEERING Dates and Times Lectures in DMP 110 MWF @ 1300 -

772 views • 44 slides
Introduction to Computer Science CSCI 109 Readings St. Amant, Ch. 3 Andrew Goodney Fall 2019

Introduction to Computer Science CSCI 109 Readings St. Amant, Ch. 3 Andrew Goodney Fall 2019

Introduction to Computer Science CSCI 109 Readings St. Amant, Ch. 3 Andrew Goodney Fall 2019 Lecture 2: Architecture Sept. 9th, 2019 Reminders u Take the survey if you havent already u https://tinyurl.com/y2cot2r5 v Password:

1.19k views • 64 slides
VAASeline: VNC Attack Automation Suite 'Lubricating blind entry' Rich Smith

VAASeline: VNC Attack Automation Suite 'Lubricating blind entry' Rich Smith

VAASeline: VNC Attack Automation Suite 'Lubricating blind entry' Rich Smith rich@immunityinc.com 1 Agenda VNC and it's underlying protocol RFB Why attack automation is needed Why RFB is hard to automate The VAASeline technique

954 views • 56 slides
Modular Reasoning for Actor Specification Diagrams Rigorous reasoning for open distributed

Modular Reasoning for Actor Specification Diagrams Rigorous reasoning for open distributed

Reasoning About Open Systems Project Collaboration with Agha, Mason, Smith, Talcott Modular Reasoning for Actor Specification Diagrams Rigorous reasoning for open distributed systems Scott F . Smith General multi-language framework

428 views • 10 slides
CS502: Compiler Design Lexical Analysis (Cont.) Manas Thakur Fall 2020 Recognizing strings in

CS502: Compiler Design Lexical Analysis (Cont.) Manas Thakur Fall 2020 Recognizing strings in

CS502: Compiler Design Lexical Analysis (Cont.) Manas Thakur Fall 2020 Recognizing strings in regular languages Done using Finite Automata A kind of state machine A state denotes a remembrance of the string read so far. An arrow

781 views • 14 slides
WDO WORKSHOP ACER Workshop on Within Day Obligations TI TRE 15 May, 2017 In total 48

WDO WORKSHOP ACER Workshop on Within Day Obligations TI TRE 15 May, 2017 In total 48

WDO WORKSHOP ACER Workshop on Within Day Obligations TI TRE 15 May, 2017 In total 48 registered participants WDO WORKSHOP Opening remarks Reflections on ACERs First Monitoring Report and the Balancing Network Code Gas Department

1.51k views • 111 slides
Samsung Galaxy Gear - A Long Time Coming cs160. cs160. valkyriesavage.com valkyriesavage.com

Samsung Galaxy Gear - A Long Time Coming cs160. cs160. valkyriesavage.com valkyriesavage.com

Samsung Galaxy Gear - A Long Time Coming cs160. cs160. valkyriesavage.com valkyriesavage.com welcome to cs160! June 22, 2015 Valkyrie Savage http://stackoverflow.com/questions/238177 Tim Mott, mid-1970s, from Moggridge, Designing

1.14k views • 64 slides
Financial Report Financial Report For the Period Ending For the Period Ending December 31, 2019

Financial Report Financial Report For the Period Ending For the Period Ending December 31, 2019

Financial Report Financial Report For the Period Ending For the Period Ending December 31, 2019 December 31, 2019 Presented in thousands. E stimate d Be ginning F YT D R e ve nue s F YT D E xpe nse s E nding Gove r nme ntal Ope r

405 views • 14 slides
Ensemble Optimization Method on SAXS EOM 2.0 tutorial Giancarlo Tria

Ensemble Optimization Method on SAXS EOM 2.0 tutorial Giancarlo Tria

Ensemble Optimization Method on SAXS EOM 2.0 tutorial Giancarlo Tria giancarlo.tria@embl-hamburg.de BioSAXS group @ EMBL Hamburg EMBO Practical Course on Solution Scattering from Biological Macromolecular October 21 st , 2012 Kratky Plots

438 views • 20 slides
2 d gravity with massive matter Harold Erbin Lptens , cole Normale Suprieure (France) SCGSC

2 d gravity with massive matter Harold Erbin Lptens , cole Normale Suprieure (France) SCGSC

2 d gravity with massive matter Harold Erbin Lptens , cole Normale Suprieure (France) SCGSC 2017 Ihp , Paris 17th February 2017 arXiv: 1612.04097 , 1511.06150 1 / 25 Outline Introduction Classical gravity Quantum gravity Mabuchi

793 views • 41 slides
SDOF linear oscillator Response to Harmonic Loading Giacomo Boffi Dipartimento di Ingegneria

SDOF linear oscillator Response to Harmonic Loading Giacomo Boffi Dipartimento di Ingegneria

SDOF linear oscillator Giacomo Boffi SDOF linear oscillator Response to Harmonic Loading Giacomo Boffi Dipartimento di Ingegneria Civile e Ambientale, Politecnico di Milano March 19, 2014 Outline of parts 1 and 2 SDOF linear oscillator

1.07k views • 67 slides
Summary of the Geometry Parallel Session and Field Working Session/Stint J. Apostolakis VecGeom

Summary of the Geometry Parallel Session and Field Working Session/Stint J. Apostolakis VecGeom

Summary of the Geometry Parallel Session and Field Working Session/Stint J. Apostolakis VecGeom = Evolved USolids Recent Developments in USolids/VecGeom + Many-Particle API Status + Plans + Geometry Model / Navigation Sandro Wenzel /

384 views • 13 slides
HODGKIN LYMPHOMA New Combinations Stephen M. Ansell, MD, PhD Chair, Lymphoma Group Mayo

HODGKIN LYMPHOMA New Combinations Stephen M. Ansell, MD, PhD Chair, Lymphoma Group Mayo

HODGKIN LYMPHOMA New Combinations Stephen M. Ansell, MD, PhD Chair, Lymphoma Group Mayo Clinic Disclosures for Stephen Ansell, MD, PhD In compliance with ACCME policy, Mayo Clinic requires the following disclosures to the activity

624 views • 16 slides
Internal Migration and Education-Occupation Mismatch: Evidence from India Shweta Grover and

Internal Migration and Education-Occupation Mismatch: Evidence from India Shweta Grover and

Internal Migration and Education-Occupation Mismatch: Evidence from India Shweta Grover and Ajay Sharma (Indian Institute of Management Indore, India) September 13, 2019 Shweta Grover and Ajay Sharma UNU-WIDER September 13, 2019 1 / 30

443 views • 30 slides
Lost in an EFT? You are here! The path to You are here! the pagoda Current LHC no obvious

Lost in an EFT? You are here! The path to You are here! the pagoda Current LHC no obvious

Lost in an EFT? You are here! The path to You are here! the pagoda Current LHC no obvious exploration.. signposts so far What can we understand about the maze in general? A first glance at some deep underlying structure of EFTs

735 views • 61 slides
Keystone Data System (KDS) Overview January 2016 Welcome! Kelsey Peterson Megan Black

Keystone Data System (KDS) Overview January 2016 Welcome! Kelsey Peterson Megan Black

Keystone Data System (KDS) Overview January 2016 Welcome! Kelsey Peterson Megan Black Caitlin Urban Jeff Berman Data Analyst Data Manager Keystone Fellow Data Analyst Todays Topics What is KDS? KDS Access Surveys:

677 views • 39 slides
4-current density . Lets suppose J is the 4-current density, let J =(c , J )

4-current density . Lets suppose J is the 4-current density, let J =(c , J )

4-current density . Lets suppose J is the 4-current density, let J =(c , J ) So Using the property Smooth function we can rewrite J as P. Piot, PHYS 571 Fall 2007 Charge continuity equation

700 views • 13 slides
SUSY Lagrangians Wess-Zumino The free WessZumino model d 4 x ( L s + L f ) S = L s =

SUSY Lagrangians Wess-Zumino The free WessZumino model d 4 x ( L s + L f ) S = L s =

SUSY Lagrangians Wess-Zumino The free WessZumino model d 4 x ( L s + L f ) S = L s = , L f = i . g = = diag(1 , 1 , 1 , 1) + +

764 views • 50 slides
Equations of motion of compact binaries at the fourth post-Newtonian order Laura BERNARD in

Equations of motion of compact binaries at the fourth post-Newtonian order Laura BERNARD in

Equations of motion of compact binaries at the fourth post-Newtonian order Laura BERNARD in collaboration with L.Blanchet, A. Boh e, G. Faye, S. Marsat Hot Topics in General Relativity and Gravitation 2015 10/08/2015 Laura BERNARD EoM of

527 views • 21 slides
Gravitational Waves Induced by Scalar Perturbations during a gradual Transition from an Early

Gravitational Waves Induced by Scalar Perturbations during a gradual Transition from an Early

Gravitational Waves Induced by Scalar Perturbations during a gradual Transition from an Early Matter era to the Radiation era Keisuke Inomata Institute for Cosmic Ray Research (ICRR), The University of Tokyo Collaborators: K.Kohri(KEK),

726 views • 25 slides
Group Theoretic Approach to Theory of Fermion Production Minho Son Korea Advanced Institute of

Group Theoretic Approach to Theory of Fermion Production Minho Son Korea Advanced Institute of

KEK-KIAS-NCTS Theory Workshop, Dec 4 - 7, 2018 Group Theoretic Approach to Theory of Fermion Production Minho Son Korea Advanced Institute of Science and Technology (KAIST) Based on Min, SON, Suh 1808.00939 Particle Production Relaxation

701 views • 37 slides

More recommend