�ݺ�ߣ

Basic Structure of SQL Queries
Queries on a Single Relation
• SQL allows us to use the keyword all to specify explicitly
that duplicates are not removed
select all dept_name
from instructor;
• Since duplicate retention is the default, we shall not use
all in our examples.
• To ensure the elimination of duplicates in the results of
queries, use distinct whenever it is necessary.

• The select clause may also contain arithmetic
expressions involving the operators +, −, ∗, and / operating
on constants or attributes of tuples.
select ID, name, dept name, salary * 1.1
from instructor;
• returns a relation that is the same as the instructor
relation, except that the attribute salary is multiplied by
1.1

• The where clause allows us to select only those rows in
the result relation of the from clause that satisfy a
specified predicate.
• Consider the query “Find the names of all instructors in
the Computer Science department who have salary
greater than $70,000.”

select name
from instructor
where dept name = ’Comp. Sci.’ and salary > 70000;
• SQL allows the use of the logical connectives and, or, and
not in the whereclause.

• The operands of the logical connectives can be
expressions involvingthe comparison operators <, <=, >,
>=, =, and <>.
• SQL allows us to use thecomparison operators to
compare strings and arithmetic expressions, as well as
special types, such as date types.

Queries on Multiple Relation
• Retrieve the names of all instructors along with their
department names and department building name.
• To answer this query each tuple in the intructor relation
must be matched with the tuple in the department
relation whose dept_name matches dept_name value of
the instructor tuple.

select name,instructor.dept_name,building
from instructor,department
where instructor.dept_name=department.dept_name;

Result of the query

• cartesian product
select instructor.ID, name, dept_name, salary,
teaches.ID, course_id, sec_id, semester,year
from instructor,teaches
• Each tuple in instructor is combined with every tuple in
teaches even those that refer to a different instructor.

• cartesian product
where instructor.ID=teaches.ID;
• The result of the query is the instructors who have taught
some course.

cartesian product
• Find the instructor names and course identifiers for
instructors in the computer science department.
where instructor.ID=teaches.ID and
instructor.dept_name='Comp.sci'

The meaning of an SQL query can be understood as
1. Generate a catesian product of the relation listed in
the from clause.
2. Apply prdicates specified in the where clause on the
result of step 1.
3. For each tuple in the result of step 2 output the
attributes specified in the select clause.

Natural Join
• The natural join operation operates on two relations and
produces a relation as the result.
• It concatenates only those pairs of tuples with the same
value on those attributes that appear in the schemas of
both relations.

Natural Join
• Select all the attributes from the instructor and teaches
relation whose tuples in with the same value on the ID
attributes that appear same on both relations.
course_id, sec_id, semester, year
from instructor natural join teaches;

Result

Natural Join
• Select name and course_id from the instructor and
teaches relation whose tuples in with the same value on
the ID attributes that appear same on both relations.
select name, course_id
from instructor natural join teaches;

Natural Join
• A from clause in an SQL query can have multiple relations
combined using natural join
select A1,A2,...,An
from r1 natural join r2 natural join r3...natural join rm;
where P;

Natural Join
• List the names of instructors along with the titles of
courses that they teach.
select name,title
from instructor natural join teaches,course
where teaches.course_id=course.course_id;

Natural Join
• The natural join of instructor and teaches is computed
first, then a cartesian product of this result with course is
computed from which the where clause extracts only
those tuples where the course identifier from the join
result matches the course identifier from the course
relation.

Natural Join
• The following SQL query does not compute the same
result:
select name, title
from instructor natural join teaches natural
join course;

• To see why, the natural join of instructor and teaches
contains the attributes (ID, name, dept name, salary,
course id, sec id).
• while the course relation contains the attributes (course
id, title, dept name, credits).
• As a result, the natural join of these two would require
that the dept name attribute values from the two inputs
be the same.

• In addition to requiring that the course id values be the
same.
• This query would then omit all (instructor name, course
title) pairs where the instructorteaches a course in a
department other than the instructor’s own department.

select name, title
from (instructor natural join teaches) join course
using (course id);
• The operation join . . . using requires a list of attribute
names to be specified.
• Both inputs must have attributes with the specified
names.

�ݺ�ߣ

Introduction basic schema and SQL QUERIES

More Related Content

Introduction basic schema and SQL QUERIES