数据库概念 SQL（二）第四课笔记

2018年3月13日
11:11

写在前面

本笔记仅仅是本人在上课时的一些随手记录，并不完整也不完全正确。
如有错误，请在评论中或直接联系我指正，谢谢！

原始文件下载：(mht)(pdf)

Main contents of SQL

Data definition
Basic query structure
Set operations
Aggregate fuctions
Null values
Nested subqueries
Complex queries
Views
Modification of the database

Nested Subqueries

SQL提供了子查询的嵌套机制
子查询 (subquery)是一个 select-from where
表达式，它嵌套在另一个查询语句中

子查询的通常使用场合是对集合成员测试，集合比较和得出集合基数

Example Query

Find courses offered in Fall 2009 and in Spring 2010

select distinct course_id
from section
where semester = 'Fall' and year = 2009 and course_id in
        (select course_id
        from section
        where semester = 'Spring' and year = 2010);

Find courses offered in Fall 2009 but not in Spring 2010

select distinct course_id
from section
where semester = 'Fall' and year = 2009 and course_id not
in
        (select course_id
        from section
        where semester = 'Spring' and year = 2010);

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);

注意：上述查询语句可以大幅简化。上述式子仅仅是为了表现SQL的特性而已。
Set Comparison
Find names of instructors with salary greater than that of some (at least one) instrutor in the Biology department
1
2
3
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
        where dept_name = 'Biology');

Definition of Some Clause

=some, <>some comparisons
Definition of all Clause
=all, <>all comparisons

Test for Empty Relations

The exists construct returns the value true if the argument subquery is nonempty
exists r ⇔ r ≠ Ø

not exists r ⇔ r = Ø

Application:
Note that X – Y = Ø ⇔ X ⊆ Y
not exists (X except Y) ⇔ X ⊆ Y
Correlation Variables
E.g. Yet another way of specifying the query “Find all courses taught in both the Fall 2009 semester and in the Spring 2010 semester”
1
2
3
4
5
6
7
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);

Correlated subquery

Correlation name or correlation variable

Not Exists

e.g. Find all students who have taken all courses offered in the Biology department

select distinct S.ID, S.name,
from student as S,
where not exists ((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.

(Evaluates to “true” on an empty set)

e.g. 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 From Clause

SQL允许在From语句中使用子查询表达式
e.g. Find the average instructors’ salaries of those departments where the average salary is greater than $42,000
1
2
3
4
5
select dept_name, avg_salary
from (select dept_name, avg (salary) as avg_salary,
from instructor,
group by dept_name)
where avg_salary \> 42000;

注意：这里我们不需要使用having语句

上述查询的另一种表述方式：

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;

还有一种：

select name, salary, avg_salary
from instructor I1,
         lateral (select avg(salary) as avg salary,
                  from instructor I2,
                  where I2.dept_name = I1.dept_name);
```    
-   Lateral语句允许from语句后面部分（在**lateral**关键词后）获取前面部分的关联变量
    -   注意：Lateral语句是SQL标准的一部分，但是在许多DBS中是不被支持的；一些DB，例如SQL Server，提供了一些替代语法
 
#### *With Clause*
-   with语句提供了定义临时view的方法 这个定义只有在有with语句的查询语句才会生效
>   **e.g. Find all departments with the maximum budget**
>   ```SQL
with max_budget(value) as
        (select max(budget)
        from department)
select budget
from department, max_budget
where department.budget = max_budget.value;

Complex Queries

Complex Queries using With Clause

with语句在写复杂查询语句时十分有用

with语句被大多数DBS所支持，当然会有一些语法上的区别

e.g. Find all departments where the total salary is greater than the average of the total salary at all departments

with dept_total(dept_name, value)as
        (select dept_name, sum(salary)
        from instructor
        group by dept_name),
        dept_total_avg(value) as
        (select avg(value)
        from dept_total)
>    
select dept_name,
from dept_total, dept_total_avg
where dept_total.value >= dept_total_avg.value

Scalar Subquery

标量子查询是对单值的查询

e.g.

select dept_name,
            (select count(\*),
            from instructor,
            where department.dept_name = instructor.dept_name)
            as num_instructors
from department;
>   
select name,
from instructor,
where salary*10 >
            (select budget from department
            where department.dept_name = instructor.dept_name)

Modification of the Database

Deletion

Delete all instructors
1
delete from instructor
Delete all instructors from the Finance department
1
2
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.
1
2
3
4
delete from instructor,
where dept_name in (select dept_name
                              from department
                              where building = 'Watson');
Delete all instructors whose salary is less than the average salary of instructors
1
2
delete from instructor
where salary< (select avg (salary) from instructor);

问题：如果我们从deposit中删除元组，那么平均工资的值是会变化的

在SQL中的解决方案：

首先算出平均工资，并找到所有要删除的元组
之后删除之前找到的元组（并不会重新计算或重新测试元组）

Insertion

Add a new tuple to course

1 2	insert into course values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);

Or equivalently

1 2	insert into course (course_id, title, dept_name, credits) values ('CS-437', 'Database Systems', 'Comp. Sci.', 4)

Add a new tuple to student with tot_creds set to null

1 2	insert into student values ('3003', 'Green', 'Finance', null);

Add all instructors to the student relation with tot_creds set to 0

1
2
3

insert into student
            select ID, name, dept_name, 0
            from instructor

Select from where 语句会在它导致的任何结果插入这个关系之前计算结果
不然的话例如
1
insert into table1 select * from table1

如果table1没有定义任何主码，那就会出现问题

Updates

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

Write two update statements

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

顺序十分重要
上述语句可以使用case语句更好实现
Case Statement for Conditional Updates

之前语句的case版本

update instructor
        set salary = case
            when salary <= 100000 then salary * 1.05
            else salary * 1.03
        end
```    
#### *Updates with Scalar Subqueries*
>   -   **Recompute and update tot_creds value for all students**
>   ```SQL
update student S
            set tot_cred = (select sum(credits)
                            from takes natural join course
                            where S.ID = takes.ID and
                                takes.grade \<\> 'F' and
                                takes.grade is not null);

Sets tot_creds to null for students who have not taken any course
除了sum(credits)之外，可以使用：
case
      when sum(credits) is not null then sum(credits)
      else 0
end