1、一 在数据库 school 中建立student , sc, course 表。学生表、课程表、选课表属于数据库 School ,其各自的数据结构如下:学生 Student (Sno,Sname,Ssex,Sage,Sdept)序号列名含义数据类型长度1Sno学号字符型(char)62Sname姓名字符型(varchar)83Ssex性别字符型(char)24Sage年龄整数 (smallint)5sdept系科字符型(varchar)15课程表 course(Cno,Cname,Cpno,Ccredit)序号列名含义数据类型长度1Cno课程号字符型(char)42cname课程名字符型(va
2、rchar)203Cpno先修课字符型(char)44Ccredit学分短整数 (tinyint)学生选课 SC(Sno,Cno,Grade)序号列名含义数据类型长度1Sno学号字符型(char)62Cno课程号字符型(char)43Grade成绩小数(decimal)12,2二 设定主码1 Student表的主码:sno 2 Course表的主码:cno3 Sc表的主码:sno,cno 1写出使用 Create Table 语句创建表 student , sc, course 的SQL语句2在student表中插入信息学号姓名性别年龄系科4001赵茵男20SX4002杨华女21JSJ3 删除
3、student表中的元组 4在数据库school中删除关系student5在student表添加属性sbirthdate 类型 datetime练习 Delete1 删除所有 JSJ 系的男生 delete from Student where Sdept=JSJ and Ssex=男;2 删除“数据库原理”的课的选课纪录 delete from SC where Cno in (select Cno fromCourse where Cname=数据库原理); Update1 修改 0001 学生的系科为: JSJ2 把陈小明的年龄加1岁,性别改为女。2 修改李文庆的1001课程的成绩为 9
4、3 分3 把“数据库原理”课的成绩减去1分Select 查询语句一 单表1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。2查询姓名中第2个字为“明”字的学生学号、性别。3查询 1001课程没有成绩的学生学号、课程号4查询JSJ 、SX、WL 系的年龄大于25岁的学生学号,姓名,结果按系排列5按10分制查询学生的sno,cno,10分制成绩 (1-10分 为1 ,11-20分为2 ,30-39分为3,。90-100为10) 6查询 student 表中的学生共分布在那几个系中。(distinct) 7查询0001号学生1001,1002课程的成绩。二 统计1查询姓名中有
5、“明”字的学生人数。2计算JSJ系的平均年龄及最大年龄。3查询学生中姓名为张明、赵英的人数4计算每一门课的总分、平均分,最高分、最低分,按平均分由高到低排列5 计算 1001,1002 课程的平均分。6 查询平均分大于80分的学生学号及平均分 7 统计选修课程超过 2 门的学生学号8 统计有10位成绩大于85分以上的课程号。9 统计平均分不及格的学生学号10 统计有大于两门课不及格的学生学号三 连接 1查询 JSJ 系的学生选修的课程号2查询选修1002 课程的学生的学生姓名 (不用嵌套及嵌套2种方法)3查询数据库原理不及格的学生学号及成绩4查询选修“数据库原理”课且成绩 80 以上的学生姓名
6、(不用嵌套及嵌套2种方法)5查询平均分不及格的学生的学号,姓名,平均分。6查询女学生平均分高于75分的学生姓名。7查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出,不能遗漏)四 嵌套、相关及其他1 查询平均分不及格的学生人数2 查询没有选修1002 课程的学生的学生姓名 3 查询平均分最高的学生学号及平均分 (2种方法 TOP , any , all)*4 查询没有选修1001,1002课程的学生姓名。 5 查询1002课程第一名的学生学号(2种方法)6 查询平均分前三名的学生学号7 查询 JSJ 系的学生与年龄不大于19岁的学生的差集8 查询1001号课程大于90分的
7、学生学号、姓名及平均分大于85分的学生学号、姓名9 查询每门课程成绩都高于该门课程平均分的学生学号10 查询大于本系科平均年龄的学生姓名答案参考答案1 create table student (sno char(6), sname varchar(8), ssex char(2), sage smallint, sdept varchar(15), primary key(sno);create table sc (sno char(6), cno char(4), grade decimal(12,2), primary key(sno,cno);insert into student v
8、alues( 4001,赵茵,男,20,SX)delete from studentdrop table studentalter table student add sbirthdate datetime 1 select sno, sname, sage from student where ssex=女 and sage between 19 and 21 order by sage desc;2 select sno, ssex from student where sname like _明% ;3 select sno, cno from sc where grade is nul
9、l and cno=1001 ;4 select sno, sname from student where sdept in (JSJ,SX,WL) and sage25 group by sdept;select sno, cno, grade/10.0+1 as level from sc ;select distinct sdept from student ;select gradefrom sc where sno=0001 and (cno=1001 or cno=1002) ;select count(*) from student where sname like %明% ;
10、select avg(sage),max(sage) from student where sdept=JSJ ;select cno,sum(grade),avg(grade),max(grade),min(grade) from sc group by cno order by avg(grade) desc ;select cno, avg(grade) from sc where cno in(1001,1002) group by cno ;select sc.sno ,avg(grade) from sc group by sc.sno having avg(grade)80 ;s
11、elect sno from sc group by sno having count(*)2 ;select cno from sc where grade85 group by cno having count(*)=10 ;select sno from sc group by sno having avg(grade)60 ;select sno from sc where grade2 ;select cno from student,sc where student.sno=sc.sno and sdept=JSJ ;a:select sname from student,sc w
12、here student.sno=sc.sno and cno=1002b:select sname from student where sno in (select sno from sc where cno=1002)select sno,grade from sc,course where o=o and cname=数据库原理 and grade 80 and cname= 数据库原理b:select sname from student where sno in (select sno from sc where grade80 and cno in (select cno fro
13、m course where cname= 数据库原理)select sno,sname,avg(grade) from sc,studentwhere student.sno=sc.snogroup by student.sno having avg(grade)75) b:select sname from sc,student where student.sno=sc.sno and ssex=女 group by student.sno having avg(grade)75select student.sno,sname,cno,grade from student left joi
14、n sc on student.sno=sc.sno and ssex=男select count(*) from student where sno in( select sno from sc group by sno having avg(grade)=all ( select avg(grade) from sc group by sno)select sname from student where not exists( select * from course where cno in(1001,1002) and not exists(select * from sc wher
15、e sno =student.sno and cno=o) )a:select top 1 sno from sc cno=1002 order by grade descb:select sno from sc where cno=1002 and grade =all (select grade from sc where cno=1002)select top 3 sno from sc group by sno order by avg(grade)desca:select*from student where sdept=JSJ and sage19b:select*from student where sdept=JSJ except select* from student where sage90 union select sno,sname from student where sno in ( select sno from sc group by sno having avg(grade)85)select sno from student where sno not in(select sno from sc x where grade ( select avg(sage) from student where sdept=x.sdept)