1 / 30

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”.

teo
Download Presentation

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. Sailors ( sid: integer, sname: string, rating: integer, age: real ) Reserves ( sid: integer, bid: integer, day: date ) An instance R of Reserves An instance S of Sailors

  3. 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!

  4. 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.

  5. 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.

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

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

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

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

  10. 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

  11. 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. • This query is also called a “projection” • It is the same as the “e-project” primitive • It simply selected a subset of the columns

  12. 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!

  13. 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.

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. Summary of Q2: • Result of SQL query • is another table • row-inclusion is determined by where-clause. • A simple analysis shows • This takes (n) row operations; where n is size (the number of records) in table S. • This query can be decomposed into • an “e-select”, followed by an “e-project” primitives

  21. IMPT: This specifies how S and Rare 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.

  22. 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 O(nm) row operations where n = size of table S, and m = size of table R. R (instance of Reserves)

  23. 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.

  24. 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.

  25. 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.

  26. 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.

  27. 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.

  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 = 58 R.sid = 58 (S.sid = R.sid) ! R.bid = 103 (R.bid = 103) ! R (instance of Reserves) Condition is true 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) End of Algorithm R (instance of Reserves)

  30. Summary of Q3: • Result of SQL query requires • information from two tables • a JOIN operation is necessary • A simple analysis shows • 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. • Joins are EXPENSIVE operations. • This query can be decomposed into • an “e-join”, then “e-select”, “e-project” primitives

More Related