1 / 36

Animation of SQL Queries

Animation of SQL Queries. To illustrate three SQL queries: Q1: simple select (one table) Q2: select with conditions (one table) Q3: select requiring a JOIN operation. Observe how they are “implemented” Measure the number of “row operations”. First, define a database: Schema, instances.

daphne-burris
Télécharger la présentation

Animation of SQL Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Animation of SQL Queries • To illustrate three SQL queries: • Q1: simple select (one table) • Q2: select with conditions (one table) • Q3: select requiring a JOIN operation. • Observe how they are “implemented” • Measure the number of “row operations”

  2. First, define a database: Schema, instances Schema Sailors ( sid: integer, sname: string, rating: integer, age: real ) Reserves ( sid: integer, bid: integer, day: date ) An instance R of Reserves These illustrative examples are small. An instance S of Sailors

  3. First, define a database: Schema, instances Schema Sailors ( sid: integer, sname: string, rating: integer, age: real ) Reserves ( sid: integer, bid: integer, day: date ) An instance R of Reserves But, also imagine larger tables: 1000 sailors, 5000 reserves An instance S of Sailors

  4. Query Q1 Simple Select

  5. Q1. Find the names and ages of all sailors. SELECT S.sname, S.age FROM Sailors S The corresponding SQL query. S (instance of Sailors) Now, animate the execution of the SQL query!

  6. Q1. Find the names and ages of all sailors. [Step 0] SELECT S.sname, S.age FROM Sailors S Result S (instance of Sailors) Query result is also a database table.

  7. Q1. Find the names and ages of all sailors. [Step 1] SELECT S.sname, S.age FROM Sailors S Result S (instance of Sailors) Output only the required fields in this entry.

  8. Q1. Find the names and ages of all sailors. [Step 2] SELECT S.sname, S.age FROM Sailors S Result S (instance of Sailors)

  9. Q1. Find the names and ages of all sailors. [Step 3] SELECT S.sname, S.age FROM Sailors S Result S (instance of Sailors)

  10. Q1. Find the names and ages of all sailors. [Step 4] SELECT S.sname, S.age FROM Sailors S Result S (instance of Sailors)

  11. Q1. Find the names and ages of all sailors. [Step 5] SELECT S.sname, S.age FROM Sailors S Result S (instance of Sailors)

  12. Q1. Find the names and ages of all sailors. [Step 6] SELECT S.sname, S.age FROM Sailors S Result S (instance of Sailors) End of Algorithm

  13. Summary of Q1: • 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

  14. Query Q3 Select with conditions

  15. Q2. 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) Now, animate the execution of the SQL query!

  16. CPU Q2. Find all sailors with a rating above 7.[Step 0] SELECT S.sid, S.sname FROM Sailors S WHERE (S.rating > 7) S (instance of Sailors) Result Query result is also a database table.

  17. CPU Q2. Find all sailors with a rating above 7.[Step 1] SELECT S.sid, S.sname FROM Sailors S WHERE (S.rating > 7) 7 > 7? No! Condition is false Do not output this entry. S (instance of Sailors) Result

  18. CPU Q2. Find all sailors with a rating above 7.[Step 2] SELECT S.sid, S.sname FROM Sailors S WHERE (S.rating > 7) 8 > 7? Yes. Condition is true Output this entry. S (instance of Sailors) Result

  19. CPU Q2. Find all sailors with a rating above 7.[Step 3] SELECT S.sid, S.sname FROM Sailors S WHERE (S.rating > 7) 10 > 7? Yes. Condition is true Output this entry. S (instance of Sailors) Result

  20. CPU Q2. Find all sailors with a rating above 7.[Step 4] SELECT S.sid, S.sname FROM Sailors S WHERE (S.rating > 7) 10 > 7? Yes. S (instance of Sailors) Result

  21. CPU Q2. Find all sailors with a rating above 7.[Step 5] SELECT S.sid, S.sname FROM Sailors S WHERE (S.rating > 7) 9 > 7? Yes. S (instance of Sailors) Result

  22. CPU Q2. Find all sailors with a rating above 7.[Step 6] SELECT S.sid, S.sname FROM Sailors S WHERE (S.rating > 7) S (instance of Sailors) Result End of Algorithm

  23. Summary of Q2: • 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

  24. Query Q3 Select requiring a JOINof multiple tables

  25. Q3. Find the names of sailors who have reserved boat number 103. DB (2 tables) 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.

  26. IMPT: “Join Condition”This specifies how S and R are to be joined together. Q3. 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) 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.

  27. Q3. 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: • 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. R (instance of Reserves)

  28. CPU Q3. 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) S.sid = 22 R.sid = 22 (S.sid = R.sid) R.bid = 101 (R.bid ≠ 103) ! R (instance of Reserves) Condition is false Do not output this entry.

  29. CPU Q3. 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) S.sid = 22 R.sid = 58 (S.sid ≠ R.sid) ! R (instance of Reserves) Condition is false Do not output this entry.

  30. CPU Q3. 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) S.sid = 31 R.sid = 22 (S.sid ≠ R.sid) ! R (instance of Reserves) Condition is false Do not output this entry.

  31. CPU Q3. 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) S.sid = 31 R.sid = 58 (S.sid ≠ R.sid) ! R (instance of Reserves) Condition is false Do not output this entry.

  32. CPU Q3. 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) S.sid = 58 R.sid = 22 (S.sid ≠ R.sid) ! R (instance of Reserves) Condition is false Do not output this entry.

  33. CPU Q3. 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) S.sid = 58 R.sid = 58 (S.sid = R.sid) ! R.bid = 103 (R.bid = 103) ! R (instance of Reserves) Condition is true Output this entry.

  34. CPU Q3. 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) End of Algorithm R (instance of Reserves)

  35. Summary of Q3: • 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,000 row operations! • This query can be decomposed into • an “e-join”, then “e-select”, “e-project” primitives

  36. RECAP: SQL Queries • We illustrated three SQL queries: • Q1: simple select (one table) • Q2: select with conditions (one table) • Q3: select requiring a JOIN operation (of multiple tables) • We know how they are “implemented” • Measure the number of “row operations” JOIN is expensive

More Related