实验四 SQL语言 SELECT查询操作.doc

实验四 SQL语言 习题一 习题二USE DATABASE jxglUSE jxglCreate 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 Student.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 王%);检索至少选修两门课程的学生学号；select DISTINCT Student.Snofrom Student,SCWHERE Student.Sno=SC.SnoGROUP BY Student.Sno HAVING COUNT(*)=2;检索全部学生都选修的课程的课程号与课程名； 注 参考 课本p111 例题46SELECT 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,Cnamefrom 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 *from 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,SCwhere 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;只显示一门课程的成绩！统计每门课程的学生选修人数，要求超过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 Studentwhere 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 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门以上课程的学生总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。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再注意：Sel