数据库试验报告

1、.WORD完美格式.实验内容与要求请有选择地实践以下各题。(1) 基于“教学管理”数据库 jxgl，使用SQL的查询语句表达下列查询: 检索年龄大于23岁的男学生的学号和姓名；SELECT Sn o,S nameFROM Stude ntWHERE Ssex=男ANDSage23; 检索至少选修一门课程的女生姓名；SELECT Sn ameFROM Stude ntWHERE Ssex=女AND Sno IN( SELECT SnoFROM SCGROUP BY SnoHAVING cou nt(*)=1;); 检索王同学不学的课程的课程号；SELECT Cno;FROM CourseWHER

2、E Cno NOT IN( SELECT CnoFROM Stude nt,SC WHERE 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

3、 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.Sno=YSno AND Course.Cno=Y .Cno)；(2) 基于“教学管理”数据库 jxgl，使用SQL的查询语句表达下列查询： 统计有学生选修的课程门数；SELECT coun t(DISTINCT Cno)FROM SC; 求选修4

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 Cno

5、HAVING 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 Sname=王菲AND Sage ( SELECT SageFROM Stude nt ZWHERE Sname=王菲AND X.Sno=Z.Sno AND Y.Sno=Z.Sno ); 检索姓名以“王”打头的所有学生的姓名和年龄；SELECT Sn ame,SageFROM Stude ntW

6、HERE 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( S

7、ELECT 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 Sname=王华AND X.Sno=YSno)； 检索选修“ 2”课程的学生中成绩最高的学生和学号；SELECT Sn ame,SC.S noFROM Stude nt,SCWHERE Cn o=2 AND Stude nt.S no=SC.S no; 检索学生姓

8、名和其所选修课程的课程号和成绩；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=YS noGROUP BY SnoHAVING cou nt(C no )4) ORDER BY SUM(Grade) DESC;，

9、请先创建数据库及根SQL语句：(3) 设有表4-1表4-4的4个基本表(表结构于表内容是假设的) 据表内容创建表结构，并添加表记录，写出实现以下各题功能的 创建以下4各表：CREATE TABLE STUDENT(SNO CHAR(6) PRIMARY KEYSNAME CHAR(20) UNIQUE,SEX CHAR(2),AGE SMALLINTCLASS CHAR(4)；CREATE TABLE TEACHER(TNO CHAR(3) PRIMARY KETTNAMW CHAR(20) UNIQUE,SEX CHAR(2),AGE SMALLINTPROF CHAR(10),DEPT C

10、HAR(10)；CREATE TABLE COURSE(CNO CHAR(4) PRIMARY KEYCNAME CHAR(20) UNIQUE,TNO CHAT(3),FOREIGN KEY TNO REFERENCES TEACHER(TNO)；CREATE TABLE SC(SNO CHAR(6),CNO CHAR(4),GRADE SMALLINTPRIMARY KEY(SNO,CNO),FOREIGN KEY SNO REFERENCES STUDENT(SNO),FOREIGN KEY CNO REFERENCES COURSE(CNO) ）；插入数据：INSERT INTO ST

11、UDENTVALUES(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 C

12、NO,AVG(GRADE)FROM SCWHERE CNO LIKE 8%GROUP BY CNO;HAVING coun t(SNO)=20; 查询最低分大于 80，最高分小于95的SNO与平均分；SELECT SNO,AVG(GRADE)FROM SCGROUP BY SNOHAVING MIN(GRADE)80 AND MAX(GRADE)(SELECT GRATEFROM SC YWHERE CNO=8105 AND SNO=980302 AND X.SNO=YSNO); 查询与学号为“ 980103”的同学同岁的所有学生的SNO,SNAME和AGE；SELECT SNO,SNAME,

13、AGEFROM STUDENT XWHERE AGE=(SELECT AGEFROM STUDENT YWHERE SNO=980103 AND X.SNO=YSNO)； 查询“钱军”教师任课的课程号，以及选修其课程学生的学号和成绩；SELECT SC.CNO,SNO,GRADEFROM SC,TEACHRTCOURSEWHERE TNAME=钱军AND TEACHER.TNO=COURSE.TNO AND SC.CON=COURSE.CON; 查询选修某课程的学生人数多于20人的教师姓名；SELECT DISTINCT TNAMEFROM TEACHER,SC,COURSEWHERE TEA

14、CHER.TNO=COURSE.TNO AND SC.CON=COURSE.CONGROUP BY SC.CNOHAVING coun t(SNO)20;11查询选修编号为“ 8105 ”课程且成绩至少高于其选修编号为“8245 ”课程成绩的同学的SNO及“ 8105 ”课程成绩，并按成绩从高到低依次排列；SELECT SNO GRADEFROM SC XWHERE CNO=81O5 AND GRADE(SELECT GRADEFROM SC YWHERE CNO=8245 AND X.SNO=YSNO)ORDER BY GRADE DESC;12查询选修编号为“ 8105”课程且成绩高于所有

15、选修编号为“8245”课程成绩的同学的 CNO、SNO GRADE;SELECT CNO,SNO,GRADEFROM SCWHERE CNO=81O5 AND GRADE(SELECT MAX(GRADE)FROM SCWHERE CNO=8245 AND X.CNO=YSNO)；13列出所有教师和同学的姓名，SEX,AGE;SELECT TNAME,TEACHER.SEX,TEACHER.AGE,SNAME,STUDENEEX,STUDENTAGE FROM TEACHER,SC14查询成绩比该课程平均成绩高的学生的成绩表；SELECT *FROM SC.XGROUP BY SNOHAVIN

16、G GRADE(SELECT AVG(GRADE)FROM SC.YWHERE X.CNO=YCNOGROUP BY CNO);15列出所有任课教师的 TNAME和DEPT;SELECT 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

17、 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查询与“李华”同性并同班的同学SNAME；SELECT SNAMEFROM STUDENT XWHERE CLASS=(SELECT CLASSFROM STUDENT YWHERE SNAME=李华AND SEX=(SELSCT SEXFROM STUDENT ZWHERE SNAME=李华AND X.SNO=YSNO AND YSNO=Z.SNO)；21查询“女”教师及其所上的课程；SELECT TNAME,CNO,CNAMEFROM TEACHER,COURSEWHERE TEACHER.TNO=COURSE.TNO,SEX=女；22查询选修“数据库系统”课程的“男”同学的成绩表；SELECT *FROM SC,COURSE,STUDENTWHERE STUDENTSNO=SC.SNO AND COURSE.