This preview shows pages 1–11. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Unformatted text preview: SQL Subqueries T. M. Murali September 1, 2010 T. M. Murali September 1, 2010 CS 4604: SQL Subqueries Linear Notation for Relational Algebra I Relational algebra expressions can become very long. I Use linear notation to store results of intemediate expressions. 1. A relation name and a parenthesised list of attributes for that relation. Use Answer as the conventional name for the final result. 2. The assignment symbol := . 3. Any expression in relational algebra on the right. T. M. Murali September 1, 2010 CS 4604: SQL Subqueries Example of Linear Notation I Name pairs of students who live at the same address. I Normal expression: π S1.Name , S2.Name ( σ S1.Address = S2.Address ( ρ S1 ( Students ) × ρ S2 ( Students ))) T. M. Murali September 1, 2010 CS 4604: SQL Subqueries Example of Linear Notation I Name pairs of students who live at the same address. I Normal expression: π S1.Name , S2.Name ( σ S1.Address = S2.Address ( ρ S1 ( Students ) × ρ S2 ( Students ))) I Linear notation: Pairs(P1, N1, A1, P2, N2, A2) := ρ S1 ( Students ) × ρ S2 ( Students ) Matched(P1, N1, A1, P2, N2, A2) := σ A1 = A2 ( Pairs(P1, N1, A1, P2, N2, A2) ) Answer(Name1, Name2) := π N1 , N2 ( Matched(P1, N1, A1, P2, N2, A2) ). T. M. Murali September 1, 2010 CS 4604: SQL Subqueries Interpreting Queries Involving Multiple Relations SELECT A, B FROM R, S WHERE C; T. M. Murali September 1, 2010 CS 4604: SQL Subqueries Interpreting Queries Involving Multiple Relations SELECT A, B FROM R, S WHERE C; I Nested loops: for each tuple t 1 in R for each tuple t 2 in S if the attributes in t 1 and t 2 satisfy C output the tuples involving attributes A and B . T. M. Murali September 1, 2010 CS 4604: SQL Subqueries Interpreting Queries Involving Multiple Relations SELECT A, B FROM R, S WHERE C; I Nested loops: for each tuple t 1 in R for each tuple t 2 in S if the attributes in t 1 and t 2 satisfy C output the tuples involving attributes A and B . I Conversion to relational algebra: π A , B ( σ C ( R × S )). 1. Compute R × S . 2. Apply the selection operator σ C () to R × S . 3. Project the resulting tuples to attributes A and B . T. M. Murali September 1, 2010 CS 4604: SQL Subqueries Motivation for Subqueries I Find the name of the professor who teaches“CS 4604.” T. M. Murali September 1, 2010 CS 4604: SQL Subqueries Motivation for Subqueries I Find the name of the professor who teaches“CS 4604.” SELECT Name FROM Professors, Teach WHERE (PID = ProfessorPID) AND (Number = ’4604’) AND (DeptName = ’CS’) ; T. M. Murali September 1, 2010 CS 4604: SQL Subqueries Motivation for Subqueries I Find the name of the professor who teaches“CS 4604.” SELECT Name FROM Professors, Teach WHERE (PID = ProfessorPID) AND (Number = ’4604’) AND (DeptName = ’CS’) ; I Do we need to take the natural join of two big relations just to get a relation with one tuple?...
View
Full
Document
This note was uploaded on 12/24/2011 for the course CS 4604 taught by Professor Cmmalik during the Fall '08 term at Virginia Tech.
 Fall '08
 CMMALIK

Click to edit the document details