SQL数据定义及查询习题课(给学生).doc

SQL 数据定义及查询习题课教案一、数据定义的练习：用SQL语句创建数据库：STCreate DataBase ST;用SQL语句创建如下三个基本表：学生表(Student)、课程表（Course）、学生选课表（SC），结构如下所示Student表结构列名说明数据类型约束Sno学号字符串，长度为9主码Sname姓名字符串，长度为20非空Ssex性别字符串，长度为2取男或女Sage年龄整数取值1545Sdept所在院系字符串，长度为20默认为CSCreate table Student(Sno varchar(9) primary key,Sname varchar(20) not null,Ssex char (2) check(Ssex=男or Ssex=女),Sage smallint check(Sage between 15 and 45),Sdept varchar(20) default(CS) Course表结构列名说明数据类型约束Cno课程号字符串，长度为4主码Cname课程名字符串，长度为40非空Ccredit学分整数取值大于0Semester学期整数取值1到8Period学时整数取值大于0Create table course(Cno varchar(4) primary key,Cname varchar(40) not null,Ccredit smallint check(Ccredit 0),Semester smallint check(Semester between 1 and 8),Period smallint check(Period0)SC表结构列名说明数据类型约束Sno学号字符串，长度为9主码，引用Student. Sno的外码 Cno课程号字符串，长度为10主码，引用Course. Cno的外码Grade成绩整数取值0100Create table SC(Sno varchar(9) foreign key references student(Sno),Cno varchar(4) foreign key references course(Cno),Grade smallint check(Grade between 0 and 100),Primary key (Sno,Cno)二、输入数据以上三表具体数据参考教材P82，COURSE 表的 Semester、Period 数据在允许范围自行确定，SC 表应根据后面各种查询情况适当增加学生选课记录。输入数据时，可以从管理平台可视化录入，也可以用INSERT 命令插入。三、数据查询的练习1查询学生选课表中的全部数据。SELECT *FROM SC 2查询计算机系学生的姓名、年龄。Select Sname,SageFrom StudentWhere Sdept=CS3查询成绩在7080分之间的学生的学号、课程名和成绩。Select Sno,Cname,GradeFrom Course,ScWhere o=sc.Cno and sc.Grade between 70 and 804查询计算机系年龄在1820之间且性别为“男”的学生的姓名和年龄。Select Sname,SageFrom StudentWhere Sage between 18 and 20 and Ssex=男and Sdept=CS5查询课程号为“1”的课程的最高分数。Select top 1 Grade From Sc Where Cno=1 Order by Grade desc或select max(Grade) as 最高分 from Scwhere Cno=16查询计算机系学生的最大年龄和最小年龄。Select cs 系, max(Sage) as 年龄最大,min(Sage) as 年龄最小From StudentWhere Sdept=CS7统计每个系的学生人数。Select Sdept, count(Sdept) as 学生人数 From StudentGroup by Sdept8统计每门课程的选课人数和考试最高分。Select o ,count(s.Sno) as 选课人数, max(Grade) as 最高分From Course c left join Sc s on o=s.CnoGroup by c.Cno9统计每个学生的选课门数和考试平均成绩，并按学号的升序显示结果。Select sno,avg(grade) as 平均成绩,count (cno) as 选课门数From scGroup by snoOrder by sno10查询总成绩超过200分的学生，要求列出学号、总成绩。Select sno,sum(grade)From scGroup by snoHaving sum(grade)20011查询选修了 “2” 号课程的学生的姓名和所在系。Select sname,sdeptFrom student s1,sc s2Where s1.sno=s2.sno and o=c0212查询成绩在80分以上的学生的姓名、课程号和成绩，并按成绩的降序排列结果。Select s1.sname,o,s2.gradeFrom student s1,sc s2Where s1.sno=s2.sno and grade 80Order by grade desc13查询哪些课程没有人选修、要求列出课程号和课程名。Select o,ameFrom course c left join sc s on o=oGroup by o,ame Having count(s.sno)=014用子查询实现如下查询： (1)查询选修了“2”号课程的学生的姓名和所在系。Select sname,sdept From studentWhere sno in (Select sno From scWhere cno =1) (2)查询计算机系成绩在80分以上的学生的学号、姓名。Select sno,snameFrom studentWhere sdept=CS and sno in(Select snoFrom scWhere grade80) (3)查询计算机系考试成绩最高的学生的姓名。Select sname from student,sc Where student.sno=sc.sno and grade=(select max(Grade) from sc,student where student.sno=sc.sno and student.sdept=CS)15 查询学生200215122 没有选修的那些课程 SELECT * FROM Course c WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.Sno=200215122 AND SC.Cno=C.Cno) 16 查询所有已经选课的学生姓名、选修的课程名及成绩。 SELECT student.sname, ame, sc.grade From student join sc on student.sno = sc.sno join course on o = o或SELECT student.sname, ame, sc.grade From student,sc,course Where student.sno = sc.snoAnd o = o17查询所有的学生姓名、选修的课程名及成绩。SELECT student.sname, ame, sc.grade From (student left join sc on student.sno = sc.sno ) left join course on o = o18查询选修了“数据库”课程的学生姓名。 SELECT sname FROM student WHERE sno IN (SELECT sno FROM