concept of sql

Outline

 Join Expressions
 Views
 Transactions
 Integrity Constraints
 SQL Data Types and Schemas
 Index Definition in SQL
Chapter 4 : Intermediate SQL  Authorization




Database System Concepts, 7th Ed.


LICET, CSE 4.2 ©Silberschatz, Korth, Sudarshan and GK




Joined Relations Natural Join in SQL

 Join operations take two relations and return as a result another  Natural join matches tuples with the same values for all common
attributes, and retains only one copy of each common column.
relation.
 A join operation is a Cartesian product which requires that tuples in the  List the names of instructors along with the course ID of the courses that
they taught
two relations match (under some condition). It also specifies the
attributes that are present in the result of the join • select name, course_id
from students, takes
 The join operations are typically used as subquery expressions in the
where student.ID = takes.ID;
from clause
 Three types of joins:  Same query in SQL with “natural join” construct
• Natural join • select name, course_id
from student natural join takes;
• Inner join
• Outer join




LICET, CSE 4.3 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 4.4 ©Silberschatz, Korth, Sudarshan and GK

, Natural Join in SQL (Cont.) Student Relation

 The from clause can have multiple relations combined using natural join:
select A1, A2, … An
from r1 natural join r2 natural join .. natural join rn
where P ;




LICET, CSE 4.5 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 4.6 ©Silberschatz, Korth, Sudarshan and GK




Takes Relation student natural join takes




LICET, CSE 4.7 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 4.8 ©Silberschatz, Korth, Sudarshan and GK

, Dangerous in Natural Join Natural Join with Using Clause

 Beware of unrelated attributes with same name which get equated  To avoid the danger of equating attributes erroneously, we can use the
incorrectly “using” construct that allows us to specify exactly which columns should be
 Example -- List the names of students instructors along with the titles of equated.
courses that they have taken  Query example
• Correct version select name, title
select name, title from (student natural join takes) join course using (course_id)
from student natural join takes, course
where takes.course_id = course.course_id;
• Incorrect version
select name, title
from student natural join takes natural join course;
 This query omits all (student name, course title) pairs where the
student takes a course in a department other than the student's
own department.
 The correct version (above), correctly outputs such pairs.




LICET, CSE 4.9 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 4.10 ©Silberschatz, Korth, Sudarshan and GK




Join Condition Join Condition (Cont.)

 The on condition allows a general predicate over the relations being  The on condition allows a general predicate over the relations being
joined joined.
 This predicate is written like a where clause predicate except for the use  This predicate is written like a where clause predicate except for the use
of the keyword on of the keyword on.
 Query example  Query example
select * select *
from student join takes on student_ID = takes_ID from student join takes on student_ID = takes_ID
• The on condition above specifies that a tuple from student matches a • The on condition above specifies that a tuple from student matches a
tuple from takes if their ID values are equal. tuple from takes if their ID values are equal.
 Equivalent to:  Equivalent to:
select * select *
from student , takes from student , takes
where student_ID = takes_ID where student_ID = takes_ID




LICET, CSE 4.11 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 4.12 ©Silberschatz, Korth, Sudarshan and GK