(1)查询学生的基本信息;
select * from S;
(2)查询“CS”系学生的基本信息;select * from S where Sdept =’CS’; (3)查询“CS”系学生年龄不在19到21之间的学生的学号、姓名; select Sno, Sname from S
(4)找出最大年龄;select max(Sage) from S; (5)找出“CS”系年龄最大的学生,显示其学号、姓名; select * from S where Sage = (select max(Sage) from S where Sdept = 'CS');
(6)找出各系年龄最大的学生,显示其学号、姓名;select Sno, Sname from S where Sage in (select max(Sage) from S group by Sdept);
(7)统计“CS”系学生的人数;select count(*) from S; (8)统计各系学生的人数,结果按升序排列; select Sdept, count(*) from S group by Sdept Order by count(*) asc;
(9)按系统计各系学生的平均年龄,结果按降序排列;select Sdept, avg(Sage) as AVG from group by Sdept order by AVG desc; (10)查询每门课程的课程名; select Cname from C;
(11)查询无先修课的课程的课程名和学时数;select Cname, Ccredit from C where Cpno is null;
(12)统计无先修课的课程的学时总数;select sum(Ccredit) from C where Cpno is null;
(13)统计每位学生选修课程的门数、学分及其平均成绩;select Sno ,count(SC.Cno), avg(Grade),Sum(C.Credit) from SC,C where SC.Cno = C.Cno group by Sno;
(14)统计选修每门课程的学生人数及各门课程的平均成绩;select Cno, count(Sno) as num, avg(Grade) as Avg from SC group by Cno; (15)找出平均成绩在85分以上的学生,结果按系分组,并按平均成绩的升序排列; select S.Sdept ,avg(Grade) as Avg , S.Sname as 'name' from S left join SC on S.Sno = SC.Sno group by Sdept,S.Sname having avg(Grade) > 85 order by avg(Grade); (16)查询选修了“1”或“2”号课程的学生学号和姓名; select S.Sno, S.Sname from S, SC where (SC.Cno = 1 or SC.Cno = 2) and S,Sno = SC.Sno group by S.Sno, S.Sname order by S.Sno, S.Sname; (18)查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩;
select S.Sno , S.Sname ,SC.Grade from S ,SC, C where C.Cname = '数据库' and Grade < 60 and SC.Cno = C.Cno and SC.Sno = S.Sno; (19)查询每位学生选修了课程的学生信息(显示:学号,姓名,课程号,课程名,成绩); select * from S,C,SC where S.Sno = SC.Sno and C.Cno = SC.Cno; (20)查询没有选修课程的学生的基本信息; select * from S where Sno not in (select Sno from SC); (21)查询选修了3门以上课程的学生学号; select Sno from SC group by Sno having count(Cno)>=3; (22)查询选修课程成绩至少有一门在80分以上的学生学号; select S.Sno from S left join SC on SC.Sno = S.Sno where Grade > 80 and Grade is not null group by S.Sno order by S.Sno (23)查询选修课程成绩均在80分以上的学生学号; select Sno, Sname from S where Sno in (select Sno from SC group by Sno having avg(Grade) >=80); (24)查询选修课程平均成绩在80分以上的学生学号; select Sno, Sname from S where Sno in (select Sno from SC group by Sno having avg(Grade) >=80);
|
|