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

1、 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,Snam

2、e 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

3、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

4、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

5、 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”的课

6、程的最高分数。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统计每门课程的选课人数和考试最高分。Selec

7、t 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

8、查询选修了 “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

9、=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

10、 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 SCWHERE SC.Sno=200215122 AND SC.Cno=C.Cno)16 查询所有已经选课的学生姓名、选修的课程名及成绩。 SELECT student.sna

11、me, 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 scWHERE cno IN (SELECT o FROM sc,course WHERE cname=数据库 and o=o);SELECT sname FROM student s,sc, course cWHERE s.sno =sc.sno and So =o and ame=数据库;