Class notes
concept of sql
- Course
- Institution
- Book
it gives a detail of sql
[Show more]
Connected book
Book Title:
Author(s):
- Edition:
- ISBN:
- Edition:
More summaries for
-
Solution Manual for Database System Concepts 7th Edition Abraham Silberschatz, Henry F. Korth, S. Sudarshan
-
Complete Solution Manual Database System Concepts 7th Edition Silberschatz Questions & Answers with rationales
-
Solution Manual for Database System Concepts 7th Edition Silberschatz / All Chapters 1 - 26 / Full Complete 2023
Seller
Follow
sathishelumalaie67
Content preview
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
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller sathishelumalaie67. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $2.99. You're not tied to anything after your purchase.
Can Stuvia be trusted?
4.6 stars on Google & Trustpilot (+1000 reviews)
80796 documents were sold in the last 30 days
Founded in 2010, the go-to place to buy study notes for 14 years now