Chapter 4: Intermediate SQL

Database System Concepts, 6th Ed.
©Silberschatz, Korth and Sudarshan
See www.db­book.com for conditions on re­use

Chapter 4: Intermediate SQL
 Join Expressions
 Views
 Transactions
 Integrity Constraints
 SQL Data Types and Schemas
 Authorization

Database System Concepts ­ 6th Edition

4.2

©Silberschatz, Korth and Sudarshan

Joined Relations
 Join operations take two relations and return as a result

another relation.

 A join operation is a Cartesian product which requires that

tuples in the two relations match (under some condition).
It also specifies the attributes that are present in the result of the join

 The join operations are typically used as subquery

expressions in the from clause

Database System Concepts ­ 6th Edition

4.3

©Silberschatz, Korth and Sudarshan

Join operations – Example
 Relation course

 Relation prereq

 Observe that prereq information is missing for CS­315 and course information is missing for CS­437
Database System Concepts ­ 6th Edition

4.4

©Silberschatz, Korth and Sudarshan

Outer Join
 An extension of the join operation that avoids loss of

information.

 Computes the join and then adds tuples form one relation

that does not match tuples in the other relation to the result of the join.

 Uses null values.

Database System Concepts ­ 6th Edition

4.5

©Silberschatz, Korth and Sudarshan

Left Outer Join
 course natural left outer join prereq

Database System Concepts ­ 6th Edition

4.6

©Silberschatz, Korth and Sudarshan

Right Outer Join
 course natural right outer join prereq

Database System Concepts ­ 6th Edition

4.7

©Silberschatz, Korth and Sudarshan

Joined Relations
 Join operations take two relations and return as a result

another relation.

 These additional operations are typically used as subquery

expressions in the from clause

 Join condition – defines which tuples in the two relations

match, and what attributes are present in the result of the join.

 Join type – defines how tuples in each relation that do not

match any tuple in the other relation (based on the join condition) are treated.

Database System Concepts ­ 6th Edition

4.8

©Silberschatz, Korth and Sudarshan

Full Outer Join
 course natural full outer join prereq

Database System Concepts ­ 6th Edition

4.9

©Silberschatz, Korth and Sudarshan

Joined Relations – Examples
 course inner join prereq on

course.course_id = prereq.course_id

 What is the difference between the above, and a natural join?
 course left outer join prereq on course.course_id = prereq.course_id

Database System Concepts ­ 6th Edition

4.10

©Silberschatz, Korth and Sudarshan

Joined Relations – Examples
 course natural right outer join prereq

 course full outer join prereq using (course_id)

Database System Concepts ­ 6th Edition

4.11

©Silberschatz, Korth and Sudarshan

Views
 In some cases, it is not desirable for all users to see the

entire logical model (that is, all the actual relations stored in the database.)

 Consider a person who needs to know an instructors name

and department, but not the salary. This person should see a relation described, in SQL, by select ID, name, dept_name from instructor

 A view provides a mechanism to hide certain data from the

view of certain users.

 Any relation that is not of the conceptual model but is made

visible to a user as a “virtual relation” is called a view.

Database System Concepts ­ 6th Edition

4.12

©Silberschatz, Korth and Sudarshan

View Definition
 A view is defined using the create view statement which has

the form

create view v as < query expression > where <query expression> is any legal SQL expression. The view name is represented by v.
 Once a view is defined, the view name can be used to refer to

the virtual relation that the view generates.

 View definition is not the same as creating a new