《数据库实验骄阳教育》由会员分享，可在线阅读，更多相关《数据库实验骄阳教育（9页珍藏版）》请在金锄头文库上搜索。
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 Stu
2、dent.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,Cnam
4、efrom 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 *fro
5、m 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,SCwhe
6、re 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 Studentw
8、here 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 Studentwhere
9、 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门以上课程的学生总成绩（不统计不及格的课程），并要求按总成绩
