实验七 带函数查询和综合查询（2）

1 检索至少选修课程“数据结构”和“C语言”的学生学号
方法一：
select Stu_id
from StudentGrade,Course
where Course.Course_id=StudentGrade.Course_id and
Course_name=‘数据结构’ and
Stu_id in (select Stu_id
from StudentGrade,Course
where Course.Course_id=StudentGrade.Course_id and
Course_name=‘C语言’)

方法二：
select Stu_id
from StudentGrade,Course
where Course.Course_id=StudentGrade.Course_id and
Course_name in(‘数据结构’,‘C语言’)
group by stu_id
having count(*)>=2/以学号分组，该学号选修满足条件的课程数>=2/

2 列出所有班名、班主任、班长、系名。
（请使用连接查询；进一步考虑使用外连接，因为很多班级可能是没有班长的，考虑需要显示所有班级的信息）
select class_name,director,monitor,depar_name
from class left join deparment on (deparment.depar_id=class.depar_id)

3．没有选修以“01”开头的课程的学生学号，姓名，选课的课程号。（用子查询完成，提示not in或not exists。需考虑没选课的学生，仔细对比实验六第5题）

方法一
select student.stu_id,stu_name,course_id
from student,studentgrade
where studentgrade.stu_id not in(select stu_id
from studentgrade
where course_id like ‘01%’)
and student.stu_id=studentgrade.stu_id
union
/没选课的学生信息/
select student.stu_id,stu_name,null
from student where stu_id not in(select stu_id from studentgrade)

/没有选修以“01”开头的课程的学生信息。/
select stu_id,stu_name
from student
where stu_id not in(select stu_id
from studentgrade
where course_id like ‘01%’)
方法二
select student.stu_id,stu_name,course_id
from student,studentgrade
where not exists(select *
from studentgrade
where course_id like ‘01%’ and stu_id=student.stu_id)
and student.stu_id=studentgrade.stu_id
union
/没选课的学生信息/
select student.stu_id,stu_name,null
from student where not exists(select * from studentgrade where stu_id=student.stu_id)

方法三 错误，不以01又以01开头的出现在结果中
select student.stu_id,stu_name,course_id
from student,studentgrade
where exists(select *
from studentgrade
where course_id not like ‘01%’ and stu_id=student.stu_id)
and student.stu_id=studentgrade.stu_id

4.统计各门课程的选修人数，并按人数降序排列，找出排名前三位的课程。（提示：可以使用TOP 3）

select top 3 course_id,count()
from studentgrade
group by course_id
order by count() desc

/distinct top 3 course_id,count() 为什么不能解决并列情况？/
select distinct top 3 course_id,count()
from studentgrade
group by course_id
order by count(*) desc

5.统计各门课程的选修人数，并按人数降序排列，找出排名前三位的课程。
（上述使用TOP 3，却不能处理人数并列的情况。试考虑一种方法能处理人数并列的情况。）
select course_id,count()
from studentgrade
group by course_id
having count() in(select distinct top 3 count()
from studentgrade
group by course_id
order by count() desc)
order by count(*) desc

6.检索选修了‘0103’和‘0105’两门课程，并且
‘0103’这门课程成绩高于‘0105’的学生的学号。
select A.stu_id /select A.stu_id,A.course_id,B.course_id,A.grade,B.grade/
from studentgrade A,studentgrade B
where A.stu_id=B.stu_id and A.course_id=‘0103’ and B.course_id=‘0105’
and A.grade>B.grade

7.检索选修了课程“数据结构”和“C语言”两门课程并且
“数据结构”分数高于“C语言”的学生学号和姓名。
select A.stu_id /*select A.stu_id,A.course_id,B.course_id,A.grade,B.grade */
from Studentgrade A,Studentgrade B
where A.stu_id=B.stu_id
and A.course_id in(select course_id from course where course_name=‘数据结构’)
and B.course_id in(select course_id from course where course_name=‘c语言’)
and A.grade>B.grade