数据库第一次作业

数据库第一次作业

3.1 使用大学模式，用SQL写出如下查询。

a.找出Comp.Sci系开设的具有三个学分的课程名称。

	select title
 	  from course
	 where dept_name = 'Comp. Sci.' and course.credits = 3;

b. 找出名叫Einstein的教师所教的所有学生的标识，保证结果中没有重复。

	select distinct takes.id
  	  from instructor natural join teaches 
	       join takes using (course_id,sec_id,semester,year)
 	 where name = 'Einstein'; 

c. 找出教师的最高工资

	select max(salary) 
	  from instructor

d. 找出工资最高的所有教师

	select name 
	from   instructor
	where salary=(select Max(salary) from instructor)

e.找出2009年秋季开设的每个课程段的选课人数

	select course_id, sec_id, count(ID) 
	from   section natural join takes 
	where semester = 'Fall' and year = 2009 
	group by course_id, sec_id

f.从2009年秋季开设的每个课程段中，找出最多的选课人数

	select Max(cnt) 
	from ( 
		select Count(ID) as cnt
		from   section natural join takes 
		where semester = 'Fall' and year = 2009 group by course_id, sec_id 
	)

g.找出在2009年秋季拥有最多选课人数的课程段。

	with Fall2009 as (
	  	select course_id, sec_id, count(ID) as cnt
	  	from   section natural join takes 
	 	where  semester = 'Fall' and year = 2009 
	  	group  by course_id, sec_id
	)

	select course_id,sec_id
	from   Fall2009
	where cnt  = (select max(cnt) from Fall2009)