2023年数据库实验报告4.docx

《2023年数据库实验报告4.docx》由会员分享，可在线阅读，更多相关《2023年数据库实验报告4.docx（11页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、实验内容与规定请有选择地实践以下各题。(1)基于“教学管理”数据库jxgl,使用SQL的查询语句表达下列查询：检索年龄大于2 3岁的男学生的学号和姓名；SE L E C T S n o, Sn a meFROM StudentWHERE Ssex=,男ANDS a ge 2 3;检索至少选修一门课程的女生姓名；SE L EC T Snam eFROM Stu dentWHERE Ssex=女AND Sno IN(SELECT SnoFROM SCGROUP BY SnoH A V ING c o unt (*)=1;)；检索王同学不学的课程的课程号；SELECT Cno;FR OM C o u

2、rseWHERE Cn o NOT IN(SELECT CnoFROM S tud e nt, SCW H E RE S n ame like 王 AND St u dent. Sn o =SC. Sno)；检索至少选修两门课程的学生学号;WHERE TEACHER. TNO=COURSE. TNO AND SC. C 0 N= C OURSE. C 0 NGROUP B Y SC. CNOHAV ING count (SNO ) 2 0 ；1 1查询选修编号为“ 8 105”课程且成绩至少高于其选修编号为“ 8 24 5”课程成绩的同学的SNO及“8 1 05”课程成绩,并按成绩从高到低依次

3、排列；SELE CT SNO G RADEFROM SC XWHERE CN0=, 810 5 AND GRADE(SELECT GRADEFROM SC YWHERE CN O = * 824 5 AND X. SNO=Y. SNO)ORDER BY GRADE DES C；1 2查询选修编号为“81 0 5”课程且成绩高于所有选修编号为“8 2 45”课程成绩的同学的CNO、SNO、GRADE；SELEC T CNO, SNO,GRADEFROM SCWHERE CNO 8105 AND GRADE(SELECT MAX (GRADE)FROM SCWHE R E CNO =8245 AN

4、D X.CN0= Y. SNO)； 1 3列出所有教师和同学的姓名，SEX, AGE；SELECT TN A ME, TEACHER. SEX.TI: A CHER. AG E , SNA M E, S T U DENT. SEX, ST UDE NT. AGFROM TEACHER, SC14查询成绩比该课程平均成绩高的学生的成绩表;S ELECT *FROM SC. XGROUP BY S NOHAVING GRADE)(0SELECT A VG(GRADE)FROM SC. YWHERE X.CN0=Y. CNOGROUP BY CNO)；15列出所有任课教师的TN A ME和DEPT；

5、SELECT TNAME, DEPTFROM TEACHER, COURSEWHERE TEACHER. TNO=COURSE. TNO16列出所有未讲课教师的TNAUE和DE PT;SELECT T NAME, DEPYFROM TEACHERWHERE NOT EXISTS(S E LECT 大F ROM COURS EWHE RE TEACHER. TNOCOURSE. TNO);17列出至少有4名男生的班号；SELECT CLASSFROM STUDENTGROUP BY CLAS SHAVING cou n t(SNO)=4;18查询不姓“张”的学生记录；SELECT *FROM S

6、TUDENTWHERE SN AME NOT LIKE 张;9查询每门课最高分的学生的SNO, CNO, GRADE；SELECT SNO, CNO, GRADEF ROM SCGROUP BY CNOHAVING GRADE=MAX (GRADE);0查询与“李华”同性并同班的同学SNAME ;S E L ECT SNAMEF ROM STUDEN T XWHERE CLASS=(SELECT CL ASSFROM STUDENT YWHERE SNAME= 李华AND S EX=(SELSCT SEXFROM ST UDENT ZWHERE SNAME=,李华AND X. SNO= Y.

7、SNO AND Y.SNO=Z.SNO);21查询“女”教师及其所上的课程；SELECT TNAME, C NO, CNAMEFROM TEACH ER,COURS EWHERE T E ACHER. TNO=COURSE. TNO, SEX=,女；2 2查询选修“数据库系统”课程的“男”同学的成绩表；SEL E C T *FROM SC, COURSE, S TUDENTWHERE STUDENT. SNO=SC. SNO AND COURSE. CNO=SC.CNO AND CNAME=数据 库系统AND SEX=男；2 3查询所有比刘涛年龄大的教师姓名，年龄和刘涛的年龄；SELECT T

8、NAME, AGEFROM TEACHER XWHE RE SNAME=,刘涛OR AGE (S ELECT AGEFROM TEACHER YWHERE X. TNO= Y. TNO ANDSNAME :刘涛)；2 4查询不讲授“810 6 ”号课程的教师姓名。SELECT TNAMEFROM TEACHER, COURSEWHERE TE ACHER.NO=COURSE.TNO AND CNO NOT I N (8106);S E LECT D I S T I NCT Sn oFROM SCGROUP B Y SnoHAV I NG coun t(*) =2;检索所有学生都选修的课程的课程

9、号与课程名；S E L ECT C no, C n ameFROM C o urseWHERE NOT EXISTS(SELECT *FROM StudentWHERE NOT EX I STS(SELECT *FROM SCWHERE SC. Sno=Stu d ent. Sno AND SC. C n o =Course. Cno)；检索选修了所有3学分课程的学生学号；SELECT DISTINCT SnoFROM SC XWHERE NOT EXISTS(SELECT *F ROM Cour s eWHERE Cc r ed i t=3 AND NOT EXISTS(SELECT *FR

10、OM SC YWHERE X. S n o =Y. Sno AND Cours e . C n o=Y. Cno);(2)基于“教学管理”数据库jxg 1 ,使用SQL的查询语句表达下列查询：记录有学生选修的课程门数；o SELECT count (DIS T I NCT Cno)FROM SC;求选修4号课程的学生的平均年龄；-SELECT AVG (Sage)FROM St u d en t , SCWHERE C n o= 4 AND St u den t . Sn o = S C. Sno;求学分为3的每门课程的学生平均成绩；SELECT AVG(Grade)FROM Cou r s

11、e , SCWHERE Ccredit=3 AND Course. Cno=SC. CnoGROUP BY SC. Cno ;记录每门课程的学生选修人数，规定超过3人的课程才记录，规定输出课程号和选修人数, 查询结果按人数降序排列,若人数相同,按课程号升序排列；S E L E CT C no, c ount (Sno)FROM SCGROUP B Y CnoHAVING c o un t (S n o) 30 RDER BY count (Sno) DESC, Cno ASC;检索学号比“王菲”同学大而年龄比他小的学生姓名；SELECT SnameFROM Student XWHER E Sn

12、o(SELECT SnoFROM S tu d en t YWHERE Sname=,王菲AND Sage(6ELE CT SageFROM Student ZWHERE S n ame=,王菲AN D X. Sn o = Z. S n o AND Y. Sn o =Z. Sn o );检索姓名以“王”打头的所有学生的姓名和年龄；SELECT Sn a me, S a geFROM St u dentWHERE Sna m e LIKE王;在S C中检索成绩为空置的学生学号和课程号；S ELECT Sno, C noFROM SCWHERE Grade is NULL；求年龄大于女同学平均年龄

13、的男学生姓名和年龄；S E L ECT Sname, S a geF ROM Student XWHERE Ssex=,男AND Sage(SELECT AVG ( S age)FROM StudentWHERE Ssex=,女AND X. Sno= Y.Sno)；求年龄大于所有女同学年龄的男同学姓名和年龄；SELECT Sname, Sag eFROM Student XWHERE Ssex=,男AND Sage(SELECT MAX (Sage)FROM Stu den t YWHERE S s ex=,女AND X. Sn o =Y. Sno)；检索所有比“王华”年龄大的学生姓名，年龄和

14、性别；SELE CT Sn a m e , S a ge, SsexFROM St u de n t XWHERE Sag e(oSELECT SageFROM Studen t YWHERE Sname=王华AND X. S no=Y. Sno);检索选修“2”课程的学生中成绩最高的学生和学号；SELECT S n ame, SC. S noFR 0 M Stud ent, SCWHE RE Cno=2 AN D S tudent. S n o= S C. S n o;检索学生姓名和其所选修课程的课程号和成绩；SEL E CT S name, C n o , G r a deFROM S t

15、 uden t , SCWHERE Studen t . S no=SC. SnoGROUP BY Snamc;检索选修4门以上课程的学生总成绩(不记录不及格的课程)，并规定按总成绩的降序排列 出来；SELECT S no, SUM (Gr a d e)FROM SC XWHERE Grade=60 AND S no IN(o SELECT SnoFROM SC YWH ERE X. Sno=Y. SnoG ROUP BY SnoHAVING count(Cno)4)ORDER BY SUM (Grade) DESC；(3)设有表4-1表4-4的4个基本表(表结构于表内容是假设的)，请先创建数

16、据库及根据表内容创建表结构,并添加表记录，写出实现以下各题功能的SQL语句：创建以下4各表：CREATE TABL E STUDENT(SNO CH/R(6) PRIMARY K EY,S NAME CHAR(20) UNIQUE,SEX CHAR (2),AGE SMALL INT,CLAS S CHAR(4)；CREATE T A B LE TEACHER(TNO CHAR (3) PRIMARY KE T,TNAMW CHAR(20) UNIQU E,SEX CHAR (2),AGE SMALLINT,PROF CH AR( 1 0),DEPT CHAR(10) )；CREATE TAB

17、LE COURSE(CNO CHAR(4) PRIMARY KEY,CNAME CHAR(20) UNIQUE,TNO CHAT(3),FOREIGN KEY TNO REFER ENCES TEAC HER(TNO) )； CREATE TABLE SC (SNO CHAR(6),CNO CHAR(4),GRADE SMALLINT,PRIMARY KEY(SNO, CNO),FOREIGN KEY SNO REFERENCE S S TUDENT(S NO),FO REIGN KEY CNO REFERENCES COURSE(CNO) )；插入数据：I NSERT INTO STU DE

18、NTVALUES ( 980 1 0 1李华，男，1 9, 9 801);同上方法依次插入数据杳询选修课程“81 0 5”且成绩在80到90之间的所有记录;SELECT *FROM SCWHERE CN0=, 8 105 AND GRADE BETWEEN 80 AND 90;查询成绩为79, 89或99的记录；S ELECT *FROM SCWHERE GRADE IN(79, 89,99);查询“9 8 03”班的学生人数；SELE C T count(SNO)F R OM STUDENTWHERE SNO LIKE 9 8 0 3%;查询至少有20名学生选修的并且课程号以8开头的课程及平

19、均成绩;SELECT CNO, A VG(GRADE)FROM SCWHERE CNO L I KE 8%GROUP BY CNO;HAVING count (SNO) =20;查询最低分大于8 0 ,最高分小于9 5的SNO与平均分；SELECT SNO, AVG(GRADE)FROM S CGROUP BY SNOHAVING MIN (GRADE) 80 AND M AX(G R ADE) (SELECT G RATEFROM S C YWHERE CN0= 8105 AND SN()= 980302* AND X.SN O=Y. SNO);查询与学号为“9 8 0103”的同学同岁的所

20、有学生的SNO, SNA ME ft AGE；SELECT SNO, SNAME, AGEFROM STUDENT XWHERE AGE=(SELECT AGEFROM STUDENT YWHERE SN0= 980 1 03 AN D X. SNO=Y. SNO)；查询“钱军”教师任课的课程号，以及选修其课程学生的学号和成绩；SELECT S C.CNO, SNO, GRADEFROM SC, TEACIIRT, C OU RSEWHERE TNAME=钱军A NDTEACHER. TNOCOURSE. TN0 AND SC. CON;COURSE. CON；查询选修某课程的学生人数多于20人的教师姓名；SELECT DISTINCT TNAMEFROM TEACHER, SC, COURSE