SQL数据定义与查询习题课给学生

1、SQL数据定义及查询习题课教案.下载可编辑一、数据定义的练习： 用SQL语句创建数据库：STCreate DataBase ST;用SQL语句创建如下三个基本表：学生表（Student）、课程表（Course）、学生 选课表（SC，结构如下所示Student表结构列名说明数据类型约束Sno学号字符串，长度为9主码Sn ame姓名字符串，长度为20非空Ssex性别字符串，长度为2取男或女Sage年龄整数取值1545Sdept所在院系字符串，长度为20默认为 CSCreate table Stude nt (Sno varchar(9) primary key,Sn ame varchar(20)

2、 not nu II,Ssex char (2) check(Ssex= 男or Ssex=女),Sage smalli nt check(Sage betwee n 15 and 45),Sdept varchar(20) default( CS )Course表结构列名说明数据类型约束Cno课程号字符串，长度为4主码Cn ame课程名字符串，长度为40非空Ccredit学分整数取值大于0Semester学期整数取值1到8Period学时整数取值大于0Create table course(Cno varchar(4) primary key,Cn ame varchar(40) not n

3、 ull,Ccredit smalli nt check(Ccredit 0),Semester smalli nt check(Semester betwee n 1 and 8), Period smalli nt check(Period0)sc表结构列名说明数据类型约束Sno学号字符串，长度为9主码，引用Student. Sno的外码Cno课程号字符串，长度为10主码，引用 Course. Cno的外码Grade成绩整数取值0100Create table SC (Sno varchar(9) foreig n key refere nces stude nt(S no),Cno va

4、rchar(4) foreig n key refere nces course(C no), Grade smalli nt check(Grade between 0 and 100), Primary key (Sn o,C no)二、输入数据以上三表具体数据参考教材 P82, COURS表的Semester、Period 数据在允 许范围自行确定，SC表应根据后面各种查询情况适当增加学生选课记录。输入数据时，可以从管理平台可视化录入，也可以用INSERT命令插入。三、数据查询的练习1. 查询学生选课表中的全部数据。SELECT *FROM SC2. 查询计算机系学生的姓名、年龄。Sel

5、ect Sn ame,SageFrom Stude ntWhere Sdept=CS3. 查询成绩在7080分之间的学生的学号、课程名和成绩。Select Sno,Cn ame,GradeFrom Course,ScWhere course.c no=sc.C no and sc.Grade betwee n 70 and 804. 查询计算机系年龄在1820之间且性别为“男”的学生的姓名和年龄。Select Sn ame,SageFrom Stude ntWhere Sage between 18 and 20 and Ssex=男and Sdept= CS5查询课程号为“ 1”的课程的最高

6、分数。Select top 1 GradeFrom ScWhere Cno=1Order by Grade desc或select max(Grade) as 最高分from Scwhere Cno=16查询计算机系学生的最大年龄和最小年龄。Select cs 系，max(Sage) as 年龄最大,min(Sage) as 年龄最小From Stude ntWhere Sdept=CS7统计每个系的学生人数。Select Sdept, coun t(Sdept) as 学生人数From Stude ntGroup by Sdept8 统计每门课程的选课人数和考试最高分。Select o ,c

7、ount(s.Sno) as选课人数,max(Grade) as 最高分From Course c left join Sc s on c.c no=s.C noGroup by c.C no9 统计每个学生的选课门数和考试平均成绩，并按学号的升序显示结果。Select sno ,avg(grade) as平均成绩，co unt (cno) as 选课门数From scGroup by snoOrder by sno10查询总成绩超过200分的学生，要求列出学号、总成绩。Select sno ,sum(grade)From scGroup by snoHaving sum(grade)2001

8、1 查询选修了“ 2”号课程的学生的姓名和所在系。Select sn ame,sdeptFrom stude nt s1,sc s2Where s1.sno=s2.sno and o= c0212查询成绩在80分以上的学生的姓名、课程号和成绩，并按成绩的降序排 列结果。Select s1.s name,s2.c no ,s2.gradeFrom stude nt s1,sc s2Where s1.s no=s2.s no and grade 80Order by grade desc13查询哪些课程没有人选修、要求列出课程号和课程名。Select c.c no ,c.c nameFrom co

9、urse c left join sc s on c.c no=s.c noGroup by c.c no ,c.c nameHaving coun t(s.s no)=014.用子查询实现如下查询：(1) 查询选修了“ 2”号课程的学生的姓名和所在系。Select sn ame,sdeptFrom stude ntWhere sno in (Select snoFrom scWhere cno =1)(2) 查询计算机系成绩在80分以上的学生的学号、姓名。Select sno,sn ameFrom stude ntWhere sdept=CS and sno in(Select snoFro

10、m scWhere grade80)(3) 查询计算机系考试成绩最高的学生的姓名。Select sn ame from stude nt,scWhere stude nt.s no=sc.s noand grade=(select max(Grade) fromsc,stude nt where stude nt.s no=sc.s no and stude nt.sdept=CS)15查询学生200215122没有选修的那些课程SELECT *FROM Course cWHERE NOT EXISTS(SELECT *FROM SCWHERE SC.S no=200215122 AND SC

11、.C no=C.C no)16查询所有已经选课的学生姓名、选修的课程名及成绩。SELECT stude nt.s name, course.c name, sc.gradeFrom stude nt join sc on stude nt.s no = sc.s nojoin course on sc.c no = course.c no或SELECT stude nt.s name, course.c name, sc.gradeFrom stude nt,sc,courseWhere stude nt.s no = sc.s noAnd sc.c no = course.c no17查询所有的学生姓名、选修的课程名及成绩。SELECT stude nt.s name, course.c name, sc.gradeFrom (stude nt left join sc on stude nt.s no = sc.s no )left join course on sc.c no = course.c no18查询选修了“数据库”课程的学生姓名。SELECT sn ame FROM stude ntWHERE s