《数据库实验(9页).doc》由会员分享，可在线阅读，更多相关《数据库实验(9页).doc（9页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、-实验三 建表、修改表、删除表1.建立数据库jxgl在jxgl数据库中建立学生表student,课程表course,选修表sc，建表过程有如下方式1）以图形界面操作2）以SQL操作（以下脚本可直接在查询分析器中执行）Create Table Student(Sno CHAR(5) NOT NULL PRIMARY KEY(Sno),Sname VARCHAR(20),Sage SMALLINT CHECK(Sage=15 AND Sage23 AND Ssex=男;检索至少选修一门课程的女学生的姓名；select Sname from Student,SCwhere Ssex=女 AND St
2、udent.Sno=SC.Sno group by Student.Sname having count(*)=1;或者Select SnameFrom StudentWhere Ssex=女AND Sno in(select sno from SCgroup by snohaving count(*)=1);检索王同学不学的课程的课程号；select Cnofrom Coursewhere Course.Cno not in (select Cno from SC,Studentwhere SC.Sno=Student.Sno AND Sname LIKE 王%);检索至少选修两门课程的学生
3、学号；select DISTINCT Student.Snofrom Student,SCWHERE Student.Sno=SC.SnoGROUP BY Student.Sno HAVING COUNT(*)=2;检索全部学生都选修的课程的课程号与课程名； SELECT Cno,Cnamefrom Course where not exists(select *from studentwhere not exists(select *from SCwhere SC.sno=Student.Sno AND SC.Cno=Course.Cno)或者假设所有学生只有两人SELECT Cno,Cna
4、mefrom Course WHERE Course.Cno in(select Cno from SCgroup by SC.Cnohaving count(Sno)=(select count(*)from Student);SELECT Cno,Cnamefrom Course WHERE Course.Cno in(select Cno from SCgroup by SC.Cnohaving count(Sno)=2);检索选修了所有3学分课程的学生学号。select distinct Student.Snofrom Student,SCwhere exists(select *fr
5、om Coursewhere Ccredit =3 AND Student.Sno=SC.Sno AND SC.Cno=Course.Cno);(2) 基于“教学管理”数据库jxgl,试用SQL的查询语句表达下列查询：统计有学生选修的课程门数；select count(distinct SC.Cno) FROM SC;求选修4号课程的学生的平均年龄；SELECT avg(Student.Sage) from Student,SCwhere Student.Sno=SC.Sno AND Cno=4;SELECT avg(Student.Sage) as 平均年龄from Student,SCwh
6、ere Student.Sno=SC.Sno AND Cno=3;求学分为3的每门课程的学生平均成绩；SELECT avg(SC.Grade)from Course,SC,Studentwhere Student.Sno=SC.Sno AND Course.Ccredit=3group by SC.Cno用group by 语句以课程号分组注意，如果程序是这样的：SELECT avg(SC.Grade)from Course,SC,Studentwhere Student.Sno=SC.Sno AND Course.Ccredit=3AND Course.Cno=SC.Cno;只显示一门课程的
7、成绩！统计每门课程的学生选修人数，要求超过3人的课程才统计，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列；SELECT Cno,count(Sno)from SCGROUP BY Cno HAVING Count(Sno)3order by count(sno)DESC,Cno;SELECT Cno,count(Sno) as 选修人数from SCGROUP BY Cno HAVING Count(Sno)1order by count(sno)DESC,Cno;检索学号比“王林”同学大而年龄比他小的学生姓名；SELECT SnameFROM Student
8、where Sno(select Snofrom Studentwhere Sname=王林)AND SageY.Sno AND X.Sage(select avg(Sage)from studentwhere Ssex=女)AND Ssex=男;求年龄大于所有女学生年龄的男学生姓名和年龄；SELECT Sname,Sagefrom Studentwhere Sage(SELECT MAX(Sage)from Studentwhere Ssex=女)AND Ssex=男;检索所有比“王林”年龄大的学生姓名、年龄和性别；SELECT Sname,Sage,Ssexfrom Studentwher
9、e Sage(select Sagefrom Studentwhere Sname=王林)检索选修“2”课程的学生中成绩最高的学生的学号；SELECT Sno,Gradefrom SCwhere Grade=(select MAX(Grade)from SCwhere Cno=2);注意：不能写成Grade=MAX(Grade)的形式，因为不存在！Count,min,avg也是检索学生姓名以及所选修课程的课程号和成绩；select Sname,Cno,Gradefrom SC,Studentwhere Student.Sno=SC.Sno;检索选修4门以上课程的学生总成绩（不统计不及格的课程）
10、，并要求按总成绩的降序排列出来。-第 9 页-选择4门以上select sno,sum(Grade) sumfrom SCwhere grade=60 AND Sno in(select Sno from SCgroup by Snohaving count(Sno)4)group by snoorder by sum(grade) desc注意：选择2门课（包括两门）select sno,sum(Grade)from SCwhere grade=60 AND Sno in(select Sno from SCgroup by Snohaving count(Sno)=2)group by snoorder by sum(grade) desc再注意：Select sno,sum(grade)From SCWhere grade=60Group by sno having count(sno)1Order by sum(grade) desc因为输入的数据分数都大于60，所以结果与上面的例子相同。但是，当分数小于60时，有课不及格的同学的学号会被删除，统计不完整。