数据库系统原理.doc

.数据库系统原理实验报告姓名： 指导教师： 学号： 实验日期：2016/6/7 一、实验目的2、用SQL语句建立上述表，自定义主键和外键，对于student表建立约束条件：ssex仅能取male或female；sage在18和22之间。并完成下面的查询语句。1、查询所有选修过“Bibliometrics”课的学生的姓名和成绩；mysql select s.sname,sc.grade from student as s,sc where s.sno in(select sno from sc where grade=60 and cno=C05) and sc.grade in(select grade from sc where grade=60 and cno=C05);2、查询考试成绩不及格的学生的个数；mysql select count(*) from sc where grade select sname,sno,ssex from student where sname like %z%;4、查询选修了“Introduction to the Internet”课程的学生的学号及其成绩，查询结果按分数的降序排列；mysql select sno,grade from sc where cno in (select cno from course where cname like Introd%to%Inter%) order by grade desc;5、查询“Zuo li”同学选修课程的总学时(time)数mysql select sum(c.ctime) from course c where cno in (select cno from sc where sno in (select sno from student where sname=Zuo li);6、查询年龄不大于20岁的学生的平均考试成绩；mysql select avg(grade) from sc where sno in (select sno from student where sage select distinct count(sno) from sc where cno in (select cno from course where cname like D%Syst%) and sno in (Select sno from student where sdept like compu%);8、查询同时选修课程“Database System”和“Introduction to the Internet”的学生姓名；mysql select sname from student where sno in (select sno from sc where cno in (select cno from course where cname like D%S%) union (select cno from course where cname like In%to%the%);9、查询选修的课程中含有“Wang gang”同学所有选修课程的学生姓名。mysql select sname from student where sno in (select sno from sc where cno in (select cno from sc where sno in (select sno from student where sname=Wang gang); 10、查询“Information Technology for Information Management”考试成绩为空的学生姓名及专业名称。mysql select sname,sdept from student where sno in (select sno from sc where grade is null); 11、查询“computer science”专业学生每个人的选修课总学分。mysql select avg(grade) from sc where sno in (select sno from student where sdept like compu%); 12、查询个人考试平均成绩高于专业平均成绩的学生姓名mysql select stu.sname from student as stu where stu.sno in (select sno from sc where (select avg(grade) from sc where sno=stu.sno) (select avg(grade) from sc); 13、查询个人考试平均成绩高于女生平均成绩的男生姓名mysql select sname from student as stu where stu.ssex=male and stu.sno in (select sno from sc where (select avg(grade) from sc where sno=stu.sno) (select avg(grade) from sc where sno in (select sno from student where ssex=female); 14、查询比“computer science”专业所有学生年龄都大的学生姓名。mysql select * from student where sdept like com% and sage in(select max(sage) from student where sdept like com%); 15、查询考试成绩仅有一科不及格学生姓名mysql select stu.sname f