資料庫ch3 — Introduction to SQL

(3–8~3–10)

set membership

in vs not in

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

查詢2009秋季＆2010春季的所有課程代碼

select distinct name
from instructor
where name not in ('Mozart', 'Einstein');

查詢所有教師姓名，但不包含Mozart和Einstein

set comparison

select distinct name
from instructor
where salary > some(select salary
                    from instructor
                    where dept_name='Biology');

查詢薪資高於「生物系最低薪資」的教師名字

select distinct name
from instructor
where salary > all(select salary
                    from instructor
                    where dept_name='Biology');

查詢薪資高於「生物系所有成員薪資」的教師名字

select dept_name
from instructor
group by dept_name
having avg(salary) >= all(select avg(salary)
                          from instructor
                          group by dept_name);

查詢擁有最高平均薪資的部門

Test for Empty Relations

select course_id
from section as S
where semester = 'Fall' and year=2017 and
      exists(select *
             from section as T
             where semester = 'Spring' and year = 2018 and
                   S.course_id = T.course_id);

查詢2017秋季＆2018春季都有開的課

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

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

查詢2009只開課一次的course_id

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

查詢2009開課不只一次的course_id

Subqueries in the From Clause

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

查詢哪些科系的平均薪資高於42000

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; <-只能用新定義的relation裡的屬性

查詢哪些科系的平均薪資高於42000

dept_avg：subquery的名字
dept_name：subquery的attribute
avg_salary：subquery的attribute

With Clause

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;

dept_total：各系的薪資總合
dept_total_avg：全校的薪資總合
查詢大於全校薪資總合的系所

Scalar Subquery

select dept_name,
       (select count(*)
        from instructor
        where department.dept_name = instructor.dept_name)
        as num_instructors
from department;

查詢各系總人數

⚠️Scalar Subquery用於回傳單一結果
若回傳多個值，則發生錯誤⚠️

Modification of the Database

deletion

delete from instructor;

刪除instructor裡面的所有資料，但這個relation仍存在於資料庫

delete from instructor
where dept_name = 'Finance';

刪掉財經系的老師

delete from instructor
where dept_name in (select dept_name
                    from department
                    where building = 'Watson');

刪除所有位在Waston大樓的科系的教師

insertion

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

一次新增一筆資料
這兩個的意思一樣，若未寫出來，就是按照relation裡attribute的順序
btw 未知的值可以用null表示

insert into instructor
       select ID, name, dept_name, 18000
       from student
       where dept_name = 'Music' and tot_cred > 144;

一次新增多筆資料

update

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

將薪資低於70000的老師加薪5%

update instructor
set salary = salary * 1.05
where salary < (select avg(salary)
                from instructor);

將薪資低於平均薪資的老師加薪5%

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

將薪資高於70000的老師加薪3%
將薪資低於10000的老師加薪5%
⚠️順序很重要，若上面兩個交換，可能有人加薪8%⚠️

update instructor
set salary = case
                 when salary<=10000 then salary * 1.05
                 else salary * 1.03
              end

跟上題一樣，只是語法不同

補充

select case
        when sum(credits) is not null then sum(credits)
        else 0
       end

如果不是null就正常
但如果是null就設成0

ch3重點複習

DDL & DML

create table(primary key, foreign key…references)

select, from(natural join), where(邏輯、比較運算子、between…and…)

as(改名字), distinct

(not) like, escape 搭配 % _

order by (asc, desc)

Union(all), Intersect(all), Except(all)

null

avg, min, max, sum, count

group by, having

(not) in, (not) exists, (not unique)
some(), all()

sub query
with(暫存), scalar subquery