(3–4~3–7)
Additional Basic Operations
The Rename Operation
//可以出現在select
select name as n
from instructor;//可出現在from
select T.name, S.course_id
from instructor as T, teaches as S <-特別稱呼為tuple variable
where T.ID = S.ID;//可同時出現
select T.name as n, S.course_id
from instructor as T, teaches as S
where T.ID = S.ID;
select 舊的名字 as 新的名字
幫attribute或relation取名字 ！不會改變原本relation的attributes名！
原因：在select做計算後 沒有attribute名稱 or 簡潔有力 不用寫那麼長
常用時機：自己跟自己natural join時
題：查詢收入超過「生物系中收入最低的人」的名字
答：
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';String Operation
select dept_name
from department
where building like '%Waston%';%：表示任何含有0個或多個的字元
_：表示任何一個字元
需搭配 like 或 not like 來使用
like表示符合 not like表示不符合
like '100\%' escape '\' ：代表要剛剛好是100%，而不是100開頭的
like 'ab\\cd%' escape '\' ：代表ab\cd開頭的字串escape表示跳脫字元
在escape(\)後面的是字元
不是有意義的String Operation
⚠️ SQL中要表示字串 一定要加單引號 ⚠️
select
select *
from instructor;*：代表所有attributes
select instructor.*
from instructor, teaches
where instructor.ID = teaches.ID;代表只印出instructor的所有attributes
Ordering the Display of Tuple
select name
from instructor
where dept_name = 'Physics'
order by name;order by：把資料排序(預設是升冪)
select *
from instructor
order by salary desc, name asc;asc：升冪
desc：降冪
也可以order by 多個attributes
⚠️寫前面的先比 若前面的一樣 才比後面⚠️
where
select name
from instructor
where salary between 90000 and 100000;//where salary>90000 and salary<100000;
是一樣的意思
只是between and比較簡潔
也有not between and的用法
select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology')//where instructor.ID = teaches.ID and dept_name = 'Biology';
超帥的用法 兩者意思相同
Set Operations
Union(或)
(select course_id
from section
where semester='Fall' and year=2009)
union all
(select course_id
from section
where semester='Spring' and year=2010);union：會自動消去重複的值
union all：會保留重複的值 有幾個就印幾個
Intersect(且)
(select course_id
from section
where semester='Fall' and year=2009)
intersect all
(select course_id
from section
where semester='Spring' and year=2010);intersect：會自動消去重複的值
intersect all：會保留重複的值 有幾個就印幾個
except(除外)
(select course_id
from section
where semester='Fall' and year=2009)
except all
(select course_id
from section
where semester='Spring' and year=2010)except：會自動消去重複的值
except all：會保留重複的值 有幾個就印幾個
A except B：在A不在B
小測驗
A = {p, p, q}, B={p, r}
題：
(1)A union all B
(2)A union B
(3)A intersect all B
(4)A except all B
(5)B except all B
答：
(1) {p, p, p, q, r}
(2) {p, q, r}
(3) {p}
(4) {p, q}
(5) {r}
Null Values
operations
null代表不知道、不確定
跟任何數值做運算的結果均為null(無意義)
//and
true and null //null
false and null //false
null and null //null//or
true or null //true
false or null //null
null or null //null//not
not null //null
可以做Boolean operations
select name
from instructor
where salary is null;可以是is null 也可以是is not null
小測驗
select C
from T1
where A = 'α' and B = 'β';答：{7}
select C
from T1
where A = 'α' or B = 'β';答：{8, 7}
Aggregate Functions
基本aggregate
avg：平均
min：最小
max：最大
sum：總和
count：個數
select avg(salary) as avg_salary
from instructor
where dept_name = 'Comp.Sci'若沒有as，資料庫會隨便給一個名稱
select count (distinct ID)
from teaches
where semester = 'Spring' and year = 2010;若要消除重複值，要自己加distinct
select count(*)
from course;count(*)用來計算資料總量（有幾個tuple）
group by
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name;group by：依設定之attribute，來進行分組
select dept_name, count(distinct ID) as instr_count
from instructor natural join teaches
where semester = 'Spring' and year= 2018
group by dept_name;⚠️在select中，唯一能沒有aggregate functions的attribute
必須出現在group by中，不然會錯⚠️
having
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary)>42000;having後面可加aggregate functions
where後面則不可
但兩者都是用於篩選資料
select course_id, semester, year, sec_id, avg(tot_cred)
from takes natural join student
where year = 2017
group by course_id, sec_id, semester, year
having count(ID)>= 2;
