The from Clause The from clause lists the relations involved in the query Corresponds to the Cartesian product operation of the relational algebra. Find the Cartesian product instructor X teaches select from instructor,teaches generates every possible instructor-teaches pair,with all attributes from both relations. For common attributes(e.g.,ID),the attributes in the resulting table are renamed using the relation name (e.g.,instructor.ID) Cartesian product not very useful directly,but useful combined with where-clause condition (selection operation in relational algebra). Database System Concepts-6th Edition 3.17 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.17 ©Silberschatz, Korth and Sudarshan th Edition The from Clause The from clause lists the relations involved in the query Corresponds to the Cartesian product operation of the relational algebra. Find the Cartesian product instructor X teaches select from instructor, teaches generates every possible instructor – teaches pair, with all attributes from both relations. For common attributes (e.g., ID), the attributes in the resulting table are renamed using the relation name (e.g., instructor.ID) Cartesian product not very useful directly, but useful combined with where-clause condition (selection operation in relational algebra)
Cartesian Product instructor teaches ID dept name salary ID name course id sec id semester year 10101 Srinivasan Comp.Sci. 65000 10101 CS-101 Fall 2009 12121 Wu Finance 90000 10101 CS-315 1 Spring 2010 15151 Mozart Music 40000 10101 CS-347 1 Fall 2009 22222 Einstein Physics 95000 12121 FIN-201 1 Spring 2010 32343 El Said History 60000 15151 MU-199 1 Spring 2010 22222 PHY-101 Fall 2009 Inst.ID name dept_name salary teaches.ID course id sec id semester year 10101 Srinivasan Comp.Sci. 65000 10101 CS-101 1 Fall 2009 10101 Srinivasan Comp.Sci. 65000 10101 CS-315 1 Spring 2010 10101 Srinivasan Comp.Sci 65000 10101 CS-347 1 Fall 2009 10101 Srinivasan Comp.Sci. 65000 12121 FIN-201 1 Spring 2010 10101 Srinivasan Comp.Sci. 65000 15151 MU-199 1 Spring 2010 10101 Srinivasan Comp.Sci 65000 22222 PHY-101 1 Fall 2009 4 444 *+ t+ 44 12121 Wu Finance 90000 10101 CS-101 1 Fall 2009 12121 Wu Finance 90000 10101 CS-315 1 Spring 2010 12121 Wu Pinance 90000 10101 CS-347 1 Fall 2009 12121 Wu Pinance 90000 12121 FIN-201 1 Spring 2010 12121 Wu Finance 90000 15151 MU-199 1 Spring 2010 12121 Wu Pinance 90000 22222 PHY-101 1 Fall 2009 Database System Concepts-6th Edition 3.18 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.18 ©Silberschatz, Korth and Sudarshan th Edition Cartesian Product instructor teaches
Examples Find the names of all instructors who have taught some course and the course id select name,course id from instructor,teaches where instructor.ID teaches.ID Find the names of all instructors in the Art department who have taught some course and the course id select name,course id from instructor,teaches where instructor.ID teaches.ID and instructor.dept name ='Art' Database System Concepts-6th Edition 3.19 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.19 ©Silberschatz, Korth and Sudarshan th Edition Examples Find the names of all instructors who have taught some course and the course_id select name, course_id from instructor , teaches where instructor.ID = teaches.ID Find the names of all instructors in the Art department who have taught some course and the course_id select name, course_id from instructor , teaches where instructor.ID = teaches.ID and instructor. dept_name = ‘Art’
The Rename Operation The SQL allows renaming relations and attributes using the as clause: old-name as new-name Find the names of all instructors who have a higher salary than some instructor in 'Comp.Sci'. select distinct T.name from instructor as T.instructor as S where T.salary S.salary and S.dept_name 'Comp.Sci.' Keyword as is optional and may be omitted instructor as T instructor T Database System Concepts-6th Edition 3.20 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 6 3.20 ©Silberschatz, Korth and Sudarshan th Edition The Rename Operation The SQL allows renaming relations and attributes using the as clause: old-name as new-name Find the names of all instructors who have a higher salary than some instructor in ‘Comp. Sci’. select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept_name = ‘Comp. Sci.’ Keyword as is optional and may be omitted instructor as T ≡ instructor T
Self Join Example Relation emp-super person supervisor Bob Alice Mary Susan Alice David David Mary Find the supervisor of“Bob” Find the supervisor of the supervisor of“Bob” Find ALL the supervisors(direct and indirect)of "Bob Database System Concepts-6th Edition 3.21 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.21 ©Silberschatz, Korth and Sudarshan th Edition Self Join Example Relation emp-super Find the supervisor of “Bob” Find the supervisor of the supervisor of “Bob” Find ALL the supervisors (direct and indirect) of “Bob person supervisor Bob Alice Mary Susan Alice David David Mary