The where Clause The where clause specifies conditions that the result must satisfy Corresponds to the selection predicate of the relational algebra. To find all instructors in Comp.Sci.dept select name from instructor where dept name Comp.Sci.' SQL allows the use of the logical connectives and,or,and not o The operands of the logical connectives can be expressions involving the comparison operators <<=>>==and <> Comparisons can be applied to results of arithmetic expressions To find all instructors in Comp.Sci.dept with salary 70000 select name name from instructor where dept_name Comp.Sci.'and salary>70000 Katz Brandt Database System Concepts-7th Edition 3.17 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 3.17 ©Silberschatz, Korth and Sudarshan th Edition The where Clause ▪ The where clause specifies conditions that the result must satisfy • Corresponds to the selection predicate of the relational algebra. ▪ To find all instructors in Comp. Sci. dept select name from instructor where dept_name = 'Comp. Sci.' ▪ SQL allows the use of the logical connectives and, or, and not ▪ The operands of the logical connectives can be expressions involving the comparison operators <, <=, >, >=, =, and <>. ▪ Comparisons can be applied to results of arithmetic expressions ▪ To find all instructors in Comp. Sci. dept with salary > 70000 select name from instructor where dept_name = 'Comp. Sci.' and salary > 70000
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 instructorX teaches select from instructor,teaches generates every possible instructor-teaches pair,with all attributes from both relations. For common attributes (e.g.,/D),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-7th Edition 3.18 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 3.18 ©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)
Examples Find the names of all instructors who have name course_id taught some course and the course id Srinivasan CS-101 select name,course id Srinivasan CS-315 from instructor,teaches Srinivasan CS-347 where instructor.ID=teaches.ID Wu FN-201 Mozart MU-199 Find the names of all instructors in the Art Einstein PHY-101 department who have taught some course El Said HIS-351 and the course id Katz CS-101 Katz CS-319 select name,course id Crick BIO-101 from instructor,teaches Crick BIO-301 where instructor.ID teaches.ID Brandt CS-190 and instructor.dept_name='Art' Brandt CS-190 Brandt CS-319 Kim EE-181 Database System Concepts-7th Edition 3.19 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 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 instructoras T,instructor as S where T.salary>S.salary and S.dept_name 'Comp.Sci.' Keyword as is optional and may be omitted instructoras T=instructor T Database System Concepts-7th Edition 3.20 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 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” Can you find ALL the supervisors(direct and indirect)of"Bob"? Database System Concepts-7th Edition 3.21 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 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” ▪ Can you find ALL the supervisors (direct and indirect) of “Bob”?