Sql 练习题 （三）

--（1）查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。

select s.Sname 名字 , c.Cno 课程号 , c.Grade 成绩 
from Student s join Sc c on s.Sno = c.Sno
where s.dept='计算机系';

--（2）查询“信息管理系”修了“计算机文化学” 的学生姓名和成绩。

select s.Sname 学生姓名 , c.Grade 成绩 
from student s join sc c on s.Sno=c.Sno 
where s.Sno in 
  (select Sno from SC where Cno in
      (select Cno from Course where Cname='计算机文化学')) and c.Cno=(select Cno from Course where Cname='计算机文化学');

--（3）查询所有选修了Java课程的学生情况，列出学生姓名和所在系。

select s.Sname 姓名 , s.Dept 所在系 from Student s  
where s.Sno in
   (select Sno from sc where Cno=
        (select Cno from Course where Cname='java'));

--（4）统计每个系的学生的考试平均成绩。

select dept 系名,AVG(grade) 考试平均成绩 
from Student s join SC c on s.Sno=c.Sno 
group by s.Dept;

--（5）统计计算机系学生每门课程的选课人数、平均成绩、最高成绩和最低成绩。

select cno,COUNT(*) 选课人数,AVG(grade) 平均成绩 , MAX(grade) 最高成绩 , MIN(grade) 最低成绩 
from student s join SC  on s.Sno = sc.Sno 
group by dept ,cno 
having Dept='计算机系';

--（6）查询与刘晨在同一个系学习的学生的姓名和所在的系。

select sname 学生姓名 , dept 所在系 from student 
where Dept =
          (select Dept from student where Sname='刘晨') and Sname!='刘晨';

--（7）查询与“数据结构”在同一个学期开设的课程的课程名和开课学期

select Cname 课程名 , Semester 开课日期 from Course 
where Semester=
      (select Semester from Course where Cname ='数据结构') and Cname != '数据结构'; 

--（8）查询至少被两个学生选的课程的课程号。

select Cno 课程号 from sc group by Cno having COUNT(*)>=2;

--（9）查询全体学生的选课情况，包括选修了课程的学生和没有选修课程的学生。

select s.Sno 学号 , s.Sname 姓名 , s.Ssex 性别 , s.Sage 年龄 , s.dept ,c.Cno 课程号 , c.grade 成绩 
from student s left join sc c on s.sno=c.sno;

--（10）查询没人选的课程的课程名。

select Cname from Course where Cno not in (select Cno from SC );

--（11）查询计算机系没有选课的学生，列出学生姓名和性别。

select sname 姓名 , Ssex 性别 
from student 
where Dept ='计算机系' and Sno not in (select Sno from SC );

--（12）统计计算机系每个学生的选课门数，包括没有选课的学生。

select COUNT(c.sno) 选课门数 
from student s left join SC c on s.sno=c.sno 
group by s.Sno ,s.dept 
having  Dept='计算机系';

--（13）查询信息管理系选课门数少于3门的学生的学号和选课门数，包括没有选课的学生。查询结果按选课门数递增排序。

select  s.sno 学号,COUNT(c.sno) 选课门数 
from student s left join SC c on s.Sno=c.Sno 
group by s.sno,s.Dept 
having s.Dept='信息管理系' and count(s.sno)<3 
order by COUNT(s.sno); 

--（14）查询考试成绩最高的三个成绩，列出学号、课程号和成绩。

select top 3 sno 学号, cno 课号 ,grade 成绩 from SC order by grade desc ;

--(15) 查询Java考试成绩最高的前三名的学生的姓名、所在系和Java考试成绩。

select top 3 s.sname 姓名 , dept 所在系 , grade 成绩 
from student s join SC c on s.Sno=c.Sno join Course co on c.Cno=co.Cno  
where cname='java' 
order by Grade desc;

--（16）查询选课人数最少的两门课程（不包括没有人选的课程），列出课程号和选课人数。

select top 2 Cno 课程号 ,COUNT(sno) 选课人数 from  SC   group by Cno order by COUNT(sno); 

--（17）查询计算机系选课门数超过2门的学生中，考试平均成绩最高的前2名（包括并列的情况）学生的学号、选课门数和平均成绩。

select top 2 c.sno 学生学号 , COUNT(cno) 选课门数 , AVG(grade) 平均成绩 
from student s join SC c on s.Sno =c.Sno 
where dept='计算机系' 
group by c.Sno 
having COUNT(cno)>2 
order by AVG(grade) desc;

--（18）将计算机系的学生信息保存到#ComputerStudent局部临时表中。

select sno,sname,ssex,sage,dept into #ComputerStudent from student where Dept='计算机系';

--（19）将选了Java课程的学生的学号及成绩存入永久表Java_Grade中。

select sno,grade into Java_Grade from Course co join SC c on co.Cno=c.Cno where Cname ='java';

--（20）统计每个学期开设的课程总门数，将结果保存到永久表Cno_Count表中。

select  semester 学期 , COUNT(*) 课程总门数 into Cno_Count from Course group by Semester;

--（21）利用例20题生成的新表，查询第2学期开设的课程名、学分和课程总门数。

select Cname 课程名 , Credit 学分 , cnt.课程总门数 
from Course c join Cno_Count cnt on c.Semester=cnt.学期 
where c.semester=2;