【2022.3.31】数据库系统概念第七版第三章

---

一、题目

二、使用步骤

3.1

a.

Select course_id 
From course
where course.dept_name='Comp. Sci.' and credits='3'

b.

select distinct student.ID
From (student inner join takes on student.ID=takes.ID) join (instructor inner join teaches on instructor.ID=teaches.ID)
on (takes.course_id=teaches.course_id and takes.semester=teaches.semester and takes.year=teaches.year and takes.sec_id=teaches.sec_id)
where instructor.name='Einstein'

不能用自然连接真的蚌埠住辣

c.

select max(salary)
From instructor

d.

select ID
From instructor
where salary>=all(select salary
from instructor
)

e.

select section.course_id,count(distinct ID) as num
from takes  join section on takes.course_id=section.course_id
where section.year='2009' and section.semester='Fall'
group by section.course_id

f.

with tmp as (select section.course_id,section.sec_id,count(distinct ID) as nums
      from takes ,section
      where takes.course_id=section.course_id and takes.sec_id=section.sec_id and section.semester=takes.semester and takes.year=section.year and section.year='2009' and section.semester='Fall'
      group by section.course_id,section.sec_id)
select max(nums)
from tmp

g.

with tmp as (
select section.course_id, section.sec_id, count(ID) as enrollment
from section,takes
 where takes.course_id=section.course_id and takes.sec_id=section.sec_id and section.semester=takes.semester and takes.year=section.year and section.year='2009' and section.semester='Fall'
group by section.course_id,section.sec_id)
select course_id, sec_id
from tmp
where enrollment >= all(select enrollment from tmp)

总结

写了半天发现写的不是作业
难绷