Lecture 05 Intro to SQL (Continued)

Null Values：

It is possible for tuples to have a null value, denoted by null, for some of their attributes.

null signifies an unknown value or that a value does not exist.

The result of any arithmetic expression involving null is null

The predicate is null can be used to check for null values.

 

Null Values and Three Valued Logic：

Three values – true, false, unknown

Any comparison with null returns unknown

Three-valued logic using the value unknown:

OR: (unknown or true) = true,

(unknown or false) = unknown,

(unknown or unknown) = unknown

AND: (true and unknown) = unknown,

(false and unknown) = false,

(unknown and unknown) = unknown

NOT: (not unknown) = unknown

Result of where clause predicate is treated as false if it evaluates to unknown

 

For Example,

user(id, name, password)

INSERT into user values (1, ‘Alex’, ‘xxxx’);

INSERT into user values (2, ‘Alice’, ‘yyyy’);

INSERT into user values (555, null, null);

If we want to select users whose name is not Alex:

SELECT *
FROM user
WHERE name != ‘Alex’

This will include the null situation.

 

Aggregate Functions:

 

These functions operate on the multiset of values of a column of a relation, and return a value

avg: average value
min: minimum value

max: maximum value 

sum: sum of values 

count: number of values

Remember that there are no spaces after aggregate functions!

 

 

Find the average salary of instructors in each department

select dept_name, avg (salary) as avg_salary from instructor
group by dept_name;

 

Attributes in select clause outside of aggregate functions must appear in group by list

/* erroneous query */
select dept_name, ID, avg (salary) from instructor
group by dept_name

ID here is illegal

 

Find the names and average salaries of all departments whose average salary is greater than 42000

select dept_name, avg (salary) from instructor
group by dept_name
having avg (salary) > 42000;

 

Note: predicates in the having clause are applied after the formation of groups whereas in the where predicates clause are applied before forming groups.

Nested Subqueries:

SQL provides a mechanism for the nesting of subqueries. A subquery is a select-from-where expression that is nested within another query.

The nesting can be done in the following SQL query

select A1, A2, ..., An 

from r1, r2, ..., rm 

where P

as follows:

Ai can be replaced be a subquery that generates a single value.

ri can be replaced by any valid subquery

P can be replaced with an expression of the form: B <operation> (subquery)

Where B is an attribute and <operation> to be defined later.



Subqueries in the Where Clause:

 

A common use of subqueries is to perform tests:

For set membership

For set comparisons

For set cardinality.

Find the total number of (distinct) students who have taken course sections taught by the instructor with ID 10101

select count (distinct ID)
from takes
where (course_id, sec_id, semester, year) in 

(select course_id, sec_id, semester, year

from teaches where teaches.ID= 10101);

 

Set Comparison – “some” Clause:

select distinct T.name
from instructor as T, instructor as S 

where T.salary > S.salary and S.dept name = ’Biology’;

 

Same query using > some clause

select name
from instructor
where salary > some (select salary 

from instructor

from where dept name = ’Biology’);

 

Set Comparison – “all” Clause:

Find the names of all instructors whose salary is greater than the salary of all instructors in the Biology department.

 

select name
from instructor
where salary > all (select salary

from instructor

from where dept name = ’Biology’);

 

 

Test for Empty Relations:

The exists construct returns the value true if the argument subquery is nonempty.

exists r <=> r ≠ Ø

not exists r <=>r = Ø



Use of “exists” Clause:

Yet another way of specifying the query “Find all courses taught in both the Fall 2009 semester and in the Spring 2010 semester”:

select course_id
from section as S
where semester = ’Fall’ and year = 2009 and

exists (select *
from section as T

where semester = ’Spring’ and year= 2010 and S.course_id = T.course_id);

 

Use of “not exists” Clause:

 

select distinct S.ID, S.name

from student as S

where not exists courses that are offered in

Biology but not taken by the student ( (select

course_id

from course
where dept_name =

’Biology’)

except
(select T.course_id

from takes as T where S.ID = T.ID));

 

 

Test for Absence of Duplicate Tuples:

The unique construct tests whether a subquery has any duplicate tuples in its result.

The unique construct evaluates to “true” if a given subquery contains no duplicates.

Find all courses that were offered at most once in 2009

select T.course_id
from course as T
where unique (select R.course_id

from section as R
where T.course_id= R.course_id

and R.year = 2009);

 

Subqueries in the Form Clause:

SQL allows a subquery expression to be used in the from clause

select dept_name, avg_salary

from the list of dept. with their avg_salary

(select dept_name, avg (salary) as avg_salary from instructor

group by dept_name)

where avg_salary > 42000;

 

Another way to write above query

select dept_name, avg_salary
from (select dept_name, avg (salary)

from instructor
group by dept_name) as dept_avg (dept_name, avg_salary)

where avg_salary > 42000;

 

With Clause：

The with clause provides a way of defining a temporary relation whose definition is available only to the query in which the with clause occurs.

with max_budget (value) as (select max(budget)

from department)
select department.name
from department, max_budget
where department.budget = max_budget.value;

 

Subqueries in the Select Clause：

Scalar subquery is one which is used where a single value is expected

select dept_name, (select count(*)

from instructor
where department.dept_name = instructor.dept_name)

as num_instructors 

from department;

 

Modification of the Database：

Deletion of tuples from a given relation.
Insertion of new tuples into a given relation

Updating of values in some tuples in a given relation

 

Deletion：

#Delete all instructors

delete from instructor

#Delete all instructors from the Finance department

delete from instructor
where dept_name= ’Finance’;

#Delete all tuples in the instructor relation for those instructors associated with a department located in the Watson building.

delete from instructor

where dept name in (select dept name

from department
where building = ’Watson’);

 

Insertion：

#Add a new tuple to course 

insert into course

values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

 

#or equivalently

insert into course (course_id, title, dept_name, credits) 

values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

 

Updates:

Increase salaries of instructors whose salary is over $100,000 by 3%, and all others by a 5%

Write two update statements:

update instructor
set salary = salary * 1.03 where salary > 100000;

update instructor
set salary = salary * 1.05 where salary <= 100000;

The order is important!

Same query as before but with case statement

update instructor 

set salary = case

when salary <= 100000 then salary * 1.05

else salary * 1.03 end

 

Updates with Scalar Subqueries：

update student S
set tot_cred = (select sum(credits)

from takes, course
where takes.course_id = course.course_id and

S.ID= takes.ID.and
takes.grade <> ’F’ and

takes.grade is not null);