Animation of SQL Queries To illustrate three SQL

Animation of SQL Queries • To illustrate three SQL queries: – Q 1: simple select (one table) – Q 2: select with conditions (one table) – Q 3: select requiring a JOIN operation. • Observe how they are “implemented” – Measure the number of “row operations” Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 1

First, define a database: Schema, instances Schema Sailors ( sid: integer, sname: string, rating: integer, age: real ) Reserves ( sid: integer, bid: integer, day: date ) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 sid bid day 22 101 10/10/02 58 103 11/12/02 An instance R of Reserves 58 Rusty 10 35 71 Zorba 10 16 74 Horatio 9 40 These illustrative examples are small. An instance S of Sailors Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 2

First, define a database: Schema, instances Schema Sailors ( sid: integer, sname: string, rating: integer, age: real ) Reserves ( sid: integer, bid: integer, day: date ) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 sid bid day 22 101 10/10/02 58 103 11/12/02 An instance R of Reserves 58 Rusty 10 35 71 Zorba 10 16 74 Horatio 9 40 But, also imagine larger tables: 1000 sailors, 5000 reserves An instance S of Sailors Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 3

Query Q 1 Simple Select Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 4

Q 1. Find the names and ages of all sailors. SELECT S. sname, S. age FROM Sailors S The corresponding SQL query. S (instance of Sailors) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 71 Zorba 10 16 74 Horatio 9 40 Leong. HW, So. C, NUS Now, animate the execution of the SQL query! © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 5

Q 1. Find the names and ages of all sailors. [Step 0] SELECT S. sname, S. age FROM Sailors S Result S (instance of Sailors) sname sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 71 Zorba 10 16 74 Horatio 9 40 Leong. HW, So. C, NUS age Query result is also a database table. © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 6

Q 1. Find the names and ages of all sailors. [Step 1] SELECT S. sname, S. age FROM Sailors S Result S (instance of Sailors) sid sname rating age sname age 22 Dustin 7 45. 0 Dustin 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 71 Zorba 10 16 74 Horatio 9 40 Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 Output only the required fields in this entry. (UIT 2201: Database) Page 7

Q 1. Find the names and ages of all sailors. [Step 2] SELECT S. sname, S. age FROM Sailors S Result S (instance of Sailors) sid sname rating age sname age 22 Dustin 7 45. 0 Dustin 45. 0 31 Lubber 8 55. 5 Lubber 55. 5 58 Rusty 10 35 71 Zorba 10 16 74 Horatio 9 40 Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 8

Q 1. Find the names and ages of all sailors. [Step 3] SELECT S. sname, S. age FROM Sailors S Result S (instance of Sailors) sid sname rating age sname age 22 Dustin 7 45. 0 Dustin 45. 0 31 Lubber 8 55. 5 Lubber 55. 5 58 Rusty 10 35 Rusty 35 71 Zorba 10 16 74 Horatio 9 40 Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 9

Q 1. Find the names and ages of all sailors. [Step 4] SELECT S. sname, S. age FROM Sailors S Result S (instance of Sailors) sid sname rating age sname age 22 Dustin 7 45. 0 Dustin 45. 0 31 Lubber 8 55. 5 Lubber 55. 5 58 Rusty 10 35 Rusty 35 71 Zorba 10 16 Zorba 16 74 Horatio 9 40 Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 10

Q 1. Find the names and ages of all sailors. [Step 5] SELECT S. sname, S. age FROM Sailors S Result S (instance of Sailors) sid sname rating age sname age 22 Dustin 7 45. 0 Dustin 45. 0 31 Lubber 8 55. 5 Lubber 55. 5 58 Rusty 10 35 Rusty 35 71 Zorba 10 16 Zorba 16 74 Horatio 9 40 Horatio 40 Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 11

Q 1. Find the names and ages of all sailors. [Step 6] SELECT S. sname, S. age FROM Sailors S Result S (instance of Sailors) sid sname rating age sname age 22 Dustin 7 45. 0 Dustin 45. 0 31 Lubber 8 55. 5 Lubber 55. 5 58 Rusty 10 35 Rusty 35 71 Zorba 10 16 Zorba 16 Horatio 40 74 Horatio 9 40 End of Algorithm Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 12

Summary of Q 1: • Result of SQL query – is another table – derived from original table. • A simple analysis shows – This takes (n) row operations, where n is size (the number of records) in table S. – Eg: Query on large S --- 1000 row operations; Query on large R --- 5000 row operations; • This query is also called a “projection” – It is the same as the “e-project” primitive – It simply selected a subset of the columns Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 13

Query Q 3 Select with conditions Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 14

Q 2. Find all sailors with a rating above 7. SELECT S. sid, S. sname FROM Sailors S WHERE (S. rating > 7) The corresponding SQL query. S (instance of Sailors) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 71 Zorba 10 16 74 Horatio 9 40 Leong. HW, So. C, NUS Now, animate the execution of the SQL query! © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 15

Q 2. Find all sailors with a rating above 7. [Step 0] SELECT S. sid, S. sname FROM Sailors S WHERE (S. rating > 7) CPU S (instance of Sailors) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 71 Zorba 10 16 74 Horatio 9 40 Leong. HW, So. C, NUS Result sid sname Query result is also a database table. © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 16

Q 2. Find all sailors with a rating above 7. [Step 1] SELECT S. sid, S. sname FROM Sailors S WHERE (S. rating > 7) CPU 7 > 7? No! S (instance of Sailors) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 71 Zorba 10 16 74 Horatio 9 40 Leong. HW, So. C, NUS Result © Leong Hon Wai, 2003 -2008 sid Condition is false Do not output this entry. sname (UIT 2201: Database) Page 17

Q 2. Find all sailors with a rating above 7. [Step 2] SELECT S. sid, S. sname FROM Sailors S WHERE (S. rating > 7) CPU 8 > 7? Yes. S (instance of Sailors) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 71 Zorba 10 16 74 Horatio 9 40 Leong. HW, So. C, NUS Result © Leong Hon Wai, 2003 -2008 Condition is true Output this entry. sid sname 31 Lubber (UIT 2201: Database) Page 18

Q 2. Find all sailors with a rating above 7. [Step 3] SELECT S. sid, S. sname FROM Sailors S WHERE (S. rating > 7) CPU 10 > 7? Yes. S (instance of Sailors) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 71 Zorba 10 16 74 Horatio 9 40 Leong. HW, So. C, NUS Result © Leong Hon Wai, 2003 -2008 Condition is true Output this entry. sid sname 31 Lubber 58 Rusty (UIT 2201: Database) Page 19

Q 2. Find all sailors with a rating above 7. [Step 4] SELECT S. sid, S. sname FROM Sailors S WHERE (S. rating > 7) CPU 10 > 7? Yes. S (instance of Sailors) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 71 Zorba 10 16 74 Horatio 9 40 Leong. HW, So. C, NUS Result © Leong Hon Wai, 2003 -2008 sid sname 31 Lubber 58 Rusty 71 Zorba (UIT 2201: Database) Page 20

Q 2. Find all sailors with a rating above 7. [Step 5] SELECT S. sid, S. sname FROM Sailors S WHERE (S. rating > 7) CPU 9 > 7? Yes. S (instance of Sailors) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 71 Zorba 74 Horatio Leong. HW, So. C, NUS Result sid sname 31 Lubber 35 58 Rusty 10 16 71 Zorba 9 40 74 Horatio © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 21

Q 2. Find all sailors with a rating above 7. [Step 6] SELECT S. sid, S. sname FROM Sailors S WHERE (S. rating > 7) CPU S (instance of Sailors) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 71 Zorba 74 Horatio Result sid sname 31 Lubber 35 58 Rusty 10 16 71 Zorba 9 40 74 Horatio End of Algorithm Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 22

Summary of Q 2: • Result of SQL query – is another table – row-inclusion is determined by the where-clause. • A simple analysis shows – This takes (n) row operations; where n is size (the number of records) in table S. Eg: Query on large S --- 1000 row operations; Query on large R --- 5000 row operations; • This query can be decomposed into – an “e-select”, followed by an “e-project” primitives Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 23

Query Q 3 Select requiring a JOIN of multiple tables Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 24

Q 3. Find the names of sailors who have reserved boat number 103. DB (2 tables) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 sid bid day 22 101 10/10/02 58 103 11/12/02 An instance R of Reserves An instance S of Sailors This query requires information from both tables S and R. To answer this query, a JOIN operation needs to be performed. Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 25

Q 3. Find the names of sailors who have reserved boat number 103. SELECT S. name FROM Sailors S, Reserves R WHERE (S. sid = R. sid) AND (R. bid = 103) The corresponding SQL query. DB (2 tables) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 An instance S of Sailors sid bid day 22 101 10/10/02 58 103 11/12/02 An instance R of Reserves IMPT: “Join Condition” This specifies how S and R are to be joined together. This query requires information from both tables S and R. To answer this query, a JOIN operation needs to be performed. Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 26

Q 3. Find the names of sailors who have reserved boat number 103. SELECT S. name FROM Sailors S, Reserves R WHERE (S. sid = R. sid) AND (R. bid = 103) S (instance of Sailors) Overview: sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 R (instance of Reserves) sid bid day 22 101 10/10/02 58 103 11/12/02 Leong. HW, So. C, NUS A JOIN operation works as follows: • for each row in table S; + try to “join” with each row in R (match the “where” conditions) Analysis: So, a JOIN takes (nm) row operations where n = size of table S, and m = size of table R. © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 27

Q 3. Find the names of sailors who have reserved boat number 103. SELECT S. name FROM Sailors S, Reserves R WHERE (S. sid = R. sid) AND (R. bid = 103) Result S (instance of Sailors) CPU S. sid = 22 sid sname rating age 22 Dustin 7 45. 0 (S. sid = R. sid) 31 Lubber 8 55. 5 R. bid = 101 58 Rusty 10 sname 35 R. sid = 22 (R. bid ≠ 103) ! R (instance of Reserves) sid bid day 22 101 10/10/02 58 103 11/12/02 Leong. HW, So. C, NUS Condition is false Do not output this entry. © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 28

Q 3. Find the names of sailors who have reserved boat number 103. SELECT S. name FROM Sailors S, Reserves R WHERE (S. sid = R. sid) AND (R. bid = 103) Result S (instance of Sailors) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 sname CPU S. sid = 22 R. sid = 58 (S. sid ≠ R. sid) ! R (instance of Reserves) sid bid day 22 101 10/10/02 58 103 11/12/02 Leong. HW, So. C, NUS Condition is false Do not output this entry. © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 29

Q 3. Find the names of sailors who have reserved boat number 103. SELECT S. name FROM Sailors S, Reserves R WHERE (S. sid = R. sid) AND (R. bid = 103) Result S (instance of Sailors) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 sname CPU S. sid = 31 R. sid = 22 (S. sid ≠ R. sid) ! R (instance of Reserves) sid bid day 22 101 10/10/02 58 103 11/12/02 Leong. HW, So. C, NUS Condition is false Do not output this entry. © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 30

Q 3. Find the names of sailors who have reserved boat number 103. SELECT S. name FROM Sailors S, Reserves R WHERE (S. sid = R. sid) AND (R. bid = 103) Result S (instance of Sailors) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 sname CPU S. sid = 31 R. sid = 58 (S. sid ≠ R. sid) ! R (instance of Reserves) sid bid day 22 101 10/10/02 58 103 11/12/02 Leong. HW, So. C, NUS Condition is false Do not output this entry. © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 31

Q 3. Find the names of sailors who have reserved boat number 103. SELECT S. name FROM Sailors S, Reserves R WHERE (S. sid = R. sid) AND (R. bid = 103) Result S (instance of Sailors) sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35 sname CPU S. sid = 58 R. sid = 22 (S. sid ≠ R. sid) ! R (instance of Reserves) sid bid day 22 101 10/10/02 58 103 11/12/02 Leong. HW, So. C, NUS Condition is false Do not output this entry. © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 32

Q 3. Find the names of sailors who have reserved boat number 103. SELECT S. name FROM Sailors S, Reserves R WHERE (S. sid = R. sid) AND (R. bid = 103) Result S (instance of Sailors) CPU S. sid = 58 sid sname rating age sname R. sid = 58 22 Dustin 7 45. 0 Rusty (S. sid = R. sid) ! 31 Lubber 8 55. 5 58 Rusty 10 35 R. bid = 103 (R. bid = 103) ! R (instance of Reserves) sid bid day 22 101 10/10/02 58 103 11/12/02 Leong. HW, So. C, NUS Condition is true Output this entry. © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 33

Q 3. Find the names of sailors who have reserved boat number 103. SELECT S. name FROM Sailors S, Reserves R WHERE (S. sid = R. sid) AND (R. bid = 103) Result S (instance of Sailors) sid sname rating age sname 22 Dustin 7 45. 0 Rusty 31 Lubber 8 55. 5 58 Rusty 10 35 R (instance of Reserves) sid bid day 22 101 10/10/02 58 103 11/12/02 Leong. HW, So. C, NUS CPU End of Algorithm © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 34

Summary of Q 3: • Result of SQL query requires – information from two tables (often more) – a JOIN operation is necessary • A simple analysis shows JOIN is expensive – This takes (nm) row operations; where n is size (the number of records) of table S, and m is size (the number of records) of table R. Eg: Query on large S and R: = 1000 * 5000 = 5, 000 row operations! • This query can be decomposed into – an “e-join”, then “e-select”, “e-project” primitives Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 35

RECAP: SQL Queries • We illustrated three SQL queries: – Q 1: simple select (one table) – Q 2: select with conditions (one table) – Q 3: select requiring a JOIN operation (of multiple tables) JOIN is expensive • We know how they are “implemented” – Measure the number of “row operations” Leong. HW, So. C, NUS © Leong Hon Wai, 2003 -2008 (UIT 2201: Database) Page 36