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

Recommend

More recommend