Intermediate SQL

Join Conditions :

The on condition allows a general predicate over the relations being joined.

This predicate is written like a where clause predicate except for the use of the keyword on rather than where. The on condition appears at the end of the join expression.

Q. Demonstrate the use of 'on' condition. [Hint: Student (ID, name, dept_name, tot_cred) and takes (ID, course_id, sec_id, semester, year, grade )

select *
from student join takes on student.ID = takes.ID;

The join expression using 'on' in this case is almost the same as the join expression student natural join takes, since the natural join operation.

The one difference is that the result has the ID attribute listed twice, in the join result, once for student and once for takes, even though their ID values must be the same.

An equivalent query without 'on' is

select *
from student, takes
where student.ID = takes.ID;

A version of this query that displays the ID value only once is as follows:

select student.ID as ID, name, dept_name, tot_cred,
course_id, sec_id, semester, year, grade
from student join takes on student.ID = takes.ID;

Outer Joins

Suppose we wish to display a list of all students, displaying their ID, and name, dept_name,and tot_cred, along with the courses that they have taken.

Natural join will not work in this case.

If some student exists who takes no courses. Then the tuple in the student relation for that particular student would not satisfy the condition of a natural join with any tuple in the takes relation, and that student’s data would not appear in the result.

To prevent this the outer join operation works in a manner similar to the join operations we have already studied, but preserve those tuples that would be lost in a join, by creating tuples in the result containing null values.

There are in fact three forms of outer join:

The left outer join preserves tuples only in the relation named before (to the left of) the left outer join operation.

The right outer join preserves tuples only in the relation named after (to the right of) the right outer join operation

The full outer join preserves tuples in both relations.

Left Outer Join

We can compute the left outer-join operation as follows.

compute the result of the inner join as before

Then, for every tuple 't' in the left-hand-side relation that does not match any tuple in the right-hand-side relation in the inner join, add the tuple 't' to the result of the join and fill all other cells with null.

select *
from student natural left outer join takes;

Q. Find all students who have not taken a course [Hint: Student (ID, name, dept_name, tot_cred) and takes (ID, course_id, sec_id, semester, year, grade )

select ID
from student natural left outer join takes
where course_id is null;

Right Outer Join

Tuples from the right hand-side relation that do not match any tuple in the left-hand-side relation are padded with nulls and are added to the result of the right outer join.

select *
from takes natural right outer join student;

Q. “Find all students who have not taken a course"

select ID
from student natural left outer join takes
where course_id is null;

Full outer join

The full outer join is a combination of the left and right outer-join types.

After the operation computes the result of the inner join, it extends with nulls those tuples from the left-hand-side relation that did not match with any from the right-hand side relation, and adds them to the result.

Similarly, it extends with nulls those tuples from the right-hand-side relation that did not match with any tuples from the left-hand-side relation and adds them to the result.

Q. Display a list of all students in the Comp. Sci. department, along with the course sections, if any, that they have taken in Spring 2009; all course sections fromSpring 2009 must be displayed, even if no student from the Comp. Sci. department has taken the course section. [Hint: Student (ID, name, dept_name, tot_cred) and takes (ID, course_id, sec_id, semester, year, grade )

select * from (select * from student where dept_name = ’Comp.Sci’) natural full outer join (select * from takes where semester = ’Spring’ and year = 2009);

Views

It is not desirable for all users to see the entire logical model

It is possible to compute and store the results of the a few queries and then make the stored relations available to users.

However, if we did so, and the underlying data in the relations changes, the stored query results would then no longer match the result of reexecuting the query on the relations.

Instead, of pre-computing and storing query results SQL allows a “virtual relation” to be deﬁned by a query, and the relation conceptually contains the result of the query.

The virtual relation is not precomputed and stored, but instead is computed by executing the query whenever the virtual relation is used.

Any such relation that is not part of the logical model, but is made visible to a user as a virtual relation, is called a view.

It is possible to support a large number of views on top of any given set of actual relations.

The database system stores the query expression associated with the view relation. Whenever the view relation is accessed, its tuples are created by computing the query result. Thus, the view relation is created whenever needed, on demand.

View Deﬁnition

We deﬁne a view in SQL by using the create view command.

The form of the create view command is:

```
create view v as <query expression>
```

where <query expression> is any legal query expression. The view name is represented by 'v'.

Q. Create a view for a clerk who should not be allowed to access instructors salary [Hint : instructor (ID, name, dept_name, salary)

create view clerk as
select ID, name, dept name
from instructor;

Q. To create a view that lists all course sections offered by the Physics department in the Fall 2009 semester with the building and room number of each section. [Hint: section(course_id, sec_id, semester, year, building, room_number, time_slot_id) ] and course(course_id, title, dept_name, credits) ]

create view physics_fall_2009 as select course.course_id, sec_id, building, room_number from course, section where course.course_id = section.course_id and course.dept_name = ’Physics’ and section.semester = ’Fall’ and section.year = ’2009’;

Q. Using the view physics_fall_2009 given above, ﬁnd all Physics courses offered in the Fall 2009 semester in the Churchill building

select course_id
from physics_fall_2009
where building = ’Churchill’;

Q. Create a view that gives for each department the sum of the salaries of all the instructors at that department.

create view departments_total_salary(dept_name, total_salary) as
select dept_name, sum (salary)
from instructor
group by dept_name;

Q. Deﬁne a view physics_fall_2009_watson that lists the course_ID and room_number of all Physics courses offered in the Fall 2009 semester in the Churchill building using the physics_fall_2009 and without it:

Query using the physics_fall_2009:

create view physics_fall_2009_watson as
select course_id, room_number
from physics_fall_2009
where building = ’Churchill’;

Query not using the physics_fall_2009:

create view physics_fall_2009_watson as
       (select course_id, room_number
        from (select course.course_id, building, room_number
              from course, section
              where course.course_id = section.course_id
                    and course.dept_name = ’Physics’
                    and section.semester = ’Fall’
                    and section.year = ’2009’)
       where building= ’Churchill’;

upscfever.com