查每课人数java_数据库实验-数据查询语言

(1)查询学生的基本信息；

select * from S;

(2)查询“CS”系学生的基本信息；

select * from S where Sdept =’CS’;

(3)查询“CS”系学生年龄不在19到21之间的学生的学号、姓名；

select Sno, Sname from S

where Sdept = ‘CS’ and Sage not

Between 19 and 21;

(4)找出最大年龄；

select max(Sage) from S;

(5)找出“CS”系年龄最大的学生，显示其学号、姓名；

select * from S whereSage= (select max(Sage) from S where Sdept = 'CS');

(6)找出各系年龄最大的学生，显示其学号、姓名；

select Sno, Sname fromSwhere Sage in(select max(Sage) from S group by Sdept);

(7)统计“CS”系学生的人数；

select count(*) from S;

(8)统计各系学生的人数，结果按升序排列；

select Sdept, count(*) from S group bySdeptOrder by count(*) asc;

(9)按系统计各系学生的平均年龄，结果按降序排列；

select Sdept, avg(Sage) as AVG from

group bySdeptorder by AVG desc;

(10)查询每门课程的课程名；

select Cname from C;

(11)查询无先修课的课程的课程名和学时数；

select Cname, Ccredit from C where Cpno is null;

(12)统计无先修课的课程的学时总数；

select sum(Ccredit) fromCwhere Cpno is null;

(13)统计每位学生选修课程的门数、学分及其平均成绩；

select Sno ,count(SC.Cno), avg(Grade),Sum(C.Credit)fromSC,Cwhere SC.Cno =C.Cnogroup by Sno;

(14)统计选修每门课程的学生人数及各门课程的平均成绩；

select Cno, count(Sno) as num, avg(Grade) as Avg

fromSCgroup by Cno;

(15)找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列；

select S.Sdept ,avg(Grade) as Avg , S.Sname as 'name' fromSleft join SC on S.Sno =SC.Snogroup bySdept,S.Snamehaving avg(Grade) > 85 order by avg(Grade);

(16)查询选修了“1”或“2”号课程的学生学号和姓名；

select S.Sno, S.Sname fromS, SCwhere (SC.Cno = 1 or SC.Cno = 2)and S,Sno = SC.Sno group byS.Sno, S.Snameorder by S.Sno, S.Sname;

(18)查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩；

select S.Sno , S.Sname ,SC.Grade fromS ,SC, Cwhere C.Cname = '数据库'

and Grade < 60

and SC.Cno =C.Cnoand SC.Sno = S.Sno;

(19)查询每位学生选修了课程的学生信息(显示：学号，姓名，课程号，课程名，成绩)；

select * fromS,C,SCwhere S.Sno =SC.Snoand C.Cno = SC.Cno;

(20)查询没有选修课程的学生的基本信息；

select * fromSwhere Sno not in (select Sno from SC);

(21)查询选修了3门以上课程的学生学号；

selectSnofromSCgroup bySnohaving count(Cno)>=3;

(22)查询选修课程成绩至少有一门在80分以上的学生学号；

selectS.Snofrom S left join SC on SC.Sno =S.Snowhere Grade > 80 and Grade is not null

group byS.Snoorder by S.Sno

(23)查询选修课程成绩均在80分以上的学生学号；

select Sno, Sname fromSwhere Sno in(select Sno from SC group by Sno having avg(Grade) >=80);

(24)查询选修课程平均成绩在80分以上的学生学号；

select Sno, Sname fromSwhere Sno in(select Sno from SC group by Sno having avg(Grade) >=80);