【Intermediate SQL-1 】五道较难的sql练习_大连理工软院数据库上机答案

破题累死我了，网上另外一份答案写的也不错

（链接：http://blog.csdn.net/cygeek_dut/article/details/13090743）

但是他有的地方显然写麻烦了，而且对于第五个问那份答案好像理解错了题意，我仔细研究了下，写了份原创的，如果改一下缩进和变量名什么的，应该比网上这份仁兄的答案短一点。

我还真头一次知道sql原来可以写的这么跳，写的如此花哨。。简直是秀操作。。。 orz

当然也有可能存在有问题的地方，欢迎指正交流~！

我这里直接贴代码了，题目数据库数据的构建之类的请参考上面提到的那份答案

第一题：

1. Find the sections which have the minimum enrollment among sections registered by students. For each section as such, information displayed should involve: 
l Identifier of section(i.e. the primary key for section) l Course name corresponding to the section l Enrollment of the section('enrollment' is the alias for the number of students who registered for the section) l TOP keyword in SQL Server is denied.  

with enrollment(course_id, sec_id, semester, year, enrollment) as(select takes.course_id,takes.sec_id,takes.semester,takes.year, COUNT(takes.course_id)from takesgroup bytakes.course_id, takes.sec_id,takes.semester, takes.year),min_enrollment(enrollment) as(select min(enrollment)from enrollment)select enrollment.course_id, enrollment.sec_id, enrollment.semester, enrollment.year, course.title,enrollment.enrollmentfrom course, enrollment, min_enrollmentwhere enrollment.enrollment = min_enrollment.enrollment and course.course_id = enrollment.course_id;

第二题：

2. USE aggregation on outer join to construct the following query 
For all students, list the registration information of the students. The students who have never registered for any courses should also be considered. In the case, the aggregative information of such students should be set to 0. For each student, information displayed should involve: 
l Identifier of student(i.e. the primary key for student) l Name of the student l Number of course registrations (Caution: Not the number of section registrations. E.g. , student A registered the course B twice in 2 sections, the number of course registrations is 1 and the number of section registrations is 2). l Number of section registrations l TOP keyword in SQL Server is denied. 

select student.ID, student.name, COUNT(distinct takes.course_id) as 'course注册数', COUNT(takes.course_id) as 'section注册数' from student left join takes on student.ID = takes.IDgroup by student.ID, student.name

第三题：

3. USE scalar subquery to construct the following query 
Find the information for the instructors who have taught more than 1 course (that is, he/she should have taught 2 distinct courses as least). For each instructor as such, information displayed should involve: 
l Identifier of instructor(i.e. the primary key for instructor) l Name of the instructor l Average salary of the department for which the instructor works l Sum of credit points taught by the instructor(for example, if instructor A  has taught course A (2 credit points) twice, course B(3 credit points) once, then the sum of credit points taught by instructor A is 7)  

--使用标量子查询with instructor_taught_num(ID, num) as(select teaches.ID, COUNT(distinct course_id)from teachesgroup by teaches.ID)select distinct ins1.ID,ins1.name,(select AVG(salary)from instructor ins2where ins1.dept_name = ins2.dept_namegroup by ins2.dept_name)as '所在学院平均工资',(select SUM(course.credits)from course, teacheswhere teaches.ID = ins1.ID andcourse.course_id = teaches.course_id)as '所授课总学分'from instructor ins1, instructor_taught_numwhere ins1.ID = instructor_taught_num.ID andinstructor_taught_num.num > 1;

第四题：

4. Find students who have registered for some but not all courses taught by instructors of department '拳脚学院'. Do this using the "not exists ... except ..." structure. For each student as such, information displayed should involve: 
l Identifier of student(i.e. the primary key for student) l Name of the student l Number of courses, taught by instructors of department '拳脚学院', registered by the student  

注意这道题说的是选了但又没全选。

select distinct student.ID, student.namefrom studentwhere student.ID in (select student.IDfrom student, takes, coursewhere student.ID = takes.ID andcourse.course_id = takes.course_id andcourse.dept_name = '拳脚学院') andstudent.ID not in(select S.IDfrom student Swhere not exists ((select course_idfrom coursewhere dept_name = '拳脚学院') except(select takes.course_idfrom takeswhere takes.course_id = student.ID)));

第五题：

5. As query requirement in Q4, Use matching of counts to fulfill the requirement. (don't forget the distinct clause!). 

据我理解，这题的意思是用比较数量的方法去做第四题，所以应该用 > < 之类的集合比较方法来做，也就是说用<来表示比总数小。

另外那份答案说使用having，我觉得有点迷。。。

with take_num(ID, name, num) as(select distinct S.ID, S.name, COUNT(distinct T.course_id)from student S, takes T, course Cwhere S.ID = T.ID andT.course_id = C.course_id andC.dept_name = '拳脚学院'group by S.ID, S.name)select *from take_numwhere num < (select COUNT(*)from coursewhere course.dept_name = '拳脚学院')