实验内容与要求请有选择地实践以下各题。

(1)基于“教学管理”数据库 jxgl，使用SQL的查询语句表达下列查询:①检索年龄大于23岁的男学生的学号和姓名；SELECT Sn o,S nameFROM Stude ntWHERE Ssex=男’ANDSage>23;②检索至少选修一门课程的女生姓名；SELECT Sn ameFROM Stude ntWHERE Ssex=女’AND Sno IN( SELECT SnoFROM SCGROUP BY SnoHAVING cou nt(*)>=1;);③检索王同学不学的课程的课程号；SELECT Cno;FROM CourseWHERE Cno NOT IN( SELECT CnoFROM Stude nt,SCWHERE Sname like '王 % AND Student.Sno=SC.Sno );④检索至少选修两门课程的学生学号；SELECT DISTINCT SnoFROM SCGROUP BY SnoHAVING cou nt(*)>=2;⑤检索全部学生都选修的课程的课程号与课程名；SELECT Cn o,C nameFROM CourseWHERE NOT EXISTS( SELECT *FROM Stude ntWHERE NOT EXISTS( SELECT *FROM SCWHERE SC.S no=Stude nt.S no AND SC.C no=Course.C no));⑥检索选修了所有 3学分课程的学生学号；SELECT DISTINCT SnoFROM SC XWHERE NOT EXISTS( SELECT *FROM CourseWHERE Ccredit=3 AND NOT EXISTS( SELECT *FROM SC YWHERE X.S no=Y.S no AND Course.C no=Y.C no))；(2)基于“教学管理”数据库 jxgl，使用SQL的查询语句表达下列查询：①统计有学生选修的课程门数；SELECT coun t(DISTINCT Cno)FROM SC;②求选修4号课程的学生的平均年龄；SELECT AVG(Sage)FROM Stude nt,SCWHERE Cn o=4 AND Stude nt.S no=SC.S no;③求学分为3的每门课程的学生平均成绩；SELECT AVG(Grade)FROM Course,SCWHERE Ccredit=3 AND Course.C no=SC.C noGROUP BY SC.C no;④统计每门课程的学生选修人数，要求超过3人的课程才统计，要求输出课程号和选修人数查询结果按人数降序排列，若人数相同，按课程号升序排列；SELECT Cn o,cou nt(S no)FROM SCGROUP BY CnoHAVING cou nt(S no )>3ORDER BY cou nt(S no) DESC,C no ASC;⑤检索学号比“王菲”同学大而年龄比他小的学生姓名；SELECT Sn ameFROM Stude nt XWHERE Sno>( SELECT SnoFROM Stude nt YWHERE Sn am6=王菲'AND Sage>( SELECT SageFROM Stude nt ZWHERE Sn amd=王菲'AND X.S no=Z.S no AND Y.S no=Z.S no ) );⑥检索姓名以“王”打头的所有学生的姓名和年龄；SELECT Sn ame,SageFROM Stude ntWHERE Sname LIKE '王 % ;⑦在SC中检索成绩为空置的学生学号和课程号；SELECT Sn o,C noFROM SCWHERE Grade is NULL;⑧求年龄大于女同学平均年龄的男学生姓名和年龄；SELECT Sn ame,SageFROM Stude nt XWHERE Ssex=男’AND Sage>( SELECT AVG(Sage) FROM Stude ntWHERE Ssex=女’AND X.S no=Y.S no);⑨求年龄大于所有女同学年龄的男同学姓名和年龄；SELECT Sn ame,SageFROM Stude nt XWHERE Ssex=男’AND Sage>( SELECT MAX(Sage) FROM Stude nt YWHERE Ssex=女’AND X.S no=Y.S no)；⑩检索所有比“王华”年龄大的学生姓名，年龄和性别；SELECT Sn ame,Sage,SsexFROM Stude nt XWHERE Sage>( SELECT SageFROM Stude nt YWHERE Sn am6=王华'AND X.S no=Y.S no)；①检索选修“ 2”课程的学生中成绩最高的学生和学号；SELECT Sn ame,SC.S noFROM Stude nt,SCWHERE Cn o=2 AND Stude nt.S no=SC.S no;②检索学生姓名和其所选修课程的课程号和成绩；SELECT Sn ame,C no,GradeFROM Stude nt,SCWHERE Stude nt.S no=SC.S noGROUP BY Sn ame;③检索选修4门以上课程的学生总成绩(不统计不及格的课程) 列出来；，并要求按总成绩的降序排SELECT Sn o,SUM(Grade)FROM SC XWHERE Grade>=60 AND Sno IN( SELECT SnoFROM SC YWHERE X.S no=Y.S noGROUP BY SnoHAVING cou nt(C no )>4)' ORDER BY SUM(Grade) DESC;(3)设有表4-1~表4-4的4个基本表(表结构于表内容是假设的) 据表内容创建表结构，并添加表记录，写出实现以下各题功能的创建以下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,TNO CHAT(3),FOREIGN KEY TNO REFERENCES TEACHER(TNO))；CREATE TABLE SC(SNO CHAR(6),CNO CHAR(4),GRADE SMALLINT,，请先创建数据库及根SQL语句：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之间的所有记录；SELECT *FROM SCWHERE CNO=8105' AND GRADE BETWEEN 80 AND 90;②查询成绩为79，89或99的记录；SELECT *FROM SCWHERE GRADE IN(79,89,99);③查询“ 9803”班的学生人数；SELECT coun t(SNO)FROM STUDENTWHERE SNO LIKE '9803% ;④查询至少有20名学生选修的并且课程号以8开头的课程及平均成绩；SELECT CNO,AVG(GRADE)FROM SCWHERE CNO LIKE ' 8%GROUP BY CNO;HAVING coun t(SNO)>=20;⑤查询最低分大于 80,最高分小于95的SNO W平均分；SELECT SNO,AVG(GRADE)FROM SCGROUP BY SNOHAVING MIN(GRADE)>80 AND MAX(GRADE)<95;⑥查询“ 9803”班的学生所选各课程的课程号及平均成绩；SELECT CNO,AVG(GRADE)FROM SCWHERE SNO LIKE ' 9803'GROUP BY CNO;⑦查询选修“ 8105”课程的成绩高于“ 980302”号同学成绩的所有同学的记录;SELECT *FROM SC XWHERE CNO=8105' AND GRATE>(SELECT GRATEFROM SC YWHERE CNO=8105' AND SNO= 980302' AND X.SNO=Y.SNO);⑧查询与学号为“ 980103”的同学同岁的所有学生的SNO,SNAM和 AGESELECT SNO,SNAME,AGEFROM STUDENT XWHERE AGE=(SELECT AGEFROM STUDENT YWHERE SNO=980103' AND X.SNO=Y.SNO)；⑨查询“钱军”教师任课的课程号，以及选修其课程学生的学号和成绩；SELECT O,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 OHAVING coun t(SNO)>20;11查询选修编号为“ 8105”课程且成绩至少高于其选修编号为“8245”课程成绩的同学的SNC及“ 8105”课程成绩，并按成绩从高到低依次排列；SELECT SNO GRADEFROM SC XWHERE CNO=81O5' AND GRADE>(SELECT GRADEFROM SC YWHERE CNO=8245' AND X.SNO=Y.SNO)ORDER BY GRADE DESC;12查询选修编号为“ 8105”课程且成绩高于所有选修编号为“ 8245”课程成绩的同学的 CNO SNO GRADESELECT CNO,SNO,GRADEFROM SCWHERE CNO=81O5' AND GRADE>(SELECT MAX(GRADE)FROM SCWHERE CNO=8245' AND O=Y.SNO)；13列出所有教师和同学的姓名，SEX,AGESELECT TNAME,TEACHER.SEX,TEACHER.AGE,SNAME,STUDENT.SEX,STUDENT.AGE FROM TEACHER,SC 14查询成绩比该课程平均成绩高的学生的成绩表；SELECT *FROM SC.XGROUP BY SNOHAVING GRADE〉(SELECT AVG(GRADE)FROM SC.YWHERE O=OGROUP BY CNO);15列出所有任课教师的 TNAME^ DEPTSELECT TNAMEQEPTFROM 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 coun t(SNO)>=4;18查询不姓“张”的学生记录；SELECT *FROM STUDENTWHERE SNAME NOT LIKE'张 % ;19查询每门课最高分的学生的SNO,CNO,GRADE;SELECT SNO,CNO,GRADEFROM SCGROUP BY CNOHAVING GRADE=MAX(GRADE);20查询与“李华”同性并同班的同学SNAMESELECT SNAMEFROM STUDENT XWHERE CLASS=(SELECT CLASSFROM STUDENT YWHERE SNAME李华’AND SEX=(SELSCT SEXFROM STUDENT ZWHERE SNAME^ 华’AND X.SNO=Y.SNO 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 O=O AND Cl数据库系统' AND SEX='男’；23查询所有比刘涛年龄大的教师姓名，年龄和刘涛的年龄；SELECT TNAME,AGEFROM TEACHER XWHERE SNAME=^^ 涛’OR AGE>(SELECT AGEFROM TEACHER YWHERE X.TNO=Y.TNO AND SNAME刘涛’)；24查询不讲授“ 8106”号课程的教师姓名。