mid-term _Review

复习题

week4(大学数据库)

第七题: Find the sections that had the maximum enrollment in Fall 2009.

--写法一
select sec_id
from takes
where semester = 'Fall' and year = 2009
group by sec_id
having count(ID) >= all (select count(ID)
                    from takes
                    where semester = 'Fall' and year = 2009
                    group by sec_id);
--写法二
select sec_id
from takes
where semester = 'Fall' and year = 2009
group by sec_id
having count(ID) = (select max(count)
                    from takes
                    where semester = 'Fall' and year = 2009
                    group by count(distinct sec_id));

集合比较

• some:至少有一个满足

  • =some 等价于in
  • <>some 不等价于not in

• all:所有都要满足

  • =all 不等价于in

    • 当子句是标量子查询(scalar subquery),相当于一般等号
    • 当子句返回集合,=all()的结果集为NULL
  • <>all 等价于not in

处理与最值有关的问题

• 使用>=all()或<=all()进行内外查询的比较
• 内查询使用max()/min()找出最值,外查询使用=比对

week5

第六题:Find the name and the SSN of everyone who works on all projects that his deparment is responsible for them.

select name, SSN
from employee as T
where not exists
(select pno
from project natural join department
where T.dno == department.dno)
except
(select pno
from (employee natural join hourlog) as S 
where T.ssn = S.ssn);

集合包含问题:A:某人从事的项目,B:其部门负责的项目,要求B包含于A->B-A=Ø

关系代数

%5Cdiv%5CPi%7Bpno%7D(project%20%5Cbowtie%20department))%5Cbowtie%20employee)%0A#card=math&code=%5CPi%7Bname%2C%20ssn%7D%28%28%5CPi%7Bssn%2Cpno%7D%28employee%20%5Cbowtie%20hourlog%29%5Cdiv%5CPi%7Bpno%7D%28project%20%5Cbowtie%20department%29%29%5Cbowtie%20employee%29%0A)

含义:参考链接:https://www.cnblogs.com/yuanqi/p/4589967.html, R÷S表示R中每个X对应像集Y包含S中像集Y的所有值,本题RS÷S表示每个X,Y对应像集Z(x,y)中包含S中像集Y的所有值,

week6(大学数据库)

1(b):Delete all courses that have never been offered (that is, do not occur in the section relation).

delete from course
where course_id not in 
(select course_id
from course natural join section);

关于集合成员资格:

参考链接:https://blog.csdn.net/baidu_37107022/article/details/77278381

• in/not in:先进行内查询,将结果集合与外表连接从而判断资格

  • 适合外表大,内表小的情况
  • 不适合内表记录有NULL情况,当内表结果有NULL时,内查询不返回记录
• exists/not exists:对外表元组循环,带入内表查询

1(f):Enroll every student in the Comp. Sci. department in the section in Fall 2009, with sec_id of 1.

insert into takes
(select id, 'CS-001' as course_id,  '1' as sec_id, 'Fall' as semester, '2009' as year
from student 
where dept_name = 'Comp.Sci.');

两种insert

• insert into table values(...):适合手动填充数据
• insert into table select (...):适合填充查询后数据

1(i):Delete all takes tuples corresponding to any section of any course with the word “database” as a part of the title; ignore case when matching the word with the title.

delete from takes
where course_id in (select course_id
                    from course
                    where title like "%database%" or title like "Database");

模糊查找:like

• postgreSQL使用ilike进行不区分大小写的模糊查找

• 字符串处理:

  • "intro%":以intro开头
  • "%body%":字符串中含有body
  • "---":字符串长度为3
  • "---%":字符串长度至少为3

week7

1(1):查询书名中有“%”的书目信息

select *
from title
where name like "%\%%";

sql使用"\"作为转义符号

1(11):查询借阅图书本数超过2本的读者的读者号及借阅本数。

select memno, count(distinct book_id)
from member join book on borrowermemno = memno
group by memno
having count(distinct book_id) > 2;

关系代数

%3E2%7D(%5Csigma%7Bborrowermemno%20%3D%20memno%7D(member%20%5Ctimes%20book%20))%0A#card=math&code=%7Bmemno%2C%7D%5Cmathcal%7BG%7D%7Bcount%5C_disinct%28book_id%29%3E2%7D%28%5Csigma%7Bborrowermemno%20%3D%20memno%7D%28member%20%5Ctimes%20book%20%29%29%0A)

或者theta连接

%3E2%7D(member%20%5Cbowtie%7Bborrowermemno%20%3D%20memno%7D%20book%20)%0A#card=math&code=%7Bmemno%2C%7D%5Cmathcal%7BG%7D%7Bcount%5C_disinct%28book_id%29%3E2%7D%28member%20%5Cbowtie%7Bborrowermemno%20%3D%20memno%7D%20book%20%29%0A)

2(1):按借阅图书本数，从高到低列出读者的姓名及其借阅本数。

select fname, count(callnumber)
from member join book on borrowermemno = memno
group by memno
order by count(callnumber) desc

结果排序:

• 降序:order by ... desc
• 升序:order by ... asc

2(2):查询借阅了123号读者和124号读者所借所有书目的读者的编号及姓名。

select memno, fname
from member as M
where not exists
(select memno
from member join book on borrowermemno = memno
where memno = 123 or memno = 124)
except
(select memno
from (member join book on borrowermemno = memno) as T
where M.memno = T.memno);

集合包含问题:A:123和124所有所有图书,B:某人所借所有图书,要求A包含于B,A-B=Ø

or的使用:前后需要为完整的boolean值,不能使用where memno = 123 or 124或where memno = (123 or 124)

2(4):124号读者续借124号图书，请将预约归还日期延后一个月。

update book
set borrowerduedate = borrowduedate + interval '1 Month'
where book_id = 124;

postgreSQL时间间隔:使用interval关键字,可使用hour,month,minute等作为单位,参考链接:https://blog.csdn.net/zacry/article/details/42742509

2(5):读者归还了124号图书，请修改其状态。

update book
set borrowermemno = null, borrowduedate = null
where book_id = 124;