数据库系统概论论文.docVIP

1.查询课程名有%字符的课程名和课程学分 select cname,ccredit from dbo.c where cname like%\%% escape\; 2.查询没有成绩的学生学号 select sno from dbo.sc where grade is null; 3.查询CS年龄小于25岁的学生姓名 select sname from dbo.s where sdept=cs and sage25; 4.查询选修课程的学生人数 select count(distinct sno) from dbo.sc; 5.s1学生平均分 select avg(grade) from dbo.sc where cno=s1; 6.各学院女学生的人数 select sdept,count(sno) from dbo.s where ssex=女 group by sdept; 7.查询没有分配系的学生学号和姓名，学号降序排列 select sno,sname from dbo.s where sdept is null order by sno desc; 8.查询每个系女生人数，超过2人的输出系名和女生人数 select sdept,count(sno) from dbo.s where ssex=女 group by sdept having count(sno)2; 9.显示所有学生信息，如果该生参加过考试，显示其考试成绩 select dbo.s.sno,sname,ssex,sage,sdept,grade from dbo.s left join sc on(dbo.s.sno=dbo.sc.sno); 10.查询学分比maths少的课程号和课程名、学分 select s1.cno,s1.cname,s1.ccredit from dbo.c s1,dbo.c s2 where s1.ccredits2.ccredit and s2.cname=maths; 11.CS系没有选修c3号课程的学生学号 select sno from dbo.s where sdept=cs and not exists (select* from dbo.sc where sno=dbo.s.sno and cno=c3); 12.被全部学生都选修的课程号、课程名 select cno,cname from dbo.c where not exists (select* from dbo.s where not exists (select* from dbo.sc where sno=dbo.s.sno and cno=dbo.c.cno)); 13.第一步：把maths不及格的成绩全部置空 update dbo.sc set grade=null where grade60 and cno in (select cno from dbo.c where cname=maths); 第二步：查询选修maths课程的信息 select * from dbo.c where cname=maths; 14、第一步：把全体女学生的学号、姓名、选课课号、课程名、成绩做成视图S_SC， Create view s_sc As Select dbo.s.sno,sname,dbo.c.cno,cname,grade From dbo.s,dbo.sc,dbo.c Where dbo.s.sno=dbo.sc.sno and dbo.sc.cno=dbo.c.cno and ssex=女; 第二步：查询该视图所有信息 Select * from s_sc