34,575
社区成员
发帖
与我相关
我的任务
分享
select m.Sno , sum(n.Ccredit) Ccredit from SC m, Course n
where m.Cno = n.Cno
group by m.Sno
having sum(n.Ccredit) >= 6
2)查询总分最高的学生的学号。
--如果总分最高的只有一个。
select top 1 Sno , sum(Grade) Grade from SC group by Sno order by Grade desc
--如果总分最高的同时不止一个。
select Sno , sum(Grade) Grade from SC group by Sno having sum(Grade) =
(select top 1 sum(Grade) Grade from SC group by Sno order by Grade desc)
(3)查询总学分已超过6学分的学生学号、总学分。
--假设sc表有一字段xf ,记录学分
select sno , sum(xf) xf from sc group by Sno having sum(xf) >= 6
(4)求各门课程的课程名及相应的选课人数。
--如果所有课程在sc表中都有人选择.
select m.Cname 课程名, count(1) 选课人数 from Course m, SC n where m.Cno = n.Cno group by m.Cname
--如果有课程在sc表中没有人选择.
select m.Cname 课程名, count(n.Cno) 选课人数 from Course m left join SC n on m.Cno = n.Cno group by m.Cname
(5)统计有学生选修的课程门数。
select Sno , count(1) [课程门数] from sc group by Sno
(6)求选修1号课程的学生的平均年龄。
select avg(m.Sage) from Student m, sc n where m.Sno = n.Sno and n.Cno = '1'
select cast(avg(m.Sage*1.0) as decimal(18,2)) from Student m, sc n where m.Sno = n.Sno and n.Cno = '1'
(7)统计每门课程的学生选修人数(超过2人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。
select cno 课程号, count(1) 选修人数 from sc group by cno having count(1) >= 2 order by 选修人数 desc , cno
(2)查询总分最高的学生的学号。
select g.Sno from SC g,Course c
where g.Cno=c.Cno
and Grade=(select max(Grade) from SC where Cno=c.Cno)
(3)查询总学分已超过6学分的学生学号、总学分。
怎么才算过 60?
select Sno, sum(c.Ccredit) 总学分
from SC g,Course c
where g.Cno=c.Cno and g.Grade>=60
group by g.Sno
having sum(c.Ccredit)>6
(4)求各门课程的课程名及相应的选课人数。
select c.Cname,count(g.Sno) 选课人数
from SC g,Course c
where g.Cno=c.Cno and g.Grade>=60
group by c.Cname
(5)统计有学生选修的课程门数。
select count(1) 课程门数 from SC group by Cno
(6)求选修1号课程的学生的平均年龄。
select avg(s.Sage) 平均年龄
from SC g,Course c,Student s
where g.Cno=c.Cno and s.Sno=g.Sno
and c.Cno='0001'
(7)统计每门课程的学生选修人数(超过2人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。
select c.Cno,count(g.Sno) 选修人数
from SC g,Course c
where g.Cno=c.Cno
group by c.Cno
having count(g.Sno)>2
order by count(g.Sno) desc,c.Cno asc
(2)查询总分最高的学生的学号。
select top 1 s.sno,sum(grade) as g
from student s
join sc on s.sno=sc.sno
group by s.sno
order by g
(3)查询总学分已超过6学分的学生学号、总学分。
select top 1 s.sno,sum(Ccredit) as g
from s join sc on s.sno=sc.sno
join course c on sc.cno=sc.cno
group by s.sno
order by g
(4)求各门课程的课程名及相应的选课人数。
select c.* ,(select count (*) from sc where sc.cno=c.cno)
from course c
(5)统计有学生选修的课程门数。
select count(distinct cno)
from Course c where exists(select 1 from sc where c.cno=sc.cno)
(6)求选修1号课程的学生的平均年龄。
select avg(s.sage)
from student s
join sc on s.sno=sc.sno and sc.cno='1'
(7)统计每门课程的学生选修人数(超过2人的课程才统计)。
要求输出课程号和选修人数,查询结果按人数降序排列,
若人数相同,按课程号升序排列。
select cno,count(*) as [人数] from sc
group by cno having count(*)>2
(2)查询总分最高的学生的学号。
select top 1 s.sno,sum(grade) as g
from student s
join sc on s.sno=sc.sno
group by s.sno
order by g
(3)查询总学分已超过6学分的学生学号、总学分。
select top 1 s.sno,sum(Ccredit) as g
from s join sc on s.sno=sc.sno
join course c on sc.cno=sc.cno
group by s.sno
order by g
(4)求各门课程的课程名及相应的选课人数。
select c.* ,(select count (*) from sc where sc.cno=c.cno)
from course c