《sql 数据库上机实验二》.doc

实目的： 掌握数据查询操作。 内容：1）创建学生表student、课程表course和选课表SC,并输入数据注意数据的完整性。;（可以使用实验一中已经建立的表和数据） 2) 对各表中的数据进行不同条件的查询；‘年龄为’+cast(sage as char(2))+’岁’ From student 查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。 查询学生总人数。 查询选修了课程的学生人数。 查询选修了7号课程的学生总人数和平均成绩 查询选修6号课程学生的最好成绩 查询每个系的系名及学生人数。 查找每门课的选修人数及平均成绩 查找没有先修课的课程情况 附录： 语句清单 --sql查询实验二的代码 SELECT sno,sname --1 from student select * ------------2 from student select sname,sage,sdept -----3 from student where student.sdept=IS select distinct student.sno -----4 from student,sc where sc.sno=student.sno select distinct student.sno ----5 from student,sc where sc.sno=student.sno and grade60 select ssex,sage,sdept -----------6 from student where sno not in (select sno from student where sdept=IS) /* 这是exists的用法 select ssex,sage,sdept from student as y where not exists(select * from student where Y.sdept=CS) */ select sno,sname,sage,sdept -------7 from student where sage between 18 and 20; select * ---------------8 from student where sname like刘% select * -----------------9 from student where sname like[刘李]% select sname ----------------10 from student where sname like刘_ _--中文占用两个字符，所以这里用两个_ select sname -------------------11 from student where 2013-sage1983 -- select year(sage) 这是取单个人的出生年份的用法，调用year函数 select year(getdate())-s.sage+1 from student as s -------12 where sdept=CS Select sname + 年龄为+cast(sage as char(2))+岁 From student ---------13 select * from student ---------------------14 order by sdept,sage desc select COUNT(sno)----------------15 from student select COUNT(distinct sno) from sc --------------16 /* SELECT COUNT(*) FROM student ----------16题解法二 WHERE EXISTS( SELECT * FROM SC WHERE sc.sno = student.sno ) */ select COUNT(student.sno),avg(sc.grade) from student,sc ------------17 where student.sno=sc.sno and cno=7 select max(grade) from sc -------------------18 where cno=6 select sdept,COUNT(sno) from student -------------------19 group by sdept select C