数据库作业（3）数据查询（自用复习）

1. SQL查询：统计各门课程选修人数，要求输出课程代号，课程名，有成绩人数（grade不为NULL），最高分，最低分，平均分，按课程号排序。

#统计各门课程选修人数，要求输出课程代号，课程名，有成绩人数（grade不为NULL），最高分，最低分，平均分(取整round函数)，按课程号排序。

select sc.cno,cname,count(*),max(grade),min(grade),round(avg(grade))
from sc,course
where grade is not null and sc.cno = course.cno
group by sc.cno
order by sc.cno

2. SQL查询：查询同时选修了c01,c02,c03课程学生，输出学号，姓名

#查询同时选修了c01,c02,c03课程学生，输出学号，姓名

select sc.sno,sname
from student,sc
where sc.sno = student.sno and cno in('c01','c02','c03')
group by sc.sno
having count(distinct cno)=3

3. SQL查询：查询没有同时选修“计算机导论”和“计算机网络”两门课的学生的学号，姓名

#查询没有同时选修“计算机导论”和“计算机网络”两门课的学生的学号，姓名

-- 不存在这两门课都选修的人
select sno,sname from student
where sno not in(SELECT sno from sc,course
                                  where sc.cno=course.cno and cname='计算机导论'
                                           and sno in(SELECT sno from sc,course
                                                               where sc.cno=course.cno and cname='计算机网络'))

4. SQL查询：查询选修了全部课程的学生的学号，姓名，系名

#查询选修了全部课程的学生的学号，姓名，系名

SELECT sno,sname,sdept 
from student -- 不存在没有选修的课程
where not EXISTS (SELECT * from  course where not EXISTS(SELECT * from sc where course.cno = sc.cno and sc.sno = student.sno))

5. SQL查询：查询与“王大力”同一个系的学生的基本信息

#查询与“王大力”同一个系的学生的基本信息。

select * from student
where sdept in (select sdept from student where sname = '王大力')
 and sname!='王大力'

6. 查询同时选修了“数据库基础”和“计算机网络”两门课的学生的学号，姓名。

#查询同时选修了“数据库基础”和“计算机网络”两门课的学生的学号，姓名

select sno,sname
from student
where sno in(select sno from sc,course
                     where sc.cno = course.cno  and cname='数据库基础' 
                                 and sno in(select sno from sc,course
                                                 where sc.cno = course.cno  and cname='计算机网络' ))

7.    查询选修通过2门（包括2门）以上的学生的学号及姓名。    

select sc.sno,sname 
from student,sc
where sc.sno = student.sno and sc.grade>=60
group by sc.sno 
having count(cno)>=2

8.    查询选修未通过2门（包括2门）以上的学生的信息，输出学号、姓名、选修未通过门数，按门数降序排序，若门数相同，按学号升序排序。    

select sc.sno,sname,count(cno)
from student,sc
where sc.sno = student.sno and sc.grade<60
group by sc.sno 
having count(cno)>=2
order by count(cno) desc,sc.sno asc

 

9.    查询选修平均分为60分(包括60分)以上的学生的各门课成绩，要求输出学号，姓名，课程名和成绩。    

select sc.sno,sname,cname,grade
from student,sc,course
where student.sno = sc.sno and sc.cno = course.cno 
group by sc.sno
having avg(grade)>=60
order by sc.sno

10.    查询所有人都选修了的课程号与课程名    

select cno,cname 
from course 
where not exists (select * from student  where not exists
                                  (select * from sc where student.sno=sc.sno and  sc.cno = course.cno ))

11.    查询选课门数最多的学生的学号和姓名    

select sc.sno,sname
from student,course,sc
where student.sno = sc.sno and course.cno =sc.cno
group by sc.sno
having count(sc.cno)>=all(select count(DISTINCT sc.cno)as t1 from sc,course where course.cno=sc.cno group by sno)

12.    查询选修了学号为9521102同学选修的全部课程的学生学号和姓名  

select distinct sc.sno,sname
from student,sc,course,(select cno from sc where sc.sno=9521102 group by cno) as g1
where g1.cno = course.cno and student.sno = sc.sno 

13.    查询超过该课程平均分的成绩信息    

select sno,cno,grade
from sc g1
where grade >(select avg(grade) from sc g2 where g1.cno = g2.cno)

14.    SQL查询：查询统计学生的不及格门数    

查询统计学生不及格门数大于等于2门的信息，输出系名，学号，姓名，不及格门数，按照系（升序）排序，不及格门数（降序）排序。

select sdept,sc.sno,sname,count(cno)
from student,sc
where grade<60 and student.sno = sc.sno
group by sc.sno
having count(cno)>=2
order by sdept,count(cno) desc;

15.    查询选修了c03课程的学生学号和姓名    

select sc.sno,sname
from student,sc,course
where course.cno = sc.cno and sc.sno = student.sno and sc.cno ='c03'

16.    找出至今没有人选修过的课程

select cno,cname
from course
where not exists(select * from  sc,student where student.sno = sc.sno and sc.cno = course.cno)

17.    查询每个学生成绩最高的成绩信息    

SELECT k.sno,k.cno,grade
from sc as k,course,student 
where student.sno = k.sno and k.cno = course.cno and grade in(SELECT max(grade) from sc where sno = k.sno group by sno)

18.    查询每个男生的选课门数（要求用嵌套查询实现）

-- 每个男生的选课
-- 没有选课的男生
-- 没有选课的不是男生
select sno,count(cno)
from sc
where not exists(select * from course where course.cno = sc.cno 
								and not exists(select * from student where ssex='男' and student.sno = sc.sno ))
group by sno