1、实验内容与要求请有选择地实践以下各题。（1）基于“教学管理”数据库 jxgl，使用 SQL 的查询语句表达下列查询：检索年龄大于 23 岁的男学生的学号和姓名；SELECT Sno,SnameFROM StudentWHERE Ssex=男ANDSage23;检索至少选修一门课程的女生姓名；SELECT SnameFROM StudentWHERE Ssex=女AND Sno IN( SELECT SnoFROM SCGROUP BY SnoHAVING count(*)=1;);检索王同学不学的课程的课程号；SELECT Cno;FROM CourseWHERE Cno NOT IN( SE
2、LECT CnoFROM Student,SCWHERE Sname like 王%AND Student.Sno=SC.Sno);检索至少选修两门课程的学生学号；SELECT DISTINCT SnoFROM SCGROUP BY SnoHAVING count(*)=2; 检索全部学生都选修的课程的课程号与课程名；SELECT Cno,CnameFROM CourseWHERE NOT EXISTS( SELECT * FROM StudentWHERE NOT EXISTS( SELECT *FROM SCWHERE SC.Sno=Student.Sno AND SC.Cno=Cours
3、e.Cno);检索选修了所有 3 学分课程的学生学号；SELECT DISTINCT SnoFROM SC XWHERE NOT EXISTS( SELECT *FROM CourseWHERE Ccredit=3 AND NOT EXISTS( SELECT *FROM SC YWHERE X.Sno=Y.Sno AND Course.Cno=Y.Cno);（2）基于“教学管理”数据库 jxgl，使用 SQL 的查询语句表达下列查询：统计有学生选修的课程门数；SELECT count(DISTINCT Cno)FROM SC;求选修 4 号课程的学生的平均年龄；SELECT AVG(Sage
4、)FROM Student,SCWHERE Cno=4 AND Student.Sno=SC.Sno;求学分为 3 的每门课程的学生平均成绩；SELECT AVG(Grade)FROM Course,SCWHERE Ccredit=3 AND Course.Cno=SC.CnoGROUP BY SC.Cno;统计每门课程的学生选修人数，要求超过 3 人的课程才统计，要求输出课程号和选修人数,查询结果按人数降序排列，若人数相同，按课程号升序排列；SELECT Cno,count(Sno)FROM SC GROUP BY CnoHAVING count(Sno)3ORDER BY count(Sn
5、o) DESC,Cno ASC;检索学号比“王菲”同学大而年龄比他小的学生姓名；SELECT SnameFROM Student XWHERE Sno( SELECT SnoFROM Student YWHERE Sname=王菲AND Sage( SELECT SageFROM Student ZWHERE Sname=王菲AND X.Sno=Z.Sno AND Y.Sno=Z.Sno);检索姓名以“王”打头的所有学生的姓名和年龄；SELECT Sname,SageFROM StudentWHERE Sname LIKE 王%;在 SC 中检索成绩为空置的学生学号和课程号；SELECT Sn
6、o,CnoFROM SCWHERE Grade is NULL;求年龄大于女同学平均年龄的男学生姓名和年龄；SELECT Sname,SageFROM Student XWHERE Ssex=男AND Sage( SELECT AVG(Sage)FROM StudentWHERE Ssex=女AND X.Sno=Y.Sno);求年龄大于所有女同学年龄的男同学姓名和年龄；SELECT Sname,SageFROM Student XWHERE Ssex=男AND Sage( SELECT MAX(Sage)FROM Student YWHERE Ssex=女AND X.Sno=Y.Sno);检索
7、所有比“王华”年龄大的学生姓名，年龄和性别；SELECT Sname,Sage,SsexFROM Student XWHERE Sage( SELECT SageFROM Student YWHERE Sname=王华AND X.Sno=Y.Sno);检索选修“2”课程的学生中成绩最高的学生和学号；SELECT Sname,SC.SnoFROM Student,SCWHERE Cno=2 AND Student.Sno=SC.Sno;检索学生姓名和其所选修课程的课程号和成绩；SELECT Sname,Cno,GradeFROM Student,SCWHERE Student.Sno=SC.Sn
8、oGROUP BY Sname;检索选修 4 门以上课程的学生总成绩（不统计不及格的课程） ，并要求按总成绩的降序排列出来；SELECT Sno,SUM(Grade)FROM SC XWHERE Grade=60 AND Sno IN( SELECT SnoFROM SC YWHERE X.Sno=Y.SnoGROUP BY SnoHAVING count(Cno)4) ORDER BY SUM(Grade) DESC;（3）设有表 4-1表 4-4 的 4 个基本表（表结构于表内容是假设的） ，请先创建数据库及根据表内容创建表结构，并添加表记录，写出实现以下各题功能的 SQL 语句：创建以下
9、 4 各表：CREATE TABLE STUDENT(SNO CHAR(6) PRIMARY KEY,SNAME CHAR(20) UNIQUE,SEX CHAR(2),AGE SMALLINT,CLASS CHAR(4);CREATE TABLE TEACHER(TNO CHAR(3) PRIMARY KET,TNAMW CHAR(20) UNIQUE,SEX CHAR(2),AGE SMALLINT,PROF CHAR(10),DEPT CHAR(10);CREATE TABLE COURSE(CNO CHAR(4) PRIMARY KEY,CNAME CHAR(20) UNIQUE,TN
10、O CHAT(3),FOREIGN KEY TNO REFERENCES TEACHER(TNO);CREATE TABLE SC(SNO CHAR(6),CNO CHAR(4),GRADE SMALLINT,PRIMARY KEY(SNO,CNO),FOREIGN KEY SNO REFERENCES STUDENT(SNO),FOREIGN KEY CNO REFERENCES COURSE(CNO);插入数据：INSERT INTO STUDENTVALUES(980101,李华 ， 男 ，19， 9801);同上方法依次插入数据查询选修课程“8105”且成绩在 80 到 90 之间的所
11、有记录；SELECT *FROM SCWHERE CNO=8105AND GRADE BETWEEN 80 AND 90;查询成绩为 79，89 或 99 的记录；SELECT *FROM SCWHERE GRADE IN(79,89,99);查询“9803”班的学生人数；SELECT count(SNO)FROM STUDENTWHERE SNO LIKE 9803%;查询至少有 20 名学生选修的并且课程号以 8 开头的课程及平均成绩；SELECT CNO,AVG(GRADE)FROM SCWHERE CNO LIKE 8%GROUP BY CNO;HAVING count(SNO)=20
12、;查询最低分大于 80，最高分小于 95 的 SNO 与平均分；SELECT SNO,AVG(GRADE)FROM SCGROUP BY SNOHAVING MIN(GRADE)80 AND MAX(GRADE)(SELECT GRATEFROM SC YWHERE CNO=8105AND SNO=980302AND X.SNO=Y.SNO);查询与学号为“980103”的同学同岁的所有学生的 SNO,SNAME 和 AGE；SELECT SNO,SNAME,AGEFROM STUDENT XWHERE AGE=(SELECT AGEFROM STUDENT YWHERE SNO=980103
13、AND X.SNO=Y.SNO);查询“钱军”教师任课的课程号，以及选修其课程学生的学号和成绩；SELECT SC.CNO,SNO,GRADEFROM SC,TEACHRT,COURSEWHERE TNAME=钱军AND TEACHER.TNO=COURSE.TNO AND SC.CON=COURSE.CON;查询选修某课程的学生人数多于 20 人的教师姓名；SELECT DISTINCT TNAMEFROM TEACHER,SC,COURSEWHERE TEACHER.TNO=COURSE.TNO AND SC.CON=COURSE.CONGROUP BY SC.CNOHAVING coun
14、t(SNO)20;11 查询选修编号为“8105”课程且成绩至少高于其选修编号为“8245”课程成绩的同学的SNO 及“8105”课程成绩，并按成绩从高到低依次排列；SELECT SNO GRADEFROM SC XWHERE CNO=8105AND GRADE(SELECT GRADEFROM SC YWHERE CNO=8245AND X.SNO=Y.SNO)ORDER BY GRADE DESC;12 查询选修编号为“8105”课程且成绩高于所有选修编号为“8245”课程成绩的同学的CNO、SNO、GRADE；SELECT CNO,SNO,GRADEFROM SCWHERE CNO=81
15、05AND GRADE(SELECT MAX(GRADE)FROM SCWHERE CNO=8245AND X.CNO=Y.SNO);13 列出所有教师和同学的姓名，SEX,AGE；SELECT TNAME,TEACHER.SEX,TEACHER.AGE,SNAME,STUDENT.SEX,STUDENT.AGEFROM TEACHER,SC14 查询成绩比该课程平均成绩高的学生的成绩表；SELECT *FROM SC.XGROUP BY SNOHAVING GRADE(SELECT AVG(GRADE)FROM SC.YWHERE X.CNO=Y.CNOGROUP BY CNO);15 列出
16、所有任课教师的 TNAME 和 DEPT；SELECT TNAME,DEPTFROM TEACHER,COURSEWHERE TEACHER.TNO=COURSE.TNO16 列出所有未讲课教师的 TNAME 和 DEPT;SELECT TNAME,DEPYFROM TEACHERWHERE NOT EXISTS(SELECT *FROM COURSEWHERE TEACHER.TNO=COURSE.TNO);17 列出至少有 4 名男生的班号；SELECT CLASSFROM STUDENTGROUP BY CLASSHAVING count(SNO)=4;18 查询不姓“张”的学生记录；S
17、ELECT *FROM STUDENTWHERE SNAME NOT LIKE 张%;19 查询每门课最高分的学生的 SNO,CNO,GRADE;SELECT SNO,CNO,GRADEFROM SCGROUP BY CNOHAVING GRADE=MAX(GRADE);20 查询与“李华”同性并同班的同学 SNAME；SELECT SNAMEFROM STUDENT XWHERE CLASS=(SELECT CLASSFROM STUDENT YWHERE SNAME=李华AND SEX=(SELSCT SEXFROM STUDENT ZWHERE SNAME=李华AND X.SNO=Y.S
18、NO AND Y.SNO=Z.SNO);21 查询“女”教师及其所上的课程；SELECT TNAME,CNO,CNAMEFROM TEACHER,COURSEWHERE TEACHER.TNO=COURSE.TNO,SEX=女;22 查询选修“数据库系统”课程的“男”同学的成绩表；SELECT *FROM SC,COURSE,STUDENTWHERE STUDENT.SNO=SC.SNO AND COURSE.CNO=SC.CNO AND CNAME=数据库系统AND SEX=男;23 查询所有比刘涛年龄大的教师姓名，年龄和刘涛的年龄；SELECT TNAME,AGEFROM TEACHER XWHERE SNAME=刘涛OR AGE(SELECT AGEFROM TEACHER YWHERE X.TNO=Y.TNO AND SNAME=刘涛);24 查询不讲授“8106”号课程的教师姓名。SELECT TNAMEFROM TEACHER,COURSEWHERE TEACHER.TNO=COURSE.TNO AND CNO NOT IN(8106);
