中级sql查询语句的几个小例子

所用到的数据库表在前一篇博客中体现，在此不再赘述。

Using the university schema that you have write the following queries. In some cases you might need to insert extra data to show the effect of a particular feature.Recommendation: With clause is strongly recommended for simplifying the query.

1. Find the sections which have the minimum enrollment among sections registered by
students. For each section as such, information displayed should involve:
（1） Identifier of section(i.e. the primary key for section)
（2） Course name corresponding to the section
（3） Enrollment of the section('enrollment' is the alias for the number of students who
registered for the section)

（4） TOP keyword in SQL Server is denied.

with secStu(sCount,title,cour_id,sec_id,semester,year) as  --建立虚表，计算每个时段的课程选修的学生人数
(select COUNT(takes.ID) sCount,course.title,section.course_id,section.sec_id,section.semester,section.year
from takes 
full join section
on takes.course_id=section.course_id
and takes.sec_id=section.sec_id
and takes.semester=section.semester
and takes.year=section.year
left join course
on section.course_id=course.course_id
group by section.course_id,section.sec_id,section.semester,section.year,course.title)
select *
from secStu
where sCount <= all (select sCount 
		    from secStu)      --选出选修人数最少的时段的课程

2. USE aggregation on outer join to construct the following query.For all students, list the registration information of the students. The students who havenever 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:
（1） Identifier of student(i.e. the primary key for student)
（2） Name of the student
（3） 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 courseregistrations is 1 and the number of section registrations is 2).
（4） Number of section registrations

（5） TOP keyword in SQL Server is denied.

with stuSec (ID,name,course_id,sec_id,semester,year) as  --建立虚表，将学生信息与选课信息相连
(select student.ID,name,takes.course_id,takes.sec_id,takes.semester,takes.year
from student 
left join takes
on student.ID=takes.ID)
select ID,name,count(distinct course_id) courseCount,count(course_id) sectionCount --计算学生选修的课程数和时段课程数
from stuSec
group by ID,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:
（1） Identifier of instructor(i.e. the primary key for instructor)
（2） Name of the instructor
（3） Average salary of the department for which the instructor works
（4） Sum of credit points taught by the instructor(for example, if instructor A has taughtcourse A (2 credit points) twice, course B(3 credit points) once, then the sum of credit points taught by instructor A is 7)

with depSal(dept_name,sal) as 
(select dept_name,avg(salary)   --建立虚表，计算每个部门的老师的平均工资
 from instructor
 group by dept_name)
select instructor.ID,instructor.name,sal,sum(course.credits) sumCredits
from instructor
left join teaches
on instructor.ID=teaches.ID
left join depSal
on instructor.dept_name=depSal.dept_name
left join course
on teaches.course_id=course.course_id
group by instructor.ID,instructor.name,depSal.sal
having count(teaches.course_id) >= 2  --教授课程超过2

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:
（1） Identifier of student(i.e. the primary key for student)
（2） Name of the student
（3） Number of courses, taught by instructors of department '拳脚学院', registered by the student

with T(student_name,total_course)as  --修过拳脚学院课的学生
	(select student.name,COUNT(course.course_id)
	from student join takes on student.ID=takes.ID
		join course on takes.course_id=course.course_id 
	where course.dept_name='拳脚学院'
	group by student.name)
select ID,name,total_course
from student join T on student.name=T.student_name 
where not exists(
			(select student_name --修过全部拳脚学院课的学生
			from T 
			where T.total_course=( select COUNT(course_id)
						from course 
						where course.dept_name='拳脚学院'))
			except
			(select student_name  --修过拳脚学院课的学生

                      from T) )

注意：

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

--5
with T(student_name,total_course)as --修过拳脚学院课的学生
	(select student.name,COUNT(course.course_id)
	from student join takes on student.ID=takes.ID
		join course on takes.course_id=course.course_id 
	where course.dept_name='拳脚学院'
	group by student.name)
select ID,name,total_course
from student join T on student.name=T.student_name 
where ( select COUNT(course_id)             --拳脚学院开设的所有课程数
		from course 
		where course.dept_name='拳脚学院')
    >
    (select total_course --修过拳脚学院课的学生//只有陈近南
    from T t
    where t.student_name=T.student_name)